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

Ethernet Networks

To adapt the technology for the consumer market, Ethernet home network vendors have designed networking kits - consisting of low-cost network adapters, an inexpensive non-managed hub and simple configuration software - to make the technology easier to … [Read More...]

DSTN LCD monitors

A normal passive matrix LCD comprises a number of layers. The first is a sheet of glass coated with metal oxide. The material used is highly transparent material so as not to interfere with the quality of the image's integrity. This operates as a grid of row and column electrodes which passes the … [Read More...]

Cloud Technology Changes the Calculus of Risk Management in a Global Economy

Cloud technology has changed the state of the global economy in previously unimaginable ways. Ninety-four percent of enterprises utilize some form of cloud technology. The cloud has helped create a more interconnected economy, as more companies use cloud-based internet hosting and cloud SaaS tools … [Read More...]

Gaming Laptop Security Guide: Protecting Your High-End Hardware Investment in 2025

Since Jacob took over PC Tech Guide, we’ve looked at how tech intersects with personal well-being and digital safety. Gaming laptops are now … [Read More...]

20 Cool Creative Commons Photographs About the Future of AI

AI technology is starting to have a huge impact on our lives. The market value for AI is estimated to have been worth $279.22 billion in 2024 and it … [Read More...]

13 Impressive Stats on the Future of AI

AI technology is starting to become much more important in our everyday lives. Many businesses are using it as well. While he has created a lot of … [Read More...]

Graphic Designers on Reddit Share their Views of AI

There are clearly a lot of positive things about AI. However, it is not a good thing for everyone. One of the things that many people are worried … [Read More...]

Redditors Talk About the Impact of AI on Freelance Writers

AI technology has had a huge impact on our lives. A 2023 survey by Pew Research found that 56% of people use AI at least once a day or once a week. … [Read More...]

11 Most Popular Books on Perl Programming

Perl is not the most popular programming language. It has only one million users, compared to 12 million that use Python. However, it has a lot of … [Read More...]

Guides

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

Recent Posts

Virtual Memory

Video Covering Virtual Memory - Part of our Fix Slow Computer tutorial found on  … [Read More...]

Kong Games Ads Removal

Kong Games Ads is another type of adware that will display ads on your computer. They will show up in the web pages you visit and can become very … [Read More...]

On the Go With Microsoft Office

If you have a tablet with Windows 10 installed, you are in for an extra bonus. Microsoft unveiled its new Office Mobile applications. These include … [Read More...]

[footer_backtotop]

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