Does EVALUATE( ) still exist in V. 2003? I couldn't find it from Excel Help. Seeing that DATEDIF can't be found via F1 but still alive and kicking, I need to hear about EVALUATE ( ) for sure although I think I see something like "undefined." If it is not available, what do we use now? I found it from the following writeup. By the way, does naming a formula goes thru the same steps as naming a range on a worksheet? InsertNameDefine?

************************************************** ********************

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1) to
the right of the cell containing the string, highlight cell B1 (for this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

************************************************** *************

Thank you for your help.

Epinn

Hi Epinn,

Seems to be ok as a function in the Visual Basic Editor, for example:

Function csum(i)
csum = Evaluate(Application.WorksheetFunction.Substitute( i, ",", "+"))
End Function

Then in b1: =csum(a1)

Anthony


"Epinn" wrote:

Does EVALUATE( ) still exist in V. 2003? I couldn't find it from Excel Help. Seeing that DATEDIF can't be found via F1 but still alive and kicking, I need to hear about EVALUATE ( ) for sure although I think I see something like "undefined." If it is not available, what do we use now? I found it from the following writeup. By the way, does naming a formula goes thru the same steps as naming a range on a worksheet? InsertNameDefine?

************************************************** ********************

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1) to
the right of the cell containing the string, highlight cell B1 (for this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

************************************************** *************

Thank you for your help.

Epinn

Thanks for the info, Anthony.

Epinn

"Anthony D" wrote in message ...
Hi Epinn,

Seems to be ok as a function in the Visual Basic Editor, for example:

Function csum(i)
csum = Evaluate(Application.WorksheetFunction.Substitute( i, ",", "+"))
End Function

Then in b1: =csum(a1)

Anthony


"Epinn" wrote:

Does EVALUATE( ) still exist in V. 2003? I couldn't find it from Excel Help. Seeing that DATEDIF can't be found via F1 but still alive and kicking, I need to hear about EVALUATE ( ) for sure although I think I see something like "undefined." If it is not available, what do we use now? I found it from the following writeup. By the way, does naming a formula goes thru the same steps as naming a range on a worksheet? InsertNameDefine?

************************************************** ********************

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1) to
the right of the cell containing the string, highlight cell B1 (for this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

************************************************** *************

Thank you for your help.

Epinn

On Sat, 14 Oct 2006 19:57:04 -0400, "Epinn"
wrote:

Does EVALUATE( ) still exist in V. 2003? I couldn't find it from Excel Help. Seeing that DATEDIF can't be found via F1 but still alive and kicking, I need to hear about EVALUATE ( ) for sure although I think I see something like "undefined." If it is not available, what do we use now? I found it from the following writeup. By the way, does naming a formula goes thru the same steps as naming a range on a worksheet? InsertNameDefine?

************************************************* *********************

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1) to
the right of the cell containing the string, highlight cell B1 (for this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

************************************************* **************

Thank you for your help.

Epinn



It is still present in Excel 2002.

BUT

EVALUATE is an old macro command from when Excel used to have Macro sheets.

Entering and using it in the manner outlined by Hager still works in Excel 2002
and I'd guess it'd work in Excel 2003. But I believe you need to use an
absolute reference in the defined name formula.

You CANNOT use EVALUATE in a worksheet cell by itself (you never could).


--ron

But I believe you need to use an
absolute reference in the defined name formula.

You can use R1C1 references:

=EVALUATE(SUBSTITUTE(INDIRECT("RC[-1]",FALSE),",","+"))

Refers to the column to the immediate left of the column where the formula
is entered. For example, if the formula (=csum) was entered in B1 the
reference would be to A1.

Biff

"Ron Rosenfeld" wrote in message
...
On Sat, 14 Oct 2006 19:57:04 -0400, "Epinn"
wrote:

Does EVALUATE( ) still exist in V. 2003? I couldn't find it from Excel
Help. Seeing that DATEDIF can't be found via F1 but still alive and
kicking, I need to hear about EVALUATE ( ) for sure although I think I see
something like "undefined." If it is not available, what do we use now?
I found it from the following writeup. By the way, does naming a formula
goes thru the same steps as naming a range on a worksheet?
InsertNameDefine?

************************************************ **********************

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1) to
the right of the cell containing the string, highlight cell B1 (for this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

************************************************ ***************

Thank you for your help.

Epinn



It is still present in Excel 2002.

BUT

EVALUATE is an old macro command from when Excel used to have Macro
sheets.

Entering and using it in the manner outlined by Hager still works in Excel
2002
and I'd guess it'd work in Excel 2003. But I believe you need to use an
absolute reference in the defined name formula.

You CANNOT use EVALUATE in a worksheet cell by itself (you never could).


--ron

Biff,

Wow! You read my mind. I was happy with my test on a range but I felt that it wasn't dynamic enough. With your formula, I don't have to hard code the range when I define the formula. Great!

However, I am not familiar with R1C1 references; my only encounter with it is via ToolsOptionsGeneral. I know "FALSE" in the formula indicates that R1C1 style is used. Can you or someone point me to some reference material on R1C1 please?

I tested RC[+5] for the fifth column to the right. I know INDIRECT is not confined to the local sheet with A1-style. How about R1C1-style? Can we refer to a different sheet? What are other codes than RC[ ]?

Thanks.

Epinn

"Biff" wrote in message ...
But I believe you need to use an
absolute reference in the defined name formula.

You can use R1C1 references:

=EVALUATE(SUBSTITUTE(INDIRECT("RC[-1]",FALSE),",","+"))

Refers to the column to the immediate left of the column where the formula
is entered. For example, if the formula (=csum) was entered in B1 the
reference would be to A1.

Biff

"Ron Rosenfeld" wrote in message
...
On Sat, 14 Oct 2006 19:57:04 -0400, "Epinn"
wrote:

Does EVALUATE( ) still exist in V. 2003? I couldn't find it from Excel
Help. Seeing that DATEDIF can't be found via F1 but still alive and
kicking, I need to hear about EVALUATE ( ) for sure although I think I see
something like "undefined." If it is not available, what do we use now?
I found it from the following writeup. By the way, does naming a formula
goes thru the same steps as naming a range on a worksheet?
InsertNameDefine?

************************************************ **********************

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1) to
the right of the cell containing the string, highlight cell B1 (for this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

************************************************ ***************

Thank you for your help.

Epinn



It is still present in Excel 2002.

BUT

EVALUATE is an old macro command from when Excel used to have Macro
sheets.

Entering and using it in the manner outlined by Hager still works in Excel
2002
and I'd guess it'd work in Excel 2003. But I believe you need to use an
absolute reference in the defined name formula.

You CANNOT use EVALUATE in a worksheet cell by itself (you never could).


--ron

Can you or someone point me to some reference material on R1C1 please?

I really don't know of any. The Help system has a couple of paragraphs.

Think of it like the Offset function. You offset the selected cell by x rows
and/or x columns:

R = row C = column

So, RC[-1] is a relative reference to the cell on the same row and one
column to the left.

R[5]C[2] is a relative reference to the cell 5 rows below and 2 columns to
the right.

I know INDIRECT is not confined to the local sheet with A1-style. How
about R1C1-style?

You can refer to other sheets as well:

=EVALUATE(SUBSTITUTE(INDIRECT("sheet2!RC[-1]",FALSE),",","+"))

Sheet1 C1 =Esum

In this case Esum refers to Sheet2 B1.

Biff

"Epinn" wrote in message
...
Biff,

Wow! You read my mind. I was happy with my test on a range but I felt that
it wasn't dynamic enough. With your formula, I don't have to hard code the
range when I define the formula. Great!

However, I am not familiar with R1C1 references; my only encounter with it
is via ToolsOptionsGeneral. I know "FALSE" in the formula indicates that
R1C1 style is used. Can you or someone point me to some reference material
on R1C1 please?

I tested RC[+5] for the fifth column to the right. I know INDIRECT is not
confined to the local sheet with A1-style. How about R1C1-style? Can we
refer to a different sheet? What are other codes than RC[ ]?

Thanks.

Epinn

"Biff" wrote in message
...
But I believe you need to use an
absolute reference in the defined name formula.

You can use R1C1 references:

=EVALUATE(SUBSTITUTE(INDIRECT("RC[-1]",FALSE),",","+"))

Refers to the column to the immediate left of the column where the formula
is entered. For example, if the formula (=csum) was entered in B1 the
reference would be to A1.

Biff

"Ron Rosenfeld" wrote in message
...
On Sat, 14 Oct 2006 19:57:04 -0400, "Epinn"
wrote:

Does EVALUATE( ) still exist in V. 2003? I couldn't find it from Excel
Help. Seeing that DATEDIF can't be found via F1 but still alive and
kicking, I need to hear about EVALUATE ( ) for sure although I think I see
something like "undefined." If it is not available, what do we use now?
I found it from the following writeup. By the way, does naming a formula
goes thru the same steps as naming a range on a worksheet?
InsertNameDefine?

************************************************ **********************

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1) to
the right of the cell containing the string, highlight cell B1 (for this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

************************************************ ***************

Thank you for your help.

Epinn



It is still present in Excel 2002.

BUT

EVALUATE is an old macro command from when Excel used to have Macro
sheets.

Entering and using it in the manner outlined by Hager still works in Excel
2002
and I'd guess it'd work in Excel 2003. But I believe you need to use an
absolute reference in the defined name formula.

You CANNOT use EVALUATE in a worksheet cell by itself (you never could).


--ron

Hi Epinn

To give you a practical example of how I use Evaluate.
I am often involved in developing applications for clients where
financial transactions are involved. Here in the UK we have different
VAT rates Standard 17.5%, Lower 5% and Zero 0% (there are many others,
but these will suffice for the example)

On my Sheet I have column headings called VAT_Rate, Gross, Net and VAT
and when the user enters the VAT_code (Data validation used to limit to
one of the valid Vat codes) and Gross amount, I then want to calculate
the Net of VAT figure and the amount of VAT.
Rather than use a series of IF statements or lookup tables for making
the calculation, I make use of EVALUATE.

I set up the following named ranges / formulae

VAT_Rate = INDEX(Sheet3!2:2,MATCH("VAT_Rate",Sheet3!$1:$1,0))
Gross = INDEX(Sheet3!2:2,MATCH("Gross",Sheet3!$1:$1,0))
Net = INDEX(Sheet3!2:2,MATCH("Net",Sheet3!$1:$1,0))
VAT = INDEX(Sheet3!2:2,MATCH("VAT",Sheet3!$1:$1,0))
as you can see, these are all identical, and are just using Match to
find the relative position of each column within the table.

Now define the calculation that turns Gross to net for each of the Vat
rates
s =1/(1+17.5%)
l =1/(1+5%)
z =0%
and finally, the Evaluate to turn the relevant code for that row that
into a value
Vatcalc =EVALUATE(VAT_Rate)

In the column called Net I just use the formula
=Gross*Vatcalc
In the column called VAT I use
=Gross-Net

All of these formulae are relative to the row being used on the sheet,
columns can be moved or inserted and there is no use of the volatile
functions Offset or Indirect.

I have always thought it is a great shame that EVALUATE cannot be used
directly in a cell, but, with named formulae there is a way around the
problem.
--
Regards

Roger Govier


"Epinn" wrote in message
...
Biff,

Wow! You read my mind. I was happy with my test on a range but I felt
that it wasn't dynamic enough. With your formula, I don't have to hard
code the range when I define the formula. Great!

However, I am not familiar with R1C1 references; my only encounter with
it is via ToolsOptionsGeneral. I know "FALSE" in the formula
indicates that R1C1 style is used. Can you or someone point me to some
reference material on R1C1 please?

I tested RC[+5] for the fifth column to the right. I know INDIRECT is
not confined to the local sheet with A1-style. How about R1C1-style?
Can we refer to a different sheet? What are other codes than RC[ ]?

Thanks.

Epinn

"Biff" wrote in message
...
But I believe you need to use an
absolute reference in the defined name formula.

You can use R1C1 references:

=EVALUATE(SUBSTITUTE(INDIRECT("RC[-1]",FALSE),",","+"))

Refers to the column to the immediate left of the column where the
formula
is entered. For example, if the formula (=csum) was entered in B1 the
reference would be to A1.

Biff

"Ron Rosenfeld" wrote in message
...
On Sat, 14 Oct 2006 19:57:04 -0400, "Epinn"

wrote:

Does EVALUATE( ) still exist in V. 2003? I couldn't find it from
Excel
Help. Seeing that DATEDIF can't be found via F1 but still alive and
kicking, I need to hear about EVALUATE ( ) for sure although I think I
see
something like "undefined." If it is not available, what do we use
now?
I found it from the following writeup. By the way, does naming a
formula
goes thru the same steps as naming a range on a worksheet?
InsertNameDefine?

************************************************ **********************

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1)
to
the right of the cell containing the string, highlight cell B1 (for
this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

************************************************ ***************

Thank you for your help.

Epinn



It is still present in Excel 2002.

BUT

EVALUATE is an old macro command from when Excel used to have Macro
sheets.

Entering and using it in the manner outlined by Hager still works in
Excel
2002
and I'd guess it'd work in Excel 2003. But I believe you need to use
an
absolute reference in the defined name formula.

You CANNOT use EVALUATE in a worksheet cell by itself (you never
could).


--ron

Hi Roger,

Thank you very much for sharing. This is what I call "generosity." It's great to see a real life example especially when there is nothing, absolutely nothing, on Excel Help.

All of these formulae are relative to the row being used on the sheet,
columns can be moved or inserted ..... <<

In other words, the rows are "fixed" and the columns are not. Please note that I put quotes around fixed. If you move the rows around, you can easily modify four formulae via InsertNameDefine, right?

Thanks again. Appreciate your example.

Epinn

"Roger Govier" wrote in message ...
Hi Epinn

To give you a practical example of how I use Evaluate.
I am often involved in developing applications for clients where
financial transactions are involved. Here in the UK we have different
VAT rates Standard 17.5%, Lower 5% and Zero 0% (there are many others,
but these will suffice for the example)

On my Sheet I have column headings called VAT_Rate, Gross, Net and VAT
and when the user enters the VAT_code (Data validation used to limit to
one of the valid Vat codes) and Gross amount, I then want to calculate
the Net of VAT figure and the amount of VAT.
Rather than use a series of IF statements or lookup tables for making
the calculation, I make use of EVALUATE.

I set up the following named ranges / formulae

VAT_Rate = INDEX(Sheet3!2:2,MATCH("VAT_Rate",Sheet3!$1:$1,0))
Gross = INDEX(Sheet3!2:2,MATCH("Gross",Sheet3!$1:$1,0))
Net = INDEX(Sheet3!2:2,MATCH("Net",Sheet3!$1:$1,0))
VAT = INDEX(Sheet3!2:2,MATCH("VAT",Sheet3!$1:$1,0))
as you can see, these are all identical, and are just using Match to
find the relative position of each column within the table.

Now define the calculation that turns Gross to net for each of the Vat
rates
s =1/(1+17.5%)
l =1/(1+5%)
z =0%
and finally, the Evaluate to turn the relevant code for that row that
into a value
Vatcalc =EVALUATE(VAT_Rate)

In the column called Net I just use the formula
=Gross*Vatcalc
In the column called VAT I use
=Gross-Net

All of these formulae are relative to the row being used on the sheet,
columns can be moved or inserted and there is no use of the volatile
functions Offset or Indirect.

I have always thought it is a great shame that EVALUATE cannot be used
directly in a cell, but, with named formulae there is a way around the
problem.
--
Regards

Roger Govier


"Epinn" wrote in message
...
Biff,

Wow! You read my mind. I was happy with my test on a range but I felt
that it wasn't dynamic enough. With your formula, I don't have to hard
code the range when I define the formula. Great!

However, I am not familiar with R1C1 references; my only encounter with
it is via ToolsOptionsGeneral. I know "FALSE" in the formula
indicates that R1C1 style is used. Can you or someone point me to some
reference material on R1C1 please?

I tested RC[+5] for the fifth column to the right. I know INDIRECT is
not confined to the local sheet with A1-style. How about R1C1-style?
Can we refer to a different sheet? What are other codes than RC[ ]?

Thanks.

Epinn

"Biff" wrote in message
...
But I believe you need to use an
absolute reference in the defined name formula.

You can use R1C1 references:

=EVALUATE(SUBSTITUTE(INDIRECT("RC[-1]",FALSE),",","+"))

Refers to the column to the immediate left of the column where the
formula
is entered. For example, if the formula (=csum) was entered in B1 the
reference would be to A1.

Biff

"Ron Rosenfeld" wrote in message
...
On Sat, 14 Oct 2006 19:57:04 -0400, "Epinn"

wrote:

Does EVALUATE( ) still exist in V. 2003? I couldn't find it from
Excel
Help. Seeing that DATEDIF can't be found via F1 but still alive and
kicking, I need to hear about EVALUATE ( ) for sure although I think I
see
something like "undefined." If it is not available, what do we use
now?
I found it from the following writeup. By the way, does naming a
formula
goes thru the same steps as naming a range on a worksheet?
InsertNameDefine?

************************************************ **********************

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1)
to
the right of the cell containing the string, highlight cell B1 (for
this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

************************************************ ***************

Thank you for your help.

Epinn



It is still present in Excel 2002.

BUT

EVALUATE is an old macro command from when Excel used to have Macro
sheets.

Entering and using it in the manner outlined by Hager still works in
Excel
2002
and I'd guess it'd work in Excel 2003. But I believe you need to use
an
absolute reference in the defined name formula.

You CANNOT use EVALUATE in a worksheet cell by itself (you never
could).


--ron

Thank you, Ron, for the information.

Yes, "$" was what I missed. I modify the example and use a range, and it works beautifully for V. 2003 as well. I like this tip and I see some users can really apply it.

You CANNOT use EVALUATE in a worksheet cell by itself (you never could).

What *stand-alone* function can I use in place of EVALUATE then?

Epinn

"Ron Rosenfeld" wrote in message ...
On Sat, 14 Oct 2006 19:57:04 -0400, "Epinn"
wrote:

Does EVALUATE( ) still exist in V. 2003? I couldn't find it from Excel Help. Seeing that DATEDIF can't be found via F1 but still alive and kicking, I need to hear about EVALUATE ( ) for sure although I think I see something like "undefined." If it is not available, what do we use now? I found it from the following writeup. By the way, does naming a formula goes thru the same steps as naming a range on a worksheet? InsertNameDefine?

************************************************* *********************

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1) to
the right of the cell containing the string, highlight cell B1 (for this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

************************************************* **************

Thank you for your help.

Epinn



It is still present in Excel 2002.

BUT

EVALUATE is an old macro command from when Excel used to have Macro sheets.

Entering and using it in the manner outlined by Hager still works in Excel 2002
and I'd guess it'd work in Excel 2003. But I believe you need to use an
absolute reference in the defined name formula.

You CANNOT use EVALUATE in a worksheet cell by itself (you never could).


--ron

What *stand-alone* function can I use in place of EVALUATE then?

None! There aren't any worksheet functions that work like Evaluate. See
Anthony's reply. You'd have to use a VBA UDF.

Biff

"Epinn" wrote in message
...
Thank you, Ron, for the information.

Yes, "$" was what I missed. I modify the example and use a range, and it
works beautifully for V. 2003 as well. I like this tip and I see some users
can really apply it.

You CANNOT use EVALUATE in a worksheet cell by itself (you never could).

What *stand-alone* function can I use in place of EVALUATE then?

Epinn

"Ron Rosenfeld" wrote in message
...
On Sat, 14 Oct 2006 19:57:04 -0400, "Epinn"
wrote:

Does EVALUATE( ) still exist in V. 2003? I couldn't find it from Excel
Help. Seeing that DATEDIF can't be found via F1 but still alive and
kicking, I need to hear about EVALUATE ( ) for sure although I think I see
something like "undefined." If it is not available, what do we use now? I
found it from the following writeup. By the way, does naming a formula
goes thru the same steps as naming a range on a worksheet?
InsertNameDefine?

************************************************* *********************

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1) to
the right of the cell containing the string, highlight cell B1 (for this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

************************************************* **************

Thank you for your help.

Epinn



It is still present in Excel 2002.

BUT

EVALUATE is an old macro command from when Excel used to have Macro sheets.

Entering and using it in the manner outlined by Hager still works in Excel
2002
and I'd guess it'd work in Excel 2003. But I believe you need to use an
absolute reference in the defined name formula.

You CANNOT use EVALUATE in a worksheet cell by itself (you never could).


--ron

On Sat, 14 Oct 2006 23:24:52 -0400, "Epinn"
wrote:

Thank you, Ron, for the information.

Yes, "$" was what I missed. I modify the example and use a range, and it works beautifully for V. 2003 as well. I like this tip and I see some users can really apply it.

You CANNOT use EVALUATE in a worksheet cell by itself (you never could).

What *stand-alone* function can I use in place of EVALUATE then?

Epinn

There is no stand-alone function that you can use, unless you write one of your
own in VBA.


--ron

Epinn,

You wrote: What *stand-alone* function can I use in place of EVALUATE
then?

and you have had two replies saying you need to write your own UDF.
This is not difficult, and I thought you might be interested in the
following post from Alan Linton in December 1999 (!):

" ...
VBA provides an Evaluate(string) function that does what you want but
it is not directly accessible from the worksheet. To make it accessible
you can write a VBA eval function like this :-

Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
End Function

I made the function volatile because it does not always know when it
needs to recalculate. A volatile function is recalculated whenever
calculation occurs in any cell on the worksheet. This is not efficient
but it works.

Here are some examples of the eval function in action.
I am using Excel 97, SR2a. I think this should work in Excel 2000.

A B C D E
-------------------------------------------------
1 20 30 A1 20 =eval(C1)
2 20 30 A2*B2 600 =eval(C2)
3 20 30 IF(A3=30,A3,A3*B3) 600 =eval(C3)
4 30 30 IF(A4=30,A4,A4*B4) 30 =eval(C4)
5 1 1 "A"&A5&"*"&"B"&B5 A1*B1 =eval(C5)
6 1 1 "A"&A6&"*"&"B"&B6 600 =eval(eval(C6))
7 sin(pi()/4) 0.7071 =eval(C7)

I just thought of this today and the tests shown above are all the
testing I have done so I won't be surprised if someone picks holes in
this.

See the VBA built-in help for more information on Evaluate(String).

Hope this helps.
--
Alan Linton
.... "

It's easier to read it here rather than provide a link to it.

This is the earliest reference I've found, but other posters have
quoted similar routines since then - do a Google search in the Excel
newsgroups for Eval. Obviously, Evaluate was available in VBA for Excel
97. The most common variation to this seems to be:

Function Eval(str As String) As Variant
Eval = Application.Evaluate(str)
End Function

so you can see that it is only 3 lines in the VBA editor. Use it as:

=Eval(A1)

in your worksheet, where A1 is a text representation of a valid Excel
formula.

Hope this helps.

Pete


Epinn wrote:
Thank you, Ron, for the information.

Yes, "$" was what I missed. I modify the example and use a range, and it works beautifully for V. 2003 as well. I like this tip and I see some users can really apply it.

You CANNOT use EVALUATE in a worksheet cell by itself (you never could).

What *stand-alone* function can I use in place of EVALUATE then?

Epinn

"Ron Rosenfeld" wrote in message ...
On Sat, 14 Oct 2006 19:57:04 -0400, "Epinn"
wrote:

Does EVALUATE( ) still exist in V. 2003? I couldn't find it from Excel Help. Seeing that DATEDIF can't be found via F1 but still alive and kicking, I need to hear about EVALUATE ( ) for sure although I think I see something like "undefined." If it is not available, what do we use now? I found it from the following writeup. By the way, does naming a formula goes thru the same steps as naming a range on a worksheet? InsertNameDefine?

************************************************* *********************

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1) to
the right of the cell containing the string, highlight cell B1 (for this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

************************************************* **************

Thank you for your help.

Epinn



It is still present in Excel 2002.

BUT

EVALUATE is an old macro command from when Excel used to have Macro sheets.

Entering and using it in the manner outlined by Hager still works in Excel 2002
and I'd guess it'd work in Excel 2003. But I believe you need to use an
absolute reference in the defined name formula.

You CANNOT use EVALUATE in a worksheet cell by itself (you never could).


--ron

Hi Pete,

Thank you for taking the time to search and explain things to me. You read my mind - I have been wondering if it is okay to have nested EVALUATE. The post you quoted answers my query.

6 1 1 "A"&A6&"*"&"B"&B6 600 =eval(eval(C6))

I did InsertNameDefine etc. and =Evaluate(Evaluate( )) works as well.

Appreciate your kindness.

Epinn

"Pete_UK" wrote in message oups.com...
Epinn,

You wrote: What *stand-alone* function can I use in place of EVALUATE
then?

and you have had two replies saying you need to write your own UDF.
This is not difficult, and I thought you might be interested in the
following post from Alan Linton in December 1999 (!):

" ...
VBA provides an Evaluate(string) function that does what you want but
it is not directly accessible from the worksheet. To make it accessible
you can write a VBA eval function like this :-

Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
End Function

I made the function volatile because it does not always know when it
needs to recalculate. A volatile function is recalculated whenever
calculation occurs in any cell on the worksheet. This is not efficient
but it works.

Here are some examples of the eval function in action.
I am using Excel 97, SR2a. I think this should work in Excel 2000.

A B C D E
-------------------------------------------------
1 20 30 A1 20 =eval(C1)
2 20 30 A2*B2 600 =eval(C2)
3 20 30 IF(A3=30,A3,A3*B3) 600 =eval(C3)
4 30 30 IF(A4=30,A4,A4*B4) 30 =eval(C4)
5 1 1 "A"&A5&"*"&"B"&B5 A1*B1 =eval(C5)
6 1 1 "A"&A6&"*"&"B"&B6 600 =eval(eval(C6))
7 sin(pi()/4) 0.7071 =eval(C7)

I just thought of this today and the tests shown above are all the
testing I have done so I won't be surprised if someone picks holes in
this.

See the VBA built-in help for more information on Evaluate(String).

Hope this helps.
--
Alan Linton
.... "

It's easier to read it here rather than provide a link to it.

This is the earliest reference I've found, but other posters have
quoted similar routines since then - do a Google search in the Excel
newsgroups for Eval. Obviously, Evaluate was available in VBA for Excel
97. The most common variation to this seems to be:

Function Eval(str As String) As Variant
Eval = Application.Evaluate(str)
End Function

so you can see that it is only 3 lines in the VBA editor. Use it as:

=Eval(A1)

in your worksheet, where A1 is a text representation of a valid Excel
formula.

Hope this helps.

Pete


Epinn wrote:
Thank you, Ron, for the information.

Yes, "$" was what I missed. I modify the example and use a range, and it works beautifully for V. 2003 as well. I like this tip and I see some users can really apply it.

You CANNOT use EVALUATE in a worksheet cell by itself (you never could).

What *stand-alone* function can I use in place of EVALUATE then?

Epinn

"Ron Rosenfeld" wrote in message ...
On Sat, 14 Oct 2006 19:57:04 -0400, "Epinn"
wrote:

Does EVALUATE( ) still exist in V. 2003? I couldn't find it from Excel Help. Seeing that DATEDIF can't be found via F1 but still alive and kicking, I need to hear about EVALUATE ( ) for sure although I think I see something like "undefined." If it is not available, what do we use now? I found it from the following writeup. By the way, does naming a formula goes thru the same steps as naming a range on a worksheet? InsertNameDefine?

************************************************* *********************

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1) to
the right of the cell containing the string, highlight cell B1 (for this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

************************************************* **************

Thank you for your help.

Epinn



It is still present in Excel 2002.

BUT

EVALUATE is an old macro command from when Excel used to have Macro sheets.

Entering and using it in the manner outlined by Hager still works in Excel 2002
and I'd guess it'd work in Excel 2003. But I believe you need to use an
absolute reference in the defined name formula.

You CANNOT use EVALUATE in a worksheet cell by itself (you never could).


--ron