Parallel Odyssey

As I have already mentioned in this blog, I'm a fan of virtualization.  Specifically, running Windows XP on my MacBook Pro.  I've had a relatively good experience using Parallels 3.0, but I have resisted upgrading it.  I went through quite a bit of pain during the early days because the software was buggy and the company was still ironing things out.  I stuck with it, reverting to a few snapshots and re-installing once, but eventually got a stable instance of Windows XP that I use for Excel development.

The company that makes Parallels bombarded me with offers to upgrade to version 4, but I resisted.  Like my steadfast loyalty to Excel 2003, I saw no reason to upgrade for upgrade's sake - the risks are just too high.  The net was full of postings from people who suffered through bitter difficulties getting their upgraded virtual machines to boot correctly; to be stuck without a usable Windows environment means I am unable to do billable work.

Finally, with version 5.0 a mature product, and 50% discounts being thrust at me, along with promises of huge performance increases and support for Windows 7, I decided to take the plunge.  The notion of having more speed - "up to 3 times faster than previous versions" - sealed the deal.

I downloaded the installer for $30, but parked it for a couple of days, because I had some deliverables to complete and had no idea how long the machine would be out of action.  Here's what happened:

Thursday, 2:30pm - finished my work for the day, time to start backing up.  My virtual machine's directory is 109GB.  Copying this to my network drive takes the better part of 2 hours.  I watch a movie on my other computer.

4:30pm - I use the Parallels VM manager to "shrink" my Windows disc.  First I need to get rid of the snapshots I've created over the past two years.  Deleting a snapshot is not a simple matter; each one is "delta" over the ones that precede it.  I had 10 to delete, at around 10 minutes each!  What a pain.

6:30pm - Snapshots gone, I initiate the Shrink operation.  The progress bar moves at hour-hand speed, but no e.t.a. is given.

9:00pm - Kids are to bed, and the shrink operation seems to be hung-up at around 80%.  I debate interrupting it, not sure if it's frozen or what.  I put my ear to the computer and detect some faint noises coming from the hard disc, so something must be happening.  I leave it overnight.

Friday 8:00am - To my relief, I'm greeted with a "process complete" message.  I check the folder size and it has gone from 109 down to 63 GB.  That's much better, but still too big considering that the actual data on the VM's HDD is only 20 GB.  What is up with the missing space?  Better read up on what others are doing about this.

9:45am - Educated, I launch the Compress utility from within the guest OS, which removes all the chattel that builds up in a typical Windows install: temporary files, logs, etc.  It also compresses the data.  This program displays a progress bar, which also creeps.  I go for a bike ride while it grinds away.

11:30 - still not done, but it's definetly doing something - the laptop's cooling fans are at maximum and the laptop is hot!

12:10pm - Compress has completed, and to my elation the VM's folder is now a paltry 18 GB!  That's an 83% reduction.  More importantly, that frees up 30% of the space on my MacBook's hard drive, which was nearly full.  Phew!  I make a backup of the folder again, which only takes 20 minutes this time.

12:30pm - I start the Parallel's 5.0 upgrade.  I'm given a series of warnings about how it will need to update my virtual hard drive image, and once done, I won't be able to use this on older versions of Parallels, so basically it's a one-way trip.  Having fully backed up everything, I click Start.  The installation only takes a few minutes.

12:45 - Parallels 5 launches, and it actually boots my Windows XP machine!  But wait - this must now be updated.  I get the old progress bar again... slow like stalagmite.  It gets stuck at around 50% for two hours but I've come so far, I dare not intervene.

2:30 - I give up hope and start intervening.  I see a little padlock icon so I click it and am taken to manual mode - and I see that Windows is hung up on a retry-ignore-cancle dialog - I click "ignore" and the process resumes.  I thank my lucky charms.

4:18 - It's done!  Paralles 5.0 is installed, Windows XP boots up just like always.  My directory structure is intact, and Excel launches - nice and snappy!  The whole deal ate up about 6 hours of my actual time, but my development environment was down for 26 hours.  Oh well, it will all be worth it, right?

The first thing I do is load up my biggest, most complex Excel model and initiate it's comprehensive recalculation routine - a process I had previously benchmarked to take roughly 58 seconds.  It runs, but not apparently faster... I check the output and am stunned to see the result: 58.23 seconds.  WTF?

I don't know what performance increases those guys were talking about, but apparently it wasn't anything to do with typical user functions like using Microsoft Office in Windows XP!  Time will tell if I see any measurable benefits from this upgrade.  At least it wasn't slower!

One thing is for sure: when I replace my laptop, I will need this version of Parallels, because 3.0 doesn't run on the latest Mac OS, so this upgrade had to be done eventually.  But I'm still a bit disappointed - I was hoping for more of an immediate payback.  If anything, this experience reinforces one of my primary credos: if it ain't broke, don't fix it.

I'd love to hear about any of your experiences on this topic.

Custom Forms: File Chooser

If you are developing solutions using Excel and VBA, you will probably come across the situation where your user must navigate to a folder and select a file from the OS to feed some process.  One example might be a report generator that imports data from an existing workbook to produce a new report.  You can use this technique to provide a file picker for your users.

On a user form, place a text box (call it "txtFile") and a command button (call it "cmdPickFile").  Make sure the text box is large enough to display the directory path and file name your user will select, and put the command button "inside" the right edge of the text box, like the Import File field on this dialog:

Set the command button's caption to be an elipse (3 dots) which is a standard and intuitive Windows construct indicating that clicking the button will allow you to select a file.

In the button's click event, put the following code:


Private Sub cmdPickFile_Click()
  Dim strFile As String

  On Error Resume Next

 'set default dir
  ChDir$ ThisWorkbook.Path

 'show file open dialog
  strFile = Application.GetOpenFilename("Excel Files (*.xls),*.xls", 1, "Select File", , False)

  If strFile = "False" Then Exit Sub

 'drop name in text box
  txtFile.Text = strFile

End Sub

This routine leverages Excel's GetOpenFilename method, which displays a standard File Open dialog, allows you to navigate through the directory tree, and select a specific file.  This doesn't actually open the file, it simply captures the selected file's name and path.

If the user clicks the Cancel button on the standard dialog, the GetOpenFilename method returns "False", so we test for that before capturing the selected file's info and putting it in our text box.

The advantage of this approach is that the selection is visible to the user, and could in fact be typed in, or pre-populated by our macros, depending on the situation.  The line that sets the default directory could be adjusted to make a pre-determined path the default.  Also, you can change the file filter ("Excel Files (*.xls),*.xls") to show other types of files, in case we need to process data that isn't in Workbook format.

In this example, the user must click the OK button on our user form to start the import process.  Our code simply needs to look in the txtFile control to get the full path and file name of the file we want.  This file could then be opened using code like this:


Dim WKBK as Workbook
Set WKBK = Workbooks.Open(txtFile.text) 

It may seem so simple, but unless you've done it, these sorts of details can be time-consuming to figure out.  It's worth doing, because intuitive, user-friendly controls such as this make your applications work the way user's expect, and this get you hired back.

Home Spun Scenarios

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

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

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

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

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

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

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

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

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

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

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

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

Easy Dashboarding

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

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

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

         

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

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

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

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