Weighted average rate in VBA

Question

I am quite new to VBA so I am sorry if my question might seems very trivial. I would love to write a function in VBA which helps to estimate weighted average rate (for loan portfolio, for instance). I wrote the following VBA code:

Function WAIRS(Amount As Range, InterestRate As Range, MatchRange As Range, Match1)
WAIRS = Evaluate("=SUMPRODUCT(--(""" & MatchRange & """ = """ & Match1 & """),""" & Amount & """, """ & InterestRate & """)")      /Application.WorksheetFunction.SumIfs(Amount, MatchRange, Match1)
End Function

The problem is that when I run this function in Excel by adding respective function criterias I get an "#VALUE#". I have tried a lot but cannot find out what is wrong. I Would highly appreciate if you can help me.

Thank you in advance.

Best, Jeyhun

Answer 1

The string you build for Evaluate should (in this case) not include literal double quotes. Instead of quoting the result of a range value

"""" & MatchRange & """"

...you should retrieve the address notation of that range, and use that without wrapping it in quotes:

MatchRange.Address()

If you apply that consistently, it would make the Evaluate part of the formula look like this:

"=SUMPRODUCT(--(" & MatchRange.Address() & " = " & Match1.Address() & "), " & _
                    Amount.Address() & ", " & InterestRate.Address() & ")" 

When range is another sheet:

The above will not work if your ranges are on another sheet. In that case, I would suggest to create this function:

Public Function fullAddr(range As Range)
    fullAddr = "'" & range.Parent.Name & "'!" & _
                  range.Address(External:=False) 
End Function

And then in your formula:

"=SUMPRODUCT(--(" & fullAddr(MatchRange) & " = " & fullAddr(Match1) & "), " & _
                    fullAddr(Amount) & ", " & fullAddr(InterestRate) & ")"