Scope of Variables in Excel VBA

Home/Excel VBA/Scope of Variables in Excel VBA

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


There are four levels of Scope:

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:

LIMITED TIME OFFER
By |May 5th, 2013|Excel VBA|3 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

3 Comments

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

    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

    • PNRao July 20, 2014 at 11:06 PM - Reply

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

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

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

Leave A Comment


Related pages


schedule template powerpointexcel 2010 vba editorexcel vba programming for dummies pdfvbscript tutorial for beginners step by stepms excel 2007 formulas and functionsexcel tutorial advancedsql excel tableexcel macro merge cellsmessage in vbaexcel macro transposecopy sheet to another workbookhyperlink shortcut in excelexcel macro for loopexcel beginners tutorialprotect an excel workbookdefinition of vlookup in excel 2007using vlookup excel 2010vba inputbox passwordlearn access vbavba hyperlinksactivecell rangehow to protect worksheet in excelarrays excel vbalooping excellistbox selectionexcel macro userformexcel formulas shortcut keys 2007ms excel formulas with examples for 2007vba samplesvba dim integerproject vbaexample of sumifexcel simple macrodashboard excel examplesvba closeunprotect vbaexcel 2010 remove blank rowsvba excel dirdml sqlformula for lookup in excelhow to learn macros in excelhow to unprotect excel worksheetshortcut key to insert a row in excelvba combobox exampledashboard formats in excelexcel vba manualsort vbavba advancedsurface chart in excelexcel 2007 developer tabvba programming for beginnersclose file vbaunlock spreadsheetexcel auto open macrovba copy folderpivot table tutorial excel 2010cool vb scriptshow to hyperlink excel sheetsrun sub in vbaworksheet tab in excelvba if statement examplesexcel 2003 macrosautofit column width exceldashboard template excelmysql dsn connection stringremove duplicate in excelvlookup excel 2010 examplemicrosoft excel quick keysexcel merge sheets by columnexcel 2013 sumifms access vba open formhlookup excel 2013unlock macrovba word tutorialproject management dashboard excel template free downloadexcel vba remove