VBA Declaring Variables Access Modifiers
Example
The Dim statement should be reserved for local variables. At module-level, prefer explicit access modifiers:
Privatefor private fields, which can only be accessed within the module they're declared in.Publicfor public fields and global variables, which can be accessed by any calling code.Friendfor variables public within the project, but inaccessible to other referencing VBA projects (relevant for add-ins)Globalcan also be used forPublicfields in standard modules, but is illegal in class modules and is obsolete anyway - prefer thePublicmodifier instead. This modifier isn't legal for procedures either.
Access modifiers are applicable to variables and procedures alike.
Private ModuleVariable As String
Public GlobalVariable As String
Private Sub ModuleProcedure()
ModuleVariable = "This can only be done from within the same Module"
End Sub
Public Sub GlobalProcedure()
GlobalVariable = "This can be done from any Module within this Project"
End Sub
Option Private Module
Public parameterless Sub procedures in standard modules are exposed as macros and can be attached to controls and keyboard shortcuts in the host document.
Conversely, public Function procedures in standard modules are exposed as user-defined functions (UDF's) in the host application.
Specifying Option Private Module at the top of a standard module prevents its members from being exposed as macros and UDF's to the host application.
