VBA - struggling to calc and write StDev data into an array with a For Next loop

[msaldut]

I have made several attempts, this is the latest iteration. It shouldn't produce meaningful output until the minimum periods have been looped thru (volstperiod = 10).

--PctChg1() is an array which holds percent change data from i=2 to i = 2541... declared as variant
--volstperiod = 10 ...declared as integer
--i is a counter ...declared as integer
--VolST1() is an empty array which I hope to populate with annualized volatilities for a rolling x day period ...declared as variant
--Option Base 1 and Option Explicit are on

For i = 2 To 2541
If IsNumeric(i) And i <> 0 Then
VolST1(i, 1) = Application.WorksheetFunction.stdev(Range(PctChg1(i, 1).Offset(-volstperiod, 0), PctChg1(i, 0)))
Else
VolST1(i, 1) = 0
End If
Next i

Any guidance would be immensely appreciated. Thanks!

 

[msaldut]

Just to simplify, this is how I would express it in a worksheet formula...

=IF(ISNUMBER(OFFSET($E3,-volstperiod+1,0)),STDEV(OFFSET($E3,0,0,-volstperiod)),0)

...with "volstperiod" = 10 and column E holding 1 period %chg data (ie =$E3/$E2-1).

 

[msaldut]

might this be easier to do by creating a smaller array which could roll through the larger array? ...then any necessary calcs could be done on the entirety of the small array.

I cannot figure out how to isolate just a (rolling) subset of an array. The rolling subset could be used for moving averages, standard devs, max/min, etc.