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"

 

[JoeMo]

VARSUM is a string. Excel's worksheet function SUM is looking for numbers. Here's an example of one way to do what you want that you can adapt to your code.

Sub test()
Dim VARSUM As String, Ar As Variant, S As Double
VARSUM = "1,2,3,4"
Ar = Split(VARSUM, ",")
For i = LBound(Ar) To UBound(Ar)
    S = S + Ar(i)
Next i
Cells(1, 2).Value = S
End Sub

 

[V_Malkoti]

Sum accepts numbers or an array of numbers. Code modified to get values in array:

Sub SumMethod()
    Dim arr() As Variant
    Dim num As Integer: num = -1
    Dim X As Integer: X = 0
  
    Do Until Cells(3, 5 + X) = "~~"
        If Cells(2, 5 + X).Value > Cells(3, 2) Then
            num = num + 1
            ReDim Preserve arr(num)
            arr(num) = Cells(3, 5 + X).Value
        End If
        X = X + 1
    Loop
    Cells(1, 2) = WorksheetFunction.Sum(arr)
End Sub

 

[Jonmo1]

This version works for me

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

IF this is true

This is what VARSUM equals at this point "26 , 6 , 29 , 4034 , 102 , 39 , 76 , 28 , 10 , 12 , 13 , 11 , 258"

 

[Jusrober]

This version works for me


IF this is true

Yeah the top one works because it pieces it together then puts into excel for excel to activate the function.
I'm not sure what you are doing in your earlier response. I'm pretty new to VBA. That does work but I was only using sum as an easy test I need to do STDEV.S of the variable. Thanks for the help!

 

[Jonmo1]

That does work but I was only using sum as an easy test I need to do STDEV.S of the variable. Thanks for the help!

Perhaps then

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

 

[Jusrober]

I had considered using that work around, but I really wanted to use only code. If I must, I must. Thanks Again!

 

[V_Malkoti]

What about the array variable solution I posted? Did that not work?

 

[Jusrober]

What about the array variable solution I posted? Did that not work?

So sorry!! that one does work. Again I wasn't sure what you were doing. So getting it into my code was confusing. But it is in there and works!! Can you explain to me what it does to get the arr to work?

 

[V_Malkoti]

Like others have pointed out WorksheetFunction.Sum expects numbers to be passed as arguments. Since your code passed a string instead, it throws an error.

WorksheetFunction.Sum can also accept an array. When you pass an array, all numbers in the array will be used in addition. WorksheetFunction.Stdev_S works in same way.

The modified code iterates on the range just like the original one. But instead of appending values to a string, it stores them in a dynamically sized array. After the range iteration is over, this array is passed to Sum function in VBA.

References:
Sum : https://msdn.microsoft.com/en-us/library/office/ff838410.aspx
Stdev_S : https://msdn.microsoft.com/en-us/library/office/ff835583.aspx