需求：用VBA循环计算动态长度波动率列的Zscore并填充单元格

First, let's fix the code for column B to compute Z-scores for every row instead of just the last one. We'll avoid using Select (which is inefficient and error-prone) and precompute the average and standard deviation once for the entire column (since those values are the same for all rows in the column).

Modified Code for Column B #

Private Sub CalculateZscoresForColumnB()
    Dim sht As Worksheet
    Dim lastRow As Long
    Dim volRange As Range
    Dim avgVol As Double
    Dim stdevVol As Double
    Dim i As Long
    
    ' Set reference to your worksheet
    Set sht = Worksheets("2Y Data Import")
    
    ' Find the last row with data in column B (starting from B7)
    lastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
    If lastRow < 7 Then Exit Sub ' Exit if no data exists below B7
    
    ' Define the full range of volatility values
    Set volRange = sht.Range("B7:B" & lastRow)
    
    ' Calculate average and standard deviation once for the column
    avgVol = Application.WorksheetFunction.Average(volRange)
    stdevVol = Application.WorksheetFunction.StDev_S(volRange)
    
    ' Loop through each row to compute Z-score
    For i = 7 To lastRow
        ' Skip empty cells to avoid errors
        If Not IsEmpty(sht.Cells(i, "B").Value) Then
            ' Calculate Z-score and write to column C
            sht.Cells(i, "C").Value = (sht.Cells(i, "B").Value - avgVol) / stdevVol
        End If
    Next i
End Sub

Key Improvements: #

• No more Select/Selection calls—this makes the code faster and more reliable.
• Computes average and standard deviation only once per column (instead of per row), which saves a lot of processing time for large datasets.
• Includes a check to exit early if there's no data to process.
• Skips empty cells to prevent invalid calculations.

Extending to All 294 Columns (B to KI) #

To expand this to your full set of columns (B to KI, outputting to KJ to VQ), we'll add an outer loop to iterate through each volatility column. We'll also handle edge cases like zero standard deviation (to avoid division by zero errors).

Private Sub CalculateZscoresForAllColumns()
    Dim sht As Worksheet
    Dim lastRow As Long
    Dim startCol As Long, endCol As Long
    Dim outputStartCol As Long
    Dim avgVol As Double
    Dim stdevVol As Double
    Dim col As Long, i As Long
    
    ' Set reference to your worksheet
    Set sht = Worksheets("2Y Data Import")
    
    ' Define the range of volatility columns (B to KI)
    startCol = sht.Columns("B").Column
    endCol = sht.Columns("KI").Column
    
    ' Output columns start right after KI (KJ)
    outputStartCol = endCol + 1
    
    ' Find the last row with data in the first volatility column (B)
    lastRow = sht.Cells(sht.Rows.Count, startCol).End(xlUp).Row
    If lastRow < 7 Then Exit Sub ' Exit if no data exists
    
    ' Loop through each volatility column
    For col = startCol To endCol
        ' Define the range for the current column's data
        Dim volRange As Range
        Set volRange = sht.Range(sht.Cells(7, col), sht.Cells(lastRow, col))
        
        ' Calculate column-wide average and standard deviation
        avgVol = Application.WorksheetFunction.Average(volRange)
        stdevVol = Application.WorksheetFunction.StDev_S(volRange)
        
        ' Handle zero standard deviation (avoid division by zero)
        If stdevVol = 0 Then
            ' Set all Z-scores in the output column to 0
            sht.Range(sht.Cells(7, outputStartCol + col - startCol), _
                      sht.Cells(lastRow, outputStartCol + col - startCol)).Value = 0
            GoTo NextColumn ' Skip to next column
        End If
        
        ' Loop through each row to compute Z-score
        For i = 7 To lastRow
            If Not IsEmpty(sht.Cells(i, col).Value) Then
                ' Map to the corresponding output column
                sht.Cells(i, outputStartCol + col - startCol).Value = _
                    (sht.Cells(i, col).Value - avgVol) / stdevVol
            End If
        Next i
        
NextColumn:
    Next col
End Sub

Notes for the Full Column Version: #

• The code automatically maps each volatility column (B, C, ..., KI) to its corresponding output column (KJ, KK, ..., VQ).
• If a column has zero standard deviation (all values are the same), it sets all Z-scores in that output column to 0 instead of throwing an error.
• This code is optimized for performance, as it only calculates average and standard deviation once per column.

Using Your Custom Zscore Function (Optional) #

If you prefer to use your existing Zscore function, you can modify the inner loop to call it directly. Note that this will recalculate the average and standard deviation for every row (which is less efficient for large datasets), but it's a valid option:

' Inside the inner loop of the full column code:
sht.Cells(i, outputStartCol + col - startCol).Value = Zscore(sht.Cells(i, col).Value, volRange)

Just make sure your custom function is accessible (in a standard module, not a worksheet module) if you're calling it from this subroutine.

内容的提问来源于stack exchange，提问作者user14360433