Quick one... To confirm. Is a global variable defined in a private module, before any procedures, available to all modules? In the example below, SQL_SERVER_AVAILABLE & SQL_SERVER_ADODB_CN will be available in all modules
Thanks.
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Interesting...
That is not true and incorrect all at the same time. In one excel file, if a module is defined as Private, from any worksheet, when you hit Alt-F8 (ribbon: developer | macros), subs will not be listed for any modules defined as private.
It says nothing about subroutines being available, or not.When a module contains Option Private Module, the public parts, for example, variables, objects, and user-defined types declared at the module level, are still available within the project containing the module, but they are not available to other applications or projects.
Nor does your sample code refer to subroutines.
Your link says "Option Private is only useful for host applications that support simultaneous loading of multiple projects". That does not reference subroutines being available or not.
The original question was about scope of variables in a private module
The short answer is that your global variables will be available with the Option Private Module. Incidentally, "Public" is more common because it's not limited to being declared in a module.
The MS explanation isn't very clear IMO and less so because I don't think that cross referencing excel vba projects is very common. The main practical use of Option Private Module appears to be to remove the subs from the macro list.
Nevertheless, here's how you can demonstrate the effect.
Save and open two xlsm workbooks Book1 and Book2.
Add modules to both.
Change the VBAProjects to VBABook1 and VBABook2 respectively.
In a Book1 module, declare a public constant Public Const x As Integer = 10
In a Book2, create a reference to Book1's vba project (see picture)
In the Book2 module add a sub to show x as a message
You should find that x will not be available to Book2 if Book1 has Option Private Module.Please Login or Register to view this content.
However, if you know the macro name, you can still type it in and run it, even though it doesn't appear in the macro list.The main practical use of Option Private Module appears to be to remove the subs from the macro list.
shhh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks