I’m currently building a project in Excel with VBA. It basically imports and combines data sets.
The issue I have is that I want to set some variables that are available across multiple modules that hold the parameters of the main data sheet (the row ID of the header row, the column ID of when certain data sets commence, specific cells that hold the “last updated” data, etc). I want all these parameters to be in one location so there is one point to change them if required, for example if the layout is modified.
I know I could set a heap of public variables, but as I understand it, this is not sound coding practice. I am currently designing a class to hold them as properties and using Let/Get, but is this an in efficient method to do it?
Is there any “best practice” for setting parameters like these, being used across multiple modules.

Thanks in advance.

CodePudding user response：

What I would do:

Add a (hidden) sheet, as iDevelop already said. Give the sheet a codename, e.g. Params

Put the Parameters on that sheet, either within a table or as single cells.

• If cells: name them accordingly, eg.lastUpdated
• If table: fill first column with a unique paramname, eg. lastUpdated, second with the value

Then in the codemodule of the sheet, place public get-properties for each parameter. Use a generic routine to either return the named-cells-value or the value from the table.

Then you can access them all over the project by eg. params.lastUpdated.

Caveat: each new parameter needs programming - but I accept this for readability within the rest of the code.