CODEWITHSUNDEEP
excelvbafunctionsumproduct
Niels Bjørn Hansen
Niels Bjørn Hansen

Reputation: 53

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?

Upvotes: 0

Views: 615

Answers (3)

Daniel Baker
Daniel Baker

Reputation: 27

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 & ")")

Upvotes: -1

Domenic
Domenic

Reputation: 8104

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

Upvotes: 1

QHarr
QHarr

Reputation: 84465

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)")

Upvotes: 1

Related Questions