Easy Dashboarding

You can add visual pizazz to any report with a simple bit of logic and the Wingdings 3 font.

In this example, an expense statement compares one family's household spending to the standard working family.  There are several items which could be lower, higher, or the same as the standard ratio.

Showing the numbers side-by-side is an effective way to highlight this information.  Adding a graphic goes even further, literally illustrating the data.  But linking an image to data in a cell is pretty tricky, which is why Excel makes charts so nicely.  Charts can be overkill sometimes, while I try to keep things as simple as possible whenever possible.  To which, this is precisely why Microsoft added the Wingdings fonts to Windows.  This formula...

         

=IF(c2>d2,"r",IF(c2<d2,"s","D"))

...evaluates to either an r, s or D, depending on a value being higher, lower or equivalent to another value.  These characters, rendered in the Wingdings 3 font, give an "up", "down" or "equality" pictogram which shows up nicely in the spreadsheet, in line with the data it illustrates.  Here's how it looks (click to enlarge):

Depending on your data set, you can go further: applying conditional formatting to add colour based on some condition, or displaying a temperature gauge for "corporate heat" levels.

There are many hundreds of interesting symbols built-in to the standard font libraries on your computer.  Explore the choices visually by menuing to Insert...Symbol and paging through the lists in the dialog.  Just pick the symbols you think will best exemplify the "at-a-glance" impact of your findings, figure out what their letter-equivalent characters are, and sub those into your nested-if formula.  Your reports will pop, your workbooks will stay light and lean, and accolades are sure to follow.

Programming with Class... Modules

A recent discussion on LinkedIn asked: who is using Class Modules, and for what purposes? Answers were either something like "Class what? Never heard of it." or "Couldn't live without 'em."  Rather than post my obscure personal opinions there, I post them here...

To put it very simply: a class is just another type of variable.  Variables, as you know, act as containers, designed to hold a piece of data of a certain type.  So in vba if I type Dim intX as Integer, I'm defining the variable intX and telling the computer to set aside some space for a number I'll be tracking.  Then, when I type intX = 32,765, I'm putting a number in that container.  I can pull it out later and do something with it, as in: Cell(1,1) = intX/8.

A class takes this storage of data to the next level, allowing you to build a complex data structure to hold many different types of data.  A class can (and should) also contain logic pertaining to itself - code that resides in sub-routines, functions, and class properties.

To help visualize the distinction between a simple variable and a class, imagine we're trying to track a list of team members for a payroll application we're building.  Each team member has a variety of information that distinguishes him or her from the other people: Name, DOB, Job Title, etc.  By creating a "Person" class in vba, with methods to handle the storage and retrieval of each of these personal attributes, we can keep each person's details together in memory, and move those details around in fun and also in beguilingly complicated ways.

Object Variables

The class module in Excel's code editor is where you define the attributes of a class - its name, methods, properties, and so on.  Elsewhere in your code you create Instances of the class using an object variable, as in: Dim objTM as clsTeamMember and Set objTM = New clsTeamMember. You can create many, many instances of a class, just like there can be many, many integer variables, all instances of the Integer data type.   

Collecting Objects

Once packed up into an object variable, the class can join others like it in a Collection.  A collection is like a list or stack that can then be searched, summarized, or iterated using the For Each... construct, as in: For Each objTM in colMembers.  Collections are, for me, the best reason I can think of to use class modules in vba.

Collections can be used to generate a unique list (as I demonstrated in an earlier posting), or to fill pick-lists in user dialogs, among other things.  A class can even contain a collection as one of it's properties, allowing you to create nested collections of collections, without limit. [Tip: Always set your object variables to Nothing when done with them to avoids those pesky memory leaks!]

But why would you need this?  That really is the question.  Most of what you can do in Excel's memory can be done in a worksheet's cells.  But pulling your data into memory to manipulate it there is much faster and, I find, more interesting, than copying and pasting onto worksheets.  Knowing when to apply object-oriented programming techniques is largely a matter of gut feeling, which comes with experience.

Personally, when building something like a reporting engine, I often define in-memory collections containing the data I'm trying to summarize.  It's then quite easy to make new reports by writing code against the classes and collections I have set up.  Effectively, I think of classes and collections as a giant universe of infinite worksheets in the sky, to conjure or disperse as needed...  

Try to read up what you can on this topic, and chip away at understanding until you begin see the light.  I recommend diving in and adding a class module to your next vba project.  This is, after all, an essential step in the road from Macro Recorder to Power Programmer.

Staying Tethered

It is now very easy for the mobile worker to produce billable work while on a ski holiday.  I'm not referring to some sort of client schmooze, or a seminar in Aspen on behalf of some large corporate employer, though these are both highly respected and sought-after ways to get paid by just about anyone who skis.   No, what I mean is that, using the technology now available, a consultant can keep up to date on email, answer calls, work on code and deploy software from virtually anywhere in the world thanks to 3g Cellular data networks and powerful, portable computers.  In my case, a Macbook Pro and an iPhone.  

Hands down, my favourite thing about the iPhone is the Tethering feature.  This allows the phone to act as a wireless hotspot for my laptop.  It's actually quite fast - averaging about 2 Mbps download, which is only slightly slower than my home dsl service.  Where I live (Toronto, Canada) my cell carrier (Rogers) requires that I subscribe to a 1GB data plan in order to use the Tethering functionality.  This runs me about $65 per month.  

It's dead-easy to use.  On my Macbook, I just click on the bluetooth icon, scroll down to "iPhone", and select "Connect to Network".  In about 10 seconds, I'm on-line and doing anything I can do in the office.  I'm using virtualization software to run Microsoft Windows XP on my macintosh. I believe it is possible to obtain the same connectivity using a Windows pc, but I can't speak to the effort of setting it up.

Independent Consultants who are any good provide quick response to phone calls - new business prospects move on if you don't follow-up instantly.  Existing customers expect a certain level of support.  Installed code needs to work.  If your macro's break down during report crunch time, and you can't get to it until next Monday... ugh. it's a major falling down and this can impact other people, cost you business, and destroy your credibility and self-esteem... well, maybe not all that, but it depends on the standards you set for yourself.  

With the right tools we can avoid touchy situations and still do the things we love: An email comes through on the chairlift at 10:30 am, asking for help.  You bomb back to the cabin for a 20 minute bug fix, uploading the 10MB file to your grateful client.  You then fire off a quote for another job that came in, and slide back to the slopes for a few more runs before Lunch.  After the skiing (or wake-boarding, or golfing, or whatever sort of lifestyle you're trying to include in your life) is done for they day, you might put in a few hours more before dinner just to keep on top of ongoing projects.     

In olden times, before mobile office technology, we had to work for companies and book annual vacation time, during which we forgot about work for two weeks and let other people take care of things.  But then for the rest of the year we had to show up every weekday, right on time, and stay there all day long.  Times are changing.  

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.