user defined function based on contents of cell

T

tacarnevale

Would like to define a VBA function in Excel based on a string
contained in a cell.

For example, suppose the string "X^2 - X + 1" is the value of cell A!.

Can I easily define a VBA function, say fnx(x as double) as double,
that returns x^2-x+1.

Thanks!
 
H

Harlan Grove

[email protected] wrote...
Would like to define a VBA function in Excel based on a string
contained in a cell.

For example, suppose the string "X^2 - X + 1" is the value of cell A!.

Can I easily define a VBA function, say fnx(x as double) as double,
that returns x^2-x+1.
Click to expand...

Function ef(x As Variant, fr As Range) As Variant
ef = Evaluate( _
Application.WorksheetFunction.Substitute(fr.Cells(1).Formula, "x",
CStr(x)) _
)
End Function

called in the worksheet as

=ef(x,$A$1)
 
Top