More New Beginnings

A while back I spoke disdainfully about certain job-style choices that many among us have or haven't made, and in re-reading it, my lament seems harsh.  Especially now that I find myself back in "cubeland", wardrobe and all.  That's right, I have accepted a contract to work at a major downtown corporation - after a decade in the side-streets of business and working for myself.

Sell-out! they exclaimed, from their sofas, in their robes, with their cats.  Perhaps...  But I must explain some important motivating factors before allowing this judgement to stand.

Firstly, when I embarked, years ago on my solo career mission, I underestimated the importance of working with peers.  As a software artisan, one must never stop learning, as there is such an incredibly vast galaxy of things to know.  Learning from the inter web gets you only so far.  You need live minds around you, working together, to get the best solution out of a problem.  This is why code builders often assemble into teams.

I've written and shipped a lot of code as a one-person company.  I'm proud of all the work I did and the people I helped.  (There's nothing like the smile you get when you show someone how the days of tedious data shuffling she had to do each month will now take 5 minutes.)  Yet, as the projects piled up, I started to notice a pattern in my solutions - all Excel apps, of course - and began to wonder if some problems might actually be solved better by, say, a web app? Or some technique I just didn't know existed.

Having worked on software teams before, I sometimes longed for a session where I could white-board my ideas with some other "dudes", and stride out of the room absolutely confident that the design was right.  As I crafted my clever report engines and whatnot, I saw the internet evolving and getting more powerful as a software platform.  I began to hear about things like Sharepoint, jQuery and MVC.Net.  I glimpsed the world outside my home-office window beginning to slide past.

Then one day, at a luncheon with an old friend and former work associate, I mentioned my feelings of unease, and also that my billable hours were down significantly.  My friend indicated that his place of work, where he is a manager, was in need of a .Net programmer.  Even though that platform is not my forte, I used to work in it, and my broader experience and problem-solving skills would, my friend said, "more than make up for" any lack of specific syntactical knowledge, which I could learn.

This friend had tried to tempt me before, but this time I wavered.  Thinking deeply, I had to admit that the lifestyle I once described as optimal: working from home, answering to no boss, etc. - had actually started to lose its lustre.  Then I calculated the potential wage increase that regular hours would represent, and that lustre faded down to a dull, carpet-like finish.

Then, for balance, I considered the down-sides of working for "the man" again: a somewhat scary deep dive into new languages and paradigms, (and I'm no toddler.)  Then there's all the office crap, which I think I summed up pretty well in my March 1, 2010 post.  But it has been 10 years since I worked at a corporation - maybe things aren't so bad now.

Well, I'm two months in, and so far not suffering.  In fact, I've been able to make some really solid contributions, especially in applying my Excel skills and experience.  The first project I got into, a data import & cleanup tool, I ran with.  Using my artistic approach and applying my mantra of letting the user design it as they use it, I produced a solution far and beyond anything else they had ever seen.  (Feel the power of my Golden Hammer!)

The client (a working group among thousands of groups world-wide in this massive company) is really, really pleased, and we are now scoping out the next iteration; where I make Excel read data from PDF questionnaires.  There's a massive need, and this little Excel tool has the potential to save thousands of person-hours of data re-keying, for users across the country.  Rumour is that the CEO is even taking an interest in the project!

But that's just 20% of my time.  I'm also working on a "major" project using the new (to me) MVC.Net platform.  My C# and Javascript chops are slowly coming along, thanks to my patient and helpful associates, (and PluralSight.)  But I'm still able to pull my weight, since I'm well-versed in the concepts of relational data storage and retrieval, and SQL programming is a big chunk of the project logic.

Another way I am making this transition more palatable is by riding my bicycle to work.  It's getting cold now, here in Toronto, and the 8km journey is fraught with traffic hassles, but I'm sure the exercise is doing me good.  My building has a secure indoor bike room, and a change room with lockers and shower, so I can still look sharp in my new slacks and smooth shirts when I arrive at the elevator.

If you must, call me a sell-out... if by selling out you mean tripling my income, making strides to improve my knowledge and skills, and getting back to work in the engine room of the economy.  But I feel no shame in my reversal.  Now I am armed with some solid Excel skills that I can bring to a place that is sorely in need.  I will gain experience using the latest developing tools and techniques, and certainly discover new and better ways to help more people.   

Shape Charge

I was recently asked by a portfolio manager to create a new kind of chart, not available in Excel's standard set of chart styles:  a visual stock portfolio using proportionally sized boxes to represent the different stocks and bonds, coloured to show their respective investment categories, like this:

The initial prototype used a thousand tiny rows in a spreadsheet, with merged cells, background colours and cell borders to create the different boxes. This proved to be very difficult to render via code.  It also looked poor when printed, and was slow to scroll.

Looking for a better way, (because there's always a better way) I tried drawing objects.  With VBA you can easily draw a box at the position you specify, in the colour you want, and with whatever text you need inside.  It's very fast, and as I discovered, very efficient.

For a load test, I created a workbook with 100,000 rectangles in it, and the file size was only 48 kb!  And a bonus for the performance-aware: drawing boxes using VBA code is incredibly fast.

There are several ways you can set the background colour of a shape: hexadecimal, VB constants... But giving a nod to some basic design principals, I recommend using the workbook's colour pallet vial the SchemeColour property.  Excel offers a "pallet" of 80 colours which can be used in cell backgrounds, pie-chart slices, text, or shapes.  From a coding perspective, it's nice when your code refers to colour number 4, instead of &H2300FF45&

You can customize these colours, and copy pallets from one worksheet to another.  But there is one small trap to keep in mind when using SchemeColor in your VBA code: The 80 pallet slots could show up as totally different shades when your code is run on another workbook.

You see, Excel files inherit the pallet from the default template on the computer of the person that originally created it.  In my experience, many workbooks are actually copies of copies of some other model, and come with vastly different colour pallets. Colour 17 could be a nice olive drab on the test workbook I created on my laptop, but bright pink on the client's version of the report workbook.

To verify which SchemeColor values my Portfolio report should use, I created a handy macro to drop all 80 choices on a worksheet.  This test matrix is a handy reference when you need to know the colour assigned to each scheme number in the current workbook's pallet.

Below is the function I wrote to draw a sized rectangle in position on my report, as well as the macro to generate the test matrix.  I now generate this colour matrix in any model where I will need to programatically set the cell backgrounds, chart series colours and the like.  Tip: select a blank worksheet before generating the matrix.

Got the chops? Your challenge: extend the DrawBox function to handle light backgrounds where the default white text is tough to read.  Passing a text colour parameter would be one way, but figuring out how to invert it automatically would be plenty cooler.

Public Sub DrawBox(ByRef sht As Worksheet, _ 
                          ByVal lft As Single, _
                          ByVal tp As Single, _
                          ByVal wdth As Single, _
                          ByVal hite As Single, _
                          ByVal bg As Variant, _
                          ByVal strCaption As String)

'render a box using a floating box shape object
 
 Dim shap As Shape
 
'drop the rectangle on the worksheet
 Set shap = sht.Shapes.AddShape(msoShapeRectangle, lft, tp, wdth, hite)
 shap.Select
 
'set attributes
 With Selection.ShapeRange
   .Shadow.Visible = msoFalse
   .Fill.ForeColor.SchemeColor = CInt(bg)
   .Fill.Solid
   .Fill.Transparency = 0#
   .Line.Weight = 1
   .Line.DashStyle = msoLineSolid
   .Line.Style = msoLineSingle
   .Line.Transparency = 0#
   .Line.Visible = msoTrue
 End With
 
 With Selection.Characters
    .Text = strCaption
    .Font.Name = "Arial"
    .Font.FontStyle = "Regular"
    .Font.Size = 8
 End With
 
 With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
 End With

'always release object memory when done
 Set shap = Nothing
 
End Sub

Public Sub BuildColorMatrix()

 'Generate a Pallet test matrix on the active sheet
 
  Dim idx As Integer
  Dim idy As Integer
  Dim intColor As Integer
  Dim shap As Shape

 'build a 12 x 12 matrix of shapes
  For idy = 0 To 11
     For idx = 0 To 11
        intColor = intColor + 1
        If intColor > 80 Then Exit Sub
        DrawBox ActiveSheet, idx * 80, idy * 80, 75, 75, intColor, CStr(intColor)
     Next
  Next

 'always release object memory when done
  Set shap = Nothing

End Sub

Spreadsheets and Risk

Most Excel models start as flat spreadsheets, using formulas and functions to produce results.  The useful ones grow, and take on new responsibilities, and sometimes things get out of hand.

As an example: CompanyZ sells stuff, and needs to figure out when to buy its raw materials.  Factors such as shipping rates, lead time, and sales forecasts must be accounted for.

The purchasing guy draws up a model to calculate the buying numbers for the next three months, cleverly using variables and named ranges to allow for adjustments in the factors.  His boss likes it, and asks him to blow it out to 1 year.

Ok... not too difficult, since the formulas were set up right.  A bunch of new months are tacked on to the right end of the sheet.  Then the marketing guys ask him to break it down by region.  That's not so easy, because this is a brand new DIMENSION on the data.  Here's where a typical business model starts to become a "house of cards", as significant additions are hung onto a basic functional foundation.

As our models take on more and more of a mission-critical role, they expand into dozens of worksheets, with invisible formulas linking everything together.  Some key person understands the unique mechanics of it all, and every Excel jockey out there, (you know who you are,) has a personal spreadsheet-building style, usually self-learned and utterly unique.

Then that person quits or dies, and the model becomes a "black-box" for a while... still in use, still giving answers, but we're not 100% sure how.  Then the business environment changes, the model can't be understood and adapted, and it dies.  The company scrambles to replace it, and a small ripple of chaos rolls through the departments that relied on all those reports and forecasts that are suddenly unavailable.

This situation is very common.  Thousands of companies use millions of complex Excel models for critical business decision-making tasks.  Based on 20 years of experience in the field, and on the horror stories I've read on-line, most of the spreadsheets out there contain errors, and some of those errors can lead to catastrophic results.

To mitigate this risk, companies need to take active steps to manage their Excel assets.  The creation of spreadsheet-based tools needs to be approached the way other business software projects are: with planning, a solid design methodology, controlled testing and documentation.

This will entail extra cost, to be sure.  The required engineering resources may not be available.  But this is a budget question, which boils down to a matter of priority.  When the value at risk is actually computed, (what is the cost of losing this customer? Losing all customers?) the numbers might make sense.

A flat spreadsheet model is fine for one-off reports, or succinctly defined micro-problems that are purely mathematical.  At a certain point, as spreadsheets expand to fulfill more advanced forecasting and reporting duties, the flat spreadsheet needs to be treated as a working prototype for an actual Application, which can be audited, understood, fixed and validated.

Excel is still a viable platform, but complex models need to be assembled methodically, by someone who understands basic software engineering.  Object Classes should be used to abstract the different business entities.  A Relational Database should be used to store the data and intelligently retrieve it.  A Report Engine should be used to produce the output.

Such a system, with it's logic and structure openly visible in VBA code, will not break down and be cast aside, but will grow, prosper, and add far more value to the company than it's original cost.  Any programmer can step in, see what's happening, and make the required fixes or additions, confident that the whole thing won't come crumbling down.

The Hidden Corners of Excel Hold Many Secrets

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

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

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

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

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

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

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

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

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

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

It's what makes Excel development so exciting.