macro include directive

[bkelly]

I want to put a bunch of constants in a separate file so that can be referenced by several macro modules. I also have some support macros that are used by many other macros. What is the command to include a file as in the C language statement:
#include file.bas

Thank you

 

[SamTYler]

Whenever I need to look for help on the VBE Object model, I use F1 on "ActiveVBProject," in the string "Application.VBE.ActiveVBProject," and then go to the contents tab.

You can "include" via several ways. See the VBE objevct model help for the Addin and Addins Collection Objects.

You can use the Import method on .bas files. See the Export method or use Excel Menu:File|Export to save .bas files.

 

[Norie]

Bryan

There isn't one for VBA, not all computer languages work in that way.

I suppose you could create a file with all these constants and then add a reference to it in any file you want to use them.

What are these constants?

PS There are directives but as far as I know they are only compiler directives and couldn't be used for this sort of thing.

 

[bkelly]

Whenever I need to look for help on the VBE Object model, I use F1 on "ActiveVBProject," in the string "Application.VBE.ActiveVBProject," and then go to the contents tab.

You can "include" via several ways. See the VBE objevct model help for the Addin and Addins Collection Objects.

You can use the Import method on .bas files. See the Export method or use Excel Menu:File|Export to save .bas files.

Yeah, I do that also. What happend to the Help button? Why did microsoft get rid of that. When I use F1, the window has a search, but no tab. NONE. Entering "include" done not reveal what I need.

In the VBA window, I clicked on the Add In button and got a blank form. That was not helpful. Where do I find the "VBE objevct model?"

Yes, I can import and export VBA modules, but that does not help either.

 

[bkelly]

Bryan...What are these constants? ...

I work on telemetry. A vendor gives us a map of the parameters, but it does not match the format needed for ingesting by the decommutator. I extract the data I need from the first vendor, calculate what is needed by the decommutator, and build an Excel file than the decommutator can read. This uses several worksheets and many columns. I use constants to select the worksheets, select the columns and to represent various values that I need to transpose the data. There are several vendors for the source data.

Since starting this thread I have re-discovered that by declaring a constant PUBLIC it can be seen by other modules. This is a little but ugly in that every single constant must be prefixed with PUBLIC, and that every module sees all the constants. BUT it is much better than having a copy of the constants in every module. (That is a maintenance nightmare.) I can also have a module of common functions that are used for multiple vendors.

Thanks to each one of you that took the time to respond.

 

[Norie]

Bryan

Did you try creating a file with all the required constants and adding a reference to it to every file that needs it?

 

[xenou]

Bryan

Did you try creating a file with all the required constants and adding a reference to it to every file that needs it?

That's a good point. If it turns out you want to use these common functions and constants in other Excel files then you can add a reference to the project with the constants from any other workbook. The key is only that you must give the project a unique name (rather than the default VBAProject), and then you can set a reference to it from Tools | References (using Browse and searching for files of type Excel).

 

[bkelly]

Bryan

Did you try creating a file with all the required constants and adding a reference to it to every file that needs it?

No I haven't. I just used Help from VBA and did not recognize anything that looks like what you mention. Please give me a simple example.

 

[xenou]

You can create a demo like this:

• Create a new workbook. Add a vba standard module and a single public function of your choosing. Throw in a public constant while your at it.
• In the vba editor, go to Tools | Properties and give the project a unique name rather than the default VBAProject
• Save the workbook and close it.
• Now, create a new workbook and in the VBA editor go to Tools | References. Use the browse button to browse out to your just-created workbook and set a reference to it (note: you need to change the "files of type" to "excel" so you can see it when you are browsing for the file).
• That's it. Now the function can be used in both workbooks, and your public constant as well. One thing to note is that Excel will open the referenced workbook. So I like to make such "code workbooks" hidden.

I think this is what Norie means (Norie, let us know if you had something else in mind).

ξ

Note: just adding some thoughts out loud for others who might read this post: It's a simple way to create some shared code but one thing I've found is that if I try to create such references all over the place and give them to other users then you start to get messages about the workbook being "in use" - that is, since someone else is using it of course! Which is a little strange because then folks will be wondering what it is! It would be nice if it could really be "invisible" when you do this {sigh}.

 

[bkelly]

You can create a demo like this:

... then you start to get messages about the workbook being "in use" - that is, since someone else is using it of course! Which is a little strange because then folks will be wondering what it is! It would be nice if it could really be "invisible" when you do this {sigh}.

Can the workbook be explicitly opened read only? Would that eliminate this particular problem.

Several years ago I wrote a suite of macros to chart a bunch of data. When I wrote it up so others could use it, all my subroutines were visible and making it difficult to tell the user what to do. At that point I started adding a menu drop down list to Excel. Then I could tell them to run macro aa_add_menu, which would be at the top of the list. After the menu was added, then the user would only see the macros they needed to call and not all the subroutines.

Thank you for your suggestion.