vba evaluate expression from string name

Question

I would kindly ask your support for the following issue: I have a ws in EXCEL with a column of input (e.g. from L5 to L15) and in the last row of the column (L16) i put a generic formula that can be modified.

The VBA subroutine should read the formula in L16, traslate it in a vba expression and use it inside a FOR-LOOP.

This is an extract :

'get the formula 
formulaText = ws.Range("L" & ultimaRigaQuote + 1).formula
'manage the formula
formulaText = Mid(formulaText, 2)
formulaText = Replace(formulaText, "SUM", "Application.WorksheetFunction.Sum")
formulaText = Replace(formulaText, "IF", "IIf")
formulaText = Replace(formulaText, "SIN", "Application.WorksheetFunction.Sin")
formulaText = Replace(formulaText, "COS", "Application.WorksheetFunction.Cos")
formulaText = Replace(formulaText, "TAN", "Tan")
formulaText = Replace(formulaText, "RADIANS","Application.WorksheetFunction.Radians")
formulaText = Replace(formulaText, "SQRT", "Application.WorksheetFunction.Sqr")
formulaText = Replace(formulaText, "PI()", "Application.WorksheetFunction.Pi")

For i = 1 To nSimulazioni
'assign the value to the input
For j = 5 To ultimaRigaQuote
Z_Var(j - 5) = Application.WorksheetFunction.Norm_Inv(Rnd, MediaVal(j - 
5), DevStd(j - 5))
MediaCalc(j - 5) = Z_Var(j - 5)
Next j
'use them in the expression
risultato(i) = Evaluate(formulaText)
Next i

all works fine but the problem is in the "Evaluate(formulaText)". If I put directly the content of the string "formulaText" into the brackets, the script works but if i put the name of the string (formulaText) it does not work

for example the string is MediaCalc(0)+MediaCalc(1)-2*MediaCalc(2)/MediaCalc(3)+TAN(RADIANS(MediaCalc(4)))+MediaCalc(5)+MediaCalc(6)

if I write in the script risultato(i) = Evaluate(formulaText) --> does not work

if I write in the script risultato(i) = Evaluate(MediaCalc(0)+MediaCalc(1)-2*MediaCalc(2)/MediaCalc(3)+TAN(RADIANS(MediaCalc(4)))+MediaCalc(5)+MediaCalc(6)) --> it works

Now i wnat to understand why the system does not works with the title of the string but it works with the content of the string

please can you help me?

tnx Ema

fix the problem with the evaluate function

Answer 1

Analyse the code below. The Str function is necessary if you use comma as decimal separator (in Excel). If you use decimal point you can omit Str.

Sub Test_Evaluate()
    Dim MediaCalc
    MediaCalc = Array(0.2, 0.4, 0.6, 0.8, 12.5, 1.5, 1.8)
    Dim res As Double, ftext As String
    ftext = Str(MediaCalc(0) + MediaCalc(1) - 2 * MediaCalc(2) / MediaCalc(3)) & "+TAN(RADIANS(" & Str(MediaCalc(4)) & "))+" & Str(MediaCalc(5) + MediaCalc(6))
    Debug.Print ftext
    res = Evaluate(ftext)
    Debug.Print res
    res = Evaluate(MediaCalc(0) + MediaCalc(1) - 2 * MediaCalc(2) / MediaCalc(3) + Tan(WorksheetFunction.Radians(MediaCalc(4))) + MediaCalc(5) + MediaCalc(6))
    Debug.Print res
End Sub