Note that variable names must begin with an
alphabetic character, can't be longer than 255 characters, can't contain an
embedded period or type-declaration character, and must be unique within the
same scope. It is recommended to follow a naming convention and capitalize the
second word, or the first too (although first capitalization is rather used to
name procedures).
Types of Variables
Excel VBA accepts different types of variables that
correspond to the different types of data. The table below shows the different
types of VBA variables with their respective storage size and value range. A
Boolean variable has just two possible values and requires 2 bytes of memory.
Integer, Long, Double variables contain numerical values, while String
variables store text.
Data
type
|
Storage
size
|
Range
|
Boolean
|
2 bytes
|
True or False
|
Byte
|
1 byte
|
0 to
255
|
Currency (scaled integer)
|
8 bytes
|
-922,337,203,685,477.5808
to 922,337,203,685,477.5807
|
Date
|
8 bytes
|
January
1, 100, to December 31, 9999
|
Double (double-precision floating-point)
|
8 bytes
|
-1.79769313486231E308
to -4.94065645841247E-324 for negative values
4.94065645841247E-324 to 1.79769313486232E308 for positive values |
Integer
|
2 bytes
|
-32,768
to 32,767
|
Long (Long integer)
|
4 bytes
|
-2,147,483,648
to 2,147,483,647
|
Single (single-precision floating-point)
|
4 bytes
|
-3.402823E38
to -1.401298E-45 for negative values
1.401298E-45 to 3.402823E38 for positive values |
String (variable-length)
|
10
bytes + string length
|
0 to
approximately 2 billion Unicode characters
|
Variant (numeric)
Variant (text)
|
16
bytes
22
bytes + length
|
Any value
up to the range of a Double
Same as String
|
Variable Declaration
Variables need to (or should) always be
declared. In the declaration statement, a data type can be specified or not. If
not specified, Excel will assign the “Variant” data type, which is the heaviest
of them all, and can accommodate any type of data (numbers, string, etc). The
following statement is used to declare a variable:
Dim myVariable As DataType
Let’s see some variable declaration examples:
Dim availableItems As Integer
Dim myName As String
Dim deliveryDate as Date
Option Explicit is a command used at the top of
the module to make variable declaration mandatory. If we use Option Explicit
and do not declare a variable we get an error.
Variable Definition
To define a variable means to assign a value to
that variable, and is done using the = symbol as shown in the following
expression.
myVariable
= AnyValueHere
Let’s see some examples of variables definition:
myName = "John"
availableItems = 139
deliveryDate = "12/06/2020"
Variables can also be passed on information in
the workbook by referring to the relevant object. In most cases, that’s the
range or cell in a particular worksheet. Similarly, we can assign the value of
a variable to a particular range or cell with the Value property.
userName = ActiveSheet.Range("B2").Value
ActiveSheet.Range("D8").Value = approvalDate
We can perform mathematical operations with
numerical variables or concatenation and other text manipulations with string
variables. See below some examples.
TotalCost = numberOfItems * itemPrice
myFullName = myName & " " & mySurname
Scope of Variables
Variables can be declared at different levels in
the VBA project. Variables declared at the procedure level are only available
within that particular procedure. They hold the value only until the procedure
ends (if declared with the Dim statement).
The following example adds 1 to the
availableItems variable. As the variable is declared inside the procedure,
availableItems only exists there and until the Sub ends. The message box always
shows the same number because every time we run the macro the variable is
reset.
Sub AddToItems()
Dim availableItems As Integer
availableItems = availableItems + 1
MsgBox "Total items available = "
& availableItems
End Sub
We can keep the value in the computer’s memory after
the Sub ends using the ‘Static’ statement declaration. In this case, every time
we run the macro it will add 1 item and keep that in memory until the project
is reset (by editing the code or pressing stop).
Static availableItems As Integer
If we want a variable to be available to other
procedures we need to declare it at the module level. That’s done with the Dim
statement in the general declaration section of that module. That’s the section
where we add the Option Explicit statement explained earlier. Variables
declared at module level behave as Static and hold the value until the project
is reset.
The general declarations section is the code at
the top of the module and does not have any procedure statements such as Sub,
Function, etc. In the example above, we declare the variables uName and uRole
there, so they are available throughout the module. This means, if we assign a
value to uName in the first procedure (GetInfo), it holds the value and can be
used in any other procedures in that module. On the other hand, the uDate
variable in that example has been declared at the procedure level and therefore
can only hold its value within the GetInfo procedure (would not have any value
in the DisplayInfo procedure).
Variables declared with Dim (or Static) are
private and can only be accessed within a procedure or module. But sometimes we
need to carry over the value of a variable to other modules or Userforms. In
that case we need to use global (or public) variables. We declare these with
the ‘Public’ statement in the general declarations section at the top of a
module.
Object Variables
Variables can also store objects such as a
Worksheet or a Range object, thus making it easier to refer to multiple
instances of the same object. Object variables are declared in the same way we
just learnt, but with an object as data type.
Dim Rng As Range
Dim Payments As Worksheet
However, to define object variables we use the
Set statement as follows:
Set Payments = Worksheets("Sheet1")
Set Rng = Worksheets("Sheet2").Range("A1:D8")
Then we can refer and use the object variable in
the same way we do with any other object.
Payments.Range("B1").Value = "Import"
Rng.Interior.Color = vbYellow
No comments:
Post a Comment