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

2008 to 2009 Updates of the PCTechGuide

Date Details of recent updates 18May 09 New page on the digital image series explaining colour palettes, colour look-up tables, and particularly how to make bitmaps with the RGB colour system 14May 09 New Article - Digital Computer Image and Video Data This new … [Read More...]

Evolution of the motherboard

The original PC motherboard had a minimum of integrated devices, just ports for a keyboard and a cassette deck (for storage). Everything else, including a display adapter and floppy or hard disk controllers, were add-in components, connected via expansion slots. Over time, more devices were … [Read More...]

Digital Video Performance Requirements

Digital video relies primarily on hard disk power and size, and the important characteristic is sustained data throughput in a real-world environment. Video files can be huge and therefore require a hard disk drive to sustain high rates of data … [Read More...]

Goodbye Bitcoin: the 3 alternative cryptocurrencies that have great upside potential, according to experts

Bitcoin has been a very lucrative investment for people that got into it early. One report from The Motley Fool pointed out that $10 of bitcoin … [Read More...]

Self-driving cars face their Achilles’ heel and may be targets of hackers

The market for self-driving cars is booming. Customers spent $22.22 billion on these autonomous vehicles in 2021 and they will likely spend more in … [Read More...]

How to avoid scams with bitcoin and other cryptocurrencies

Cryptocurrencies got a bad reputation when scams multiplied like ants on a piece of cake. Even today many people associate bitcoin and other … [Read More...]

WHAT IS CLOUD COMPUTING AND WHAT ARE ITS MAIN BENEFITS?

Users are Increasingly using cloud computing to store their information, which is replacing local storage. The business digitization process goes … [Read More...]

AI-Driven Software Improves Trading Strategies for Investors

Artificial intelligence technology is changing the state of modern finance. A growing number of investors are utilizing AI to improve the … [Read More...]

What You Need to Know When Choosing the Best VPN for an iPhones

Today millions of people have smartphones; most use them to access the Internet. Research shows that more than 50% of Internet users access it with … [Read More...]

Guides

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

Recent Posts

CrossFire Graphic Cards

Launched at Computex in the summer of 2005, ATI launched their own tandem graphics card solution, branded CrossFire, and … [Read More...]

Principles of CPU architecture – logic gates, MOSFETS and voltage

The underlying principles of all computer processors are the same. Fundamentally, they all take signals in the form of 0s and 1s (thus binary … [Read More...]

Travan Tape Storage

While the build quality of QIC/Travan cartridges is superb - making them ideal for long-term storage - for a long time … [Read More...]

[footer_backtotop]

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