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

"Dave Peterson" wrote:

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.

LGY wrote:

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

"Bernie Deitrick" wrote:

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" wrote in message
...
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




--

Dave Peterson