Why is EVALUATE so Powerfull?? :)

[draycut]

Hi

My first threas here, I am pretty (not entirely) new to VBA, and I have a pretty simple question..

Why is the Evaluate formula considered the most powerfull function in VBA?

I get that you can use Worksheet functions a lot easier, but is that it?

Can anyone clear this up a bit? A few examples would be greatly appreciated!

Thanks !

 

[VoG]

Cross posted Why is the evaluate function so powerfull??

Please read Excelguru Help Site - A message to forum cross posters

 

[royUK]

This post by Aaron Blood is a good explanation

 

[joeu2004]

Why is the Evaluate formula considered the most powerfull function in VBA?

Don't confuse opinion with fact. For sure, Evaluate can be useful; sometimes it is the best tool in the box. For example:

Evaluate("sumproduct((A1:A10<>1)*(B1:B10=2),C1:C10)")

That is difficult to do using WorksheetFunction.SumProduct because we must create the VBA arrays for (A1:A10<>1) and (B1:B10=2) ourselves.

On the other hand....

This post by Aaron Blood [1] is a good explanation

Therein, Aaron writes: "It's a bit of a surprise to me that I don't see people suggesting its usage more often". Perhaps the following will demonstrate why.

Option Explicit

Public Declare Function QueryPerformanceFrequency Lib "kernel32" _
   (ByRef freq As Currency) As Long
Public Declare Function QueryPerformanceCounter Lib "kernel32" _
   (ByRef cnt As Currency) As Long


Sub doit()
' execute macro several times.
' first time might incur VBA overhead; skew results
Dim t1 As Single, t2 As Single
Dim st1 As Currency, et1 As Currency, et2 As Currency, et3 As Currency
Dim x As Double, v As Variant
' first calls take longer(!)
x = WorksheetFunction.Sum(Range("a1:a9"))
x = Evaluate("sum(a1:a9)")
t1 = Timer
st1 = myTimer
x = WorksheetFunction.Sum(Range("a1:a9"))
et1 = myTimer
v = Range("a1:a9")
x = WorksheetFunction.Sum(v)
et2 = myTimer
x = Evaluate("sum(a1:a9)")
et3 = myTimer
t2 = Timer
' be sure "interrupt?" is False
' timer interrupt might skew results
MsgBox Format(convertMyTimer(et1 - st1), "0.000000") & "  Sum(Range)" & _
    vbNewLine & Format(convertMyTimer(et2 - et1), "0.000000") & "  Sum(v)" & _
    vbNewLine & Format(convertMyTimer(et3 - et2), "0.000000") & "  Evaluate" & _
    vbNewLine & "interrupt? " & (t1 <> t2)
End Sub


Function myTimer() As Currency
    QueryPerformanceCounter myTimer
End Function

Function convertMyTimer(ByVal dt As Currency) As Double
    Static freq As Currency, df As Double
    If df = 0 Then QueryPerformanceFrequency freq: df = freq
    convertMyTimer = dt / df
End Function

On my computer, Evaluate takes 2.2 to 3.6 times longer than WorksheetFunction.Sum. YMMV.

Of course, we are talking about less than 1 microsec in any case [2]. But it can make a difference when called repeatedly in a loop.

The point is: Evaluate requires interprocess communication with Excel, and Excel must parse the Evaluate argument. And of course, Evaluate is not usable if the arguments are in VBA arrays.


-----
[1] http://www.ozgrid.com/forum/showthread.php?t=52372

[2] It can be misleading to measure such short code paths directly, due to the timer overhead. Usually, it is better to measure a loop. But that can be risky, too, if the loop increases the likelihood of a timer interrupt. On my computer, the "mytimer" overhead is less than 10% of the shortest time measured.

 

[joeu2004]

Errata....

Function myTimer() As Currency
    QueryPerformanceCounter myTimer
End Function

[....]
Of course, we are talking about less than 1 microsec in any case

Less than 1 millisec; specifically, 60 to 300 microsec on my computer. Again, YMMV.

And you might wonder why I don't call QueryPerformanceCounter directly. In this context, I might. But I actually put myTimer et al into a "bas" file that I import when needed. So things are usually more encapsulated than it appears; in particular, the lib kernel32 declarations.

 

[DocAElstein]

Hi

My first threas here, I am pretty (not entirely) new to VBA, and I have a pretty simple question..

Why is the Evaluate formula considered the most powerfull function in VBA?

I get that you can use Worksheet functions a lot easier, but is that it?

Can anyone clear this up a bit? A few examples would be greatly appreciated!

Thanks !

. That question crops up quite often. And has also recently reared its head again in this forum. Try a Google type search as in my signature below.

. For example in the Google search bar type something like
site:MrExcel.com "VBA evaluate"
or
site:MrExcel.com "VBA evaluate Method"

. I have been getting to grips with the evaluate question just recently. It may be worth a quick glance at some of my recent posts.. I will not be a “feature creeper” and give you the links.. If you are interested then check out my recent posts and threads in my profile..

Alan

 

[royUK]

Charles Williams also shares his findings here

 

[draycut]

Thank you all for great and fast responses, and sorry for cross-posting - New to the game

 

[DocAElstein]

Thank you all for great and fast responses, and sorry for cross-posting - New to the game

You're welcome