I like Graeme's solution ...
And want to remember a rare word in Excel: "Evaluate".
As VBA is not compiled this gives a quite simplified way to
parse expressions, try Debug.Print (Evaluate("Sin( 4.2 )"))
This also works if you use a unique string for your variable,
replace it by a value and evaluate then (on a non-english PC
remember your language settings: decimal separators ...).
This can be carried further: if your function is Sin then
Sin(x) is called a function expression and in a symbolic
computer system (like Maple) it does not matter too much
whether you want to work with a function or an expression
as long as you know the variable: f and unapply(f(x),x)
are almost the same in Maple for a function f. This idea
can be translated to Excel:
As Excel has no parser just tell it that in "Sin( myX )"
the string myX has to be seen as variable. Thus instead
of working with function pointers (=adresses + type) as
in C one can work with function expressions as strings.
The advantage against "run"? You do not need to set up a own
function to input x^2. Disadvantage? String processing ...
It is possible to work with function pointers in Excel as
well, but i only know a way by using a 'general DLL' to
let it work (so i omit that).
Ok, much of words and noise to explain a single line:
Debug.Print Evaluate(Replace(fctString, "theX", "1.3"))
For a german Excel it might look like this:
Dim fctString, yString, x, separatorTst, xStr, y
fctString = "-1.4*sqrt(2)*exp(-(theX^2))"
x = 1.3
separatorTst = InStr(1, CStr(1.1), ",")
If 0 < separatorTst Then ' xlDecimalSeparator ?
xStr = Replace((CStr(x)), ",", ".")
yString = Replace(fctString, "theX", xStr)
y = Evaluate(yString)