Solved: What Is the ?Evaluate? Method Used For?

[Cyberdude]

I?m trying to figure out what the Evaluate method is used for.
In the followiing procdeure, is there any functional difference between statements 1A and 1B, and between statements 2A and 2B?
Sub WhatsTheDiff
Dim Temp As Long
1A: Evaluate("A1").Value = 25
1B: Range(?A1?).Value = 25

2A: Temp = Evaluate(?A1?).Value
2B: Temp = Range(?A1?).Value
End Sub What?s the purpose of Evaluate?? When would you use it?

[acw]

Hi

I find it useful if you have an excel formula that you cannot replicate in VBA. Say the Sumproduct function.

Say the range B12:B17 has 1,2,3,1,2,3 and C12:C17 the same, the the formula

=SUMPRODUCT(--(B12:B17=1),(C12:C17))

returns 2. Trying to replicate this formula in VBA is hideous. However, using evaluate you can do
MsgBox Evaluate("=SUMPRODUCT(--(B12:B17=1),(C12:C17))")
and get the answer.


Tony

[johnske]

Hi Sid,

Also have a look here (http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_20964126.html) and here (http://www.ozgrid.com/forum/showthread.php?t=52372) for more - you'll see that what Tony gave you above can also be written as: MsgBox [=SUMPRODUCT(--(B12:B17=1),(C12:C17))] :)

[Jacob Hilderbrand]

Also note that the above examples will calculate the value on the Active sheet. Even if the formula is on another sheet. So just make sure you activate the proper sheet before evaluating a formula that does not specify the sheet names explicitly.

[johnske]

Also, as Aaron mentions (2nd link (http://www.ozgrid.com/forum/showthread.php?t=52372)) there are only a few worksheet functions directly available to VBA, but by using Evaluate (or the shorthand [ ] method) - anything that can be done with a worksheet function can be done with VBA, it provides a "virtual cell" for visual basic to work with.

[Bob Phillips]

But why would anybody use

=SUMPRODUCT(--(B12:B17=1),(C12:C17))

:devil2:

[johnske]

Dozen matter, but 'someone' may want to use...

[A1] = [SUMPRODUCT((A9:A20="Ford")*(B9:B20="A"),(C9:C20))] :devil2:

the formula's unimportant here, the principle is :yes

[Cyberdude]

Fantastic replies, guys! I already see several uses for it.
Thanks a lot for your insights.

Sid

[acw]

XLD

As an example of how to use Evaluate of course......


Tony