Using a variant of the SUMPRODUCT formula in VBA

Question

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?

Answer 1

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

Answer 2

Wrap it in evaluate

Converts a Microsoft Excel name to an object or a value.

Name... Variant .....A formula or the name of the object, using the naming convention of Microsoft Excel. The length of the name must be less than or equal to 255 characters.

Application.Evaluate("=SUMPRODUCT(A1:A2,C1/B1:B2)")

Answer 3

Very similar to the above - but just in case it helps anyone, here is what I did with reference to sheet names and variable column lengths

Set a = Sheets("TheSheetName").Range([M10], [M10000].End(xlUp)) Set b = Sheets("TheSheetName").Range([L10], [L10000].End(xlUp))

c = Application.Evaluate("=SUMPRODUCT(" & a.Address & ",1/" & b.Address & ")")