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!

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.