Public (2003)

Topic

New Reply

I have a .xla with a global module for hold all my global constants (surprise)
If I declare some of the constants as Public should they be available to other spreadsheets that reference the .XLA? My documentation says public are available to all applications and projects but that doesn’t seem to be the case. Any advise?

Thanks
David

Reply | Quote

Replies

Normally, public variables are public to the project (“workbook”) in which they are defined. You can make them available in another workbook:

1) Open your .xla
Activate the Visual Basic Editor.
Click on the name of the .xla in the Project Explorer.
Set a unique name for the (Name) of the project in the Properties pane. (Default is the generic name VBAProject).
Save the .xla.

2) Open or create a workbook in which you want to use a variable from the .xla.
Activate the Visual Basic Editor.
Select Tools | References…
Set a reference to the project name that you defined in step 1.
Click OK.

You can now use the public procedures, functions, variables and constants from the .xla.

Reply | Quote

Thanks.
Still no luck on the public constants.
xla project is named (vbaCBBT)
The spreadsheet references the xla (Shows up in reference dialog and in the project folder references.
I can call the various public functions and subs included in the xla. However the project will not compile; stopping on the first constant with a ‘variable undefined’ error.

Could this be caused by the fact that there used to be a variable of the same name in the spreadsheet but it is now in the XLA?

Reply | Quote

Let’s say your constant is named clngTest. Try referring to it as vbaCBBT.clngTest

Reply | Quote

Thanks Hans, that is a great idea.
However, it did not solve the problem. It did produce some interesting results thought.
A list of of the available procedure/function/object in vbaCBBT (created by vbaCBBT. ) shows some of what should be available and none from the mGlobals module. Interestingly, while the functions in mGlobals do not appear in the lists, they can be called from the other project.

Confusing. I will probably export and rebuild. It seems to help sometimes. But I would welcome any other imparted wisdom.

Reply | Quote

If you wish, you can attach a zipped copy of your add-in, so that Loungers can investigate the problem directly.

Reply | Quote