In VBA, and in programming in general, variable is a resource for saving information.
Variable = "An information"
Variables are used to store data, such as numbers or texts.
Before using a variable it is advisable to declare it, indicating its name (and its data type, which will be seen later).
A declaration is made through the word Dim, as in the example below:
Dim VarText Dim VarNumber VarText = "I was previously declared"
The variable name, which comes after the Dim keyword, must comply with the following rules:
When naming a variable, give preference to specific names such as DiscountSales instead of generic names like Var1. This will make the code much easier for other users to understand.
Do not start variables with numbers!
Dim 1variable 'This statement is wrong!
After defining the variable name, you should also declare its data type. This tells VBA what data type to expect from each variable, avoiding errors with improper associations and making it possible to optimize the memory allocation of the computer.
Dim VarText As String 'String is a data type for texts Dim VarNumber As Integer 'Integer is a data type for numbers
By default in VBA it is not mandatory to declare variables, but this helps in the code performance and in the accuracy of the results.
The table below shows the main data types along with their data entries, byte allocations, and ranges:
|Data Type||Data Entry||Byte Allocation||Allowed Range|
|Byte||Numeric||1||0 a 255|
|Integer||Numeric||2||-32.768 a 32.767|
|Long||Numeric||4||-2.147.483.648 to 2.147.483.647|
|String||Text||1||1 per character|
|Date||Date||8||January 1, 100 to December 31, 9999|
|Boolean||Binary||2||True or False|
All variables with an undeclared type are considered Variant. This sorting of data requires more of the computer that will run the code.
It is a good practice to accurately declare the data type of all variables, not only for optimization, but also because it is a necessary practice in some other programming languages.
The Decimal type can not be declared, although it exists. You can only access it through an association or conversion (with , which will be seen later).
When we declare a variable, a default value is assigned to it according to its type:
Variables declared outside the scope of a Sub can be used in multiple subroutines:
Dim text As String Sub Exemple1() text = "A text" End Sub Sub Exemple2() MsgBox text End Sub
Run Sub Exemple1 and then run Sub Exemple2
Variables declared as Public can be used in multiple Modules
Public globalText as String
You can assign a value to a variable after declaring it.
In VBA the values are assigned from right to left($\leftarrow$), using the equal sign (=).
VarText = "SuperExcelVBA" VarNumber = 10 MsgBox VarText MsgBox VarNumber
One way to get values from users is through the Inputbox.
VarName = Inputbox("What is your name?") Msgbox VarName
Try to run the code below to see what happens!
Sub experiment() VarNome = Inputbox("What is your name?") Msgbox VarNome End Sub
Using the same concept of association (from left to right), we can perform operations. Code execution always occurs from top to bottom.
VarNumber = 5 VarNumber = VarNumber + 7 Msgbox VarNumber
As a result from running the code above we see that VarNumber equals 12.
VBA programming should not be confused with mathematical equations. The example above would be equivalent to VarNumber $\leftarrow$ 7 + VarNumber. Since VarNumber had the value 5 assigned to it, the second assignment of VarNumber would be equivalent to VarNumber = 7 + 5, resulting in 12.
It is often possible to associate the content of one data type with another when declaring variables. However, VBA will attempt to convert the content to the declared data type (E.g. a number as text). Failure to do so will return an error.
Dim VarText As String VarText = 1
The VarText above will be a text (and you will not be able to perform mathematical calculations with it).
Dim VarNumber As Integer VarNumber = "Two"
VBA will try to assign "Two" to VarNumber performing the automatic conversion, but it will return an error:
In order for the conversion to work the Assassination should be done with "2" (instead of "Two").
When debugging a code, try to use the Locals Window in which all data types are explicit.
Boolean variables only accept the values True and False. However, if your system language is not English, a translated equivalent may be displayed in message boxes:
Although the MsgBox displays "Verdadeiro" (which is the Portuguese-BR translation for true), in the code the value remains as True.
SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.
Excel ® is a registered trademark of the Microsoft Corporation.
© 2021 SuperExcelVBA | ABOUT