Storing important values: ini file, XML, Registry, where else ?

[syswizard]

For small amounts of data, there seems to be several ways to store
important values and parameters used in an Excel application.
I've always thought that using the ini file approach is the best, but
there are no built-in functions in Excel to read and write this data.
XML seems too verbose and again, there are no XML parsers built into
Excel.
Storing values in the Windows registry seems like one of the best
alternatives.

Anyone have comments on the above ? Obviously using a database like
Access or MySQL is one other alternative, but it seems like it would
be overkill for maintaining a small set of data.

TIA.

 

[Vergel Adriano]

How about placing them in a hidden sheet in the workbook itself?

 

[syswizard]

How about placing them in a hidden sheet in the workbook itself?

That's OK, but what if they unhide the sheet ? Also, consider the fact
that most of the code will be in an XLA file and that multiple XLS
files will be created. WIth your approach, I would need to copy the
hidden sheet every time a new XLS file is created. Finally, what
happens if the first spreadsheet needs to use any changed values which
are in the latest spreadsheet.
Your approach is good if I need versioning of the values; terrible if
I need a central and consistent source of the values.

 

[Peter T]

Since W95 the registry is (was?) the preferred location, and there are
simple built in functions in VB to
HKCU\Software\VB and VBA Program Settings
In help SaveSetting and 'see also'

However for quite a while there has been a retro like move back to using ini
by many, including MS (though perhaps they've moved on to xml). There are
plenty of ini examples in this ng, search GetPrivateProfileString &
WritePrivateProfileString.

Would need to consider if you need one ini for all (eg your xla path) or
separate ini's for each user in which case you would need another function
to determine a suitable user path.

A matter of what's simplest and effective for your overall needs.

Regards,
Peter T

 

[Vergel Adriano]

That the XLA file generates spreadsheets and that the spreadsheets need to
have different sets of values was unknown before (or at least I missed it).
But anyway, given your description below, all your points are valid. Storing
the values in a hidden sheet is a very simple approach that will not work
well in a scenario such as yours. Personally, I would prefer a file-based
approach in a scenario like this, either XML or INI.

By the way, a sheet has 3 "visibility" values: Visible, Hidden, and Very
Hidden. A "Very Hidden" sheet is visible only through the VBA IDE. A user
wouldn't be able to unhide it.