In general, variables are something that varies , just like the files in your cabinet or the mailboxes where their content always change from time to time. In Excel VBA 365 , variables are areas allocated by the computer memory to hold data.
Like the mailboxes, each variable must be given a name. The following are the rules when naming a variable :
Examples of valid and invalid variable names are displayed in Table 2.1
|Valid Name||Invalid Name|
Excel VBA 365 data types can be grossly divided into two types, the numeric data types and non-numeric data types. They are classified below:
Numeric data types are types of data that consist of numbers, which can be computed mathematically with various standard operators such as addition, subtraction, multiplication, division and more. Examples of numeric data types are examination marks, height, weight, number of students in a class, share values, the price of goods, monthly bills, fees and others.
|Data Type||Storage||Range of Values|
|Byte||1 byte||0 to 255|
|Integer||2 bytes||-32,768 to 32,767|
|Long||4 bytes||-2,147,483,648 to 2,147,483,648|
|Single||4 bytes||-3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values.|
|Double||8 bytes||-1.79769313486232e+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232e+308 for positive values.|
|Currency||8 bytes||-922,337,203,685,477.5808 to 922,337,203,685,477.5807|
|Decimal||12 bytes||+/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/- 7.9228162514264337593543950335 (28 decimal places).|
Non-numeric data types are data that cannot be manipulated mathematically using standard arithmetic operators. The non-numeric data comprises text or string data types, the Date data types, the Boolean data types that store only two values (true or false), Object data type and Variant data type .They are summarized in Table 2.3
|Data Type||Storage||Range of Values|
|String(fixed length)||Length of string||1 to 65,400 characters|
|String(variable length)||Length + 10 bytes||0 to 2 billion characters|
|Date||8 bytes||January 1, 100 to December 31, 9999|
|Boolean||2 bytes||True or False|
|Object||4 bytes||Any embedded object|
|Variant(numeric)||16 bytes||Any value as large as Double|
|Variant(text)||Length+22 bytes||Same as variable-length string|
We can test out the type of data held by a variant by using the function VarType. To run the test, enter the Excel VBA 365 code IDE and create a subroutine such as below:
Sub TestVarType() MyVariable1 = "Francis" MsgBox VarType(MyVariable1) MyVariable1 = 1234 MsgBox VarType(MyVariable1) End Sub
In Excel VBA 2010, we need to declare the variables before using them by assigning names and data types.
We can use a variable without openly(explicitly) declare it if we assign an initial value to it. For example,
Excel 2010 VBA will automatically create a variable MyFirstName as a variant, and it will hold the data as John. This type of declaration is called implicit declaration.
Implicit declaration of the variable often leads to errors in writing code, therefore, it is better to declare a variable explicitly. It is declared using the Dim statement as follows:
Dim VariableName As DataType
If you want to declare more variables, you can declare them in separate lines or you may also combine more in one line, separating each variable with a comma, as follows:
Dim VariableName1 As DataType1, VariableName2 As DataType2, VariableName3 As DataType3
Dim password As String Dim yourName As String Dim firstnum As Integer Dim secondnum As Integer Dim total As Integer Dim doDate As Date Dim password As String, yourName As String, firstnum As Integer
For fixed-length string, you can use the statement to declare the variable:
Dim VariableName as String * n
where n defines the number of characters the string can hold. Example:
Dim yourName as String * 10
yourName can hold no more than 10 Characters.
Other than using the Dim keyword to declare the data, you can also use other keywords to declare the data. These keywords indicate the scope of the declaration, they are private ,static and public,as follows:
Private VariableName as Datatype Static VariableName as Datatype Public VariableName as Datatype
The Private declares a local variable or a variable that is local to a procedure or module in Excel VBA 365.
The Static keyword declares a variable that can be used multiple times, even after a procedure has been terminated. Most variables created inside a procedure are discarded by Visual Basic when the procedure is finished, static keyword preserves the value of a variable even after the procedure is terminated.
The Public keyword declares a global variable, which means it can be used by all the procedures and modules of the whole program.
Constants are different from variables in the sense that their values do not change during the running of the program. In Excel VBA 365 , the statement to declare a constant is
Const Constant Name As Data Type = Value
Const Pi As Single=3.142 Const Temp As Double=37 Const Score As Single=100