ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   UDF evaluate string output (https://www.excelbanter.com/excel-programming/415858-udf-evaluate-string-output.html)

David
UDF evaluate string output
 
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
UDF evaluate string output
 
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



David
UDF evaluate string output
 

David
UDF evaluate string output
 
Thanks Bernie
works great

"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




LGY
UDF evaluate string output
 
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




Per Jessen[_2_]
UDF evaluate string output
 
Hi

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

Regards,
Per

On 8 Sep., 22:18, 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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Charles Williams
UDF evaluate string output
 
Try this:

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

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

"LGY" wrote in message
...
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
UDF evaluate string output
 
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

Bernie Deitrick
UDF evaluate string output
 
LGY,

Another option is that you could use

=INDIRECT(A2)

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

Bernie

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

LGY
UDF evaluate string output
 
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


All times are GMT +1. The time now is 01:40 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com