Средневзвешенная ставка в VBA

Question

Я довольно новичок в VBA, поэтому мне жаль, если мой вопрос может показаться очень тривиальным. Я хотел бы написать функцию в VBA, которая помогает оценить средневзвешенную ставку (например, для кредитного портфеля). Я написал следующий код VBA:

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

Проблема в том, что когда я запускаю эту функцию в Excel, добавляя соответствующие критерии функции, я получаю "#VALUE#". Я много пробовал, но не могу понять, что не так. Я был бы очень признателен, если бы вы могли мне помочь.

Заранее спасибо.

Лучший, Джейхун

Answer 1

Строка, для которой вы строите Evaluate не должен (в этом случае) включать буквальные двойные кавычки. Вместо цитирования результата значения диапазона

"""" & MatchRange & """"

... вы должны получить адресную запись этого диапазона и использовать ее, не заключая ее в кавычки:

MatchRange.Address()

Если вы применяете это последовательно, это сделало бы Evaluate часть формулы выглядит так:

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

Когда диапазон другой лист:

Выше не будет работать, если ваши диапазоны находятся на другом листе. В этом случае я бы предложил создать эту функцию:

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

И тогда в вашей формуле:

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