Excel VBA оценивает строку из ячейки как формулу с переменной

Question

Я потратил часы на это и нашел связанные вопросы, но я не нашел ответ, который работает, поэтому я не верю, что это дубликат.

У меня есть строка в ячейке, которая представляет собой простую формулу переменной в нем. Эта переменная будет предопределена. Тогда мне нужно оценить строку. Я не могу получить значение ячейки для оценки. Сама строка вычисляется в непосредственном окне, но точно такая же строка значения ячейки не делает:

?myVar
 1 
?[M115].Value
"0.1*" & myVar & "*(0.2+0.3*" & myVar & "/.4)"
?Evaluate("0.1*" & myVar & "*(0.2+0.3*" & myVar & "/.4)")
 0.095 
?Evaluate([M115].Value)
Error 2029

Мое лучшее предположение в переменной не оценивается, так как я могу заменить myVar на 1, и это работает. Я перепробовал все способы тройного цитирования, и это тоже не работает. Как я могу выполнить эту, казалось бы, простую вещь? Я попытался обернуть в сабвуферы и функции, например, так:

Function ev(r As Range, ByVal myVar As Double) As Variant
    Debug.Print myVar
    Debug.Print r.value
    ev = Evaluate(r.value)
End Function

?ev([M115], 1)
 1 
 "0.1*" & myVar & "*(0.2+0.3*" & myVar & "/.4)"
Error 2029

myVar=1
?Evaluate("0.1*" & myVar & "*(0.2+0.3*" & myVar & "/.4)")
0.095 

Что я пропускаю? Благодарю.

Answer 1

Попробуйте это решение и вернитесь обратно, если оно работает для вас. Нажмите ALT + F11, чтобы открыть VBA Editor. Вставьте модуль и вставьте в него следующий UDF.

Public Function test(r As Range, myvar As Double) As Double
Z = r.Formula
Z = Replace(Z, "myvar", myvar)
test = Evaluate(Z)
End Function

Теперь ваше выражение в ячейках Excel не должно содержать никаких символов, которые не являются частью формулы, например & "и т.д. Оно должно содержать простую формулу со статическим именем и сохранять имя постоянным во всем и с учетом регистра.

например, myvar + 3 здесь myvar - просто статический текст, нигде не объявленный. Используйте функцию и передайте ячейку как диапазон, а значение переменной как удвоенное значение. Нет большого количества проверок на месте, передача многомерного массива может привести к ошибке здесь. Смотрите скриншот ниже. Попробуйте и проверьте, соответствует ли это вашему требованию.

Answer 2

У меня недостаточно прав на суперпользователя, чтобы поднять голос, я просто связал свою учетную запись stackoverflow сегодня. Однако этот ответ был достаточно близок, чтобы отклеиться. Вот что работает:

Public Function evtest(r As Range, myVar As Double) As Variant
    Dim z As Variant
    z = r.formula
    z = Replace(z, "myVar", myVar)
    Debug.Print z
    evtest = Evaluate(z)
End Function

Теперь я думаю, что мне есть с чем работать

?[M113].Value
0.1*myVar*(0.2+0.3*myVar/.4)
?evtest([M113], 1)
0.1*1*(0.2+0.3*1/.4)
 0.095 

Если я найду что-то нужное изменить, я обновлю этот ответ. Большое спасибо.

РЕДАКТИРОВАТЬ: я сделал это немного более универсальным:

Function evtest(r As Range, myVarName As String, myVarValue As Double) As Variant
    Dim z As Variant
    z = r.formula
    z = Replace(z, myVarName, myVarValue)
    Debug.Print z
    evtest = Evaluate(z)
End Function

Теперь используйте так:

?[M113].Value
0.1*myVar*(0.2+0.3*myVar/.4)
?TEST.evtest([M113], "myVar", 1.5)
0.1*1.5*(0.2+0.3*1.5/.4)
 0.19875 

Надеюсь, это поможет кому-то еще.