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'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.
perkin_warbeck: I am facing the same problem. And I am new to VBA for excel to. Until my retirement I was a IBM mainframe (systems) programmer. I also would like my code and data in a separate file. I think the solution could be: Create a workbook with the code en with some empty sheets. Create or use a separate Data workbook. In your code workbook import the data in your empty sheet, manage/modify the data, and export this modified data to the data workbook. Lot of work and I/O but worth trying it.
LinuxJoeser Since my original post, I have come up with a method that works for my application but is certainly not a general solution. To every Excel workbook I have added an Import button on the Home worksheet. When I create a new version of version of the software, I open both the old and the new workbook and press the Import button on the new workbook. The Import code copies data from old to new. The import code must account for the handling of new fields, fields that have changed their location, or been removed, so the Import code is very ad hoc. But some of my workbooks contain dozens of worksheets, so importing saves a lot of time. What makes this relatively easy for my application is that although my workbooks have many worksheets, they are all cloned from a hidden template worksheet. So the Import code only has to deal with differences between two templates.
