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

Intel Mobile Pentium III and Tualatin Pentium III-M Guide

The October 1999 announcement of a range of 0.18-micron Pentium III processors included the launch of the first mobile Pentium IIIs. The new processors - available at speeds of 400MHz, 450MHz and 500MHz and featuring a 100MHz system bus - … [Read More...]

Defend Your Computer Against Ransomware

When you are infected by Ransom-malware, you’ll recognize it, for this is a malware that hinder the functionality of your computer access. It will demand a ransom payment so that the functionality of the system will work again. Locking your computer in some way is what Ransomware program does. It … [Read More...]

V92 Technology

Announced in 2000, the ITU's V.92 analogue modem standard has the same download speed as the V.90 standard (56 Kbit/s) but increases the maximum upload speed from 33.6 Kbit/s to 48 Kbit/s. As well as this performance improvement - referred to as PCM … [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...]

10 Exceptional Books on ChatGPT that Will Blow Your Mind

ChatGPT is a powerful new AI tool that is taking the world by storm. You are going to find a lot of amazing books that will teach you how to make the … [Read More...]

Guides

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

Recent Posts

Get the Facts on Virtualization

If you have any kind of involvement with IT in business then you have probably heard of the technology known as virtualization. It is changing the way … [Read More...]

Digital Video Television

Digital TV For years, the broadcasting and computer industries have proclaimed that "convergence" was just around the … [Read More...]

RAID tutorial – preparing for the installation

You'll need first to complete a couple of preparatory steps: 1. Create a floppy disc containing the relevant RAID Controller drivers Locate the … [Read More...]

[footer_backtotop]

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