UDF evaluate string output

[David]

I'm using a UDF to check the math content of text within a cell
Example: if the cell contains "blah 2+2=4 blah" my UDF will return "2+2=4"
How can I get excel to evaluate my new string so that the output of the UDF
is TRUE or FALSE?
Thanks

 

[Bernie Deitrick]

Function myUDF() As Boolean
Dim myStr As String
myStr = "2+2=4"
myUDF = Application.Evaluate(myStr)
End Function

Sub test()
MsgBox myUDF
End Sub

HTH,
Bernie
MS Excel MVP

 

[David]

Thanks Bernie
works great

Function myUDF() As Boolean
Dim myStr As String
myStr = "2+2=4"
myUDF = Application.Evaluate(myStr)
End Function

Sub test()
MsgBox myUDF
End Sub

HTH,
Bernie
MS Excel MVP

 

[LGY]

Hi Bernie,

I have similar question on using Evaluate and hope you can help me out. I
want to evaluate a text expression in a cell. The function is from web and as
below:

Function yEval(entry As String)
yEval = Evaluate(entry)
End Function

The problem is, it returns the same value to all sheets that are using it.
For example,

In my Sheet1:
A1 = 123
A2 = A1
A3 = yEval(A2)

In my Sheet2:
A1 = abc
A2 = A1
A3 = yEval(A2)

When Sheet1 is active and I press F9, it gives the following results:

Sheet1.A3 = 123
Sheet2.A3 = 123 (supposed to be "abc")

And when Sheet2 is active and I press F9, it gives the following results:

Sheet1.A3 = abc (supposed to be "123")
Sheet2.A3 = abc

Thanks very much!

- LGY

 

[Per Jessen]

Hi

Used on sheet1 to evaluate values on sheet2, the address must be like
Sheet2!A1 and vica verca.

Regards,
Per

 

[Charles Williams]

Try this:

Function yEval(entry As String)
yEval = Application.Caller.Parent.Evaluate(entry)
End Function

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Dave Peterson]

Another option:

Option Explicit
Function yEval(entry As String)
Application.Volatile
yEval = Application.Caller.Parent.Evaluate(entry)
End Function

Application.caller is the cell with the formula.
Application.caller.parent is the worksheet with the cell with the formula.

And Evaluate has two parents--the application and a worksheet.

If you use application.evaluate (or just evaluate), then any address that it
sees in that string will be treated like it's on the activesheet.

But if you use worksheet.evaluate(), then excel will see those addresses like
they are on that worksheet that you included in your code
(application.caller.parent in this case).

Some notes...

But since you're passing text to the UDF, excel won't know when to reevaluate
your function. By making it volatile, the cells with your function in it will
be recalculated whenever excel recalcs.

That means that if you have lots of these formulas, then you may experience a
slowdown. And more importantly (to me anyway), your function may not be showing
the correct results until excel recalcs. You should force a recalc before you
trust what you see.

 

[Bernie Deitrick]

LGY,

Another option is that you could use

=INDIRECT(A2)

in each of the cells A3, forgetting about the UDF.

Bernie

 

[LGY]

Hi, Per, Charles, Dave, and Bernie,

Great Thanks to All!

The command "yEval = Application.Caller.Parent.Evaluate(entry) " is exactly
what I am looking for. Thanks for Dave's detailed post, which tells not only
how, but also why. It's true that for my particular question, Bernie's
alternative is the first choice.

Regards,
LGY