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...