Working with Massive Files

I see this quite often: A very large worksheet laid out in a massive table, containing thousands of records and multiple, complex formulas.  The miracle of Excel makes this sort of number-crunching possible, but when going large, things can get sketchy.

A recent finance project I worked on tracked 2,500 corporations and calculated a number of statistics about them using several different estimation models in concert. There were 140 data columns, of which about 100 were calculated using lengthy, nested formulas.

As I constructed the solution, I noticed my file size growing rapidly.  Soon the file was up to 24MB. This is much too large to send by email, which complicates my delivery and backup schedules slightly. But more importantly, it makes Excel slow and "sandy", which is what I call it when the hourglass starts showing up during routine operations.

On my model I set the column width for a range of cells, and thought I had missed the button because for several seconds nothing happened. As I added to the model, edits to formulas started taking more and more time to recalculate, and save time for the workbook became a factor.  A few seconds here and there may not seem important, but the impact on your daily productivity can be serious. Its like trying to drive a truck that has a loose steering wheel.

The following graph shows how productivity can start to fall off steeply as soon as recalculation time starts to exceed 1 second, (based on an average of 4 seconds between each user-performed operation.)

You can throw hardware at the problem (ie: get a faster computer), but there are cheaper and more immediate options available to keep your workbooks snappy and your deliveries on schedule.

1. Turn off Automatic Calculation.
Sort of obvious - this can make a huge difference, and is achieved via the Tools / Options dialog -> Calculation tab.  You will need to hit F9 frequently to recalculate, but at least you get to choose when it happens.  This might not help if your bottleneck is caused by volume rather than formula complexity.

2. Commit Formulas
In situations where the required "result" of a model involves a number of interim calculations, one generally uses calculated columns in the data to represent the various steps.  If your source data is not likely to change, you can convert formulas into actuals using copy / paste special -> values.  Only do this when you're satisfied that the formulas are correct and the inputs won't change.

If those formulas were being fed by a bunch of data columns, those too can be deleted, further lightening the file.  Hint: keep at least one row of data using the original formulas, so you can edit and re-copy them down the table if required.  If you delete data columns, be sure to keep back-up versions of your workbook.

3. Work in Abstract
If your data set consists of thousands of records, each fundamentally the same in structure, why not delete 99% of them and work on the sub-set?  Providing you design your formulas to be range-copyable, it shouldn't matter if your sheet has 20 rows or 20,000.  Keep it lean and snappy while you assemble the overall model and tweak the formatting.  When it's perfect, bring in the full data set, copy down your formulas, and save your final version.

4. Zip it up
When it comes time to send the final workbook to the end user, and the file is 2 MB or larger, the polite thing to do is compress the xls file and send it as a ZIP attachment.  In File Manager, right-click the workbook and choose Send To-> Compressed (zipped) Folder.  A 80% reduction in file size can usually be achieved, making it possible for the recipient to actually receive your work.  An added bonus for those who really care: ZIP files can be password-protected, which is more secure than the built-in workbook password scheme.

Simple tactics such as these will make you the star of the shop, and the one they call when those massive workbooks needs wrangling.  Less [hourglass] is more.

Speed Optimization Tips

If you are writing (not recording) macros that contain more than a few dozen lines of code and take more than a few seconds to execute, you can consider yourself a power macro programmer.   In this day of incredibly fast processors and massive amounts of RAM, hardware speed can usually overcome the sluggishness of inefficient or poorly written code.  But competent developers are never satisfied with this attitude, and will invest a little extra time making sure no processor cycles are wasted.

As your coding skills advance, and you take on bigger and bigger automation tasks in VBA, you will eventually get to the point where you need to optimize your code for speed.  This is especially true when your code will be used by other people.  If your users are forced to wait an unreasonable amount of time for a process to run, they may get annoyed, or start clicking around on Excel's menus or buttons to see what's going on.  This usually results in your macro running even slower, or worse: an Excel crash.

Presented here are a few strategies you can use to make sure you code runs quickly and your users are happy with the performance of your macros.

Tracking Execution Time
Say you have a complex Excel model with many specific methods (macros) that need to be run over the course of a user's interaction with the file.  If things appear to be running slowly, the first thing you need to do is figure out where the bottlenecks are.

A simple way to do this is to annotate your code with some debug feedback so you, as developer, can track the performance of the individual macros and functions.  This is done using the Debug.Print directive along with the Timer construct, as in the following code example:

Sub MyMethod()
  Dim tmr As Single
  tmr = Timer
  ... your code here ...
  Debug.Print "MyMethod took " & Timer - tmr & " seconds to run"
End Sub


If you put this code in each of your subroutines, you will quickly be able to see which ones took the longest, and you can focus your optimization efforts there.

Caching
John Carmack, master programmer and one of the founders of ID software (creators of the game Quake) was an early pioneer in the field of 3D rendering.  In the 90's when he was writing Quake, computers were running Pentium 1 processors and had 16 MB or ram on average.  In order for his game to be playable, John had to squeeze every last bit of performance from his code, and his most important strategy for this was Data Caching.  Caching refers to storing the data you need close at hand, so you don't have to go looking for it when you need it.

Think of a worker high on a construction scaffold.  He has his tools and supplies up there with him, so he doesn't have to climb down to the truck and get the next piece each time he does a bit of work.  Your Excel logic can be set up to act the same way - instead of searching for the piece of data you need on every iteration of a loop, find it and store it locally outside the looped code.

For example, this would be slow:


  For X = 1 to 30000
     Cells(x,1) = Range("MyVariable") + X
  Next


But this would be much faster, because we don't need to look up a Range variable 30,000 times:


  MyVar = Range("MyVariable")
  For X = 1 to 30000
     Cells(x,1) = MyVar + X
  Next


Keeping local references to cell values can be a big performance win in VBA, because there is a lot of overhead involved in going to the spreadsheet and extracting a value.  Any time you can remove this sort of lookup from inside a loop it's worth doing.

Screen Refresh and Recalculation
Another extremely helpful way to speed up your code is to turn off screen refresh and, if possible, workbook calculation.  Depending on what your code is doing, you may enjoy a 1,000 times speed improvement when suppressing screen updates while your macro executes.  Just be sure to turn it back on again after your macro is done.


Sub MyMethod()
 'turn off refresh and recalc
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  ... your code runs ...
 'turn it back on!
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub


Perceived Speed - providing progress feedback
Perhaps you have done everything possible to make sure your code is as snappy and efficient as possible, but it still takes several seconds to execute because of the sheer amount of data being manipulated.  To keep your users from dozing off or getting worried, you should provide some sort of feedback.  An easy way to do this is through the Excel status bar, and this works especially well when giving feedback on the progress of a loop, as in the following example:

  iTotal = 1000
  For idx = 1 to iTotal
     something happens...
    Application.StatusBar = "Running... "&Int((idx / iTotal)*100)&"% Complete"
  Next


Even though the speed of your code won't change, the users will actually think it is running faster because they can see that it's running, and how long it's going to take. Without this, they may wonder if your code has bogged down, and click the Launch button repeatedly - not good.

Conclusion
By taking some time to consider the experience your users are having while running your code, and following some of these strategies, you will be able to produce efficient macros that enhance the productivity of those who rely on your work.

Living Mac, Working Windows

I spent most of my career working on Microsoft Windows-based PC's - programming superb Excel macros, but enduring the endless maintenance, system crashes and sketchy security that all PC owners must put up with.  I envied those with Macintosh laptops and their 6 hour batteries, complete lack of viruses, and beautiful hardware, with just about all the productivity software you could ever want pre-installed!  Unfortunately, the business world of which I was a part uses Excel on Windows.  I could not leave Windows because of the need to maintain file compatibility with my client base.

When Apple announced the Intel-based Macs, and the ability to run Windows through virtualization, I made the jump with enthusiasm.  Suddenly, my personal computing experience transformed from constant discomfort to effortless joy - no exaggeration!  For a while I thought I'd even be able to do my work in Excel for Mac, as the 2003 version supported VBA, but it was not really 100% compatible, and VBA has been removed from the latest Mac Office, to the dismay of switchers everywhere!  (Microsoft claimed that the new Mac Office 2008 required such a complete re-write that it would be impossible to update the VBA engine... Of course, if they had more that 3 programmers on the job it might be feasible, but we all know how much Mr. Balmer and Co. care about supporting the Apple platform.)

But using Parallels, (or VMWare Fusion, which I hear is also excellent) one can run Windows XP along side the usual Macintosh applications.   Now I can develop Excel macros in the latest version of Excel for Windows, knowing that my code will run on my clients' computers, while still enjoying the superior personal computing experience that only Macs can give.  Switching between the two OS's is instantaneous - no reboot is required.  If you run dual monitors, you can even show them both side-by-side.

My computer is the tool of my trade - it must be enjoyable to use every day, or else the trade becomes a painful chore.  Thanks Parallels (and Apple, and MS Office 2003 for Windows), for allowing me to have the best of both worlds.  Instead of quarterly OS re-installs, shoddy shareware, and Outlook viruses, I have Garage Band, bluetooth iPhone tethering, a mag-safe power cord, and a lighted keyboard!  If you have the option, I strongly recommend porting your life (if not your business software) to Mac.

Stories from the Jungle

Some years ago I worked on a project where a huge corporation (my employer) hired another huge corporation to develop a custom desktop application.  There would be about 5,000 users, and the app was built to handle contact, workflow and scheduling information in a client/server environment - typical early 90's stuff.  I was the junior guy, prepping data and helping my boss understand the new "PC" lingo. A couple of hot-shot programmers sat in a room for a month and wrote the application - in Visual Basic 2.0!  I suppose there were a few months of specification and gui design work ahead of this, but I wasn't involved in that work, having recently joined the company.   Anyway, the application was a failure.  We spent several million dollars, but the user community didn't like the app and refused to adopt it.

I believe the reason why is because the users would have had to change their business processes to make the software useful to them - they had to adapt to the app.  Since these were senior people - high achievers and highly trained - adapting to anything just wasn't going to happen.  Did the software fail to meet their needs?  Yes, it did.  But what the designers failed to consider was that it was doomed from the start.  Every user, having worked their way up in the business, had a distinct working style.  The software, in trying to satisfy everyone, satisfied no one.   The huge corporation went on to attempt this project at least two more times that I know of, but the adoption rate never broke 40%.

One funny thing that happened involved a routine that loaded names and addresses from a main-frame data feed.  The names and addresses were all in capital letters, which looked like crap on the application's output.  My boss was asking the consultants to quote the work of modifying the application to convert the ALL CAPS into Proper Case.  The programmers hummed and hawed a bit and then said this particular item would add 3 days to the bill.  These guys were costing $1,500 per day, so that's nine grand!  My boss leaned out his office door and asked me if I thought this was a reasonable estimate.  in the 30 seconds it took him to described the problem to me, I had fired up Excel and written a macro, using Excel's Proper function, to do the job.  "3 days?  Well, actually... just a second... it's done - I just did it with an Excel macro!"  

The consultants gave me a stinky look, but my boss smiled, and told them to skip that item.  I think I got a decent bonus that year.

Office 07 - Upgrade or Not?

Many Excel developers assert that Office 2003 (SP 3) is the high-water mark of the Microsoft Office product, mainly due to resistance against the radical interface changes that arrived with Office 2007.  The infamous Ribbon, which replaces a host of "standard" toolbars and menus which users have been interacting with for decades, has been criticized for being a giant leap backward in usability, among other things.  But more than the interface has changed with Excel '07.  The question is: are the changes worth upgrading for?

Besides the user interface, another major difference is the size of the worksheet - up to 1,000,000 rows and 16,300 columns may now be used, up from around 65,500 and 256 respectively in 03. This is a significant improvement if data storage is any measure.

Under the hood, not all that much has changed.  There are a few new worksheet functions which can simplify data summary calculations, and a few extensions to the Office object model, but for the majority of business applications, there is very little difference in functionality between Excel '03 and '07.

From a programmer's perspective, weighing the Pros and Cons leads me to the conclusion that upgrading to '07 is a BAD IDEA.  Here's why:

1. Unless you really need the space, insanely large worksheets are a huge liability.  Try copying a row or column in '03.  The same task in '07 might need to move up to 50 times more cells - using 50 times more system resources to do so.  Accidently paste something into a whole column?  Go get a coffee while your screen repaints, it'll take a while before the hourglass goes away and you can click that Undo button.

2. Macro performance is way down.  One model I created in '03 takes 10.8 seconds to execute the main refresh process.  This same process, in the same file, on the same computer, on the same day, in Excel '07 takes over 23 seconds!  Weren't upgrades supposed to buy you speed improvements?

3. The new SUMIFS and COUNTIFS functions are nice, but the same functionality can be achieved using array formulas in '03, so even if you desperately need these functions, you don't actually need '07.

4. The new file formats used by 07 are not fully backwards compatible with '03, so if your customers or anyone you need to share files with hasn't upgraded, there will be blood.   If you haven't upgraded yet but they have, no problem. '03 Macros run just fine in '07.  Yes, I know that Open XML is a good idea, I'm just sayin...

5. Excel '07 takes more than twice as long to load as '03.  Use it every day and those seconds start adding up to some real lost productivity, or at best, mild aggrivation.  The new version also uses more system resources.  My CPU cooling fan seems to rev at a higher (louder) speed whenever I have '07 loaded.

6. Office '07 costs money.  If the boss is paying, that's one thing, but for those of us who work independantly, the prospect of spending several hundred dollars for no good reason is a significant downer.

There are many other, minor reasons why I'm sticking with Excel '03 for development.  (I do run both on my system, which you can do without trouble in Windows XP.  I'm not sure about vista though.)  Microsoft seems to think that we should all just upgrade because it's there.  But if upgrading is a step backward, what's the sense of that?  I'll be sticking with Excel '03 until the reasons to upgrade are more compelling.

Controlling Page Breaks Automagically

When it comes time to generate printed output from your Excel application, one sometimes finds that the standard toolset falls a bit short, and additional work must be done to get the pages looking just right. This is especially true in situations where the shape and size of the output is not predictable. For example: when the report is the output of a query or process, rather than a standard page template, it may not be possible to know how many rows, columns or pages may be involved.

Excel does give you access to many powerful printing control features, such as forcing the output to fit within a set number of printed pages (either width or height.) Most of these settings can be applied at design time, but one particularly tricky thing to manage is automatic page breaking.

An example Excel model contained a set of financials: an Income Statement, Balance Sheet, and Cashflow analysis. Each statement is set up for several hundred potential line items, many of which will be blank or zero, according to the data being summarized. To avoid a huge, ugly report that wastes reams of paper and toner every time it is printed, logic was added to hide blank rows, resulting in a nice, compact set of reports. Unfortunately, there's no way to know ahead of time how many pages will be needed, and Excel will insert automatic page breaks with no regard for logical placement.

When you're working on a static worksheet, it's relatively simple to manually adjust these page breaks so they happen in logical places, but in an automated environment, this is not an option. So, the following routine was developed to ensure page breaks only fall where allowed.


Public Sub Paginate(ByRef ws As Worksheet)

 Dim intPages As Integer
 Dim blnBadBreak As Boolean
 Dim intCol As Integer, intRow As Long

'must be in pagebreakpreview for all page
'breaks to be visible to the code
 ws.Activate
 ActiveWindow.View = xlPageBreakPreview

 intCol = 6 'control column

'clear all manual pagebreaks
 ws.ResetAllPageBreaks

'forced hard breaks (those with xx in control col)
 For intRow = 1 To ws.UsedRange.Rows.Count
   If ws.Cells(intRow, intCol) = "xx" Then
     ws.HPageBreaks.Add Before:=ws.Cells(intRow, 1)
   End If
 Next

'move arbritrary breaks (chosen by Excel) up to
'next viable row.

TopOfLoop:

 intPages = ws.HPageBreaks.Count
 If intPages <= 1 Then GoTo Done 
 For idx = 1 To intPages    
    intRow = ws.HPageBreaks(idx).Location.Row    
    blnBadBreak = False    
    While Cells(intRow, intCol) = ""      
       blnBadBreak = True      
       intRow = intRow - 1
       If intRow = 1 Then Goto Done    
    Wend    
    If blnBadBreak Then      
       ws.HPageBreaks.Add Before:=ws.Cells(intRow, 1)      
       GoTo TopOfLoop    
    End If 
 Next 


Done: 
'restore normal view 
 ActiveWindow.View = xlNormalView 


End Sub


This code requires a control column on the worksheet - this can be a hidden column outside the print area.  Use this column to indicate where page breaks are acceptable (with a single "x" - between sub-sections or at spacing rows) or where they are absolutely required (with "xx" - the top of each report section, for example.)  When the routine runs, it will first force page breaks where the xx's have been placed.  Then it will scan the automatic page breaks that Excel has "suggested", and if they don't fall where allowed, the code will move them up to the next allowable position.  The main program loop must be iterated multiple times, because every time you set a manual page break, this will cause Excel to recalculate the positions of the automatic breaks below that.

Just call this routine before printing the sheet, and you will be sure that the page breaks won't split a graph in half or bisect a section that needs to be continuous.  Your reports will look superb every time, no matter what the data set is.

Why Excel?

When asked why I develop in Excel, the answer usually contains one or more of the following points:

1. It's the most powerful platform available
The spreadsheet as a design paradigm is incredibly powerful, which is why Excel has become THE standard business application - used throughout the world for the past 18 years with no signs of ever fading away. The cells, rows and columns provide a great scaffold for your data, charts and text. The spreadsheet provides an elegant data entry, data storage, calculation and transformation infrastructure, and contains everything you need to support just about any complex business functionality imaginable.

2. It's high-level
One of Excel's greatest features as a development environment is all the built-in functionality you get. The Excel object model is comprehensive and well-documented. Integration with other Office applications is seamless (well, since 2000 it's been pretty stable.) Want to generate a report? Just lay out the worksheet the way you want up front, drop the data in, and you're done. 10 lines of code in VBA, using Excel's standard interface, can take the place of several hundred lines of standard Visual Basic code (or several thousand lines of C++.)

3. Everybody's using it
There aren't very many Windows PC's in the business community that don't have Office installed. Kids are learning to use Excel in schools, and most people use it in some aspect of their work, from data entry clerks to CFOs. The XLS file format is viewable on every operating system that supports e-mail attachments, including most cell phones. In short, Excel is, without a doubt, the most popular application in the business world. It was Lotus 1-2-3 on the IBM PC which enabled the Business PC revolution, and Microsoft created Excel in Lotus' image.

4. It's a known entity
Along the lines of number 3 above, Excel is already on just about every business desktop, so you don't need to install anything on the corporate network when you deliver a solution to a company. It's easy to send an XLS file attachment to a customer by email, but asking them to download and install an executible file is another matter - probably won't be allowed at all in a major corporate environment like a bank.

5. It's well supported in the community
For developers, you can be reasonably sure someone else has already done what you're trying to do, and has worked out the tricky parts for you. Bugs in Excel are well known and workarounds abound. Forums, support groups and blogs such as this one provide an incredible wealth of knowledge for beginning and seasoned developers alike.

There are many other good reasons to develop in Excel - what are yours?