EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Coding VBA Public Variable
Secondary Sidebar
VBA Coding
  • VBA Coding
    • VBA Set Range
    • VBA ByVal
    • VBA Long
    • VBA Data Types
    • VBA Enum
    • VBA Clear Contents
    • VBA Call Sub
    • VBA Exit Sub
    • VBA END
    • VBA Class Module
    • VBA excel programming
    • VBA Code
    • VBA Option Explicit
    • VBA Global Variables
    • VBA Variable Declaration
    • VBA SUB
    • VBA ByRef
    • VBA Variant
    • VBA Public Variable
    • VBA Variable Types
    • VBA Goal Seek
    • VBA Variable Range
    • VBA Class Module
    • VBA XLUP
    • VBA XML
    • VBA ByVal

VBA Public Variable

By Madhuri ThakurMadhuri Thakur

VBA Public Variable

Excel VBA Public Variable

Variable is an important part of a programmers life. Every time a programmer codes he creates some set of variables. A variable can be considered as a voucher which we frequently use on online shopping sites such as Flipkart, Amazon, etc. that voucher allows you to have a certain discount on certain products predefine. Same is the case with a variable. A variable can grammatically be defined as a placeholder which reserves some memory in the system with certain name. That memory space can then be utilized anytime with the same name.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (85,982 ratings)

A VBA variable is considered as a public variable in two circumstances. When –

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  • It can be used along with all the codes within a module.
  • It can be used along all within all the codes for different modules.

In layman terms, a variable is considered as a public variable when it can be used in any code and in any module. In this article, we are about to see, how a variable can be defined. How it can be defined publicly within VBA.

Before starting the concept of VBA Public Variable, we need to understand that the variables which are used in one sub-procedure can’t be used in the other sub-procedure (that is where we started to feel the need of having a public variable which can be used across any module, any sub-procedure).

Suppose we create a sub-procedure in a VBA and define a new variable there using Dim statement.

VBA Public Variable 1

In this code, var1 is the variable defined under sub procedure Ex_Var.

Now, I will define a new sub-procedure and irony is, I can’t use previously defined var1 in this sub-procedure. See the screenshot below, where I am trying to assign value 10 to var1 under different sub-procedure. See the error message as well after we run the code.

VBA Public Variable 2

By declaring variables under sub-procedures, we restrict them to be used by that sub-procedure only. It is a major drawback of declaring a variable within a sub-procedure.

This leads to the new theory, which includes the variable declaration outside the sub-procedures. This will help the programmer to use the defined variables widely with different subprocedures.

How to Declare Public Variables in VBA?

Let us look at a few examples to declare public variables in VBA.

You can download this VBA Public Variables Excel Template here – VBA Public Variables Excel Template

Example #1 – Defining a Variable Publicly Within a Module

Suppose we want to define a variable which can be accessed publicly across all the codes within a single module. We can do that. Follow the steps below:

Step 1: Open your Visual Basic Editor (VBE) by hitting Alt + F11 or clicking the Visual Basic button under Developers tab in Excel. Select Insert tab, navigate to Modules and click on it. It will add a new module within the VBE to work on.

VBA Public Variable Example 1-1

Step 2: Within the newly created module, add Option Explicit command at the start of your VBA code (before creating any variable). Option Explicit helps you if there is any typo while using the defined variable. If the used variable is not defined in the system, this option throws an error message saying the variable is not defined.

Code:

Option Explicit

VBA Public Variable Example 1-2

Step 3: Use a Dim statement to create a new variable named my_name with data type as String.

Code:

Option Explicit

Dim my_name As String

VBA Public Variable Example 1-3

If you could pay attention, we have defined this variable explicitly. Meaning, it is not a part of specific sub-procedure. Which automatically makes this variable available for all the codes within the module created (Module1). This is called as defining a variable publicly within a module. This variable can be used anywhere across the multiple sub-procedures within the same module.

Suppose the code given below, I have assigned value to variable my_name as “Lalit Salunkhe”.

Code:

Option Explicit

Dim my_name As String

Sub Ex_1()

my_name = "Lalit Salunkhe"
MsgBox "My name is: " & my_name

End Sub

VBA Public Variable Example 1-4

If I run this code, I will see a message box as shown in the screenshot below:

VBA Public Variable Example 1-5

We can also use this variable in different sub-proc within the same module. See the screenshot given below:

Code:

Option Explicit

Dim my_name As String

Sub Ex_1()

my_name = "Lalit Salunkhe"
MsgBox "My name is: " & my_name

End Sub

Sub Ex_2()

Dim my_age As Integer
my_name = "Lalit Salunkhe"
my_age = 28
MsgBox "My name is: " & my_name & "; My age is: " & my_age

End Sub

VBA Public Variable Example 1-6

See the Ex_2 sub-proc. It has a new variable defined my_age. I have used my_name to add my name and my_age to add the current age of mine. You can see my_name is still accessible within the second sub-procedure. Once I run this code, we can get the output as a message box shown below:

VBA Public Variable Example 1-7

This is how we can define a variable publicly within a module.

Example #2 – Define a Variable Publicly Across all the Modules

In order to achieve this feat, you need to use the keywords as Public or Global instead of Dim which sets the variable as public for all the module wherever you work across.

Step 1: Create a new module. Go to Insert tab, click on Module to add a new module under VBE.

VBA Public Variable Example 2-1

Step 2: Add Option Explicit within the module.

Code:

Option Explicit

VBA Public Variable Example 2-2

Step 3: Define a new variable named var1 and var2 both as a type integer. But this time using Public statement instead of Dim (which we used previously).

Code:

Option Explicit

Public var1 As Integer
Public var2 As Integer

VBA Public Variable Example 2-3

Step 4: Now, these two variables can be used across multiple modules if created under the same VBE. See the example given below:

Code:

Option Explicit

Public var1 As Integer
Public var2 As Integer

Sub Ex_3()

var1 = 10
var2 = 100
MsgBox "Value for var1 is: " & var1 & Chr(13) & "Value for var2 is: " & var2

End Sub

VBA Public Variable Example 2-4

We have assigned the values to both of the variables using a MsgBox feature, trying to display values for those two variables.

Step 5: Run the code by pressing F5 key or by clicking on Play Button, you can see an output as shown below:

VBA Public Variable Example 2-5

Step 6: We will use the same variables under Module1 which we have used in the first example and try to figure out whether the variables are accessible there or not.

Code:

Option Explicit

Dim my_name As String

Sub Ex_1()

my_name = "Lalit Salunkhe"
MsgBox "My name is: " & my_name

End Sub

Sub Ex_2()

Dim my_age As Integer
my_name = "Lalit Salunkhe"
my_age = 28
MsgBox "My name is: " & my_name & "; My age is: " & my_age

End Sub

Sub Ex_public()

var1 = 1000
var2 = 999
MsgBox "Value for var1 is: " & var1 & Chr(13) & "Value for var2 is: " & var2

End Sub

VBA Public Variable Example 2-6

If you could see the screenshot above, we have used the same set of variables defined under Module2 and have values like 1000 and 999 respectively. We are using MsgBox function to present the values of these two variables respectively. Run the code and you can see the output as below:

VBA Public Variable Example 2-7

This is how a variable can be used publicly within the module and across the modules.

Things to Remember

  • It is always preferred to define the variables explicitly in VBA. So that they are not limited to the sub-procedure they are defined within.
  • You can assign public variable in two ways: one within a module using conventional Dim statement and second across all the modules present in VBE using either of the Public or Global statement.

Recommended Articles

This is a guide to VBA Public Variable. Here we discuss how to declare Public Variable in Excel using VBA code in excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Global Variables
  2. VBA Web Scraping
  3. VBA FileSystemObject
  4. VBA AND
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) Learn More