Separating program from data in a VBA application

[perkin_warbeck]

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?

[#2]

@perkin_warbeck 

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.

[#3]

@Hans Vogelaar 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. 

[#4]

@perkin_warbeck 

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.

[#5]

@Hans Vogelaar  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.

[#6 · Accepted solution]

@perkin_warbeck 

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.

[#7]

@Hans Vogelaar Thank you. You have explained well how Microsoft Office handles this use case. 

[#8]

@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.

[#9]

@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.

[#10]

@perkin_warbeck  Thank you for your reply. That is also a solution. At the moment I am investigating using linked tables. In Accees that is possible and probably in Visual Basic (not VBA) too. I wil keep you informed.