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