How to code a worksheet expression in vba?

[JenniferMurphy]

I am working on a fairly simple UDF to calculate weighted ratings. The expression in the worksheet is:

=SUM(Weights*(Ratings<>0))

where both Weights and Ratings are ranges.

This vba code works:

Public Function WtdRtgS(prRatings As Range, prWeights As Range) As Double
Dim SumWts As Double
SumWts = Application.WorksheetFunction.Sum(prWeights)

but this gets a Value error:

SumWts = Application.WorksheetFunction.Sum(prWeights * (prWeights <> 0))

What do I need to do to be able to do in the UDF the calculation that works in the sheet?

Thanks

 

[Marcelo Branco]

Maybe...

SumWts = Evaluate("=SUM(" & prWeights.Address & "*(" & prRatings.Address & "<> 0))")

M.

 

[JenniferMurphy]

Thanks, Marcelo. That works.

So the rule is that I can execute worksheet functions by themselves, but if they are part of an expression, I need to assemble it as a text string and then pass that to the Evaluate function. Right?

 

[Marcelo Branco]

Yes, array formulas require Evaluate that uses a formula string.

M.

 

[RoryA]

You can't compare arrays and values or use simple operators on arrays directly in VBA. So the issue in your original code was both the prWeights <> 0 part and the attempt to multiply that by another range/array.

 

[JenniferMurphy]

You can't compare arrays and values or use simple operators on arrays directly in VBA. So the issue in your original code was both the prWeights <> 0 part and the attempt to multiply that by another range/array.

Got it, thanks.

This limitation seems like another example of a stunningly mediocre product from M$FT. And they have had a few years to work on it.

 

[RoryA]

I have no doubt it does.