Best practice for storing state information in Excel / VBA

[CharlieNVA]

The thing I love and hate about Excel is that it gives you too many ways to do things. My question to the board is what is the best practice.

Problem - I have a complex Excel/VBA application where the structure can occasionally change, i.e., change the number of columns or rows used. Initially I code the maximum number of columns and rows as a common variable in the declaration of my main module. I then decided that the end users, long after I am gone, may try to tweak the number of columns so I tried the following more robust user friendly methods:

Which would you pick?

1) Storing the value in the spreadsheet itself
2) Storing it in a defined name (I found defined names great for ranges but not constant values)
3) Static sub or function called to get the key dimensions, one function per dimension ( I have about 8 key dimensions that the user could tweak).
4) Recalculating the dimensions every time I need them
5) Passing the key dimensions as parameters to all the key functions that need the information
6) creating a class module with static memory
7) creating a function/sub that updates the current common dimensions at the start of my main module

Update the dimensions is not calculation intensive, but I want to use identical and clear (no complicated formulas) references to the same dimension where ever it appears in the program for consistency and understandability.

 

[Dan_W]

I don't know what "best practice" is, but if it were me, and there is the possibility that the end users may try to "tweak" the number of columns (already, this sends shivers up my spine), I would aim to keep the end user from having to delve into the code as much as possible. Of course, this all depends on how comfortable they are dealing with VBA code, but the end of the day, there's no guaranteeing who will be dealing with it (I would have thought...). If it's just the number of rows/columns, maybe store that information in a xlVeryHidden worksheet, with an interface to 'tweak' those numbers in the form of another worksheet or perhaps a userform.

It isn't entirely clear what it is that your spreadsheet does, so I don't see why you would come up with some of the options you list (why a class module with a static variable, for example, and not a public variable in a standard module?). I suppose it depends on how long you would want the stored state information to persist, but there are a number of other options available to you - custom worksheet/file properties, Excel hidden's namespace (link), the registry, an INI file, etc.