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

Can't argue with you there Dave. As you found, once you understand how
evaluate works you think the examples are naff. Not good technical help <g.

Bob

"Dave Unger" wrote in message
ups.com...
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

Hi Bob,

Sorry to bother you again, but could you provide me with one example
where using Evaluate is necesary? Thank you

regards

DaveU

To utilize an array formula in code:
res =
Evaluate("Sumproduct(--(Sheet1!A1:A100=""dog""),--(Sheet1!B1:B100=""House""),Sheet1!C1:C100)")

why are you agonizing over this. If you don't want to use it or don't know
how to use it, then don't use it. This function is so versatile that there
isn't one or two examples that will bring it all together for you. That
said, you could probably write a lot of code and never have a reason to use
it.

--
Regards,
Tom Ogilvy



"Dave Unger" wrote:

Hi Bob,

Sorry to bother you again, but could you provide me with one example
where using Evaluate is necesary? Thank you

regards

DaveU

Dave,

When in VBA, you could write code that does some complex operation, but in
many cases you can call worksheet functions directly from within VBA. Many
of these are called directly, such as

myVar = Application.COUNTIF(Range("A1:A10"),"Y")

Sometimes, Application.function, or Worksheetfunction.function_name doesn't
work, so this is where Evaluate scores.

For example, SUMPRODUCT, which can handle complex multi-conditional tests,
is not supported, such as
=SUMPRODIUCT(--(MONTH(A1:A10)=3),--(B1:B10="Y"),C1:C10)").

Or an array formula, such as =MIN(IF(A1:A100,A1:A10)).

Or a complex formula where Application.function_name just gets too
convoluted.

--
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 Bob,

Sorry to bother you again, but could you provide me with one example
where using Evaluate is necesary? Thank you

regards

DaveU

Hi Tom,

Thanks for your reply. Sorry to belabor the point, but to date I
haven't come across an adequate (for me) explanation of this. The
people who responded to this post provided that, and I just wanted a
confirmation that I "got it", which your example did. Appreciate your
help,

Regards,

DaveU

Hi Bob,

Thanks for your patience, it's starting to make sense to me now. For
some reason I had a problem getting a handle on this, but I believe I
finally got it.

Again, thanks so much, really appreciate all your help.

regards

DaveU