How to use Excel VBA Variable Scope in Excel Office 365?

In this article, you will learn how to use Excel VBA Variable Scope in Excel Office 365. Let’s see them below!! Get the official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

VBA Variable Scope

In all the programming languages , we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers. These scope specifiers can be used to set the visibility/scope of a variable in Excel VBA.

Types of scope specifiers

• Procedure Level
• Private – Module Level
• Public – Project Level

VBA Procedure Level Variable Scope

• Firstly, in the Excel Worksheet, you have to go to the Developer Tab.
• Then, you need to ** ** select the Visual Basic option under the Code section.

Select Visual Basics

• Now, you have to copy and paste the code given below.

 Option Explicit

Sub scopeTest()
 Dim x, y As Integer ' Procedure level variable in VBA
 x = 2
 y = 3
 Debug.Print x + y
End Sub

Sub sum()
x = 5
y = 7
Debug.Print x + y
End Sub

• After that, you need to save the code by selecting it and then close the window.

Save the Code

• Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
• You need to choose the Macros option in the Code section.

Macro option

• Now, you have to make sure that your macro name is selected and click the Run button.

Run the Code

• Finally, you will receive the output in the Microsoft Excel.

Output

VBA Private Variable- Module Level Scope

• Firstly, in the Excel Worksheet, you have to go to the Developer Tab.
• Then, you need to ** ** select the Visual Basic option under the Code section.

Select Visual Basic

• Now, you have to copy and paste the code given below.

 Option Explicit
'Module level variable in VBA. Both variables x and y are private to this module. and
'can be accessed from any sub or function within this module.
Dim x As Integer 
Private y As Integer

Sub scopeTest() 'This can be accessed from any module in the project
 x = 2
 y = 3
 Debug.Print x + y
End Sub

Private Sub sum() ' This can't be accessed from other modules
x = 5
y = 7
Debug.Print x + y
End Sub

• After that, you need to save the code by selecting it and then close the window.

Save the Code

• Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
• You need to choose the Macros option in the Code section.

Choose Macro option

• Now, you have to make sure that your macro name is selected and click the Run button.

Run the Code

• Finally, you will receive the output in the Microsoft Excel.

Public Variables- Project Level Scope

• Firstly, in the Excel Worksheet, you have to go to the Developer Tab.
• Then, you need to ** ** select the Visual Basic option under the Code section.

Select Visual Basic

• Now, you have to copy and paste the code given below.

 Option Explicit
'Project level variable in VBA.
Public x As Integer
Public y As Integer

Public Sub scopeTest() 'This can be accessed from any module in the project
 x = 2
 y = 3
 End Sub

Private Sub sum() ' This can't be accessed from other modules
 x = 5
 y = 7
Debug.Print x + y
End Sub

• After that, you need to save the code by selecting it and then close the window.

Save the Code

• Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
• You need to choose the Macros option in the Code section.

Choose Macro option

• Now, you have to make sure that your macro name is selected and click the Run button.

Run the Code

• Finally, you will receive the output in the Microsoft Excel.

Wind-Up

We hope that this short tutorial gives you guidelines to use Excel VBA Variable Scope in Excel Office 365. ** Please leave a comment in case of any **queries, and don’t forget to mention your valuable suggestions as well. Thank you so much for Visiting Our Site!! Continue learning on Geek Excel!! *Read more on Excel Formulas *!!

Read Next:

• Excel Formulas to Remove the Text By Variable Position ~ Quickly!!
• Excel Formulas to Get the Maximum Value with Variable Columns!!
• Formulas to Count Variable Range with COUNTIFS Function!!
• Excel Office 365 VBA Variables & Data Types - A Complete Guide!!
• How to use the CONFIDENCE.NORM function in Excel office 365?