REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

When we are working with variables, it is important to understand the Scope of a Variable. The Scope describes the the accessibility or life time or visibility of a variable.

There are four levels of Scope:

Scope of Variables in Excel VBA

Procedure-Level Scope:

Also called as Local Variables, all Procedure-Level variables are accessible only within the procedure or Function in which they are declared. As soon as the procedure finishes, the variable lost its scope.

In the following example, iCntr is a Local Variable which can be only accessible in this procedure.

Sub sbScopeProcedureLevel()
Dim iCntr As Integer
iCntr = 2000
MsgBox "Example of a Procedure level Variable: " & iCntr
End Sub

Module-Level Scope:

All Procedure-Level variables are accessible only within the Module in which they are declared. These are variables that are declared outside the Procedure itself at the very top of any Module. Its value is retained unless the Workbook closes or an End Statement is used.

In the following example, lRow can be accessible any procedure in the Module in which it is declared.

Option Explicit

'Module Level Variables
Dim lRow As Long

Sub sbProcedure1()
MsgBox "Example of a Module Level Variable " & lRow
End Sub
Sub sbProcedure2()
MsgBox "Example of a Module Level Variable " & lRow
End Sub 

Global-Level Scope:

All Global-Level variables are accessible in anywhere in the Project (.i.e; in any Module, User Form, Classes) within the Workbook in which they are declared. And also accessible to outside of this project or workbook. These are variables that are declared using ‘Public’ keyword at the very top of any Public Module .

In the following example, lRow can be accessible any procedure in the project or workbook and also out-side of the module.

'Code in the Module 1:

Option Explicit
'Module Level Variables
Public lRow As Long

Sub sbProcedure1()
lRow = 220
MsgBox "Example of a Public Level Variable " & lRow
End Sub 

'Code in the Module 2:

Sub sbProcedure2()
MsgBox "Example of a Public Level Variable " & lRow
End Sub

Project-Level Scope:

We set Project -Level Scope to the variables if we want to make the public variable to be accessed only in the project in which they are declared and not out side of this project. To set this option we need to add “Option Private Module” statement at the top of the declaration area.

In the following example, lRow can be accessible any procedure in the project or workbook only in which it is declared.

'Code in the Module 1:

Option Explicit
Option Private Module

‘Module Level Variables
Public lRow As Long

Sub sbProcedure1()
lRow = 220
MsgBox “Example of a Public Level Variable ” & lRow
End Sub

'Code in the Module 2:

Sub sbProcedure2()
MsgBox “Example of a Public Level Variable ” & lRow
End Sub

Scope of Variables in Excel VBA – Here is the Pictorial Representation of the Scope of the Variables:

Scope of Variables in Excel VBA

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: June 17, 2022

5 Comments

  1. priyesh July 20, 2014 at 2:19 PM

    Module level

    All Procedure-Level variables are accessible only within the Module in which they are declared. It was module level but written procedure level,small mistake please correct it

  2. PNRao July 20, 2014 at 11:06 PM

    Hi Priyesh,
    Yes- It should be Module Level! Thanks for finding! I have corrected it.
    Thanks-PNRao!

  3. Mutafa Alloush September 6, 2015 at 2:37 PM

    thank you very much , I hope there are complete course about vba in ebook

  4. Georry December 8, 2017 at 3:13 AM

    For the global variable, it doesn’t seem to work across projects. Can someone help?

  5. Pravin W January 6, 2020 at 10:01 AM

    Sub Prog1()

    Dim Myname As String

    Myname = “Pravin”

    Prog2 (Myname)

    End Sub

    Sub Prog2(Myname)

    Dim Surname As String

    Surname = “Waghchoure”

    Range(“A1″).Value = Myname & ” ” & Surname

    End Sub

Leave A Comment