How to Calculate the Monthly Volatility in Excel VBA Under Column Time Series

Question

'H' Column is the year data column, 'I' Column is the month data column, 'D' Column is the 10-year bond yield we want to calculate the monthly volatility.This is another improved code for this question, please help me. The return is still #NAME. Thanks the answer from @Comintern. According to @Comintern's advice, I've revised the code. In the name manager, 'Yr' refers to the column of the year (H3: H3696), 'M' refers to the column of the month (I3: I3696), 'C_10' refers to the original yield data of China 10 Year Treasury Bond.

Right now, I want to get the monthly volatility of the yield.

Function Volatility(n As Variant) As Variant
'this function uses to calculate volatility of a bond yield
'"n" is the number of data/date we need to calculate
'please manage the data name in the name manager of formulas
Dim i As Integer, dnum As Integer, mnum As Integer, vectornum As Integer
'dnum count day number, mnum count month number
Dim Result(), TempSave() As Variant
Dim Yr, M As Range
vectornum = Int(n / 20) + 1
ReDim Result(vectornum) As Variant

Yr = ActiveWorkbook.Names("Yr").Value
M = ActiveWorkbook.Names("M").Value
Bond = ActiveWorkbook.Names("C_10").Value

For i = 1 To n
    If Yr(i) = Yr(i + 1) And M(i) = M(i + 1) Then
        dnum = dnum + 1
        ReDim Preserve TempSave(1 To dnum)
        TempSave(dnum) = Bond(i)
        'this is the temporary data container for the same month bond yield
    Else
        TempSave(dnum + 1) = Bond(i)
        'because there is a gap between two month, so, we add the last 'same month bond yield' back
        dnum = 0
        mnum = mnum + 1
        Result(mnum) = Application.WorksheetFunction.StDev_S(TempSave)
    End If
Next i

Volatility = Result
End Function

Answer 1

This code has multiple issues. As for the #NAME error, you probably just need to move the function into a module. I'd also suggest making it explicitly public:

Public Function Volatility(n As Variant) As Variant

I'd also remove Option Base 0 - it's the default base. The only arrays you're using are being generated by Excel (so they'll always be base 1) and TempSave, which you explicitly declare with a lower bound of 1 here:

ReDim Preserve TempSave(1 To dnum)

I'd rename your Year and Month variables. Both are the names of VBA functions, and your local variables are hiding them.

This code...

dnum = 0
mnum = 0

...doesn't do anything - variables are always initialized to their defaults. Although in this case, the default is Empty (which will implicitly cast to 0), because you (likely accidentally) declare them as Variant here:

Dim i, j, dnum, mnum, vectornum As Integer

If they're all supposed to be Integer, you need to declare that explicitly:

Dim i As Integer, j As Integer, dnum As Integer, mnum As Integer, vectornum As Integer

You can remove Step 1 from your loop count - it's the default, but it also needs to start with 1, not 0 here (again, Excel's arrays are base 1):

For i = 0 To n Step 1

This line...

If Year(i) = Year(i + 1) And Month(i) = Month(i + 1) Then

...will give a "Subscript out of range" error, because if Range.Value refers to more than one cell, you'll get a two dimensional array. That means you need to provide both indexes. I'm assuming that these are all in the same column, so it should be:

If Year(I, 1) = Year(i + 1, 1) And Month(I, 1) = Month(i + 1, 1) Then

You have the opposite problem here:

    ReDim Preserve TempSave(1 To dnum)
    TempSave(i, 1) = Bond(i)

You declare TempSave as a one dimensional array, but attempt to index it with 2 dimensions. Note that you can only ReDim Preserve the highest dimension, so if you need to create a column with a dynamic bound you'd need to either calculate the length from the source arrays or transpose it. Bond is also going to be a 2 dimensional array (assuming ActiveWorkbook.Names("C_10") is more than one cell).

If any of your ranges are 1 cell, these assignments will give you a type mismatch:

Year = ActiveWorkbook.Names("Year").Value
Month = ActiveWorkbook.Names("Month").Value
Bond = ActiveWorkbook.Names("C_10").Value

Finally, Volatility = Result probably isn't returning what it should, because you have it declared as:

ReDim Result(vectornum, 1) As Variant

When you return an array of Variant from a UDF, you'll only get the first value in the array - in this case Result(1, 1).