Does EVALUATE( ) still exist in V. 2003?

[Epinn]

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? Insert>Name>Define?

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

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

 

[Guest]

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

 

[Ron Rosenfeld]

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? Insert>Name>Define?

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

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]

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

 

[Epinn]

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.

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

Epinn

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? Insert>Name>Define?

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

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]

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

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.

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

Epinn

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?
Insert>Name>Define?

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

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

 

[Epinn]

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 Tools>Options>General. 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

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

 

[Epinn]

Thanks for the info, Anthony.

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

 

[Biff]

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

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 Tools>Options>General. 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

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

 

[Roger Govier]

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


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 Tools>Options>General. 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

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]

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.


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]

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.
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 Insert>Name>Define, right?

Thanks again. Appreciate your example.

Epinn

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


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 Tools>Options>General. 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

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

 

[Pete_UK]

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

 

[Roger Govier]

Hi Epinn

the rows are "fixed"

No, as you drag the formula down the page, the formula is relative to
the row upon which it resides.
What I am saying, is, the columns are not "fixed" in that it does not
matter if you move their location on the sheet by inserting other
columns before them or after them.
There is no question about having to alter via Insert>Name>Define

Try setting up a sheet for yourself in this way and you will see what I
mean.
--
Regards

Roger Govier


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.
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 Insert>Name>Define, right?

Thanks again. Appreciate your example.

Epinn

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


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 Tools>Options>General. 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

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

 

[Epinn]

Roger,

I can't thank you enough. Your example is such a beauty and I am inspired. I always find Excel challenging in the sense that we sometimes have to put together so many functions as one long formula. You have no idea how many times in the past I wished I could have a few short formulae in one cell. Now that I have seen some practical use of named formulae and Evaluate, I think it can be done.

I set up the worksheet and things become quite clear.

The only row that is "fixed" is the header. It will still be okay if we insert rows above it but we can't move (cut and paste) it to another row without changing the definition.

It is acceptable to key in =Gross*s in a cell but not =Gross*VAT_Rate even though VAT_Rate returns "s." We have to use Evaluate to fix the #VALUE! error.

I have a question on the tax calculation. Why is z = 0% and not 100%? If "z" means no VAT is required, then net should equal to gross, and 100% can make this happen. Explanation is optional as the purpose of this exercise is to learn Evaluate and not VAT.

Thanks again.

Epinn

the rows are "fixed"

No, as you drag the formula down the page, the formula is relative to
the row upon which it resides.
What I am saying, is, the columns are not "fixed" in that it does not
matter if you move their location on the sheet by inserting other
columns before them or after them.
There is no question about having to alter via Insert>Name>Define

Try setting up a sheet for yourself in this way and you will see what I
mean.
--
Regards

Roger Govier


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.
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 Insert>Name>Define, right?

Thanks again. Appreciate your example.

Epinn

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


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 Tools>Options>General. 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

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

 

[Epinn]

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 Insert>Name>Define etc. and =Evaluate(Evaluate( )) works as well.

Appreciate your kindness.

Epinn

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

 

[Roger Govier]

Hi Epinn

Why is z = 0% and not 100%?

My mistake. It should have been set to 1 in this scenario (or 100%). In
other cases where I am calculating the VAT amount (starting from the Net
of VAT figure), then it would be 0%. I apologise for the confusion in
typing in this example.

It is acceptable to key in =Gross*s in a cell but not =Gross*VAT_Rate
even though VAT_Rate returns "s."

Yes, this is exactly the problem and why EVALUATE is needed as an
intermediate step.
Let's say Vat_Rate of "s" was held in cell D2 and Gross in E2, even
E2*INDIRECT(D2) returns a #REF error.
It was my original attempt to use IINDIRECT and its failure to operate
as I had hoped that led me to discover the use of Evaluate to carry out
the intermediate calculation.

--
Regards

Roger Govier


Roger,

I can't thank you enough. Your example is such a beauty and I am
inspired. I always find Excel challenging in the sense that we
sometimes have to put together so many functions as one long formula.
You have no idea how many times in the past I wished I could have a few
short formulae in one cell. Now that I have seen some practical use of
named formulae and Evaluate, I think it can be done.

I set up the worksheet and things become quite clear.

The only row that is "fixed" is the header. It will still be okay if we
insert rows above it but we can't move (cut and paste) it to another row
without changing the definition.

It is acceptable to key in =Gross*s in a cell but not =Gross*VAT_Rate
even though VAT_Rate returns "s." We have to use Evaluate to fix the
#VALUE! error.

I have a question on the tax calculation. Why is z = 0% and not 100%?
If "z" means no VAT is required, then net should equal to gross, and
100% can make this happen. Explanation is optional as the purpose of
this exercise is to learn Evaluate and not VAT.

Thanks again.

Epinn

Hi Epinn

the rows are "fixed"

No, as you drag the formula down the page, the formula is relative to
the row upon which it resides.
What I am saying, is, the columns are not "fixed" in that it does not
matter if you move their location on the sheet by inserting other
columns before them or after them.
There is no question about having to alter via Insert>Name>Define

Try setting up a sheet for yourself in this way and you will see what I
mean.
--
Regards

Roger Govier


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.
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 Insert>Name>Define, right?

Thanks again. Appreciate your example.

Epinn

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


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 Tools>Options>General. 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

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

 

[Epinn]

Hi Roger,

As you know, SUMPRODUCT has evolved to something beyond the original creator's imagination. Guess this has not happened to EVALUATE! I wish we could use EVALUATE to put the result of a function in a cell temporarily, until the series of functions are completed.

e.g. If INDEX(MATCH( = not found, NA( ), INDEX(MATCH(

As you see, INDEX(MATCH is performed for a second time if a match is found. If we can use EVALUATE to store the result of the first trial in a cell, we can just plug in the result if a match is found. Wonder if this is possible. If we name the INDEX(MATCH( portion of the formula, will speed be improved? Probably not.

The only alternative I can think of is to use COUNTIF for the logical test for a match. This should be faster than INDEX(MATCH(.

Hope you understand what I am after. Thank you for reading.

Epinn

Why is z = 0% and not 100%?

My mistake. It should have been set to 1 in this scenario (or 100%). In
other cases where I am calculating the VAT amount (starting from the Net
of VAT figure), then it would be 0%. I apologise for the confusion in
typing in this example.

It is acceptable to key in =Gross*s in a cell but not =Gross*VAT_Rate
even though VAT_Rate returns "s."

Yes, this is exactly the problem and why EVALUATE is needed as an
intermediate step.
Let's say Vat_Rate of "s" was held in cell D2 and Gross in E2, even
E2*INDIRECT(D2) returns a #REF error.
It was my original attempt to use IINDIRECT and its failure to operate
as I had hoped that led me to discover the use of Evaluate to carry out
the intermediate calculation.

--
Regards

Roger Govier


Roger,

I can't thank you enough. Your example is such a beauty and I am
inspired. I always find Excel challenging in the sense that we
sometimes have to put together so many functions as one long formula.
You have no idea how many times in the past I wished I could have a few
short formulae in one cell. Now that I have seen some practical use of
named formulae and Evaluate, I think it can be done.

I set up the worksheet and things become quite clear.

The only row that is "fixed" is the header. It will still be okay if we
insert rows above it but we can't move (cut and paste) it to another row
without changing the definition.

It is acceptable to key in =Gross*s in a cell but not =Gross*VAT_Rate
even though VAT_Rate returns "s." We have to use Evaluate to fix the
#VALUE! error.

I have a question on the tax calculation. Why is z = 0% and not 100%?
If "z" means no VAT is required, then net should equal to gross, and
100% can make this happen. Explanation is optional as the purpose of
this exercise is to learn Evaluate and not VAT.

Thanks again.

Epinn

Hi Epinn

the rows are "fixed"

No, as you drag the formula down the page, the formula is relative to
the row upon which it resides.
What I am saying, is, the columns are not "fixed" in that it does not
matter if you move their location on the sheet by inserting other
columns before them or after them.
There is no question about having to alter via Insert>Name>Define

Try setting up a sheet for yourself in this way and you will see what I
mean.
--
Regards

Roger Govier


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.
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 Insert>Name>Define, right?

Thanks again. Appreciate your example.

Epinn

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


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 Tools>Options>General. 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

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