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.