Microsoft Excel – A Developer’s View
I have this love/hate relationship with Excel, especially Excel 2007. I haven’t played with Excel 2010 yet, so this may extend to it as well.
I won’t go into a lot of history here, but I had Visicalc a gazillion years ago, then Lotus 1-2-3 (the text version). In fact in Enron’s early days, I automated some of the curve calculations one of the VPs had built in a version of 1-2-3 running on a Sun workstation using Lotus’ macro language they had extended from the original Visicalc.
My favorite “historical” spreadsheet, however, was MultiPlan. Its Row/Column (R1C1) address style made a lot more sense to me and was much easier to visualize in formulas because both relative and absolute cell references could remain the same when copied from cell to cell. A quick set of examples:
No matter what cell you are in, “R1C1” refers to the cell in the first row and first column. To perform relative addressing, you place the offset in brackets, or nothing if there is no offset. “RC” refers to the current cell, RC[1] refers to the cell to the immediate right of the current cell, R[-1]C[4] refers to the cell one row up and four columns to the right, and so forth. It just makes sense, and the dollar signs in the “A1” addressing style are ugly.
Microsoft gave in to peer pressure in Excel and made the “A1” style the default, but also gave me the option to use R1C1. Woot!! I actually have spreadsheets that require this method due to the complexity of some of the conditional formatting in the sheet that breaks the “A1” model.
I consult primarily in the energy trading industry where Excel use has move from common to rampant. I use the negative connotation because its use creates as much of a headache (or more) for both Information Technology and upper management as it does benefit. Users with many levels of experience, expertise, and development skill build tools that end up as critical applications at the company, and this is dangerous and costly when the creator moves on to a new company, leaving IT to often rewrite the application code or even replace the Excel application with a more suitable (and supportable) system.
The problem and promise of Excel both lie in the ease of automation provided by Microsoft and Excel’s ubiquity in most compaies. By embedding Visual BASIC for Applications (VBA) – yes, BASIC is an acronym – and providing a way to record keystrokes and translate them into VBA, users are able to start building applications with no prior development skill or experience. You can imagine the code that I have seen.
Because of Excel’s ubiquity, people with moderate or better development experience can, and often do, usurp IT management by creating reasonably complex applications and distribute them to fellow users, creating the “critical application” scenario I mentioned above. On the brighter side, consultants like me can develop reasonably complex applications and distribute them to users, allowing local IT and business the ability to rapidly deploy tactical solutions and prototype solutions to users while more complex and supportable applications are developed.
That’s only the first half of my love/hate relationship with Excel.
I learned BASIC in 1973 at high school and have developed more in BASIC than any other language – mostly due to Excel. I think its odd that my most notable works, the Buddy List and the MIG program that won me the Windows World Competition in 1993 were both written in VB. In 1994 I wrote Enron’s first electricity scheduling system in VBA/MS Access 2.0.
I abandoned BASIC (and Visual BASIC) for application development in favor of compiled languages (Delphi, C) in the ’90s, but always came back to VBA in Excel and MS Access. Quick Note: We chose Delphi as our development platform instead of Visual BASIC primarily because Delphi compiled its code whereas VB still created threaded p-code. Microsoft didn’t fully compile VB code until VB 6.
Back to Excel
From what I read on the interwebs, Excel VBA is based on Microsoft Visual Basic 6, a version I never used, so it introduced classes to VBA. I had worked with classes and objects in Delphi, Java, C#, and other languages, but the VBA implementation is different, so I was slow to embrace them. After all, I could do everything I needed in Code Modules. However, as I coded more and more in VBA, I started really missing classes/objects, so I looked them up on Google which led me to www.cpearson.com. Chip Pearson’s site has been one of my best resources since then, and now it’s time for me to “pay it forward”. I have dug deeply into Excel VBA, finding answers to issues that I could not find on the web and developing tricks that I haven’t seen elsewhere.
Here are some highlights.
Excel tables. It amazes me that people still create worksheets containing “tables” of data and don’t put them in Excel tables. To the VBA developer, they are ListObjects and are most often associated with external data connections (QueryTables). I have created classes to encapsulate and extend ListObjects.
Class / Object Tricks. Most tutorials or discussions on VBA Classes are pretty basic (pardon the pun) and don’t demonstrate the power and peril of classes. Primarily missing from VBA are Class Methods. I have some tricks up my sleeve to emulate them.
Impementing R1C1. Sometimes this addressing scheme just makes more sense than A1.
Memory Management. Code that I have seen on expert sites often contain glaring memory leaks, which aren’t a huge problem unless you are developing complex applications with large datasets making memory management a huge issue. I’ve tackled a lot of those.
Excel (and I assume all Office) VBA is recursive, but not reentrant. This is a huge problem for developers since Excel is event-driven. I’ve learned some tricks for working with those limitations.
As time permits with all the other fires I have burning, I will post some of my tricks and tips, and will open up many of the classes I have developed. I’ll also bore you with some development philosophy I have “developed” through my experience. I’ll also rant about “other” development philosophies and IT management WTFs.
So there.
