Variables and Data types in VBA

Variable is a named memory location used to hold a value that can be changed during the script execution.Later, you can use that value in code and execute.  The computer will fetch that value 

from the system and show in the output. Each variable must be given a name.

To name the variable in VBA, you need to follow the following rules.

• You must use a letter as the first character.

• You can't use a space, period (.), exclamation mark (!), or the characters @, &, $, # in the name.

• Name can't exceed 255 characters in length.

• You cannot use Visual Basic reserved keywords as variable name.

       

Valid Names	Invalid Names
My_Watch	My.Watch
NewCar1	1_NewCar (not begin with number)
EmployeeID	Employee ID ( Space not allowed)

Syntax

In VBA, you need to declare the variable before using them.

Dim <<variable_name>> As <<variable_type>>

Dim : Dim is short for the word Dimension and it allows you to declare variable names and their type.  Dim is often found at the beginning of macro codes.

<<variable_name>> : After writing Dim you need to write the variable name that you are going to use in your code.

<<variable_type>> : It is the type of data that we are going to store in our variable.

 For example : if we are going to store a whole number then we will use integer, or if we are storing a decimal value then we will use Single etc.

Below code shows that more than one variable with different-different data types can be declared in a single line.

Dim a, b, c As Single, x, y As Double, i As Integer
' a, b, and c are all Single; x and y are both Double

here, a, b,c variables are declared as Single.

x, y variables are declared as Double.

i variable is declared as an Integer.

Data Types

Data types are just the types of data that we are going to store in our declared variables in the code. There are a lot of data types, which can be divided into two main categories.

• Numeric Data Types

• Non-numeric Data Types

Numeric Data Types

Below table clearly displays the numeric data types and the allowed range of values accordingly.

Type	Range of Values
Byte	0 to 255
Integer	-32,768 to 32,767
Long	-2,147,483,648 to 2,147,483,648
Single	-3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values.
Double	-1.79769313486232e+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232e+308 for positive values.
Currency	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal	+/- 79,228,162,514,264,337,593,543,950,335  if no decimal is use +/- 7.9228162514264337593543950335  (28 decimal places).

Non-Numeric Data Types

Following table displays the non-numeric data types and the allowed range of values accordingly.

Type	Range of Values
String (fixed length)	1 to 65,400 characters
String (variable length)	0 to 2 billion characters
Date	January 1, 100 to December 31, 9999
Boolean	True or False
Object	Any embedded object
Variant (numeric)	Any value as large as double
Variant (text)	Same as variable-length string

       

Example

Program that will display the Student Name with Roll No and his/her Birth-Date in the form of Dialogue Box i.e. MsgBox

Private Sub Student_Click()
   Dim name As String
   name = "Abhishek Singh"

   Dim num As Integer
   num = 101

   Dim BirthDay As Date
   BirthDay = DateValue("30 / 10 / 2020")

   MsgBox "Name is " & Name & Chr(10) & "Roll No is " & num & Chr(10) & "Your Birth-Date is " & BirthDay
End Sub

Below program is same as above it only shows that all the variables with their data types can also be written in a single line but cannot be initialized or given a value while declaring.

They can be initialized in further codes.

Private Sub Student_Click()
   Dim name As String, num As Integer, Birthday As Date
   
   name = "Abhishek Singh"   

   num = 101

   Birthday = DateValue("30 / 10 / 2020")

   MsgBox "Name is " & name & Chr(10) & "Roll No is " & num & Chr(10) & "Your Birth-Date is " & Birthday
End Sub

Output

Output of two programs below upon executing the script.