Oh just something to ponder for a Friday.
What's the most powerful command in VBA?
Hmmm... perhaps this one. But few seem to use it, or mention it. I posted this one to my mail group and received no comments... it got burried quickly so I thought I'd allow it to live a little longer here. It's one of my favorites.
- 'One of the most powerful commands in VBA: "EVALUATE" but hardly
- 'anyone knows about it, understands it or uses it.
- 'Can't use worksheet formulas directly in VBA right?
- 'Run this macro:
- Sub Neato()
- MsgBox = EVALUATE("SUM(A1:A10)")
- End Sub
- 'Yeah I know, what about:
- Set Fn = Application.WorksheetFunction
- x = Fn.SUM(Range("A1:A10"))
- 'or if you prefer just:
- x = Application.SUM(Range("A1:A10"))
- '...but in most cases, why bother?
- 'Another little known EVALUATE fact; you're familiar with the
- 'shorthand brackets for referencing ranges right?
- 'Did you know those brackets were shorthand for EVALUATE?
- Sub NeatoNeato()
- 'is the same as...
- 'then this should work right?
- x = [SUM(A1:A10)]
- MsgBox x
- 'or just...
- MsgBox [SUM(A1:A10)]
- 'hey... you know with those brackets, it looks just like a cell
- 'in VBA doesn't it? hehehehe...
- End Sub
Oh, and did I mention you can use EVALUATE in defined range names
too? But thats EVALUATE as it existed in the old command language
as opposed to VBA, so there are ways to access the power of EVALUATE
in your cell formulas without even using VBA, perhaps a secret for
I'm working on posting an example file... here's the intro text I'm working on.
[COLOR="Blue"]EVALUATE is probably the single most powerful command in VBA. It's a bit of a surprise to me that I don't see people suggesting its usage more often; probably just a situation where people in general 'think' they know what a command does but can't really see the benefit of it. Very similar I think to the SUMPRODUCT worksheet formula; if you just took it at face value it doesn't seem like that big of a deal. If you dig a little further, a whole world of options starts to unfold.
The VBA help topic for EVALUATE is a bit vague and probably doesn't do it justice. In fact, after reading through it for yourself, you probably wouldn't see much cause to use it. Here's what it can do:
1. Converts string math expressions to values.
2. Converts 1D and 2D string arrays to their array equivalents.
3. Capable of processing any formula a worksheet cell can process! (wow, WOW)
That's right, it can do anything a cell can do! It contains all the functionality of a worksheet cell wrapped in a single VBA command. In fact, it can even do one thing that cells can't do... it can return whole arrays. So it's like having free access to a worksheet cell... only it's better than a worksheet cell in the sense that it can evaluate and return arrays.
EVALUATE, this is one of the better ones, remember it![/COLOR]
Hmmmm... "can do anything a worksheet cell can do", a lofty claim. Maybe too much, maybe not?