UDF to evaluate result of concatenate() with additional arg.
 

I need some help creating a UDF that will evaluate the results of a
string that has been created using concatenate() that includes an
argument to be substituted in the resulting text string.

Concatenate results with the following text string:

(LogCCoef*LN(x))+LogBCoef

LogCCoef and LogBCoef are named excel formulas that return values. The
"x" is the placeholder for the argument that I would also pass to UDF
in the form a cell reference. The UDF would look like:

EvaluateThis(ModelFormula,b12)

Assuming:
ModelFormula (a named formula) is:
=concatenate((LogCCoef*LN(x))+LogBCoef)
LogCCoef (a named formula) returns: -6.35
LogBCoef (a named formula) returns: 55.8
Value in cell b12 is: 400

The UDF would return 17.75 [(-6.35*ln(400))+55.8]
Could someone provide some guidance?

GH

GH,

Function EvaluateThis(myModelFormula As String, _
inRange As Double, myReplace As String) As Double
EvaluateThis = Application.Evaluate(Replace(myModelFormula, _
myReplace, CStr(inRange)))
End Function

This assumes that ModelFormula, LogCCoef, and LogBCoef are properly defined
named formulas.

Used like

=EvaluateThis(ModelFormula, B12, "x")

If you can't get it to work, I have a working sample workbook that I could
send you.

HTH,
Bernie
MS Excel MVP

wrote in message
oups.com...
I need some help creating a UDF that will evaluate the results of a
string that has been created using concatenate() that includes an
argument to be substituted in the resulting text string.

Concatenate results with the following text string:

(LogCCoef*LN(x))+LogBCoef

LogCCoef and LogBCoef are named excel formulas that return values. The
"x" is the placeholder for the argument that I would also pass to UDF
in the form a cell reference. The UDF would look like:

EvaluateThis(ModelFormula,b12)

Assuming:
ModelFormula (a named formula) is:
=concatenate((LogCCoef*LN(x))+LogBCoef)
LogCCoef (a named formula) returns: -6.35
LogBCoef (a named formula) returns: 55.8
Value in cell b12 is: 400

The UDF would return 17.75 [(-6.35*ln(400))+55.8]
Could someone provide some guidance?

GH

Thanks Bernie.

I ultimately used a similar concept of searching the text string and
replacing the "x" with the cell contents in B12 but using the excel
worksheet function to accomplish the replace before passing the text
string to VBA to be evaluated. It worked, but not as elegantly as your
suggestion.

I would like to receive your sample file as I always learn from those
more gifted than I in the mysteries of VB. If your offer still stands,
please e-mail the file to:

Best regards,

GH