In VBA for Excel, is it possible to run worksheet functions on arrays rather than cell ranges?-CodePudding

For example, instead of finding standard deviation for the range of cells in the example below, is it possible to apply this same function to the elements of an array?

I could copy all the array elements to a working sheet, but hoping there's a cleaner solution.

Set myRange = ActiveSheet.Range("h3:bm3")
answer = Application.WorksheetFunction.StDev(myRange)

CodePudding user response：

Yes, StDev can be used with arrays.

1-D array:

Dim x As Variant
x = Array(1, 2, 3, 4)
    
Debug.Print WorksheetFunction.StDev(x) ' returns 1.29099444873581

2-D array:

Dim x As Long
ReDim x(1 To 10, 1 To 10)
    
Dim i As Long, j As Long
For i = 1 To 10
    For j = 1 To 10
        x(i, j) = i
    Next
Next
    
Debug.Print WorksheetFunction.StDev(x) ' returns 2.88675134594813

CodePudding user response：

Arguments of StDev can either be numbers, arrays, or references that contain numbers.

From this help infotmation, it should be possible to apply this same function to the elements of an array.