Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default 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   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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   Report Post  
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate cells without specifying/writing cell address individually Hari Excel Discussion (Misc queries) 4 January 3rd 05 07:05 PM


All times are GMT +1. The time now is 03:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"