SQuirreL away that data

The most useful language any programmer should know is T-SQL - structured query language.  When you have a solid understanding of relational database architecture and the tools to manipulate it, extracting handy information from your business data becomes almost effortless.

Relational DB systems abound: Access, SQL Server, Oracle, MySQL and PostgreSQL are a few examples.  You can even use SQL to intelligently compile data right out of your Excel worksheets.

As macro developers, we often rely on canned reports produced by corporate systems to feed us data.  This is usually in the form of .csv text files, web scrapings, or stacks of steaming, reeking workbooks that take hours to recalculate.  Sometimes, especially if data volumes are large, moving everything into Access and pulling it back into Excel using SQL Select statements can be the best way to carve out the specific information required.

In a recent project, a client was mining several years worth of member profile data to compile statistics and spot geographic trends.  The prototype system, using a small subset of data and a few pivot tables, appeared to get the job done.  But the workbook was already topping 20MB in size - a real pain to shuttle around.  Then the full data set accumulated to over 65,000 records - more than the number of rows in an Excel 03 (client's spec) worksheet... time to step up to MDB.

I massaged the data in pieces in Excel, imported it into an Access database, and was stunned to find the .mdb file to be only 3.2 MB in size!  That's sweet compression.  Smaller than my prototype workbook was, zipped, but holding 20 times the data.

Next, do away with pivot tables.  Nothing personal. I actually think pt's are a pretty good idea, but they can be unwieldy, and many end users fear them.  Programming them takes you into the outer wastelands of VBA - sketchy knowledge for most of us, (but examples are out there,) and getting exactly the result you need might not be possible - this has been my experience with most of Excel's special power features.

I think SQL is considerably more powerful, and a lot less complicated.  With a simple* piece of code I can build a mini pivot table that doesn't pivot, because it's already pivoted to what the user wants to see.  Here's an example:

SELECT TOP 10 ZipCode, AVG(Revenue) 
FROM MemberView GROUP BY ZipCode ORDER BY 2 DESC

This gives us a nice little Answer, suitable for publication in this years Annual Report - a breakdown of the top 10 zip codes by average revenue.

The key to this is setting up a View, which is another more complex SELECT query that joins data from different tables.  This View can also pre-compute some answers, convert ugly binary 1's and 0's to pleasant things like "Yes" and "No", and limit the data set as needed.  It sits in the DB and looks just like another table.  This brings all the data we need to a single point of light.

I wanted to let users pick the field to zip-rank.  For this I linked a drop-down control to a list of cells containing the fields in my View, and the report macro simply subs the user choice into the Select statement.  With this dead-simple, pre-pivoted table maker, users can't mess up.  Instead, they grind out report after report, all day long, grinning like madmen.

Doing some of the heavy lifting in the database itself means the macros and formulas can be much simpler, and the JET database engine can crunch the numbers a lot faster than Excel anyway.  I love that my Business Reporting System workbook is only 100Kb.  Not having data to lug around means macro updates and bug fixes don't impact the client, which helps keep me off-site.  When the data set scales up even further, I'm well positioned to migrate to an enterprise system like SQL server, with minimal impact on the Excel code.

In a Previous Post I showed you some VBA code to support Access DB connectivity and  pulling data using SQL Select statements.  This should be enough to get you started.  I'd love to hear about any nifty tricks or nasty pitfalls you encounter.

Home Spun Scenarios

Most Excel users are content with the incredible power that the basic spreadsheet model provides: named ranges, linked formulas, auto-fill, and so on.  When more in-depth analysis is required, power users often turn to one of the other significant Excel features that are often overlooked, such as Goal Seek, Pivot Tables, Outlining, and the Scenario Manager.

Today's post will discuss the Scenario Manger, and why it is better (and possibly easier) to avoid this feature and build your own scenario control logic.

The Scenario Manager provides a framework, allowing the average Excel user to define a set of input cells in a model, and specify a variety of possible inputs that can be substituted into those cells in order to perform "what-if?" analysis.  What if sales were 20% higher?  What if the price of Oil reaches $200 per barrel?  Any number of distinct scenarios can be defined, and the input data for each can be applied to your model to quickly compare different inputs.

Detailed instructions on using Excel's built-in scenario manager, with examples, can be found here on Microsoft's web site.  This feature is quite easy to use, but I personally believe that Excel's Scenario functionality is just too basic for most power users, and has one glaring disadvantage: you cannot see what scenarios have been created without drilling down into the Scenario Manger dialog, clicking the Edit button, and inspecting the substitute values (and obscure $A$1-style cell references) that have been defined.

A better approach can be achieved easily with a bit of consideration to how your worksheet is laid out, some in-cell validation, and the Offset function.

The goal is to have an easy way to quickly substitute a new set of data for our model's inputs.  This example drives a real-estate calculator (click to enlarge):

In this worksheet, the model's inputs are in column D.  Columns F through J contain alternate input sets - the scenarios.  These alternate sets of data can be substituted into the model by linking the input cells in column D to the scenario cells in F to J, using the Offset function.

All input cells in column D contain the formula: =Offset(0,1+ScenarioOffset), rather than actual data.  The ScenarioOffset reference is a named range pointing to a cell that evaluates which Scenario we have selected in the drop-down in cell D4, using the Match function like this: =Match(D4, ScenarioList, 0)  The ScenarioList reference points to the range of scenario names, which reside in cells F4 through J4.  To make this easy to use (and foolproof), I have added Data Validation to cell D4, so the user must select a value from a list, and the list coices come from... you guessed it!,  ScenarioList - the names of our scenarios.

To use this, the operator selects a value from the drop-down in cell D4: Low, Medium, High, etc.  Our Match function converts this choice to a number, so if we pick "Medium", we get 2 in the cell called ScenarioOffset.  This causes the Offset functions in cells D5 through D17 to change, selecting data from 3 columns to the right (ScenarioOffset + 1, to account for the empty column E.)  Changes to the inputs cause an automatic recalculation of the rest of the model.

Depending on how complex things are, you could write code in the worksheet's Change event to perform additional calculations, regenerate reports, and so on.  The key here is: by changing a single cell (the selected scenario), this changes the data captured by the set of input cells.

Using this approach has some distinct advantages over Excel's Scenario Manager:  It is very easy to add new scenarios by copying values from existing ones and changing the details that are different.  Also, each scenario's data is clearly visible, and sits nicely in-line with the input names (in column B.)  Managing scenarios becomes a simple matter of managing worksheet cells, rather than jerking around with a set of Excel dialogs and modifying data buried in the Scenario Manager's settings.  And if your model has dozens or hundreds of inputs, you will quickly see that the Scenario Manager is simply not viable.

Adding Scenario analysis vastly extends the usefullness of your worksheet models, giving your operators a powerful tool to compare and analyze any number of different possible parameters.  By building it yourself, you maintain control over the input and scenario data, and achieve the exact functionality you need rather than settling for the "good enough" built-in framework that Excel provides.  That's how Power Users roll.

Navigating the Model

On complex Excel models with multiple, large worksheets, your users often need to "jump" to a specific location. For example, you may have defined a set of financial statements - Balance Sheet, Income Statement, etc. To improve their user's experience, power programmers usually set up some sort of navigation buttons or menus, providing an easy way to find the useful content in the model.

Named ranges are certainly helpful in this situation. You can define a named range which points to the top-left corner of the indicated content. The user can then GOTO that range, either by selecting it in the Name box, or using the GoTo menu command. Unfortunately, Excel doesn't know that we want the named range to the the top-left cell in our view, so it only scrolls enough to get the name "in view", even if that means being on the bottom of our visible range.

To get around this particular annoyance, and to ensure my users have the most pleasant experience possible when using my Excel applications, I developed the GoScroll method. This re-usable routine accepts a named range as it's only parameter. When called, it will activate the appropriate worksheet and jump to the indicated range, making sure to scroll the window such that the range ends up in the upper-left corner of the display. Here's the code:


Public Sub GoScroll(ByVal strRange As String)

Dim intPane As Integer, rng As Range

On Error GoTo Fail

Set rng = Range(strRange)
rng.Parent.Activate

intPane = ActiveWindow.Panes.Count

With ActiveWindow.Panes(intPane)
.SmallScroll Up:=.VisibleRange.Row - rng.Row, _
ToLeft:=.VisibleRange.Column - rng.Column
End With

rng.Select

Fail:
Set rng = Nothing

End Sub


This routine leverages the SmallScroll method of the ActiveWindow.Panes object. If you have split your window, it will have more than one "pane", so this code assumes it will be working with the last pane in the collection. Adjust this as required, depending on how your window splits are set up.

With this routine in place, I can now hook up navigation buttons or menus for any named range in the workbook. I call the code like this: GoScroll("Income_Statement") where "Income_Statement" is the named range pointing the the upper-left cell in my income statement section.

Note that this logic requires that the range must be defined globally for the workbook, not locally on a specific worksheet. I'll leave it as an exercise for the reader to figure out how to handle local names. I hope you find this useful. As always, I welcome your feedback.

The many sides of self-employment

Over the years I have worked on many projects for a variety of employers. Living and working in Toronto, Canada's financial hub, the majority of this work has been for large corporations - which means spending the day in a cubicle. It seems so cliche to diss the cube farm, but diss it I must, because I have forsaken the corporate world and all it's "perks" in favour of the more challenging, lower paying, lonely life of the stay-at-home worker.

The main deciding factor in this transition was my preference for programming with Excel. Many corporate roles entail Excel use, and some even require macro programming skills, but I never managed to find a full-time position where I could create power macros and "genius" spreadsheets all day long, which is something I truly love to do. So I opted for the next best thing: becoming a consultant and taking on jobs for business at large.

Now I get to say exactly what I will work on, where and when it happens, and for whom. I can broaden or constrict my "sphere of acceptability" as necessary, according to how lean things get. I get to work on different projects all the time, and nobody tells me what to wear. The result: I never have to sit at a desk, staring at a clock, praying for the end of the working day. I also get to avoid commuting by subway, dull and insufferable co-workers, sick building syndrome, elevator news, galoshes, food courts, florescent lights, staff meetings, doorway smokers, kiosks, falling glass and noon-time birthday parties.

Sound like a dream? Not so fast... I think I already alluded to lower-pay - when you bill by the hour, you really only get paid for hours worked, unless you're corrupt, in which case you won't keep your customers for long. I once had a friend who worked for a pretty cool software company. He was bragging about his unusually large salary and how he got paid every week, no matter what he did or when he showed up. His most startling claim was that he got paid more in the time it took him to go to the toilet than the food had cost! If all you care about is money, perhaps this is the way to go. But I learned some time ago that "quality of life" is really the goal, and since you spend a big chunk of your life working, "work" needs to be quality time too.

I've been working out of a home office for three years now, and I'm very satisfied with my decision. But I am coping with a few issues, such as having nobody to talk to, and no manager to enforce deadlines or check my output. As a programmer, some of the skills I now possess came from working and interacting with other programmers. As a one-man show, I really miss that peer review, and rely on web forums and email as a pale substitute. (I also catch myself talking to the cat a bit too often, but that may be a symptom of something else.) Another thing I miss: all the free software and hardware you get when you work for someone else's company. And don't forget the added burden of marketing yourself, record-keeping and tax filing. Then there's that start-up period when you're building your business, and you have long stretches of time with no billable hours to show for it. Those can be very discouraging indeed.

There are quite a few challenges with the work-from-home, work for yourself lifestyle - it's risky and sometimes frustrating, but I have no regrets. If you are able to do it, (perhaps with the help of a partner who is also employed,) it is amazing how much simpler and pleasant life can be. Good luck to anyone who decides to make the leap.  Now, if only I could find a way to move the family out to the country...

IF only SumIF could do this...

On a recent project I had the challenge of generating a summary for a large, unsorted, filtered data set.  The model was initially created using the SumIF formula to grab subtotals for a range of cells, based on certain criteria.  Unfortunately this wasn't working, because the summary included rows of data that were not part of the filtered results - in other words: data from hidden rows needed to be ignored.

The Excel veterans out there probably already know about the SubTotal function, which contains a parameter that allows you to include or exclude hidden rows.  This works well as a simple SUM (or count, average, max, min, etc) when working on filtered sets, but does not allow you to include any additional conditions.

Another option would be to add control columns to the data set - something off to the side that evaluates the IF condition, and returns a 1 or 0.  This can then be combined with the actual data using a function called SumProduct.  SumProduct allows you to specify two ranges of numbers, and will sum the product of the items in the first range multiplied by the items in the second range.  Rows that failed the condition would multiply the data by zero, effectively removing them from the total.  This approach, though feasible, required adding a large number of new formulas to the data sheet, and was rejected because the data sheet is being populated regularly by copying and pasting from an external source.  We wanted to keep that sheet lean and simple.

In the end we opted for the power-user's approach: writing a custom function to provide the missing logic.  This is the function: SumIF_X


Function SumIF_X(rngCriteria As Range, _
rngCompare As Range, _
rngValues As Range) As Variant

'new version of the SumIf function
'that ignores hidden rows!

Dim intRow As Long
Dim rng As Range
Dim cTemp As Variant
Dim intTop As Long
Dim strTemp As String

On Error Resume Next

intTop = rngCriteria.Row
strTemp = rngCompare
For Each rng In rngCriteria
  If rng = strTemp Then
    If rng.RowHeight > 0 Then
      cTemp = cTemp + rngValues.Cells(rng.Row - intTop + 1)
    End If
  End If
Next

Set rng = Nothing
SumIF_X = cTemp

End Function

It is designed to mimic the SumIF function, so the parameters are the same: A criteria range, a compare value and a data range.  Using the For Each construct, we cycle through all the cells in the criteria range, and for any that match the compare value (and who's rowheight is not zero,) we add the associated data element to our running total.

It works fine, but there's a potential danger in using this approach: if the data set is really large (thousands of rows), recalculation time for the worksheet will be impacted due to the looping nature of the code.  In our application it was not deemed a show-stopper, but for larger workbooks, or when the custom function is being used in thousands of cells, we would probably seek out a leaner approach (or bite the bullet and set up a pivot table!)

Wrangle Those Names

Excel names are a fine way to store and reference data in a workbook.  Naming a cell allows you to include a readable reference to that cell in your formulas and macros.   Just imagine opening a spreadsheet from two years ago and seeing the formula "=$AG$377*(1+Sheet3!$BB$2110)"... not too meaningful until you navigate to the cells in question and see what's in there.  If, instead, you saw "=Balance*(1+Inflation)", you would instantly know what the formula does.

Names can do much more than simply point to a cell.  You can have a name refer to a RANGE of cells, and then act upon each cell in the range, as in: "=SUM(March_Sales)."  Names can also be dynamically-defined - something I talked about in an earlier post.  Dynamic names aren't simply linked to a particular cell, but are evaluated when the workbook is calculated.  For example, a dynamic name might refer to a member of a list, who's position is determined by some other cell's value.

Names can (and sometimes do) get ugly - especially when they refer to cells that have been deleted.  In this case, you end up with a name that evaluates to an Error reference instead of a value.  Tracking down these errors can be very difficult, because Excel's name management controls are, unfortunately, a bit rudimentary.  You need to go to the Insert menu, choose Name, and then Define.  Then click on each name in the list to verify what it's reference is.  On massive models that have evolved over time, with thousand of defined names, it can be a multi-day task to clean these up.

Another major maintenance headache is tracking down cell formulas that refer to cells in other workbooks.  Sometimes, when you copy from one workbook to another, you don't get the data you want but rather some indirect reference to it's original source.  This is occasionally desirable, but in my experience it is usually a hassle that confounds users, causes unwanted popup dialogs, and burns programming time.

To better deal with these issues, I created the Name Wrangler.

Name wrangler is a simple macro that lists and cleans up names in my workbooks, and identifies all external references in formulas.  The code resides in my Personal macro workbook, and I can invoke it on the active workbook when it's time to clean it up.

The Name Wrangler inserts a blank worksheet in your workbook and generates two tables: all the defined names found, and all formulas that refer to external files.  Names that evaluate to errors are then flagged and deleted.  I use this all the time, and it has saved me a lot of wasted hours scanning through workbooks, tracking down annoying errors and references I don't want.  The first thing I do when a client sends me a large model is run the Name Wrangler to get a handle on what I'm dealing with.

I have packaged the Name Wrangler in a workbook, which you may download here.  Feel free to use this code any way you like.  I welcome your suggestions and comments.

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.

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?

Insulate for Winter

When your VBA method (macro) needs to get data from a section of the workbook, there are many ways to achieve this, and most of them will perform quite well. But what happens when somebody tweaks the spreadsheet? When a user adds sub-totals or a new column to the worksheet, will this require a macro fix?

Developers who would rather avoid this type of distraction, (preferring instead to invent new and interesting applications,) attempt to Insulate their Code from the Interface.

In the case of Excel applications, the interface refers to everything that Excel is - the columns and rows which users tend to move around and delete, the Excel command set: filters, sorts, etc.

One strategy is to declare a Range variable and assign it to an known position - the data table's first column heading is a good one. (Use a named range, which will follow the actual cell around.) Your table scan happens relative to this anchor point; made easy using the Offset method:

dim rng as Range
dim tbl as Range
dim intRow as Integer
dim AmtCol as Integer

Set rng = Range("data_corner")
Set tbl = rng.CurrentRegion
AmountCol = Range("AmountCol").Column - rng.Column

For intRow = 1 to 20
   If rng.Offset(intRow,AmountCol) > 99.99 Then
     ... something happens!

To make this really bullet-proof, don't hard-code any assumptions about the table layout. Gold-level protection requires naming a Range on the worksheet for each column heading you need to work with (the AmountCol in the above example). Now the users can move the individual columns, insert new ones, or relocate the whole table, and your code continues to work.

Named ranges not feasible? Then you should have a routine to search for the actual column headings and cache those positions in variables before working on the table. This is reasonably reliable, until the headings get changed.

The quick and dirty method is to assume the positions of the columns will never change, set up constants for those so the code is at least readable, and hope for the best.

DNF to the fool who hard-codes an actual cell address in their VBA code!

Winning Strategies for Success

This post is written mostly for Excel developers who work as consultants in the Freelance realm. When you engage a new client, there are a few things you can do right to ensure success, which comes in the form of repeat business. Here are my recommendations:

1. Contain the Scope
The client has a specific business problem, and needs your help solving it. Obtain crystal clear agreement on what the problem is and what the solution must contain - in writing if possible. Don't throw on a bunch of extras because you think they are needed or interesting - that's another project.

Scope Creep occurs when the client (or you) ask for extras and you include them in a project that is already underway - suddenly the finish line you all thought was there has moved. Out the window goes your estimate and the client's acceptance of it. Save up any add-ons for a phase 2 deliverable.

2. Provide Good Value
The client is taking a considerable risk hiring you, both in money and time. Aim to break-even on the first job if you think there's a chance of follow-on work. Come in too high and you either won't get the job, or if you do, the client won't feel like they got their money's worth. The client's capacity to pay is a factor here: Citigroup won't blink at that $100/hour rate, but Joe's Accounting probably can't swallow it. Once you have established your value and gained the client's respect and trust, you can safely pad your quote with a reasonable margin - we all have to make a living!

3. Support Your Work
Is the solution correct? On your machine, it is, but who knows what will happen on the Client's network. Don't deliver your code to the client the day before you go on vacation - until they sign-off, consider it untested. Answer questions and address issues the instant they are reported to you. No matter how busy you are, code that has shipped but not been paid for yet is your biggest priority. Give unquestioning support at this stage, and you cement yourself as a committed partner to the client's business.

Watch out! What the client calls a bug may be something they forgot to ask for up front... tread lightly as you educate them on what is "in scope" and what is a phase 2 change request.

4. Design for Scallability
Why write an import routine when, with a small amount of extra effort, you can write an import engine? The data requirements or inputs of any project will change over time. If you need to dig into your old code and update a bunch of cell references every time this happens, your solution becomes a liability to both you and the client.

All layout information used by your code should be read from a setup table. When the particulars change, you simply update the setup values and you're done - or get the client to do it himself. You can focus on real work and the client doesn't get nickled-and-dimed on the maintenance. Even better, the likelyhood of your code being reusable goes way up, and reusing code is all margin baby!

Finding Fault

Using the FIND method in your VBA macro is a good way to locate a specific piece of data in your workbook, and can be much more efficient than looping through an array of cells. For example, say you have a massive table of data, but each record is identified with a unique key of some kind. The fastest way to get a reference to a specific record is with FIND:

Set rngSearch = sht.Range("B1:B5000")
Set rngFind = Nothing
Set rngFind = rngSearch.Find(strKey)
If Not rngFind Is Nothing Then
   Result = rngFind.Row
End If

Unfortunately, this method can let you down if you're not careful, due to it's association with the Excel FIND menu command. The problem is that the function contains a bunch of optional parameters for which the default value is not predictable. As the VBA help file states:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you donít specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

The above code might work perfectly for years, but then the user of the spreadsheet, on some totally unrelated project, uses the Find dialog to look for a number format or something. Now the Macro stops working - key values aren't being found. Depending on the data and what you're searching for, you might not even notice the problem, and simply think that the data isn't there... Maddening!

So, to avoid surprises, be sure to explicitly define ALL of the method's parameters EVERY TIME. The above code, rephrased, should look like this:

Set rngSearch = sht.Range("B1:B5000")
Set rngFind = Nothing
Set rngFind = rngSearch.Find(What:=strKey, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not rngFind Is Nothing Then
  Result = rngFind.Row
End If

Keep 'em Separated

If you develop Excel macros that will be used by others, then you can be pretty sure those users will ask for fixes or additional features at some point in the future. If your users have done anything to modify the Excel file you give them, then you run the risk of clobbering their work when you send the next update.

Consider this example: A Purchase Order system developed for a retail store uses some sophisticated lookup formulas - pick Comnpany A as the Supplier, and only products from Company A show up as choices. It's a great time-saver for the end users, and helps reduce data errors, but the formulas rely on lookup tables imbedded in the workbook. These lookup tables need to be updated occasionally as suppliers change their lines.

If there's a bug / feature update, the programmer must either: A) get the latest copy of the user's workbook before making the change, during which the user can't do any edits, or B) give the user the updated workbook and ask them to reproduce all their edits since the last update, or C) Sync the data manually (yourself - egad!). All situations are problematic. The accepted solution is to keep the data (that's anything the user controls) and the macro code (your stuff) completely separate.

There are many ways to achieve this: you could get the workbook to connect to a corporate SQL database, link the workbook to an external file, grab the data from a Web Service, or provide an import routine of some kind. There are pros and cons for each option, but in situations where an enterprise SQL Server is not available, an external data file is usually employed.

Excel provides a built-in mechanism to actively link workbooks together. This usually works... but to minimize complexity (a guiding principal) I try to avoid live file links - I've seen too many broken or corrupt links over the years, and nobody likes those popup messages.

I generally opt for passive linking. This is where a macro is used to import the external lookup data when the work process is initiated.  The needed data is copied to the local working file (your macro workbook.) All calculations and macros work on the local copy of the data, which avoids external formula references and file links, and keeps the application file portable.

Usablity Tip! You should cache the path to the data, but give the users an easy way to find the file if it moves using Excel's standard file dialog:

Dim sFile As String
...
sFile = Application.GetOpenFilename("Excel Files (*.xls),*.xls", _
        1, "Select the Data File", , False)
If sFile = "False" Then Exit Sub 'user clicked cancel
Range("Data_File") = sFile
...

Data, by it's nature, changes all the time - it could be the output from some other system, or part of a growing transaction log. Wherever it resides, that's no place for your VBA code or complex, inter-locking formulas.

Passive linking will allow you, the programmer, to maintain ownership of the code while allowing the users to control the data. Improvements to the application are painless for both parties, allowing it to stay in use (and useful) for a long time.

Dynamic Names

One of Excel's most powerful features is the ability to define Names. A Name can refer to a single cell, a range, or a calculated value. You can even define a dynamic Name that refers to a changing range of cells.

You might need to refer to a list of items in your code, but you don't know how big the list will get or where someone might move it. To automatically sync the named range to the data, define a dynamic Name using the Insert -> Name -> Define menu, and this dialog:

The Refers To: field contains a couple of formulae instead of a cell reference, rephrased:
=OFFSET($B$2,1,0,COUNTA($B$2:$B$1000)-1,1)

The Offset formula defines a corner cell, how many rows away, columns away, how many rows to include, and columns to include.  

For the corner cell use the table header, not the first data item, because somebody might delete that cell. The CountA function in the How Many Rows parameter counts non-blank cells in the column.  The heading cell is included in the count, so subtract 1 from it's result to give the actual number of rows in the range.  This example sets an arbitrary limit of 998 items - your knowledge of the likely dataset and any use of the worksheet below the data table are factors in specifying this value.

Blank rows within the data would be a problem, so if that's a possibility, you can nest a few more functions to detect the range of cells you want... but it does get messy and there's no function help when editing names in the dialog, so be warned.

Anyway, now you can use that Named Range in code...

Dim rng as range
For Each rng in Range("Customer_List")
... some code
Next

And you're not putting any actual cell addresses in there so the code stays clean and safe.

An important factor when defining a Name this way is that your Reference formula needs to be safeguarded against possible edits to the worksheet. If a user deletes a cell which is part of the Name's RefersTo property, that Name could break, resolving to a #REF error for ever more. Good Excel programmers anticipate this possibility and set up Names (and Formula references) for maximum durability, helping workbooks live longer.

Object Non-Existence Check - Trap

When you're writing code in VBA, you should stiffen up and prepare for trouble when checking for the NON-existence of an object. Why? Because this might actually trigger an error you weren't expecting or handling.


Consider this code which operates on Excel's menus:

On Error Resume Next
If NOT MenuBars(xlWorksheet).Menus("My App") Is Nothing Then...
  ...some code runs
End If

So what happens when this code executes and the menu we're looking for doesn't exist? Simply trying to reference a non-existant object triggers an error, and the code within the IF block will still run, believe it or not. The "On Error Resume Next" line makes that happen - any error moves execution to the next line, regardless of weather the IF condition was met. A proper error handler would avoid this problem.


Sometimes we can use this situation to our advantage. Lets say we want a set of unique values from a list. I like to use VBA's Collection mechanism with it's built-in Unique Key constraint to make this easy. You'll need an object Class to act as the data container:

'Class Module called "ItemClass" with a single Property
Public Key As String

Then create a collection and try adding everything. Only the ones that are new will be allowed in.

Dim colSet As New Collection
Dim clsItem As ItemClass
Dim rngCell As Range

On Error Resume Next
For Each rngCell In Range("A1:A999")

   Set clsItem = New ItemClass
   clsItem.Key = rngCell.Text

   'this line will simply fail if the key is already in use
   colSet.Add clsItem, clsItem.Key
Next

In this case we are deliberately ignoring the error that is triggered when we try to re-use an item's value as the collection key.

To Sum Up: watch out when your IF condition triggers an error, because all bets are off. You've got to know when to handle 'em, and know when to let 'em Resume Next.