The Hidden Corners of Excel Hold Many Secrets

Some projects grow and grow, especially if they are effective and well-used applications.  One such goliath is a reporting tool I've been developing for about a year now.  Lately I've been building a new charting module for it.

The program has a sophisticated report engine which allows the user to design his own reports with filters, custom fields and formulas... very powerful.  The new chart builder is an extension to this.  It's coming along, but I've been reminded how tricky Excel chart object programming can be.  Coding in the chart zone reveals some of the dark hallways in Excel's VBA implementation that seemingly never got debugged by the engineers at Microsoft.

My application is 13,000 lines of code and growing, so I've run into a fair share of head-slappers on this project.  Coding VBA can sometimes make one feel like a salmon, swimming up stream; occasional WaTerFalls (WTF?) hinder progress, and perfectly reasonable code yields beguiling results and cryptic error messages such as "User-defined type not defined", or "Error xxx-xxx" (literally, with x's! I should have screen-grabbed that one.)  But despite these obstacles, I just keep trying different ideas and eventually find an alternate way.  That's what makes Excel development so exciting.

Recently my project has started acting strangely, like a creaky building, emitting unpleasant sounds and smells.  One contributing factor could be the sheer size of the workbook.  There are so many custom dialogs, report templates, code modules and worksheets that the file is now over 12MB.  Not obesely large, by most measures, but still a bit to big to send in the mail.  It takes longer to save too, (in conflict with the programmer's mantra: "save often"), and it feels like Excel is struggling (drunk on code?)  The VBA editor has started exhibiting weirdness such as random pauses, strange screen artifacts, and the odd workbook crash. There's even a mysterious worksheet object in the project explorer now, called "Sheet3", that doesn't appear to exist anywhere in the file.  I'm not the only person to experience this particular enigma, so my trust in Excel has wavered and it's a bit scary.  

Once, back in Excel 95, I created a system that, at a certain point, hit a wall and just imploded.  I don't know exactly what happened, but I was coding away one night and some buffer overflowed and I got the blue screen of death, and the workbook thereafter was totally corrupt and un-readable.  I restored a backup file, repeated the day's work I had lost, and then Bang! The same thing happened again.  That's when I informed the client it was time to upgrade the platform to a compiled application.

That was a long time ago.  For efficiency and agility I still prefer developing in Excel.  The ease of deployment is still unmatched, and Excel is much bigger and stronger now... but then again, so am I. My apps are larger and memory-hungryer, and growing in scale; reaching out to other programs and stretching the limits of the Excel platform in new ways.  I dread that all my hard work is built on top of some kind of fault, and one day Excel is going to split apart and swallow the whole thing up.

What can I do to avoid this?  An option I don't relish is to break my program up into smaller pieces: probably not practical for this particular application, but before I go that route I need to investigate other ways of correcting the workbook bloat. A careful code review is in order, to ensure all object variables are being deallocated correctly and the logic is as memory-efficient as possible.  Also, large workbooks tend to accumulate left-over data and toxic clutter such as conditional formatting.  This wastes power and grows the file.  A real-estate audit is the next step.

As another resort, I can migrate the entire program to a brand new, innocent, lean, clean workbook file.  There are tools out there to automate this task, but major applications will require a careful effort. It involves physically copying the contents of each worksheet to a new file.  Then the code from each VBA module and the controls from each user dialog are painstakingly copy-pasted across.  Also, all the named ranges and splits need to be replicated - don't forget the code in the worksheet and workbook objects, and all those object names and properties!

The trick is to NOT copy the Excel objects - just their contents.  This is the only way to leave behind all the invisible, ghostly protoplasm that is haunting your model.  In a workbook like mine, this is a big job that will involve a lot of testing and bug mitigation.  I think, for now, I'll keep that option my back pocket, for if things get hectic.

My client is running this on Windows 7 / Excel 2010, and it appears to be more stable than my 2007 / Windows XP / Mac / Parallels development environment..  Perhaps the coders at Microsoft managed to clear out some of those dusty hallways after all. But as a programmer I also need to remain vigilant against ill-handled errors, wasted screen resources, and memory leaks in my growing code opus.

It's what makes Excel development so exciting.

Maintenance-Free Solutions

As a software creator, I like to spend as much of my working time as possible inventing cool stuff and solving tricky problems using my creativity and skill.  But in order to get paid to do this as an independent consultant, one must also handle such tasks as Sales and Marketing (doing that now), Bookkeeping, Documentation, and the dreaded System Maintenance.

By maintenance, I'm not really talking about bug fixes.  Most of the time it's about changing business logic, connecting to other systems, and new user requirements.  

Some developers, and in my experience, most consulting "Firms", put a high value on the downstream Maintenance hours that come with any major software rollout.  To me, this Vendor-centric attitude sounds a bit like planned redundancy, or selling cheap printers so you can lock 'em in to a lifetime of overpriced ink.  I believe in craftsmanship, which means having a more client-centric approach.  And like I said, I'd rather be inventing new stuff than fixing old stuff.

I learned long ago that a bit of planning up front can minimize or even eliminate the maintenance burden.  Now, whenever I design a system for a client, I make sure to include some Maintenance-mitigation strategies in my design.

1. Keep it simple - this is obvious, but basically, the less code you have, the easier it will be to fix, change, extend, etc.  Do you really need a set of routines to populate, show and validate a dialog box, or could the user update the spreadsheet directly?  Bells and whistles are fun and exciting to build, but aren't always necessary.  A terse design will go farther between oil changes.

2. Build an engine - Reports, File Imports, Calculations... if you can build a data-driven structure to handle each of these, it becomes much easier to modify and extend your application.  Here's an example: my app needs to import data from a 3rd party file.  What if the data provider changes the file layout without warning?  If I hard-coded the import logic, it breaks.  But if I build an import engine that tracks the position of each import column, along with a simple screen where this configuration info can be updated, then the user (or I) can just update the config and re-run - job done.

3. Let the User Design it - This is the cornerstone of my software design strategy: I never want to hard-code the look, feel, or even workflow of a program.  If the app warrants it, I set up all the views as blank canvases which the users can modify to suit their needs.  This requires some planning:

• You need to model all of your data entities as object classes
• All lists and reports must use a data-driven layout - nothing hard-coded on worksheets
• In a multi-user environment, Login accounts let individual users configure the app to suit their personal needs

This is a lot more work up front, but the goal is to avoid those calls asking you to move a field or rename a heading - now the users can do that themselves.  As you start to build a few solutions this way, you amass a library of ideas and code that you can re-use and improve from project to project.

The biggest challenge with this mission can be selling it.  Small budget projects might not warrant this level of future-proofing, and it will take time to educate the customer/boss on the potential benefits. You don't need to mention that the biggest benefit is not being interrupted by minor change requests when you're deep in the creative zone on the next cool project.

Don't give them a fish, make them anglers.

Hard Lessons

In the last post I described the new hardware setup I recommended for a client, to improve a slow and memory-hungry Excel app.  The hardware is in place and the client is happy, but the transition didn't go smoothly.

I discovered that the hardware upgrade in itself was not enough to cure our ills.  After coming up against some disappointing results, despite the bump to Windows 7 and Excel 2010, I was obligated to re-write a big chunk of the data handling logic; something I was hoping to avoid.

My initial research led me to believe that 64 bit Excel has access to as much ram as you can slot into your machine, but this is not my experience.  The spreadsheet part of Excel might be bigger, faster, stronger and fresher smelling, but the VBA object space is not.

Upgrading my VBA code to work in Excel 2010 was actually a non-event.  I only found one line, (related to checking the amount of ram available, ironically) that choked.  Otherwise the macros and formulas all behaved exactly as designed.  Excel 2010 saves files in the Office 2007 file format, so that was my first clue that this isn't really that much of an Excel upgrade.

Then, after seeing Excel freeze, just like before, when my code tried to pre-load six million+ data points into the application's memory structures, I was beginning to think the whole ordeal and expense may have been for nothing.  Further reading on-line found some comments from a Microsoft engineer who wrote that they achieved significant performance gains on Excel 2010's built-in functions, but basically didn't do a thing to optimize or beef up the VBA compiler... that will be in the next release, supposedly.

Perhaps the recent push towards Cloud-based applications is drawing some of the programming talent away from the Excel group at Microsoft.  I'm a developer, so I can't really hold a grudge.  Excel is still a miracle of engineering in my opinion, and the best software ever to run on any computer anywhere. (Yes, I like it.)

Anyway, I had no choice but to re-work the data logic so the application fetches what it needs at report time, instead of pre-loading on start-up.  I made this change, and the app became usable again - even improved, from the client's perspective, because it "starts up a lot faster."  Reports now require additional processing, but the fast new chip and fast ram more than make up for it.  Also, thanks to the solid state hdd, (and possibly Widows7's superior design,) the memory swapping issue has totally gone away.

We didn't get the 800% performance boost that Moore's Law predicted, but our app's data ceiling is now a clear blue sky, and one busy brokerage is crunching numbers with it on a daily basis.  The client is armed with an unbelievably powerful stock analysis tool, and now I know what to expect (and what expectations to instil) the next time a client wants to level up.

Throw More Hardware At It

A very large data set can be taxing on Excel.  On older systems, Excel is limited to 1 GB of ram.  Office 2007 enables up to 2GB.  But opening a workbook that is hundreds of megabytes in size can be an exercise in patience.


A recent project is tracking statistics for 5,000 companies going back for several years.  Some numbers, like Earnings and Dividends are reported quarterly.  Stock Price, which ties into a host of downstream reporting and industry averaging logic, is tracked daily.


Rolling all these numbers together to figure out potential investment strategies requires juggling several million data values in memory.  The data is being housed in an Access database for now, with plans to bump up to SQL server.  But the data interchange is not the bottleneck, it is Excel's ram limit.


My client is running Windows XP and Excel 2003.  I have optimized and re-factored the object model to squeeze every last drop of performance from the platform, but we have hit the proverbial wall.  


As more and more data was captured and the record count approached 4.8 million, some of the long reporting macros began to get bogged down by Windows memory swapping - this is when the hard drive is used as the spillover for physical memory, and it is 100 times slower than normal Ram operations.  To make the application usable, we needed to either reduce the data universe (and thus, the value of the analysis), or implement a hardware and software upgrade.  We're taking the upgrade path.


The new machine is a Dell Latitude E5520, billed as a portable workstation - more pricy than a back-to-school netbook, but with the horsepower to churn through these advanced computations.  (This is, after all, the stuff computers were invented for.)  The laptop upgrade will run Windows 7 64 bit, and Office 2010 Pro, leaving nothing on the table.  Let's see what's in the package:

• Intel Core i7-2620M, 2.70GHz - Four duo-cores, that's 8 processors!
• 8.0GB, DDR3-1333MHz SDRAM - that's fast memory, and a lot of it.
• 128GB Dell Mobility Solid State Drive - THIS is the money.

On the old hardware and platform, one rather large report that computes historical moving averages across industry sectors, can take up to 4 minutes to complete.  If Windows decides it's time to start swapping ram to the hard drive, macro time jumps to 15 minutes.  


Using a solid state hard drive, this won't be a concern as swaps to a ram disc are just about as fast as normal ram.  (and this kind of hard drive allows the computer to boot up in seconds, so I'm told.)  Exploiting the newest 64 bit Windows os and Office suite will allow Excel to access up to 1TB (that's terabyte) of memory.  


The Intel Core i7 is a linked array of 64 bit processors that makes this equivalent to a 90's era SuperComputer costing millions of dollars. Lets see if the performance gains pan out...

The hardware is on-order.  The client is waiting expectantly, as am I.  Next post, I will outline my steps in upgrading the Excel 03 vba code to run in Excel 2010 (if any) and benchmark the new system.  This is a $3,000 blind faith endeavour.  But as the client says, if he makes just one good decision because of the software, it's all paid for! 

Database integration using object classes

This month I am working on a quantitative analysis tool for a stock broker.  He wants to track stock data over time and rank stocks in a variety of ways.  Rather than try to figure out all the different reports and charts we will need to build, we agreed that the tool needs to be completely open-ended.  This means the customer can configure every aspect of the application, including the data fields being captured, the format of the reports, and the formulas used for ranking and classifying the data.

Capturing daily numbers for 5000+ companies requires a robust data storage and query component, so an Access database has been created.  This de-cupling of the data from the program logic is essential for an application of this scale.  Since an Excel project is never finished, the steady stream of updates and fixes can be deployed simply by emailing an updated Excel workbook.  The macro then connects to the Access file on the client's system, loads all the settings and information the client has been inputting and collecting, and configures itself.

Logic was created to handle custom field definitions and custom formulas.  A report designer was also built allowing the operator to layout new reports, control grouping and sorting of data, sub-totals, and formatting.  All this setup data is stored in the Access database.

When the user wants to view a report, the macro loads the setup details from the database, queries the company data tables for the required data, and draws up the report on a worksheet.  To handle all these inter-related data entities, we need to define a set of object classes in VBA.

I created classes for Field, Report, Company, Sector, Industry, Price, and all the other entities that our database is tracking.  At the project level I define Collections which are loaded with the individual instances of each class when the workbook is opened.  So when the user asks for a report, the code iterates through the Reports collection and builds the list of available reports to choose from.

The classes contain the logic they need to interact with the database and with each other.  For example, each class has Load, Insert, Update and Delete methods.  When the user defines a new custom Field, the code creates an instance of the Field object, updates it's properties with the settings the user has input, and calls it's Insert method to write the actual record to the database and insert itself into the Fields collection.

When the reports get generated, the Report object "knows" how to load a sub-collection of the fields that are on the report.  Each Field knows how to fetch the appropriate piece of data and format it for presentation.  If a Field is calculated based on other pre-defined fields, the logic will parse this out and use new Field objects to fetch the individual elements that make up the calculation.

By carefully modelling your objects this way, your code becomes incredibly powerful, because the "work" is distributed across the object model.  Your core routines can be quite lean and efficient, and much easier to read and maintain.  Instead of 20 different Reporting macros, you have one generic macro that produces all possible reports.  And since you're manipulating massive data structures completely in RAM, your code can run very quickly compared to the basic approach of stacking tables of data on different spreadsheets and iterating through cells row by row.

Learning how to model your data using object classes is not an easy skill to master.  I have many years of programming experience on a variety of platforms, including VB.Net, to call upon.  I have also made it a priority to master the design and programming relational databases using SQL.  

Yet gaining this knowledge was achieved by doing.  When you start experimenting with advanced techniques such as object classes and external databases, you build confidence, overcome challenges, and ultimately learn how to produce enterprise-level applications that are extendible, powerful, portable and maintainable.

For more information on these topics, please see some of my prior postings:

• Using SQL and Access
• Excel as front-end to an Access Database
• Using Object Classes

An Unlikely Speed Boost

I've been programming with Excel since version 1.0 and I've picked up a few trick along the way.  Some are passed along from other power programmers, and some I just discover, like this one:

I have a reporting system that is generating a 5000 line report on a worksheet.  It runs in two modes.  The first mode fetches the data from an Access database into memory, and then loops through the memory collections to draw the output.  The second mode simply draws the output, assuming the data is already in memory.  Much to my surprise, mode 2 was running a lot slower than mode 1, which is unexpected because mode 1 is fetching and looping through a recordset, hitting twice as many lines of code as mode 2.

To figure out what was going on, I added a progress counter to both parts of the code.  This allows me to watch the macro's percentage of completion as it chugs along.  For no obvious reason, the macro running in mode 1 finished in about 6 seconds, whereas running it in mode 2 took a whopping 32 seconds.  No obvious bottleneck or data differences could be discovered.

I then had a look at how the macros were being called.  Mode 1 was being fired by a change event on a drop-down list control.  Mode 2 is called using a macro button.  I thought that might be the difference, so I tried calling both modes from a temporary macro, and suddenly I'm seeing slow performance in mode 1!  I tried a few variations, but found that it wasn't how the macro is being called, but a subtle difference in the code between the two routines.

My drop-down control's event code had the line:    Application.Cursor = xlWait at the top, followed by the call to the report macro, and finally:    Application.Cursor = xlDefault.  I put this in originally because I thought the extra step of fetching data might cause a delay in the macro, and showing an hourglass reassures my users that something is happening.

When I added these lines to the button click event for mode 2 of the macro, I observed the dramatic speed improvement I was looking for.  I'm already suppressing calculation and screen updating, which makes a big difference on any code that draws to a spreadsheet, but I had no idea that changing the mouse cursor to an hourglass could give a further 400% speed boost!  I will now go back and use this technique on all the models I've created that have lengthy macro processes.

Let me know if you have any other obscure tricks like this - I'm always on the lookout for more macro speed, and I know Excel has many more undiscovered secrets in store for us all.

Check your References

When you are building advanced Excel applications, you occasionally need to call upon logic or features provided outside of Excel. This is done by adding a REFERENCE to an external application or library, by way of the VBA Editor's Tools menu.  Just about every application on your computer shows up here, enabling your code to leverage highly specific functionality such as enhanced user interface elements, or internet data services.

In a previous post I explained how to get Outlook to send email from Excel. The code to do so requires a reference to the Microsoft Outlook 11 Object Library. A recent application I created does this, and also uses several other libraries, including an HTML Browser control to preview PDF files, an Access database, and has a set of Custom UserForms - all requiring project references.  Each one of these represents an actual file - usually a DLL - installed and registered on the host system.

This makes Excel incredibly powerful.  But using this mechanism can complicate the deployment of your software, because you can't always control what is or isn't installed and registered on the end-user's computer. This can also be a problem if you are a stand-alone developer, but need to move your work between home and office machines.  If a referenced DLL or program is not available, you end up with a Broken Reference.

Another coder, Andrew Roberts, blogged about this very problem, and provided a technique to programatically re-add broken references, (for Outlook, in his example.)  It's a good fix, but has limitations.  For one, it depends on a specific Excel security setting to work, which is  probably not something you're going to want to ask 30 novice operators to change on 30 different computers.  And if your project code is password-protected, this approach may not work at all.


Protected Project and Protected References

In my project, I am using the Redemption library for Outlook.  This toolset allows VBA to manipulate Outlook objects quietly, in the background, instead of requiring the user to approve a series of popup warnings shown as part of Outlook's security protocol.

Not all users need this extra level of functionality, so I wrote some code to "ask for Redemption" - users who do not install this extra software should not be show-stopped by a broken reference.  The program should instead default to the standard Outlook experience.

I ran into a major snag: the VBA code that allows you to view and verify project references doesn't work when your VBA project is password-protected - unless the project is open in the VBA editor!

My code needs to be password-protected because of the nature of my programming work - I program for profit.  I don't want every employee at my client's location to freely view and copy the code I have written - this is my sweat and blood, and I try to maintain at least an illusion of copyright protection on it.  I also don't want operators to change or hack it.

Still, some code I share freely: here's a routine you can call from your workbook's open event to programatically inspect the project reference collection.

Sub reftest()
    
   'which references are visible and/or broken?
    Dim chkRef As Variant
    
    Debug.Print vbCrLf & vbCrLf & " - Reference Check -"
    
    For Each chkRef In ThisWorkbook.VBProject.References
      Debug.Print chkRef.Description & "  " & _
      Choose(chkRef.isBroken + 1, "", " - broken"
    Next

End Sub

In the VBA development environment, it all works wonderfully. All the references I added showed up.  But if I close Excel, and then re-open the model, (which calls this routine from it's Workbook Open event,) the references I added to the project become invisible to the code.  All you see are the 4 standard references that all projects have:


VBA   Visual Basic For Applications
Excel   Microsoft Excel 11.0 Object Library
stdole  OLE Automation
Office  Microsoft Office 11.0 Object Library

My code can't tell if the Redemption reference is broken or not, and so can't handle the situation gracefully.

I believe Excel hides the extra References by design - these are part of your code's functionality and overall design strategy, which is the I.P. that your trying to protect by putting a password on it.  However, since it's the code "asking", not a person via the IDE, I think it should be possible to enumerate the full collection of program references.  This would allow the kind of self-diagnostic logic I'm looking for.

I'm not sure what the work-around is for this at present - I'll probably end up using some sort of in-function error trap.  I'll report on my solution in a future post.  I'm open, as always, to suggestions via this blog's comment facility.

One of the really good things about Microsoft Windows and the Office programs is the ability to interoperate and exchange data between applications.  If you want to ensure portability and longevity of your code, just don't get carried away adding 3rd party libraries.