Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate cells without specifying/writing cell address individually | Excel Discussion (Misc queries) |