VBA Declaring Variables Access Modifiers

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

The Dim statement should be reserved for local variables. At module-level, prefer explicit access modifiers:

  • Private for private fields, which can only be accessed within the module they're declared in.
  • Public for public fields and global variables, which can be accessed by any calling code.
  • Friend for variables public within the project, but inaccessible to other referencing VBA projects (relevant for add-ins)
  • Global can also be used for Public fields in standard modules, but is illegal in class modules and is obsolete anyway - prefer the Public modifier 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.



Got any VBA Question?