VBA - Curly bracket {} equivalent operation

Question

In VBA for excel, I can simply use a formula as such as =SUM(X*{Y,Z}) to sum the multiplied results, however transferring this to VBA Is not a 1:1 as it does not support curly brackets for array purposes. Using a matching setup such as Result = Application.Worksheetfunction.Sum(X * Array(Y, Z)) results in a type mismatch error.

I do know I can simple use the evaluate function, or individually multiply and add after, very specifically seeing if there is a way to accomplish the above in a 1:1 matching method.

user_2040023 · Answer

WorksheetFunction.SumProduct can be used, requiring no additional function.

Debug.Print Application.WorksheetFunction.SumProduct(Array(y, z), Array(x, x))

Note that you just have to express your single factor as an array with the same shape as the target array.

user_6460297 · Answer

Approach using the MMULT() function

If you you want to multiply e.g. {10,1,30} with a single factor you'd have to repeat the factor in the 2nd argument within another array:

    Dim a = Array(10, 1, 30)
    b = WorksheetFunction.MMult(a, Application.Transpose(Array(1, 1, 1)))
    Debug.Print b(1)     ' 41

It would also be possible to define a separate array with corresponding individual factors for each base item:

    Dim a = Array(10, 1, 30)
    Dim b: b = WorksheetFunction.MMult(a, Application.Transpose(Array(1, 1.5, 1)))
    Debug.Print b(1)     ' 41.5

A flexibilized function comprising both versions (for any zero or 1-based arrays, filling up missing factors in version 2. with zero values by default) might be:

Function SumPro(a, factor, Optional missingFactor As Long = 0)
'Purp: sum products of array items multiplied by a) a single value or b) a set of factors in a 1-dim array
'Note: assumes "flat" 1-dim array(s)
'      fills up missing factors with zero values by default (missingFactor = 0)
    Dim n As Long: n = UBound(a) - LBound(a) + 1        ' items count independant from base start
    Dim b: ReDim b(1 To n, 1 To 1)                      ' provide for sufficient 1-based factor items
    Dim i As Long                                       ' counter
    If Not IsArray(factor) Then                         ' a) := single value
        For i = 1 To UBound(b): b(i, 1) = factor: Next  '       insert as many factors as array items
    Else                                                ' b) := already a factor array
        For i = LBound(factor) To UBound(factor)
            b(i - LBound(factor) + 1, 1) = factor(i)    '       collect existing factors
        Next
        For i = i - LBound(factor) + 1 To UBound(b)
            b(i, 1) = missingFactor                     '       complete missing factor items
        Next
    End If
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' calculate SumProduct (without Evaluate)
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Dim tmp: tmp = Application.MMult(a, b)              ' execute MMULT() function
' return function result
    SumPro = tmp(1)                                     ' return result
End Function

Example call

 Dim a: a = Array(10, 1, 30)     ' base values
 Dim b: b = Array(1, 1.15)       ' zero-based factors, third factor left free ~> changes to zero by default
 debug.print SumPro(a, b)        ' ~> 11.15 as it multiplies only first two items with 10*1 + 1*1.15 + 30*0 = 11.15

For a single factor it suffices to pass a number as 2nd argument:

 Debug.Print SumPro(a, 2)

user_13813219 · Answer

Alternatively to using .Product() or .SumProduct() you an write your own functions to do algebra on arrays.

Put the following in a module

Public Function FactorArray(ByVal factor As Double, ByRef a() As Variant) As Variant()
    Dim i_1 As Long, i_2 As Long, i As Long
    i_1 = LBound(a, 1)
    i_2 = UBound(a, 2)
    
    Dim result() As Variant
    ReDim result(i_1 To i_2)
    For i = i_1 To i_2
        result(i) = factor * a(i)
    Next i
    FactorArray = result
End Function

Public Function AddArrays(ByRef a() As Variant, ByRef b() As Variant) As Variant()
    Dim i_1 As Long, i_2 As Long, i As Long
    Dim j_1 As Long, j_2 As Long, n As Long
    i_1 = LBound(a, 1)
    i_2 = UBound(a, 2)
    j_1 = LBound(b, 1)
    j_2 = UBound(b, 2)
    
    n = WorksheetFunction.Min(i_2 - i_1 + 1, j_2 - j_1 + 1)
    
    Dim result() As Variant
    ReDim result(0 To n-1)
    For i = 0 To n-1
        result(i) = a(i + i_1) + b(i + i_2)
    Next i
    AddArrays = result
End Function

Public Function SubArrays(ByRef a() As Variant, ByRef b() As Variant) As Variant()
    SubArrays = AddArrays(a, FactorArray(-1, b))
End Function

Public Function DivArray(ByRef a() As Variant, ByVal divisor As Double) As Variant()
    DivArray = FactorArray(1 / divisor, a)
End Function

And then use it as

Dim arr as Variant, res as Variant
arr = Array(1,2,3,4)
res = FactorArray(X, arr)
WorksheetFunction.Sum(res)

Of course for your specific example you could factor out X and just do a scalar multiplication.

Dim arr as Variant, res as Variant
arr = Array(1,2,3,4)
res = X*WorksheetFunction.Sum(arr)

VBA - Curly bracket {} equivalent operation

3 Answers