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.

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.