1

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

Community
  • 1
  • 1
Jack
  • 167
  • 2
  • 12

1 Answers1

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) & ")"
trincot
  • 317,000
  • 35
  • 244
  • 286
  • Thanks you trincot. I got rid of the error in cell but the formula is giving zero value which is not right. Do you think the function is written correctly? – Jack Mar 19 '16 at 11:10
  • I thinki found I have my data on another sheet so in this case the function is not evaluating. Do you know whether I can ignore the sheet name when function evaluating? – Jack Mar 19 '16 at 11:14
  • 1
    I added some suggested code to deal with ranges coming from another sheet. – trincot Mar 19 '16 at 11:39