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.

Maintenance-Free Solutions

As a software creator, I like to spend as much of my working time as possible inventing cool stuff and solving tricky problems using my creativity and skill.  But in order to get paid to do this as an independent consultant, one must also handle such tasks as Sales and Marketing (doing that now), Bookkeeping, Documentation, and the dreaded System Maintenance.

By maintenance, I'm not really talking about bug fixes.  Most of the time it's about changing business logic, connecting to other systems, and new user requirements.  

Some developers, and in my experience, most consulting "Firms", put a high value on the downstream Maintenance hours that come with any major software rollout.  To me, this Vendor-centric attitude sounds a bit like planned redundancy, or selling cheap printers so you can lock 'em in to a lifetime of overpriced ink.  I believe in craftsmanship, which means having a more client-centric approach.  And like I said, I'd rather be inventing new stuff than fixing old stuff.

I learned long ago that a bit of planning up front can minimize or even eliminate the maintenance burden.  Now, whenever I design a system for a client, I make sure to include some Maintenance-mitigation strategies in my design.

1. Keep it simple - this is obvious, but basically, the less code you have, the easier it will be to fix, change, extend, etc.  Do you really need a set of routines to populate, show and validate a dialog box, or could the user update the spreadsheet directly?  Bells and whistles are fun and exciting to build, but aren't always necessary.  A terse design will go farther between oil changes.

2. Build an engine - Reports, File Imports, Calculations... if you can build a data-driven structure to handle each of these, it becomes much easier to modify and extend your application.  Here's an example: my app needs to import data from a 3rd party file.  What if the data provider changes the file layout without warning?  If I hard-coded the import logic, it breaks.  But if I build an import engine that tracks the position of each import column, along with a simple screen where this configuration info can be updated, then the user (or I) can just update the config and re-run - job done.

3. Let the User Design it - This is the cornerstone of my software design strategy: I never want to hard-code the look, feel, or even workflow of a program.  If the app warrants it, I set up all the views as blank canvases which the users can modify to suit their needs.  This requires some planning:

• You need to model all of your data entities as object classes
• All lists and reports must use a data-driven layout - nothing hard-coded on worksheets
• In a multi-user environment, Login accounts let individual users configure the app to suit their personal needs

This is a lot more work up front, but the goal is to avoid those calls asking you to move a field or rename a heading - now the users can do that themselves.  As you start to build a few solutions this way, you amass a library of ideas and code that you can re-use and improve from project to project.

The biggest challenge with this mission can be selling it.  Small budget projects might not warrant this level of future-proofing, and it will take time to educate the customer/boss on the potential benefits. You don't need to mention that the biggest benefit is not being interrupted by minor change requests when you're deep in the creative zone on the next cool project.

Don't give them a fish, make them anglers.

Hard Lessons

In the last post I described the new hardware setup I recommended for a client, to improve a slow and memory-hungry Excel app.  The hardware is in place and the client is happy, but the transition didn't go smoothly.

I discovered that the hardware upgrade in itself was not enough to cure our ills.  After coming up against some disappointing results, despite the bump to Windows 7 and Excel 2010, I was obligated to re-write a big chunk of the data handling logic; something I was hoping to avoid.

My initial research led me to believe that 64 bit Excel has access to as much ram as you can slot into your machine, but this is not my experience.  The spreadsheet part of Excel might be bigger, faster, stronger and fresher smelling, but the VBA object space is not.

Upgrading my VBA code to work in Excel 2010 was actually a non-event.  I only found one line, (related to checking the amount of ram available, ironically) that choked.  Otherwise the macros and formulas all behaved exactly as designed.  Excel 2010 saves files in the Office 2007 file format, so that was my first clue that this isn't really that much of an Excel upgrade.

Then, after seeing Excel freeze, just like before, when my code tried to pre-load six million+ data points into the application's memory structures, I was beginning to think the whole ordeal and expense may have been for nothing.  Further reading on-line found some comments from a Microsoft engineer who wrote that they achieved significant performance gains on Excel 2010's built-in functions, but basically didn't do a thing to optimize or beef up the VBA compiler... that will be in the next release, supposedly.

Perhaps the recent push towards Cloud-based applications is drawing some of the programming talent away from the Excel group at Microsoft.  I'm a developer, so I can't really hold a grudge.  Excel is still a miracle of engineering in my opinion, and the best software ever to run on any computer anywhere. (Yes, I like it.)

Anyway, I had no choice but to re-work the data logic so the application fetches what it needs at report time, instead of pre-loading on start-up.  I made this change, and the app became usable again - even improved, from the client's perspective, because it "starts up a lot faster."  Reports now require additional processing, but the fast new chip and fast ram more than make up for it.  Also, thanks to the solid state hdd, (and possibly Widows7's superior design,) the memory swapping issue has totally gone away.

We didn't get the 800% performance boost that Moore's Law predicted, but our app's data ceiling is now a clear blue sky, and one busy brokerage is crunching numbers with it on a daily basis.  The client is armed with an unbelievably powerful stock analysis tool, and now I know what to expect (and what expectations to instil) the next time a client wants to level up.

Throw More Hardware At It

A very large data set can be taxing on Excel.  On older systems, Excel is limited to 1 GB of ram.  Office 2007 enables up to 2GB.  But opening a workbook that is hundreds of megabytes in size can be an exercise in patience.


A recent project is tracking statistics for 5,000 companies going back for several years.  Some numbers, like Earnings and Dividends are reported quarterly.  Stock Price, which ties into a host of downstream reporting and industry averaging logic, is tracked daily.


Rolling all these numbers together to figure out potential investment strategies requires juggling several million data values in memory.  The data is being housed in an Access database for now, with plans to bump up to SQL server.  But the data interchange is not the bottleneck, it is Excel's ram limit.


My client is running Windows XP and Excel 2003.  I have optimized and re-factored the object model to squeeze every last drop of performance from the platform, but we have hit the proverbial wall.  


As more and more data was captured and the record count approached 4.8 million, some of the long reporting macros began to get bogged down by Windows memory swapping - this is when the hard drive is used as the spillover for physical memory, and it is 100 times slower than normal Ram operations.  To make the application usable, we needed to either reduce the data universe (and thus, the value of the analysis), or implement a hardware and software upgrade.  We're taking the upgrade path.


The new machine is a Dell Latitude E5520, billed as a portable workstation - more pricy than a back-to-school netbook, but with the horsepower to churn through these advanced computations.  (This is, after all, the stuff computers were invented for.)  The laptop upgrade will run Windows 7 64 bit, and Office 2010 Pro, leaving nothing on the table.  Let's see what's in the package:

• Intel Core i7-2620M, 2.70GHz - Four duo-cores, that's 8 processors!
• 8.0GB, DDR3-1333MHz SDRAM - that's fast memory, and a lot of it.
• 128GB Dell Mobility Solid State Drive - THIS is the money.

On the old hardware and platform, one rather large report that computes historical moving averages across industry sectors, can take up to 4 minutes to complete.  If Windows decides it's time to start swapping ram to the hard drive, macro time jumps to 15 minutes.  


Using a solid state hard drive, this won't be a concern as swaps to a ram disc are just about as fast as normal ram.  (and this kind of hard drive allows the computer to boot up in seconds, so I'm told.)  Exploiting the newest 64 bit Windows os and Office suite will allow Excel to access up to 1TB (that's terabyte) of memory.  


The Intel Core i7 is a linked array of 64 bit processors that makes this equivalent to a 90's era SuperComputer costing millions of dollars. Lets see if the performance gains pan out...

The hardware is on-order.  The client is waiting expectantly, as am I.  Next post, I will outline my steps in upgrading the Excel 03 vba code to run in Excel 2010 (if any) and benchmark the new system.  This is a $3,000 blind faith endeavour.  But as the client says, if he makes just one good decision because of the software, it's all paid for! 

Database integration using object classes

This month I am working on a quantitative analysis tool for a stock broker.  He wants to track stock data over time and rank stocks in a variety of ways.  Rather than try to figure out all the different reports and charts we will need to build, we agreed that the tool needs to be completely open-ended.  This means the customer can configure every aspect of the application, including the data fields being captured, the format of the reports, and the formulas used for ranking and classifying the data.

Capturing daily numbers for 5000+ companies requires a robust data storage and query component, so an Access database has been created.  This de-cupling of the data from the program logic is essential for an application of this scale.  Since an Excel project is never finished, the steady stream of updates and fixes can be deployed simply by emailing an updated Excel workbook.  The macro then connects to the Access file on the client's system, loads all the settings and information the client has been inputting and collecting, and configures itself.

Logic was created to handle custom field definitions and custom formulas.  A report designer was also built allowing the operator to layout new reports, control grouping and sorting of data, sub-totals, and formatting.  All this setup data is stored in the Access database.

When the user wants to view a report, the macro loads the setup details from the database, queries the company data tables for the required data, and draws up the report on a worksheet.  To handle all these inter-related data entities, we need to define a set of object classes in VBA.

I created classes for Field, Report, Company, Sector, Industry, Price, and all the other entities that our database is tracking.  At the project level I define Collections which are loaded with the individual instances of each class when the workbook is opened.  So when the user asks for a report, the code iterates through the Reports collection and builds the list of available reports to choose from.

The classes contain the logic they need to interact with the database and with each other.  For example, each class has Load, Insert, Update and Delete methods.  When the user defines a new custom Field, the code creates an instance of the Field object, updates it's properties with the settings the user has input, and calls it's Insert method to write the actual record to the database and insert itself into the Fields collection.

When the reports get generated, the Report object "knows" how to load a sub-collection of the fields that are on the report.  Each Field knows how to fetch the appropriate piece of data and format it for presentation.  If a Field is calculated based on other pre-defined fields, the logic will parse this out and use new Field objects to fetch the individual elements that make up the calculation.

By carefully modelling your objects this way, your code becomes incredibly powerful, because the "work" is distributed across the object model.  Your core routines can be quite lean and efficient, and much easier to read and maintain.  Instead of 20 different Reporting macros, you have one generic macro that produces all possible reports.  And since you're manipulating massive data structures completely in RAM, your code can run very quickly compared to the basic approach of stacking tables of data on different spreadsheets and iterating through cells row by row.

Learning how to model your data using object classes is not an easy skill to master.  I have many years of programming experience on a variety of platforms, including VB.Net, to call upon.  I have also made it a priority to master the design and programming relational databases using SQL.  

Yet gaining this knowledge was achieved by doing.  When you start experimenting with advanced techniques such as object classes and external databases, you build confidence, overcome challenges, and ultimately learn how to produce enterprise-level applications that are extendible, powerful, portable and maintainable.

For more information on these topics, please see some of my prior postings:

• Using SQL and Access
• Excel as front-end to an Access Database
• Using Object Classes

An Unlikely Speed Boost

I've been programming with Excel since version 1.0 and I've picked up a few trick along the way.  Some are passed along from other power programmers, and some I just discover, like this one:

I have a reporting system that is generating a 5000 line report on a worksheet.  It runs in two modes.  The first mode fetches the data from an Access database into memory, and then loops through the memory collections to draw the output.  The second mode simply draws the output, assuming the data is already in memory.  Much to my surprise, mode 2 was running a lot slower than mode 1, which is unexpected because mode 1 is fetching and looping through a recordset, hitting twice as many lines of code as mode 2.

To figure out what was going on, I added a progress counter to both parts of the code.  This allows me to watch the macro's percentage of completion as it chugs along.  For no obvious reason, the macro running in mode 1 finished in about 6 seconds, whereas running it in mode 2 took a whopping 32 seconds.  No obvious bottleneck or data differences could be discovered.

I then had a look at how the macros were being called.  Mode 1 was being fired by a change event on a drop-down list control.  Mode 2 is called using a macro button.  I thought that might be the difference, so I tried calling both modes from a temporary macro, and suddenly I'm seeing slow performance in mode 1!  I tried a few variations, but found that it wasn't how the macro is being called, but a subtle difference in the code between the two routines.

My drop-down control's event code had the line:    Application.Cursor = xlWait at the top, followed by the call to the report macro, and finally:    Application.Cursor = xlDefault.  I put this in originally because I thought the extra step of fetching data might cause a delay in the macro, and showing an hourglass reassures my users that something is happening.

When I added these lines to the button click event for mode 2 of the macro, I observed the dramatic speed improvement I was looking for.  I'm already suppressing calculation and screen updating, which makes a big difference on any code that draws to a spreadsheet, but I had no idea that changing the mouse cursor to an hourglass could give a further 400% speed boost!  I will now go back and use this technique on all the models I've created that have lengthy macro processes.

Let me know if you have any other obscure tricks like this - I'm always on the lookout for more macro speed, and I know Excel has many more undiscovered secrets in store for us all.

Check your References

When you are building advanced Excel applications, you occasionally need to call upon logic or features provided outside of Excel. This is done by adding a REFERENCE to an external application or library, by way of the VBA Editor's Tools menu.  Just about every application on your computer shows up here, enabling your code to leverage highly specific functionality such as enhanced user interface elements, or internet data services.

In a previous post I explained how to get Outlook to send email from Excel. The code to do so requires a reference to the Microsoft Outlook 11 Object Library. A recent application I created does this, and also uses several other libraries, including an HTML Browser control to preview PDF files, an Access database, and has a set of Custom UserForms - all requiring project references.  Each one of these represents an actual file - usually a DLL - installed and registered on the host system.

This makes Excel incredibly powerful.  But using this mechanism can complicate the deployment of your software, because you can't always control what is or isn't installed and registered on the end-user's computer. This can also be a problem if you are a stand-alone developer, but need to move your work between home and office machines.  If a referenced DLL or program is not available, you end up with a Broken Reference.

Another coder, Andrew Roberts, blogged about this very problem, and provided a technique to programatically re-add broken references, (for Outlook, in his example.)  It's a good fix, but has limitations.  For one, it depends on a specific Excel security setting to work, which is  probably not something you're going to want to ask 30 novice operators to change on 30 different computers.  And if your project code is password-protected, this approach may not work at all.


Protected Project and Protected References

In my project, I am using the Redemption library for Outlook.  This toolset allows VBA to manipulate Outlook objects quietly, in the background, instead of requiring the user to approve a series of popup warnings shown as part of Outlook's security protocol.

Not all users need this extra level of functionality, so I wrote some code to "ask for Redemption" - users who do not install this extra software should not be show-stopped by a broken reference.  The program should instead default to the standard Outlook experience.

I ran into a major snag: the VBA code that allows you to view and verify project references doesn't work when your VBA project is password-protected - unless the project is open in the VBA editor!

My code needs to be password-protected because of the nature of my programming work - I program for profit.  I don't want every employee at my client's location to freely view and copy the code I have written - this is my sweat and blood, and I try to maintain at least an illusion of copyright protection on it.  I also don't want operators to change or hack it.

Still, some code I share freely: here's a routine you can call from your workbook's open event to programatically inspect the project reference collection.

Sub reftest()
    
   'which references are visible and/or broken?
    Dim chkRef As Variant
    
    Debug.Print vbCrLf & vbCrLf & " - Reference Check -"
    
    For Each chkRef In ThisWorkbook.VBProject.References
      Debug.Print chkRef.Description & "  " & _
      Choose(chkRef.isBroken + 1, "", " - broken"
    Next

End Sub

In the VBA development environment, it all works wonderfully. All the references I added showed up.  But if I close Excel, and then re-open the model, (which calls this routine from it's Workbook Open event,) the references I added to the project become invisible to the code.  All you see are the 4 standard references that all projects have:


VBA   Visual Basic For Applications
Excel   Microsoft Excel 11.0 Object Library
stdole  OLE Automation
Office  Microsoft Office 11.0 Object Library

My code can't tell if the Redemption reference is broken or not, and so can't handle the situation gracefully.

I believe Excel hides the extra References by design - these are part of your code's functionality and overall design strategy, which is the I.P. that your trying to protect by putting a password on it.  However, since it's the code "asking", not a person via the IDE, I think it should be possible to enumerate the full collection of program references.  This would allow the kind of self-diagnostic logic I'm looking for.

I'm not sure what the work-around is for this at present - I'll probably end up using some sort of in-function error trap.  I'll report on my solution in a future post.  I'm open, as always, to suggestions via this blog's comment facility.

One of the really good things about Microsoft Windows and the Office programs is the ability to interoperate and exchange data between applications.  If you want to ensure portability and longevity of your code, just don't get carried away adding 3rd party libraries. 

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.

Shrapnel

I've begun to migrate with some of my clients to the 3 year old Excel.  I'm well past the whole Ribbon controversy - yet I still get a sense that the new ship is still a bit leaky.  Office has thrown a few curve balls at me, so here are some cheap and effective tidbits of advice I can pass along:

Don't... 

...create pivot tables in Excel 2007 and then save the file as a 2003 compatible .xls - it all goes to hell.


...try to draw a flow chart in a Word 2007 document using AutoShapes.  Visio is better if you have it.


...run untested Excel VBA code that references the MS Word object model without setting up error handler's first.


...insert a PDF object into a document that may end up being printed to PDF.


...dispair.  No matter how low the economy goes, businesses will always have spreadsheets.


Cheap and Effective

I've been working on a B2B application - a variation on the standard "request for quote" problem.  In this case, a request is assembled from data on the spreadsheet, dropped into an email, and sent to a slew of individuals somewhere who may not even have Excel (poor sods.) They hit reply, fill in some sections of the copied message, and send.  Another macro connects to Outlook, scans a particular in-box, and pulls the details from any messages found as they come back.

This is, like my advice above, cheap and effective.  It's a way to do email forms with no workgroup software or exchange programming.  It doesn't involve sending or sharing files, and is not limited to the corporate LAN.  Data can even be collected electronically from people who aren't sitting at their desk, working, like I am always.

Hey, isn't this what websites are for?  Um, yeah, but this approach bypasses the need for website programming - no offence to web programmers (since I used to be one,) but power macro artistes deal in turn-around times of hours, not weeks, and the data needs to get back to Excel in the end anyway, right?  
 
Excel talks quite nicely to outlook.  Here's some code that sends an email message - it's really dead easy as long as you set up the appropriate references.  Perhaps the next post can explore the response capture side of things.

Public Function SendAMessage(strSubject As String, _
         strBody As String, _
         strAddress As String, _
         Optional strAttachment As String = "") _
                As Boolean

 'strAttachment, if used, will be the full 
 'file path of a file
 'requires a reference to the Microsoft 
 'Outlook 12.0 Object Library
 'see Tools Menu - References
  
  Dim email As Outlook.MailItem
  
  On Error GoTo Fail
  SendAMessage = False

  Set email = Outlook.CreateItem(olMailItem)
     
 'make sure we've been passed sufficient info
  If Len(strAddress) = 0 Or Len(strSubject) = 0 _
  Or Len(strBody) = 0 Then
  
    'trigger an error
     Err.Raise 999, , "Missing Information"
     
  Else

    'assemble the message
     With email
        .Subject = strSubject
        .To = strAddress
        .Body = strBody
        
        'attachment if required
         If Len(strAttachment) > 0 Then
           .Attachments.Add strAttachment
         End If
        
        .Send
        
     End With
     
    'it worked
     SendAMessage = True
  
  End If

Cleanup:
  On Error Resume Next
  Set email = Nothing
  Exit Function

Fail:
 'it didn't work
  gGlobalError = Err.Description
  Debug.Print "SendAMessage Failed. " &_
               Err.Description
  Resume Cleanup
  
End Function

Back to Business

This post is so long overdue I'm almost ashamed. If I'd had the entire summer off, that would be one excuse, but the real reason for the delay was that I've been too busy writing code to write anything else.  Okay, I will admit to enjoying a reduced workload in July - 20 hours instead of my usual target of 120!  In any other month this would be a catastrophe, but I try to keep July work-free, as a personal reward for the year's efforts.  That's one of the principal advantages of being self-employed, after all.

As mentioned in a previous post, I landed a return engagement with the company for whom I created an Excel front end to an Access database.  Another department within the company saw how effective the first application was, and hired me to build something similar, to handle their Purchase Order processing workflow.  This has kept me busy for all of August.

The business logic was a lot more complex for this project, but I was able to re-use quite a bit of the architecture from the previous application: User Account Management, database connectivity code, list filtering and sorting... There's nothing like the efficiency and confidence you get from writing code around a proven framework: instead of messing with the fiddly bits, I was able to focus on adding business functionality and making the software friendly and usable.  Client's don't generally get excited to learn that the TCP/IP bottleneck has been optimized, but they are always thrilled to hear about the one-click report engine you've designed!  This project is currently in testing, and so far, the reception has been 100% positive.  I aims to please.

As a challenge to myself, I decided to do this development entirely in Excel 2007.  I usually do my work in 2003, since I find that version of Excel more responsive and pleasant to use.  If the client will be running 2007, the "xls" file from Excel 03 usually just works.  But this time I opted to skip 2003 altogether, to see what would happen.  To my surprise, I found I was able to make the transition with very little pain and suffering.  

It took me a few days to really get the hang of using the Ribbon - I still find myself hunting around for some Excel features, but the learning curve was not as steep as I had predicted.  The benefit is that the file is running "native" in Excel 2007 - no "compatibility mode."  I'm not sure if this is a real advantage (other than looking cleaner on the Excel title bar), but at least I'll know that, if some strange bug or behaviour pops up, it's not an artifact of the file conversion process.  More importantly, I am now more efficient using and interacting with Excel 2007, and feel comfortable porting my personal spreadsheets and Client work to this platform.

Looking forward to the Fall, it appears I will continue to be very busy for the foreseeable future.  Since starting out as an independent consultant 4 years ago, I have been slowly building my roster of clients.  Many projects are one-off, where I go in, figure out what needs doing, write the app, and that's more-or-less the end of the engagement.  But several of my clients are power Excel users who are constantly looking for new short-cuts, utilities, or techniques to apply to their daily business activities.  

As I add more and more of these types of clients to my roster, I'm given an increasing load of "casual" hours - short fixes, mini projects, and extension of existing work.  This ad-hoc stuff is taking up enough of my time that it effectively fills in the gaps between bigger projects.  It has taken a few years, but now I have the business humming along perfectly - working enough to pay the bills, but not so much that I'm overwhelmed.  If that starts happening, I'll just start raising my hourly rate.  Everything is going according to plan.

Applying the Application Object

VBA coders who want do do more than automate the basic Select, Copy and Paste functions of Excel eventually discover the object model, and the world of possibilities it brings.  One object that is sometimes overlooked is Application.  Here are a few examples of how we can use the Excel Application object in our VBA code.

One of my personal favourites is Application.StatusBar.  The status bar is the strip of real estate at the bottom of the Excel window where you see system messages and so forth.  Programmers can write to this area, which can be an intuitive and logical way to keep your users informed about what your macro is doing.  As explained in a previous post on Speed Optimization, if your process is long and involves a loop, periodic updates to the status bar can tell the user how far along the process has advanced.  I usually use a percent complete message, as exemplified in the following code:


For idx = 1 to intNumRows
   {... code does something here....}
   Application.StatusBar = "Running... " & _
   CInt((idx/intNumRows)*100) & "% complete"
Next


Given this feedback, the user can predict how long the macro will take to run, which actually makes it appear to run faster.  More importantly, users can see that SOMETHING is happening.  Its also nice to finish up with a parting word...


Application.Statusbar = "Calculation Complete."


Another really important use of the Application object is the WorkSheetFunction method.  This allows you to execute any function that would normally only work when placed directly in a worksheet cell.  For example, say you had a VBA process that needs to take an average of a bunch of cells.  You could loop through them and add each cell's value to a running total, then divide by the number of items, or you could use the built-in AVERAGE function, as in:


varResult = Application.WorksheetFunction.Average(rngRange)

I ofetn use this approach when trying to find the position of a value in a list, because the MATCH worksheet function has no equivalent in VBA.


intPos = Application.WorksheetFunction.Match(varVal, rngRange, 0)

What if you have written a custom function and you need to know what cell that function was entered into? This is important, for example, if the function must refer to other values on the same row or in the same column.  For this, we use Application.Caller, which returns a RANGE reference to the cell where the function was "called" from.


Function MyFunction(N as integer) as Integer
 'use whatever is in the Nth column of the row in which
 'this function is placed as an argument for another function
  intRow = Application.Caller.Row
  MyFunction = MyOtherFunction(Cells(intRow, N))
End function

Another very powerful method is Application.Run.  This allows you to specify the name of another macro as a text string, and execute it, along with up to 30 arguments.  Why do we need this?  Let's say we are working on a model that encodes a large set of business rules.  We might have dozens of macros that do specific things depending on what may be contained in the data.  Using the Run method, we can assemble the name of our macro in code based on changing data conditions, and then run the code.  I have used this in situations where macro names reside in a database, allowing me to change the behaviour of the application by modifying data.  This facility comes close to enabling Self-Modifying Code, which any old-school programmer will tell you is the holy grail of power in any language that allows it.

There are many many more powerful features available to the VBA programmer by way of the Application object.  Just open your Code Editor and type "Application.", and observe the miriad of methods and properties that the IDE exposes for you.  With a bit of experimentation I guarantee you will discover a way to do something you never thought possible in VBA.  Random code exploration is the gateway to advancing the art.

Excel in Front, part 2

Project Update: I'm happy to say that so far the Excel Front End project has been as smooth as glass.  No only were the client's expectations exceeded in every regard, but my personal experience writing the application has been immensely satisfying.

If you recall from last post: I was hired to design and build a work-order tracking system for a manufacturing facility, using Excel as the front-end to an Access Database.  When I set out on this project, the main challenge was that the client's shared workbook approach was causing a lot of trouble, and undermining their confidence in the data.  They asked for my help, as an Excel consultant, to sort out the file sharing issues and build in some business logic and data validation rules.  

I proposed two solutions: keep the shared workbook but apply sharing permissions effectively and write the required macros to ensure data integrity, or the more involved, expensive option: move the data to Access and create an intelligent front-end in Excel so users can interact with it.  They opted for the Access option, understanding the scalability advantages, and the cost savings they will achieve when the time comes to build out some of the other data management applications they need.

I developed a vision for how the system should work: no shared workbooks, no linked files, no open connections, and especially: no Access, as in, no developing in Access (because, well, it's crap.  Sorry, but I hate it.)  I have linked Excel to Access before, so I knew how to do it, but I didn't know if this architecture would perform acceptably with this particular set of business logic and data until I actually built it.   

Typical client-server apps use a persistent database connection.  This is fine when using an Enterprise RDBMS such as SQL Server, but in a small-scale deployment using an .mdb file, I just knew that trying to simultaneously connect 20 or so users to a shared Access db would be a recipe for failure.  So I designed the system to interact with the database in instant-batch mode: need some data? Connect to the db, fetch the data into memory, disconnect from the file, then display the formatted results.  This way, no single user has an open connection to the access file for more than a fraction of a second at a time, virtually eliminating any chance of network traffic bottlenecks, record-locking stalemates, and (hopefully) database corruption.

The following are some key design points I employed:

• Created a de-normalized view of the data, by way of an Access Query, which joins the related data tables to give a fully-qualified view of all Work Orders in the system.  This Master Query becomes the main Result Set that the Excel side queries from.
• Rather than code separate reports for all the different needs of the client, coded a Report Engine that can be configured as needed, enabling all current and future reports to be designed without programmer intervention.
• Matched the field names in the Master Query to the column headings used in Excel.  This allows the user to build their own filters and searches by selecting from known field-names.  
• All user-definable settings are stored in the database, keyed to the user's login.  This way, when the Excel front end file is updated, it can reload all the user's settings and they can continue to work without any setup time.
• Used generic database SELECT and EXECUTE routines, so all db-related code passes through a single gateway.  If we later to decide to change our Database server, we won't have to search through hundreds of routines to change all the affected code.
• Captured cell-edits using the Worksheet_Change event.  Updates are validated, and if allowed, instantly written to the database.  Cell formatting and locking are applied as the data is displayed, so the users know (and are only allowed to edit) the editable parts of each record.

Amazingly, this project came together without a hitch.  The incredible flexibility and programmability of Excel enabled me to build every requirement the client asked for, and since I didn't get bogged down searching for bug workarounds and advice, I had time left in the budget to add some really sweet features to the application.  Next Tuesday we deploy the app to the users, and they are really looking forward to the improved efficiency and productivity advantages they will be getting.  And the client has asked for help with a second project for another division in the company, which is obviously very welcome: follow-on work is a sure sign you're doing a good job.

Unfortunately I can't share many more details about this application because I have signed a non-disclosure agreement, which binds me to keep any client-specific or business-specific information secret.  But here is some code which I have used in this and other projects: I keep this in a code module called "DBConnectivity". It is offered without warranty, but it might come in handy for anyone attempting a project that connects Excel to an Access database file.  This code references the Microsoft DAO 3.6 Object Library, so you will need to create a reference to that library in your project.

Public DB as Database

Public Function OpenDBConn() As Database
     
  'Opens the database connection using global db object
     
   Dim strDBName As String
   
   On Error Resume Next
   Err.Clear
   
   Set OpenDBConn = Nothing
   
  'get DBName from cell on main worksheet
   strDBName = Range("DBPath")
   If Len(Dir$(strDBName, vbNormal)) = 0 Then
    gglobalerror = "The database could not be opened. " & _
          "Please select the database file on the " & _
          "Main Tab and try again."
      Exit Function
   End If

   Set OpenDBConn = DBEngine.OpenDatabase(strDBName)
   If Err.Number <> 0 Then
      Set OpenDBConn = Nothing
   End If

End Function


Public Function SQLSelect(ByVal strSQL As String, _
       ByRef sList() As String) As Integer

'Execute the passed SQL (strSQL) and return the 
'retrieved results in the passed array (sList)
'this method is coded for the Microsoft DAO 3.6 
'library.  modify this and SQLExecute
'routines if migrating to another data provider, 
'such as SQL Server.

  Dim rsList As Recordset
  Dim iColCnt As Integer, iRowCnt As Integer, 
  DIM idxRow As Integer, idxCol As Integer

  gGlobalError = ""
  On Error GoTo Err_SQLSelect

 'try db connection.
  SET DB = OpenDBConn
  If DB is Nothing then Goto Cleanup

  Set rsList = DB.OpenRecordset(strSQL, _
                                dbOpenSnapshot, _
                                dbReadOnly)

 'check for no data
  If rsList.EOF Then
     SQLSelect = 0
     GoTo Cleanup
  End If

 'Size the array
  iColCnt = rsList.Fields.Count
  rsList.MoveLast: rsList.MoveFirst
  iRowCnt = rsList.RecordCount
  ReDim sList(iColCnt, iRowCnt) As String

 'store recordset rows in array
  Do Until rsList.EOF
    idxRow = idxRow + 1
   'Load values for the row
    For idxCol = 1 To iColCnt
      If IsNull(rsList.Fields(idxCol - 1)) Then
        sList(idxCol, idxRow) = ""
      Else
        sList(idxCol, idxRow) = rsList.Fields(idxCol - 1)
      End If
    Next
    rsList.MoveNext
  Loop

SQLSelectExit:
  SQLSelect = UBound(sList, 2)

Cleanup:
  On Error Resume Next
  rsList.Close
  Set rsList = Nothing
  DB.Close

  Exit Function

Err_SQLSelect:
 'On Error Resume Next
  gGlobalError = "Select from the database failed!  " & _
    " & Err.Description & vbCrLf & " _
    SQL: " & strSQL
  Debug.Print gGlobalError
  SQLSelect = -1

  Resume Cleanup

End Function


Public Function SQLExecute(ByRef strSQL As String) _
 As Integer

 'Execute the passed SQL statement contained in the 
 'string strSQL
  Dim strErr As String

  SQLExecute = -1
  gGlobalError = ""
  On Error GoTo Err_SQLExecute

 'try db connection.
  SET DB = OpenDBConn
  If DB is Nothing then Goto Cleanup

  DB.Execute strSQL, dbFailOnError
  SQLExecute = 1

Cleanup:
  On Error Resume Next
  DB.Close

  Exit Function

Err_SQLExecute:
  strErr = Err.Description
  gGlobalError = "SQLExecute failed!" & _
       vbCrLf & "SQL: " & strSQL & " _
       Err:" & strErr
  Debug.Print gGlobalError
  SQLExecute = -1

End Function

Excel in Front, part 1

This post is long overdue - I've been flat-out, coding a very interesting application for a manufacturing client, using Excel as the front end on a shared Access database.  The goal is to give a workgroup the ability to perform simultaneous edits to their work-in-progress tracking data, without any of the hassles, bugs, hideous file growth, sharing violations, lock-outs, etc. that you get with the built-in workbook Sharing mechanism.  There's a host of business logic involved, and they want the ability to design their own reports at will.

Again, I find myself reinventing the wheel.  "Why not develop your application in Access?" one might ask. My reasoning: Customer didn't have it, doesn't want it, can't support it, and more importantly, developing in Access is painful.  I prefer Excel.  The advantages weigh in: custom business logic is more easily coded in Excel.  It is.  And we needed in-grid editing, flexible data presentation, filtering, sorting, graphing... and whatever else we can dream up for versions 2 through N.  That's all in my Excel quiver.

"Oh, right. Excel lets you Link to a data source and populate tables..." yeah, I thought about it, but the users don't want to fart around with Data Connections, and my experience tells me it's a long road coding for those APIs. (if you know better, please tell me.)  And I wanted this to be an always-disconnected setup, where the workbook simply grabs data or sends updates to the db in quick bursts - no live links to network files that might be unavailable, renamed or changed.

The db was designed using an SQL Script to create the tables, populate the lookup data, and set up primary keys, defaults, and so on.  I ran the script to create the database in SQL Server, and then imported the tables into my .mdb file using Access.

I did not use the Access database design tools, though you certainly can.  I prefer to use a build script whenever designing a relational database; you drop and recreate the db often as you rework your design - that's Agile.  I also find that Access is a bit too user-friendly, in other words, not powerful enough.  I guess I can type SQL faster than I can ponder through menus.

In Excel, I set up a file-select control, and some text boxes for username and password.  The users on the network will each have a local copy of the application workbook - It's not linked, welded, cross-referenced or otherwise umbilical'd to any other file.  Can't you smell the freshness?  No pop-ups... ever.  As updates come out, they will be emailed to the users.  They will enter their credentials, file-pick the .mdb file, and work. That's Data Separation.

Here, again, I'm not tying into any built-in user management.  In a secure enterprise situation you probably have to set up database user accounts linked to network id's, but my client is looking for (and I strive to offer) the simplest solution wherever possible (SSWP), so we won't be contacting network support for domain credentials and such - the .mdb file is on a password-secured network drive.  Our application will maintain it's own user list... VBA code does the rest.

The project is still underway - next post I'll explain how I set up my object classes, control cell-editing, filter and sort, and conclusions: will it be another home run, or a teetering, reeking, house-of-cards? Stay tuned.

Condition "L"

Back in 1989 while using Lotus 1-2-3 in college, printer quality was not measured in dots per inch, but in dots, as in, I just got a new 9-dot printer.  A Lotus add-in called Sideways let you print your spreadsheets in landscape mode by rendering the output as a picture, scaling it, rotating it, and printing it out 9 dots at a time.  This was a major breakthrough for me, because suddenly my spreadsheets were transformed from IBM, holes on the side, six-page, Utility Bills into one-page, big-picture, Annual Reports.  Design meets Computation / Left meets Right - it all made sense to me, and was the beginning of what is shaping up to be a life-long mania for the idea of the spreadsheet.

Forward in time: it's post-2007 and Excel, in version 12.0, gives us complete control over every aspect of the look and layout of our reports. But now the screen is where we consume the majority of our spreadsheet salad; what was a static page is now dynamic.  One way to exploit this trend is to let the data control it's own appearance.  As new numbers stream in, (conditions change) the visual appearance of the numbers also change, allowing our eyes to zero in on the Information hidden amongst the Data.

Excel's Conditional Formatting feature helps us achieve this.  Though somewhat limited in 2003, the 2007 version of Excel has greatly expanded the ways conditional formatting can be applied, and the user interface has been improved dramatically.  (This is, perhaps, the only actual improvement in Excel 07, but that's another topic entirely.)  Regardless of what version you're using, it is the "Formula" option where the power of this really comes through.

For those who haven't tried it, conditional formatting lets us apply a format to a cell that is only visible if a certain condition is met.  The interesting thing is, the actual condition doesn't even have to be directly related to the cells in question.  It's one thing to say "If the value is negative, make it red.", it's another to say "If the Region is North East or North West, and the current month is between November and April, highlight the "Salt" expenditures in Blue, otherwise in show them in Yellow.  In this second example, the salt expenditures are a dollar amount, but it's the intersection of several other factors that controls how the number will appear.  That right there is pure formatting power.

The secret to correctly applying a Formula-based conditional format is knowing how relative vs. actual cell references affect the condition, and this is only a factor if you're applying the format to a range of cells.  One tip I use is to plan my format formula as though I'm writing  it for the first cell in the range, and intend to copy it to the rest of the cells - because effectively that's what Excel is doing.

For example, say I wanted to highlight every other row of cells, to give that retro altenating green & white striped computer printout look to my report.  In Excel 2003, select the entire report, and choose Conditional Formating from the Format menu, and select "Formula Is..." in the first condition's drop-down.  In the formula field, enter an IF function to evaluate the current cell's row number, like so:

=IF(MOD(ROW(A4),2)>0,TRUE,FALSE)

And then select the formatting you want - a light green cell background in this case.  Notice that we're not actually testing the data in the actual cells, but just the row numbers of the cells.  The reference to cell A4 in this case is a relative reference to the first cell in the table.  After saving the format, if I select any other cell in the table and inspect it's conditional formatting, that cell's address should appear in place of the A4.  If not, Excel might have substituted a named range or something, which is equivalent to an absolute reference - no good, because this doesn't "move" with the formula.

Using this formula, we won't disturb the alternating row coloring if the data is re-sorted or we remove or add rows.  And using this approach, along with the boolean functions AND and OR, there's no limit to how many different conditions we can include in the formatting rules for a given cell.

In Excel 2003 you ARE limited to 3 distinct conditional formats, or looks, for a given cell, plus the default format when no conditions are true.  Unfortunately, the different visual effects are not cumulative, so you will only see the formatting for the first condition of the three that evaluates to True.  No so in 2007 - you can display the first applicable format OR indicate that the rules to be combined.  For example: a value exceeding the Budget will be red, and an item covering more than 10% of the overall total will be bold, and forecasts have a blue background but actuals are green.  One glance tells me where a given value lies in all three of these axis.

In Excel 2007 you can also define up to 5 distinct looks or formats for a cell, and you get a lot more fidelity over the order and way the conditions are evaluated.  You also get expanded formatting options - specifically, the ability to create sliding color scales and to highlight unique or duplicate values are both very cool additions.

If you haven't yet mastered the conditional formatting power of Excel, I encourage you to dive in and start experimenting.  I would also love to hear about any interesting c.d. tricks you have gleaned.  Happy formatting.

iPhone Prototyping in Excel?

I just completed what has to be the funniest project I've ever worked on.  Not a typo, I really mean Funny, as in I was laughing my ass off the whole time.  This is a departure for me, as I'm usually working on financial data (lately: not so funny), and like I've said before, there's no office banter around here.  But this particular gig amused me because I was "skinning" a workbook to appear on a Television Show, and the designers who supplied me with the graphics really nailed it.

The application is sort of secret so I can't say too much about it just yet, but the TV Show, on Canada's CBC network, is called Dragon's Den.  On this "reality" program, entrepreneurs pitch their ideas to a panel of wizened business executives - Dragons - who show no mercy and pay no compliments.  Occasionally, someone impresses one or more of the Dragons, and they literally "buy-in" to the idea.  Usually, they cut the hapless entrepreneur down and send him or her off, humiliated, (or worse: try to gain full control (51% ownership) of the start-up for a fraction of the asking amount.)

My Client will be appearing on Dragon's Den, which is taping next week.  He has invented an algorithm that he intends to evolve into an iPhone applicaton, among other things.  For now it resides as an Excel model, involving some reasonably sophisticated statistical analysis and math.  My mandate was to make the model presentable on the show.  Specifically, I needed to make the spreadsheet's input screen look like an iPhone app, so my client can get the actual Dragon's themselves to provide the input data.  This is a one-off re-skin, created just for this program, with the Dragon hosts depicted with oversized heads... it's going to be really funny when it airs.

My approach was to overlay a set of masking graphics over the input section of the spreadsheet, which contains a series of Drop-down list boxes.  Anyone interacting with the file should be able to select values in the list boxes as if doing so on an iPhone.  This design simultaneously proves the technical side (the model is running in Excel and it calculates... this!), while at the same time putting the idea into context - a portable application that you use... like this. Brilliant! I wish I'd thought of it.

It was easy enough to find the elements I needed: a hi-rez PNG file of a hand holding an iPhone does the trick.  You then need to slice it up into seperate pieces so you're removing the phone's screen - that's where your spreadsheet's elements will show through.  A transparent section won't do, because you'll need to click on the controls under it.   This graphic looks really good on a spreadsheet who's cells all have a black background.

Some traps: the graphics move around and stretch as you adjust column widths and such, so you'll want to anchor those down to keep them nice.  This is done by right-clicking, selecting Format Picture... PropertiesTab... "Don't Move or Size with Cells".  Excel is a bit buggy in that it can lose this setting if you group, copy or otherwise modify the graphic, so I wrote a little routine to flip this on and off universally.  This came in handy as I was laying out the screens.

Sub float_All_Objects(blnFloat As Boolean)
  
   Dim ctl As OLEObject
   Dim shp As Shape
   Dim plcmt As Variant
   
   On Error Resume Next
   
   plcmt = xlMove
   If blnFloat Then plcmt = xlFreeFloating
   
  'form controls
   For Each ctl In ActiveSheet.OLEObjects
       ctl.Placement = plcmt
   Next
   
  'shapes, meaning graphical elements
   For Each shp In ActiveSheet.Shapes
       shp.Placement = plcmt
   Next
   
   Set ctl = Nothing
   Set shp = Nothing
   
End Sub

Another issue is that you cannot change the font size in the standard screen controls (list boxes, buttons, etc) so if you need to scale your worksheet's zoom, (to make it readable on TV, for example,) the default font size is usually incorrect.  Instead, you must use oleControls - the difference is these controls require VBA code to interact with, but they also give you full... well, control over their property set, including size, font, source data, 3-D appearance, and a bunch more.

Many Power Users get these control types confused.  It's all about which tool bar you get them from, as this illustration illustrates:  Basic form controls come from the Forms toolbar, but oleControls come from the Control Toolbox.

It's not obvious how to configure the ole controls after you have placed one on your spreadsheet.  When you right-click on one, all you can do is control basic properties such as placement, border, and caption.  For more advanced properties you need to use the Visual Basic IDE.  Your worksheet must be in Design Mode so you can select the control, and then you may edit it's properties on the VB side.  One warning, these controls won't work at all if your user's fail to enable Macros, even if you haven't written any actual code.

I really enjoyed this challenge: make the model interactive, while completely removing any hint of "spreadsheet" so it doesn't beguile the television audience.  When the segment airs in a few months, you might not even realize that Excel is running the demo, which is just one (more) of the reasons why Excel is the ultimate prototyping framework.