Forum Discussion
Separating program from data in a VBA application
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.
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.
HansVogelaar I'm trying to imagine what it would be like if the code for Microsoft Word was bundled inside of each Word document. Then whenever a new version of Word came out, I would need to add the code to the book I'm writing. But I would also need to add it to the hundreds of other documents in my library. This is clumsy and error-prone. How do you even keep track of which documents have been updated? It places a heavy burden on users.
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.
HansVogelaar Thank you. You have explained well how Microsoft Office handles this use case.