VBA in Excel: Global Constants File

Question

I'm working on some VBA scripts for an Excel worksheet, and I've created a module that contains a whole listing of mostly strings that store the name of each of my database fields. That way, if anyone modifies the database, they can update the VBA variable in the module, and then all my scripts, which reference the global variable instead of the field name directly, don't even know the difference.

Anyway, I'm going to be working in a few different unrelated excel sheets, and I'd like them all to have access to that module. Worst case, I could copy the module manually to each workbook, but it would be awesome if they could all externally reference the same exact file... is something like that possible in VBA/Excel?

Also, right now my variables look simply like:

Global tblDeviceType As String

And then I initialize them in a function in the module that gets called when the workbook opens. Is there a better way to maintain all my constants or does this work?

Oh yeah, and I didn't actually declare them as constants since some of them have more complex datatypes (like Ranges) and couldn't be statically declared - at least it didn't seem like they could.

Answer 1

You could make an Excel Spreadsheet with all the names, then used named ranges that have the same name as the Global, change your code to use the named ranges, and make the sheet an addin.

I would just copy the module to the other spreadsheets.

Edit: You might also be able to create a blank spreadsheet with the module in it, then make that an addin. I don't know if Globals in Excel will work that way, but it's worth a try. Please post back here if you do and it works or not.

Answer 2

Alternatively, you could create a text file (Microsoft Scripting Runtime library -> class Scripting.TextStream) where you can save your variables & load them when you wish to read it.

Place the text file at a central location accessible by all the sheets.
For Ranges, you could store the Address of the range in the text file.

Let me know, if I am missing anything.

Answer 3

There are a couple ways you can probably do this:

1. Base all the excel sheets off the same excel template. This way they can all access code declared in the template. But then you have to distribute the template as well I think.
2. Create a activex dll and then reference it in each of your excel templates.
3. Or you can load the module dynamically at run time. You would use a method like ThisWorkbook.VBProject.VBComponents.Import(). You'd probably have to run your method that populates the variables manually. (Note I haven't tested this) More info on this type of manipulation of excel can be found here

Answer 4

Actually the easiest way is to add the module to a .XLA excel addin file. Then add this file through the Excel Addins dialog.

The module is now available globally to all excel solutions run under that user account.