MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Worksheet functions on arrays in VBA


Posted by Mason on August 10, 2001 11:07 AM


Posted by mason on August 10, 2001 11:10 AM

I have a large array, 5700 rows by 9 columns. My goal is to step through the array one row at a time, and calculate the standard deviation for the previous 20 elements of column 8. I can use the standard deviation function but it requires a range arguement, and I can't figure out how to reference only a portion of the array as the range. Any help would be deeply appreciated. Thanks in advance.

Posted by Jerid on August 10, 2001 1:28 PM

Mason, this is kind of clunky, and I'm not complely sure it's what your looking for but maybe it will help.

Sub test()
Dim myarray(1000, 9) As Double
Dim iX As Integer
Dim iY As Integer
Dim dResult As Double


'Fill Array with test data
For iX = 0 To UBound(myarray) - 1
For iY = 0 To 9 - 1
myarray(iX, iY) = iX + iY + 100
Next iY
Next iX

'Calc Standard Deviation using the Worksheet Function StDev
For iX = 10 To UBound(myarray) - 1 Step 10
dResult = Application.WorksheetFunction.StDev(myarray(iX, 8), myarray(iX - 1, 8), myarray(iX - 2, 8), myarray(iX - 3, 8), myarray(iX - 4, 8), myarray(iX - 5, 8), myarray(iX - 6, 8), myarray(iX - 7, 8), myarray(iX - 8, 8), myarray(iX - 9, 8), myarray(iX - 10, 8))
Next iX
End Sub

Jerid