I have two versions of an “Excel-Tool”, i.e. a spreadsheet with a lot of vba code. Lets say, this tool is used to monitor two facilities. Both facilities differ in minor aspects.
That means, I have magic numbers in both versions to set the correct ranges for each facility in the spreadsheets.
I have refactored the vba to isolate the magic numbers. I have separated the business logic into one module and the magic numbers for ranges in a second module. Now I can reuse the logic module in both versions and only have to maintain one logic module and don’t have to keep two files synchronized. That is a great improvement.
But it feels like I’m not done. I want to have only one masterfile (excel+imported vba modules) to distribute and some kind of configuration file to store the magic numbers for the actual facility.
Has anybody experience with such concept? Is there a primer or even an out-of-the-box solution for configuring a vba-program?
2015-04-29 Edit:
I didn’t want to post code in the first place, but let me clarify through numbers.
Let’s say we have to facilities, each with a number of machines. The whole business is about generating facility performance charts. The rawdata for each facility (and each machine) has the same format, but the number of machines varies between facilities.
I have a vba-function to create standardized charts. This function is called for each machine with an array of ranges, pointing to the rawdata.
For facilitiy 1 it could look like:
Set Inputdata(0) = Sheet1.Ranges("A2:A20")
Set Inputdata(1) = Sheet1.Ranges("B2:B20")
Set newChart = ChartCreator("Facility 1", Inputdata)
And for facility 2:
Set Inputdata(0) = Sheet1.Ranges("A2:A25")
Set Inputdata(1) = Sheet1.Ranges("B2:B25")
Set Inputdata(2) = Sheet1.Ranges("C2:C25")
Set newChart = ChartCreator("Facility 2", Inputdata)
I have separated the logic, the ChartCreator, but still have those numbers. Since the data for Sheet1 is generated by a SQL-Query, something similar is going on there.
If I would use the suggestion of James, I would have another sheet with the configuration. I still have to maintain two excel-files with differences in a configuration-sheet.
But what I want is to have an Excel-Sheet I can pass to both facilities and add something like an ini-file to store the specific data. Therefore I can just keep one generic copy of the excel file for me to add new stuff or do bugfixing and then I can just sent a new version of that out to both facilities and don’t have to think about configuration.
The right approach for this is to have the configuration information in a worksheet – then it can be changed even by people who don’t want to touch VBA.
The architecture is to have a macro-enabled worksheet with a sheet called, say, Configuration, with each piece of configuration in a cell which is a named range that the macro can refer to. You should have descriptions and help in this worksheet too.
This was asked long ago, but I think there’s a better approach than the one already suggested, so here it is.
I would advice to have the master file as you suggest, facility-independent (with no particular configuration data on it), and load the facility configuration from an xml file that will be specific to each one, and that you or the people there could manipulate/customize/modify (being xml very human-readable).
Take a look at this for ideas on how to parse the xml data.
So, for instance, you could store data to populate your variables:
<machine facility="Facility 1" name="Machine1">
<labels>A2:A20</labels>
<data>B2:B20</data>
</machine>
To populate it like this (after parsing it with the approaches you will find following the link):
Set Inputdata(0) = Sheet1.Ranges(machine1.labels)
Set Inputdata(1) = Sheet1.Ranges(machine1.data)
Set newChart = ChartCreator(machine1.facility, Inputdata)