Excel VBA avalia string da célula como fórmula com uma variável

Question

Passei horas trabalhando nisso e encontrei questões relacionadas, mas não encontrei uma resposta que funcionasse, portanto, não acredito que isso seja uma duplicata.

Eu tenho uma string em uma célula que representa uma fórmula simples e uma variável nela. Essa variável será pré-definida. Então eu preciso avaliar a string. Não consigo obter o valor da célula para avaliar. A string propriamente dita é avaliada na janela imediata, mas a mesma string de valor da célula não:

?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

Meu melhor palpite na variável não está sendo avaliado, pois posso substituir o myVar por um 1 e ele funciona. Eu tentei todos os tipos de citações triplas e isso também não funciona. Como posso conseguir essa coisa aparentemente simples? Eu tentei envolver em subs e funções da seguinte forma:

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 

O que eu estou negligenciando? Obrigado.

    

Answer 1

Experimente esta solução e reverta se funcionar para você. Pressione ALT + F11 para abrir o Editor VBA. Insira um módulo e cole o seguinte UDF nele.

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

Agora, sua expressão nas células do Excel não deve conter nenhum caractere que não faça parte da fórmula, como & "etc. Deve manter uma fórmula simples com nome estático e manter o nome constante e sensível a maiúsculas e minúsculas.

por exemplo. myvar + 3 aqui myvar é apenas texto estático, não declarado em lugar algum. Use a função e passe a célula como intervalo e valor da variável como duplo para ela. Não há muitas validações em vigor, passando a matriz multidimensional pode resultar em um erro aqui. Veja a imagem abaixo. Experimente e verifique se isso atende às suas necessidades.

    

Answer 2

Eu não tenho crédito suficiente no superusuário para fazer o upvote, acabei de vincular minha conta stackoverflow hoje. No entanto, essa resposta estava perto o suficiente para se soltar. Aqui está o que está funcionando:

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

Agora, acho que tenho algo para trabalhar com

?[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 

Se eu achar que algo precisa ser modificado, atualizarei esta resposta. Muito obrigado.

EDIT : Tornei um pouco mais universal:

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

Agora use assim:

?[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 

Espero que isso ajude alguém.