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 for
Publicfields in standard modules, but is illegal in class modules and is obsolete anyway - prefer the
Publicmodifier 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
Sub procedures in standard modules are exposed as macros and can be attached to controls and keyboard shortcuts in the host document.
Function procedures in standard modules are exposed as user-defined functions (UDF's) in the host application.
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.