Forum Discussion
Separating program from data in a VBA application
I'm new to VBA, but I come from a software development background. I have written an Excel application entirely in VBA (there are no formulas in worksheets). I am constantly enhancing the code, and fixing bugs, but the user interface (a worksheet) almost never changes. I'm looking for a way to separate program from data, the way it is done in most software applications.
Here is an example of the problem: The user has version N of the application. The user enters data and saves workbook, which is an .xlsm. I distribute version N+1 of the program. The user would like to use the data that they entered into the previous version with the latest version. But to do that, they must open the old workbook, copy the data, and copy it into the new workbook. This is because the data and the application are bound together. It also results in proliferation of versions of the program.
The best solution I can come up with is to add an export/import feature to the application. Now the application no longer keeps the data when you exit. If you want to keep it, you must export it. Export saves the data to an .xlsx. When you start the application, it comes up without data, and you must import an .xlsx. This solution separates program from data, and it is not terribly difficult to implement.
I was wondering if this is how others do it. Does Excel have a standard design pattern for this use case?
In Microsoft Word, you can store VBA code intended for a single document in that document.
If you want code to be available to a class of documents, you store the code in a template (.dotm) and create documents from that template (or if the document already exists, attach - i.e. connect - the template to the document). You can update the template and the updated code will be available to all documents associated with the template. The code will not be stored in the associated documents themselves.
If you want code to be available to all documents, you can store it in a global template aka add-in. By default, each Word user has a macro template Normal.dotm, but you can create others. When you update the template, the modified code will be available to all documents.
9 Replies
You might create an add-in (.xlam) to store the macros. When you change the code, you only need to send the user the new version of the add-in with instructions on where to copy it.
See for example How to Create and Use an Excel Add-in for more info.
HansVogelaar I didn't know about add-ins. Thank you for the link. However I don't think my application lends itself to an add-in approach. Let me be more specific about the nature of the application.
The user enters physical measurements into a range that is 31x6 cells. This range is fixed in size and will never change because it matches the size and format of the data set produced by the measuring instruments. The user simply pastes instrument data into the application.
The program then runs mathematical/statistical analysis and displays the results in tables and charts.
Based on the analysis, the user may reject or manually modify measurement data and may also add metadata. But all the data remains within the fixed 31x6 area.
The modified data must be preserved because it reflects the work done by the analyst. Currently, it is preserved when the user closes the workbook, but it is then bound up with the VBA code. A year from now, if the user wants to revisit the analysis and perhaps do some additional work, they'll be working with an old version of the application which lacks recent enhancements and bug fixes.
The user would be working with the data workbook.
The data workbook can run VBA code that is stored in the add-in.
If the user opens and edits the data workbook in the future, theuser/workbook would use the code in the current version (at that moment) of the add-in.
