Standard deviation of a subset in VBA

[p45cal]

First, nothing wrong with cross posting as long as you share where you've cross posted to. Have a read of https://excelguru.ca/a-message-to-forum-cross-posters/ to understand why, and more importantly, to ensure you continue to get responses.

You said: "I have checked that my subsets contains only numerical values"
Actually, they're all strings.

Try:

Function FITVol(rng As Range) As Double
    Dim subsets(1 To 5) As Variant
    Dim subsetStDev(1 To 5) As Double
    Dim sumStDev As Double, idx
    Dim lastRow As Long, i As Long, j As Long
    ' Find the last row with data in the range
    lastRow = rng.Rows.Count
    Debug.Print "Last Row: " & lastRow
    ' Loop through the data, calculate subsets, and store the natural log of the ratio in subsets array
    For i = 1 To 5
        ReDim a(1 To 1)
        idx = 0
        For j = i + 5 To lastRow Step 5
            idx = idx + 1
            ReDim Preserve a(1 To idx)
            a(idx) = WorksheetFunction.Ln(rng.Cells(j, 1).Value / rng.Cells(j - 5, 1).Value)
       Next j
       subsets(i) = a
       ' Calculate standard deviation for each subset and store in subsetStDev array
       subsetStDev(i) = Application.StDev(subsets(i))
       ' Output standard deviation to Immediate Window for debugging
       Debug.Print "Subset " & i & " Standard Deviation: " & subsetStDev(i)
    Next i
    ' Calculate the average of standard deviations
    FITVol = Application.Average(subsetStDev)
    Debug.Print "Average of Standard Deviations: " & Application.Average(subsetStDev)
End Function

Bear in mind what Aussiebear said, and perhaps choose something different for Application.StDev(subsets(i)). You have choices:

2024-04-18_231701.jpg

You can see their equivalents and explanations if you start entering the stdev function in a cell:

2024-04-18_231851.jpg

Later, when you've debugged it fully, you'll be able to do away with the intervening subsets(1 To 5) array:

Function FITVol(rng As Range) As Double
    Dim subsetStDev(1 To 5) As Double
    Dim lastRow As Long, i As Long, j As Long, idx
    lastRow = rng.Rows.Count
    For i = 1 To 5
        ReDim a(1 To 1)
        idx = 0
        For j = i + 5 To lastRow Step 5
            idx = idx + 1
            ReDim Preserve a(1 To idx)
            a(idx) = WorksheetFunction.Ln(rng.Cells(j, 1).Value / rng.Cells(j - 5, 1).Value)
        Next j
        subsetStDev(i) = Application.StDev(a)
    Next i
    FITVol = Application.Average(subsetStDev)
End Function