Using a variable inside a worksheetfunction

[Jusrober]

I am trying to pass a variable into a function and it will not accept it. What is the syntax that I am missing to get it to use the variable in the worksheet function. Thanks in advanced I have been banging my head against for countless hours!

Dim VARADD As String
Dim VARSUM As String
X = 0
Do Until Cells(3, 5 + X) = "~~"
    If Cells(2, 5 + X).Value > Cells(3, 2) Then
    
        VARADD = Cells(3, 5 + X).Value
        VARSUM = VARSUM & VARADD
        If Cells(3, 6 + X) = "~~" Then
        'nothing
        Else
        VARSUM = VARSUM & " , "
        End If
        Cells(1, 1).Value = VARSUM
        Else
        'nothing
    End If
    X = X + 1
Loop
'Cells(1, 2).Value = "=Sum(" & VARSUM & ")"
Cells(1, 4) = WorksheetFunction.Sum(1, 2, 3, 4, 5)
Cells(1, 2) = WorksheetFunction.Sum(VARSUM)'''<-- fails here (run 1004) This is what VARSUM equals at this point "26 , 6 , 29 , 4034 , 102 , 39 , 76 , 28 , 10 , 12 , 13 , 11 , 258"

 

[Jonmo1]

Perhaps then

Cells(1, 2).Value = "=Stdev.s(" & VARSUM & ")"

I had considered using that work around, but I really wanted to use only code.

That IS code.

Or do you mean more like
Cells(1, 2).Value = Application.Sum(VARSUM)

Well those are both virtually the same, They both call a function from the Excel Application, just like a cell formula.
The calculation is NOT done by VBA.
The only real difference is one puts it in the cell as a Formula, the other puts it in as a hard coded value.

If that's the difference you want, try using Evaluate

Cells(1, 2).Value = Evaluate("=Stdev.s(" & VARSUM & ")")

 

[Jusrober]

Thanks for clearing that up!

here is the code I went with.

Sub STDAuto()
Dim STRDEV As Double
Dim STRMU As Double
Dim ADJDEV As Double
Dim ADJMU As Double
Dim EXCEPT As Double
Dim arr1() As Variant
Dim arr2() As Variant
Dim num As Integer
Dim X As Integer
num = -1
X = 0
Do Until Cells(3 + Y, 1) = ""
    Do Until Cells(3 + Y, 5 + X) = "~~"
        If Cells(2, 5 + X).Value > Cells(3 + Y, 2) Then
        num = num + 1
                ReDim Preserve arr1(num)
                arr1(num) = Cells(3 + Y, 5 + X).Value
        End If
        X = X + 1
    Loop
STRDEV = WorksheetFunction.StDev_S(arr1)
STRMU = WorksheetFunction.Average(arr1)
EXCEPT = (STRDEV * 2) + STRMU
num = -1
X = 0
    Do Until Cells(3 + Y, 5 + X) = "~~"
        If Cells(2, 5 + X).Value > Cells(3 + Y, 2) Then
            If Cells(3 + Y, 5 + X).Value > EXCEPT Then
          'nothing
            Else
                num = num + 1
                    ReDim Preserve arr2(num)
                    arr2(num) = Cells(3 + Y, 5 + X).Value
            End If
        Else
        'nothing
        End If
        X = X + 1
    Loop
ADJDEV = WorksheetFunction.StDev_S(arr2)
ADJMU = WorksheetFunction.Average(arr2)
Cells(3 + Y, 26).Value = ADJDEV + ADJMU
Cells(3 + Y, 27).Value = EXCEPT
Y = Y + 1
X = 0
num = 0
EXCEPT = 0
STRDEV = 0
STRMU = 0
ADJSTD = 0
ADJMU = 0
Loop
Y = 0
End Sub