Why Evaluate?

[Dave Unger]

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

 

[John Coleman]

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

 

[Charles Williams]

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

 

[Bob Phillips]

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]

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

 

[Dave Unger]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

Hi Bob,

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

regards

DaveU

 

[Guest]

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.

 

[Bob Phillips]

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:A10>0,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]

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

 

[Dave Unger]

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