Search code examples
excelvbafunctionsumproduct

Using a variant of the SUMPRODUCT formula in VBA

I want to use following type of SUMPRODUCT formula in VBA:

=SUMPRODUCT(A1:A2,C1/B1:B2)

It works fine in excel, but when using it as a VBA function it gives an #VALUE! result:

Function Test(LineA As Range, LineB As Range, ValueC As Double)

Test = Application.WorksheetFunction.SumProduct(LineA, ValueC / LineB)

End Function

How can I write that formula with its values using vba?

Solution

  • Try using the Evaluate method...

    Function Test(LineA As Range, LineB As Range, ValueC As Double)

    Test = Evaluate("SUMPRODUCT(" & LineA.Address(external:=True) & "," & LineB.Address(external:=True) & "/" & ValueC)

End Function