pctechguide.com

  • Home
  • Guides
  • Tutorials
  • Articles
  • Reviews
  • Glossary
  • Contact

Working with Dim, Public, Private and Static Variables in VBA

VBA is a powerful application that offers a lot of versatility in Microsoft Office applications such as Word and Excel. In this article, we are going to the scope of the variables. The scope refers to where our variables apply once they are defined. It is important to know that the scope of a variable is determined at the moment the variable is declared.
In VBA for Excel, the three existing scopes for variables are: Public, Module and Procedure.

We start with the most common one, the Procedure scope, that is, the variable thus defined is recognized only within the procedure in which we declare it.
We can declare a local procedure variable with a Dim or Static instruction.

When we define or declare a local variable with the Dim instruction, the variable remains in memory only while the procedure in which we have declared it is executed, and therefore, normally, when the execution procedure ends, the values of the local variables of the procedure are not retained and the memory allocated to those variables is freed. The next time the procedure is executed, all declared local variables will be reinitialized.

An example of a DIM local variable:
In any module of our VBA project we insert the following procedures:
01
Sub Local1()
02
Dim X As Integer ‘local variable inside the Procedure
03
X = 1313
04
MsgBox “X has a value of ” & X
05
End Sub
06
”””””””””””””””””””””””””””””””””
07
Sub Local2()
08
Dim X As String ‘local variable inside the Procedure
09
X = “Affirmative
10
MsgBox “The answer was ” & X
11
End Sub

What we are going to tell you next is very important!!! Each variable X is independent of the other procedure, only the variable is recognized within its own respective procedure (Local1 or Local2).

The other way to define a local variable of Procedure is to declare the variable as Static. A local variable declared with the Static statement continues to exist the entire time it is executed in Visual Basic. The variable is reset when any of the following actions occur:

  • The macro generates an unintercepted runtime error.
  • VBA stops.
  • We exit Excel.
  • We change the module.

An example of a STATIC local variable:
In any module of our VBA project we insert the following procedures:
1
Sub Static()
2
Static Acum ‘local variable that retains its value
3
‘after the end of the procedure…
4
num = Application.InputBox(prompt:=”Enter a value: “, Type:=1)
5
Acum = Acum + num
6
MsgBox “The accumulated (static) variable returns a value ” & Acum
7
End Sub

This is also very important! In our Static procedure, the variable ‘Acum’ retains its value each time it is executed. For example, the first time the module is executed, we enter in the box the value 10, the MsgBox will show the value 10 (logically)… if the next time the module is executed, we enter the value 20, the MsgBox will show the value of 30 (10+20…) !!!.

The next interesting area to know is Module.
A variable that is recognized in all procedures in a module is called a Module variable. A module level variable is available to all procedures in that same module, but is not available to procedures in other modules.
While VBA is running, until the module in which it is declared is modified, a module variable continues to exist.
You can declare module-scoped variables with a Dim or Private statement at the top of the module, above the first procedure definition.

In the module scope, there is no difference between Dim and Private. However, we should note that you cannot declare module scope variables inside a Procedure.
In general, a recommendation, if we use Private instead of Dim for module scope variables, the code will be easier to read that is, if we use Dim only for local procedure variables and Private for module variables, the scope of a given variable will be clearer.
An example of module variable:
In any module of our VBA project we insert the following procedures:
01
Variables A and B of scope Module
02
which are used for all the procedures of this module.
03
Dim A As Integer
04
Private B As Integer
05

06
Sub Procedure1()
07
A = 100
08
B = A + 1
09
End Sub
10

11
Sub Procedure2()
12
MsgBox “A is equal to ” & A
13
MsgBox “B is equal to ” & B
14
End Sub
15

16
Sub Procedure3()
17
Dim C As Integer ‘Procedure local variable
18
C = A + B
19
MsgBox “C is equal to” & C
20
End Sub
21

22
Sub Procedure4()
23
MsgBox A ‘This MsgBox displays the value of A
24
MsgBox B ‘This MsgBox displays the value of B
25
MsgBox C ‘This MsgBox does NOT show anything, because C is defined as a local procedure variable.
26
End Sub

Filed Under: Articles

Latest Articles

How To Manage Windows Startup Items and Double The Speed Of Your Computer

Sometimes, when you turn on your computer, it seems that windows takes forever to start up. There are several causes for this problem but one of the most common is because there are too many applications at startup. Having one or two applications that run at startup, usually isn't an issue, but any … [Read More...]

HSDPA

High-Speed Downlink Packet Access (HSDPA) is a new mobile telephony protocol that provides a smooth evolutionary path for UMTS networks to higher data rates and higher capacities, in the same way as EDGE does in the GSM world. Sometimes referred to as a … [Read More...]

Use TRACERT to Diagnose Network Issues

Windows comes with several commands that help you diagnose network issues. But, one of the more useful commands available to you is the TRACERT command. TRACERT is a command line tool that will show the different hops a packet will take as it reaches its destination. You can type TRACERT followed by … [Read More...]

Why Internet Bundles Are the Next Battleground for Gamers

A weak internet connection can turn seamless gaming sessions into frustrating experiences filled with lag, buffering or sudden disconnects. For … [Read More...]

Importance of Inbound Marketing in the Digital Age

A couple of months ago, Zacks reported that Hubspot was starting to make some major changes to its inbound marketing strategy. They talk a lot about … [Read More...]

Damage Control Strategies for Resolving Online PR Crises

Last July, Astrologer faced a major crisis after its CEO went viral at a ColdPlay concert when having an affair. This was just one of the many times a … [Read More...]

AI is Not Killing Computer Jobs Like Doomers Projected

There is no denying the reality that AI technology has played a massive role in disrupting our lives. A growing number of people claim that AI … [Read More...]

Everything You Need to Know About Sourcing Circuit Boards From U.S. Suppliers

In This Article This article includes: Why Source PCBs From the United States?How to Get a Quote From a U.S.-Based PCB ManufacturerThe Top U.S. … [Read More...]

Top Taplio Alternatives in 2025 : Why MagicPost Leads for LinkedIn Posting ?

LinkedIn has become a strong platform for professionals, creators, and businesses to establish authority, grow networks, and elicit engagement. Simple … [Read More...]

Guides

  • Computer Communications
  • Mobile Computing
  • PC Components
  • PC Data Storage
  • PC Input-Output
  • PC Multimedia
  • Processors (CPUs)

Recent Posts

Guidelines on Troubleshooting Python Code

Whenever we write a computer program we need to verify that it works as expected. Usually we execute the code, if necessary we provide some inputs, … [Read More...]

What Controls The Picture Quality of a Digital Camera

The picture quality of a digital camera depends on several factors, including the optical quality of the lens and … [Read More...]

Typo 2 Keyboard Case for iPhone 6

Typo 2 is the new keyboard case for iPhone 5/5s and 6 produced by Typo. It deliberately provides users the benefit of using a physical keyboard that a … [Read More...]

[footer_backtotop]

Copyright © 2026 About | Privacy | Contact Information | Wrtie For Us | Disclaimer | Copyright License | Authors