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

Understanding the Basics of Visual Basic Script

Visual Basic Script, derived from the programming language "Visual Basic".Excerpt from the article "Playing with VBScript: TBS Gladiators": Visual Basic Script (…) comes bundled with Windows operating systems -since about 1996-, and is part of Microsoft's Visual Basic engine -year 1991 to … [Read More...]

Magnetic RAM

In late 2000 IBM and German semiconductor company Infineon Technologies AG announced plans to collaborate in the development of Magnetic Random Access Memory (MRAM) - a breakthrough memory technology that could significantly increase battery … [Read More...]

Website Builders Make Web Design Available to Everyone

The Internet is a powerful communication medium. It gives people the power to share their ideas, passions or business with millions of people. And, it can do it all hours of the day. People have come to rely on websites as a way to get their point across or promote their business. However, building … [Read More...]

Why Cross-Chain Trading Is the Future of Crypto Investing?

The rapid growth and evolution of the cryptocurrency market have opened up exciting opportunities for investors. Within this dynamic landscape, … [Read More...]

Revolutionize Your Internet Experience with Orbi 960 – The Ultimate WiFi System

In a world where seamless connectivity is essential, slow and unreliable internet connections are a major problem. Whether you are running a business, … [Read More...]

Do You Need a VPN When Trading Cryptocurrency?

There’s no doubt that the biggest global industries in 2023 are tech-driven, while there remains a significant crossover between many of these … [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...]

Guides

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

Recent Posts

Guide to PowerNow! – AMD’s dynamic power management technology

PowerNow! is effectively AMD's version of Intel's SpeedStep technology. The premise is a simple one: some applications … [Read More...]

Transferring Data Old Computer To A New Computer

In this particular article, we will discuss a few methods of transferring data from an old computer to a new one. Some of these solutions will … [Read More...]

MSI 890GXM-G65 Review

Price: $127. The MSI 890GXM-G65 motherboard packs a lot of power in a very small space. Bang for buck you are getting a lot for your money.is an Intel … [Read More...]

[footer_backtotop]

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