Winning Strategies for Success

This post is written mostly for Excel developers who work as consultants in the Freelance realm. When you engage a new client, there are a few things you can do right to ensure success, which comes in the form of repeat business. Here are my recommendations:

1. Contain the Scope
The client has a specific business problem, and needs your help solving it. Obtain crystal clear agreement on what the problem is and what the solution must contain - in writing if possible. Don't throw on a bunch of extras because you think they are needed or interesting - that's another project.

Scope Creep occurs when the client (or you) ask for extras and you include them in a project that is already underway - suddenly the finish line you all thought was there has moved. Out the window goes your estimate and the client's acceptance of it. Save up any add-ons for a phase 2 deliverable.

2. Provide Good Value
The client is taking a considerable risk hiring you, both in money and time. Aim to break-even on the first job if you think there's a chance of follow-on work. Come in too high and you either won't get the job, or if you do, the client won't feel like they got their money's worth. The client's capacity to pay is a factor here: Citigroup won't blink at that $100/hour rate, but Joe's Accounting probably can't swallow it. Once you have established your value and gained the client's respect and trust, you can safely pad your quote with a reasonable margin - we all have to make a living!

3. Support Your Work
Is the solution correct? On your machine, it is, but who knows what will happen on the Client's network. Don't deliver your code to the client the day before you go on vacation - until they sign-off, consider it untested. Answer questions and address issues the instant they are reported to you. No matter how busy you are, code that has shipped but not been paid for yet is your biggest priority. Give unquestioning support at this stage, and you cement yourself as a committed partner to the client's business.

Watch out! What the client calls a bug may be something they forgot to ask for up front... tread lightly as you educate them on what is "in scope" and what is a phase 2 change request.

4. Design for Scallability
Why write an import routine when, with a small amount of extra effort, you can write an import engine? The data requirements or inputs of any project will change over time. If you need to dig into your old code and update a bunch of cell references every time this happens, your solution becomes a liability to both you and the client.

All layout information used by your code should be read from a setup table. When the particulars change, you simply update the setup values and you're done - or get the client to do it himself. You can focus on real work and the client doesn't get nickled-and-dimed on the maintenance. Even better, the likelyhood of your code being reusable goes way up, and reusing code is all margin baby!

Finding Fault

Using the FIND method in your VBA macro is a good way to locate a specific piece of data in your workbook, and can be much more efficient than looping through an array of cells. For example, say you have a massive table of data, but each record is identified with a unique key of some kind. The fastest way to get a reference to a specific record is with FIND:

Set rngSearch = sht.Range("B1:B5000")
Set rngFind = Nothing
Set rngFind = rngSearch.Find(strKey)
If Not rngFind Is Nothing Then
   Result = rngFind.Row
End If

Unfortunately, this method can let you down if you're not careful, due to it's association with the Excel FIND menu command. The problem is that the function contains a bunch of optional parameters for which the default value is not predictable. As the VBA help file states:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you donít specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

The above code might work perfectly for years, but then the user of the spreadsheet, on some totally unrelated project, uses the Find dialog to look for a number format or something. Now the Macro stops working - key values aren't being found. Depending on the data and what you're searching for, you might not even notice the problem, and simply think that the data isn't there... Maddening!

So, to avoid surprises, be sure to explicitly define ALL of the method's parameters EVERY TIME. The above code, rephrased, should look like this:

Set rngSearch = sht.Range("B1:B5000")
Set rngFind = Nothing
Set rngFind = rngSearch.Find(What:=strKey, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not rngFind Is Nothing Then
  Result = rngFind.Row
End If

Keep 'em Separated

If you develop Excel macros that will be used by others, then you can be pretty sure those users will ask for fixes or additional features at some point in the future. If your users have done anything to modify the Excel file you give them, then you run the risk of clobbering their work when you send the next update.

Consider this example: A Purchase Order system developed for a retail store uses some sophisticated lookup formulas - pick Comnpany A as the Supplier, and only products from Company A show up as choices. It's a great time-saver for the end users, and helps reduce data errors, but the formulas rely on lookup tables imbedded in the workbook. These lookup tables need to be updated occasionally as suppliers change their lines.

If there's a bug / feature update, the programmer must either: A) get the latest copy of the user's workbook before making the change, during which the user can't do any edits, or B) give the user the updated workbook and ask them to reproduce all their edits since the last update, or C) Sync the data manually (yourself - egad!). All situations are problematic. The accepted solution is to keep the data (that's anything the user controls) and the macro code (your stuff) completely separate.

There are many ways to achieve this: you could get the workbook to connect to a corporate SQL database, link the workbook to an external file, grab the data from a Web Service, or provide an import routine of some kind. There are pros and cons for each option, but in situations where an enterprise SQL Server is not available, an external data file is usually employed.

Excel provides a built-in mechanism to actively link workbooks together. This usually works... but to minimize complexity (a guiding principal) I try to avoid live file links - I've seen too many broken or corrupt links over the years, and nobody likes those popup messages.

I generally opt for passive linking. This is where a macro is used to import the external lookup data when the work process is initiated.  The needed data is copied to the local working file (your macro workbook.) All calculations and macros work on the local copy of the data, which avoids external formula references and file links, and keeps the application file portable.

Usablity Tip! You should cache the path to the data, but give the users an easy way to find the file if it moves using Excel's standard file dialog:

Dim sFile As String
...
sFile = Application.GetOpenFilename("Excel Files (*.xls),*.xls", _
        1, "Select the Data File", , False)
If sFile = "False" Then Exit Sub 'user clicked cancel
Range("Data_File") = sFile
...

Data, by it's nature, changes all the time - it could be the output from some other system, or part of a growing transaction log. Wherever it resides, that's no place for your VBA code or complex, inter-locking formulas.

Passive linking will allow you, the programmer, to maintain ownership of the code while allowing the users to control the data. Improvements to the application are painless for both parties, allowing it to stay in use (and useful) for a long time.

Dynamic Names

One of Excel's most powerful features is the ability to define Names. A Name can refer to a single cell, a range, or a calculated value. You can even define a dynamic Name that refers to a changing range of cells.

You might need to refer to a list of items in your code, but you don't know how big the list will get or where someone might move it. To automatically sync the named range to the data, define a dynamic Name using the Insert -> Name -> Define menu, and this dialog:

The Refers To: field contains a couple of formulae instead of a cell reference, rephrased:
=OFFSET($B$2,1,0,COUNTA($B$2:$B$1000)-1,1)

The Offset formula defines a corner cell, how many rows away, columns away, how many rows to include, and columns to include.  

For the corner cell use the table header, not the first data item, because somebody might delete that cell. The CountA function in the How Many Rows parameter counts non-blank cells in the column.  The heading cell is included in the count, so subtract 1 from it's result to give the actual number of rows in the range.  This example sets an arbitrary limit of 998 items - your knowledge of the likely dataset and any use of the worksheet below the data table are factors in specifying this value.

Blank rows within the data would be a problem, so if that's a possibility, you can nest a few more functions to detect the range of cells you want... but it does get messy and there's no function help when editing names in the dialog, so be warned.

Anyway, now you can use that Named Range in code...

Dim rng as range
For Each rng in Range("Customer_List")
... some code
Next

And you're not putting any actual cell addresses in there so the code stays clean and safe.

An important factor when defining a Name this way is that your Reference formula needs to be safeguarded against possible edits to the worksheet. If a user deletes a cell which is part of the Name's RefersTo property, that Name could break, resolving to a #REF error for ever more. Good Excel programmers anticipate this possibility and set up Names (and Formula references) for maximum durability, helping workbooks live longer.

Object Non-Existence Check - Trap

When you're writing code in VBA, you should stiffen up and prepare for trouble when checking for the NON-existence of an object. Why? Because this might actually trigger an error you weren't expecting or handling.


Consider this code which operates on Excel's menus:

On Error Resume Next
If NOT MenuBars(xlWorksheet).Menus("My App") Is Nothing Then...
  ...some code runs
End If

So what happens when this code executes and the menu we're looking for doesn't exist? Simply trying to reference a non-existant object triggers an error, and the code within the IF block will still run, believe it or not. The "On Error Resume Next" line makes that happen - any error moves execution to the next line, regardless of weather the IF condition was met. A proper error handler would avoid this problem.


Sometimes we can use this situation to our advantage. Lets say we want a set of unique values from a list. I like to use VBA's Collection mechanism with it's built-in Unique Key constraint to make this easy. You'll need an object Class to act as the data container:

'Class Module called "ItemClass" with a single Property
Public Key As String

Then create a collection and try adding everything. Only the ones that are new will be allowed in.

Dim colSet As New Collection
Dim clsItem As ItemClass
Dim rngCell As Range

On Error Resume Next
For Each rngCell In Range("A1:A999")

   Set clsItem = New ItemClass
   clsItem.Key = rngCell.Text

   'this line will simply fail if the key is already in use
   colSet.Add clsItem, clsItem.Key
Next

In this case we are deliberately ignoring the error that is triggered when we try to re-use an item's value as the collection key.

To Sum Up: watch out when your IF condition triggers an error, because all bets are off. You've got to know when to handle 'em, and know when to let 'em Resume Next.

Welcome

Welcome to the blog. This is the beginning. Here I will document some of the techniques I have discovered while programming advanced macros in Microsoft Excel. I hope to add at least one new posting each week. These aren't going to be tips or tricks; I'm talking about design strategies, data manipulation algorithms, perhaps hacks... okay, probably some tips, but low-level coding tips, not keyboard short-cuts, okay? And I'll even provide some example files so you can try this stuff without a whole lot of typing.