VBA in Excel: Global Constants File

Question

I am working on some VBA scripts for an Excel worksheet, and I created a module containing a complete list of mostly rows that store the name of each of the fields in my database. That way, if someone modifies the database, they can update the VBA variable in the module, and then all my scripts that reference the global variable instead of the field name directly do not even know the difference.

In any case, I'm going to work on several different unrelated Excel worksheets, and I would like all of them to have access to this module. In the worst case scenario, I could manually copy the module into each workbook, but would it be nice if they could reference the same exact file ... something like that in VBA / Excel?

Also, now my variables look simple:

Global tblDeviceType As String

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

Oh yes, and I did not actually declare them constants, since some of them have more complex data types (for example, ranges) and cannot be statically declared - at least it seems that they could not.

Answer 1

There are several ways you can probably do this:

• Lift all excel sheets from the same excel template. This way they can access the code declared in the template. But then you should also distribute the template, I think.
• dll activex, excel.
• . , ThisWorkbook.VBProject.VBComponents.Import(). , , . (, ). excel

Answer 2

(Microsoft Scripting Runtime library → class Scripting.TextStream), , .

, .
.

, - .

Answer 3

Excel , , , , , .

.

: , . , Globals Excel , . , , , .

Answer 4

.XLA excel addin. Excel Addins.

The module is now available worldwide for all excel solutions running under this account.