Hello everyone,

I realize this probably sounds ridiculous, but maybe someone can
explain the purpose of the Evaluate method to me. If I replace the
VBA help examples with the following code, I get the same results:

VBA Help examples

Evaluate("A1").Value = 25
trigVariable = Evaluate("SIN(45)")
Set firstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).Evaluate("A1")


My Code

Range("A1").Value = 25
trigVariable = Sin(45)
Set firstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).Range("A1")

Searching the groups turns up numerous examples of using Evaluate, but
I have been unable to figure out "why" use Evaluate. Somewhere, I'm
missing something.

Thank you for your help,

Regards,

DaveU

With Evaluate you don't have to decide at design time what to
evaluate. A possible use: say that you wanted to create a function-
grapher, something which takes a user-supplied mathematical expression
and plots it on an x-y coordinate system. You could grab the
expression to be graphed and with a sub take the expression,
substitute numbers for the variable x and invokes Evaluate to generate
the y-values to be plotted. I'm sure others can think of some other
uses.

-John Coleman


On Mar 3, 11:24 pm, "Dave Unger" wrote:
Hello everyone,

I realize this probably sounds ridiculous, but maybe someone can
explain the purpose of the Evaluate method to me. If I replace the
VBA help examples with the following code, I get the same results:

VBA Help examples

Evaluate("A1").Value = 25
trigVariable = Evaluate("SIN(45)")
Set firstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).Evaluate("A1")

My Code

Range("A1").Value = 25
trigVariable = Sin(45)
Set firstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).Range("A1")

Searching the groups turns up numerous examples of using Evaluate, but
I have been unable to figure out "why" use Evaluate. Somewhere, I'm
missing something.

Thank you for your help,

Regards,

DaveU

I use evaluate for 2 reasons.
- to evaluate a Name when you cant be sure what its refersto contains(named
range, dynamic range, named formula etc etc).
- to evaluate an Excel Formula or array formula within VBA without
transferring it to a worksheet, calculating it and reading back the
resulting values.

There are a number of quirks with Evaluate that you should be aware of,
particularly the difference between Application.Evaluate and
Worksheet.Evaluate

see http://www.decisionmodels.com/calcsecretsh.htm for details

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Dave Unger" wrote in message
ups.com...
Hello everyone,

I realize this probably sounds ridiculous, but maybe someone can
explain the purpose of the Evaluate method to me. If I replace the
VBA help examples with the following code, I get the same results:

VBA Help examples

Evaluate("A1").Value = 25
trigVariable = Evaluate("SIN(45)")
Set firstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).Evaluate("A1")


My Code

Range("A1").Value = 25
trigVariable = Sin(45)
Set firstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).Range("A1")

Searching the groups turns up numerous examples of using Evaluate, but
I have been unable to figure out "why" use Evaluate. Somewhere, I'm
missing something.

Thank you for your help,

Regards,

DaveU

The worksheetfunction property doesn't support all worksheet functions, so
evaluate is useful to get the value in in VBA for such functions.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Dave Unger" wrote in message
ups.com...
Hello everyone,

I realize this probably sounds ridiculous, but maybe someone can
explain the purpose of the Evaluate method to me. If I replace the
VBA help examples with the following code, I get the same results:

VBA Help examples

Evaluate("A1").Value = 25
trigVariable = Evaluate("SIN(45)")
Set firstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).Evaluate("A1")


My Code

Range("A1").Value = 25
trigVariable = Sin(45)
Set firstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).Range("A1")

Searching the groups turns up numerous examples of using Evaluate, but
I have been unable to figure out "why" use Evaluate. Somewhere, I'm
missing something.

Thank you for your help,

Regards,

DaveU

Hi,

Thanks for the replies, everyone, you've been most helpful. This has
shed new light (for me) on this subject, and it's finally starting to
make sense.

regards,

Dave

Hi,

Sorry, forgot to add this - just a final question. So, in the VBA
Help examples shown, is it really necessary or useful to use Evaluate
here?

Evaluate("A1").Value = 25
trigVariable = Evaluate("SIN(45)")
Set firstCellInSheet =
Workbooks("BOOK1.XLS").Sheets(4).Evaluate("A1")

regards,

DaveU

The first and third seem pointless to me, but the second is necessary if you
want to use a worksheetfunction that is not supported by the
worksheetfunction property (although there is a VBA Sin function of course,
which also makes that particular example
redundant).

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Dave Unger" wrote in message
ups.com...
Hi,

Sorry, forgot to add this - just a final question. So, in the VBA
Help examples shown, is it really necessary or useful to use Evaluate
here?

Evaluate("A1").Value = 25
trigVariable = Evaluate("SIN(45)")
Set firstCellInSheet =
Workbooks("BOOK1.XLS").Sheets(4).Evaluate("A1")

regards,

DaveU

Hi Bob,

It seems to me that better examples might been used, with resulting
less confusion.

Again, appreciate your reply, this is most helpful. Thank you.

regards,

DaveU