Don't forget about the "A" in "VBA"!

infoblog » Don’t forget about the “A” in “VBA”!

Fri, 17 Oct 2008 22:16:21 GMT

PingBack from http://blog.a-foton.ru/index.php/2008/10/17/dont-forget-about-the-a-in-vba/

re: Don't forget about the "A" in "VBA"!

Jon Peltier — Fri, 17 Oct 2008 23:25:52 GMT

This is a very good thing to remind programmers of. Unfortunately, many programmers are as unfamiliar with the parent application as they are with the business domain they are programming for.

Excel is a fine platform for application development, because it has an extensive calculation engine and a decent presentation layer. But I have seen Excel VBA "solutions" (I hesitate to use that word) which reproduce many built-in features of Excel, usually much less efficiently.

re: Don't forget about the "A" in "VBA"!

Mike Woodhouse — Fri, 17 Oct 2008 23:28:38 GMT

Very true. The "dive for VBA" thing is symptomatic of the user who's trying to make a little knowledge go too far.

Throw in a good understanding of indirection, offset, lookups, match/index and array formulae, and there's an astonishing amount one can achieve without ever getting to the VBE. And once that's mastered, I reckon the user will know when to go to code.

I have to confess to having written a VBA "RangeJoin" function, that wraps Join(), mind you.

re: Don't forget about the "A" in "VBA"!

dermotb — Sat, 18 Oct 2008 02:53:58 GMT

Stephane, VBA is faster than you may think. I can read in and sort unique values from 60,000 cells, in just 0.2 seconds.

This is because VBA can create unique lists extremely quickly using Collections, and readily available QuickSort code handles the sorting.

So VBA can replicate the functionality of the add-in without using any Excel functions. That doesn't mean it's a better solution, naturally, but let's not undersell VBA!

re: Don't forget about the "A" in "VBA"!

Stephane Viot — Mon, 20 Oct 2008 18:11:38 GMT

Thank you all for your comments.

Dermot, I guess my post is as much about performance as it is about not "reinventing the wheel". Your observation as well as Dany's excellent article ("What is the fastest way to scan a large range in Excel?" at http://blogs.msdn.com/excel/archive/2008/10/03/what-is-the-fastest-way-to-scan-a-large-range-in-excel.aspx) clearly show that VBA can be extremely fast... when used properly. The other benefits of taking advantage of the application OM usually are more compact code, less complex code, and (in my case :-)) less buggy code. Think about the number of developers and testers behind the OM...

re: Don't forget about the "A" in "VBA"!

gkeramidas — Thu, 23 Oct 2008 08:23:08 GMT

please don't forget about the speed in excel 2003 and port it over to excel 2007 in sp2. excel code execution in excel 2007 is atrocious and you should be ashamed of yourselves.