Different types of Variable in Excel VBA

As you have learn that, it is important to specify the cell format in Excel worksheet, so that Excel can calculate or look values smoothly and can ignore errors. Imagine that, a variable is a cell and to calculate or look values smoothly you have to specify the format of variables in Excel VBA. In variable there are different types of Variable Format. You need to set the matched variable format for the variable values you wish to store. For example if you wish a variable will store date, then set the variable type as Date. By this way you can use Long, Single, Boolean, Decimal, Currency etc. This discussion will help you to know about the types of Variable Format.


Types of Variable:

There are 11 different types of Variable format, which you can classified into 2 major groups. These are Numerical and Non-Numerical Variable formats.

Image 1: Variable Types

Numerical Variable Formats:

There are 6 Numerical Variable formats are available in Excel VBA. These formats are allowed for mathematical calculation.

Image 2: Numerical variables

(1) Byte:

A data type used to hold positive integer numbers ranging from 0 to 255. Byte variables are stored as single, unsigned 8-bit (1-byte) numbers. More clearly I would like to say, for declaring a variable that would hold natural numbers (which is not negative) whose range from 0 to 255, use the Byte data type. Here is an example:

Sub vt1()
   Dim x As Byte
      x = 255
      Range("A1").Value = x
End Sub

In the above code, x is set to Byte. so that, it will store numerical integer (not decimal) and obviously positive numbers from 0 to 255. The above code will run successfully and shows the result in A1 cell as 255. But if it was X = 256, or X = -1 then it would return run-time error 6 which said Overflow.

Image 3: Run-time error 6

If x was X = 240.51 or X = a12, then it would return run-time error 13, "Ttype Mismatch"

Image 4: Run-time error 13

Example of Byte data is Age, Class Roll Number which is 0 to 255, exam marks in specific subject etc.


(2) Integer:

To declare a variable that would hold a number that ranges from -32768 to 32767, use the Integer data type. It is the same thing as Byte but the difference is the range. Here is an example of declaring an integer variable:

Sub vtype1()
     Dim x As Integer
          x = 32767
          Range("A1").Value = x
End Sub

In the above code, x is set to Integer. so that, it will store numerical integer (not decimal) and this can stores Negative and Positive numbers from -32768 to 32767. The above code will run successfully and shows the result in A1 cell as 32767. But if it was X = 32768, or X = -32769 then it would return run-time error 6 which said Overflow.

If x was X = 240.51 or X = a12, then it would return run-time error 13, "Ttype Mismatch".

Examples of Integer variable format are Stock of a specific product, Monthly sales quantity of a brand shop.


(3) Long:

A long integer is a number that can be used for a variable involving greater numbers than integers. The range of this variable format is -2147483648 to 2147483648. To declare a variable that would hold such a large number, use the Long data type. Here is an example:

Sub vtype1()
       Dim x As Long
            x = -2147483648
            Range("A1").Value = x
End Sub

In the above code, x is set to Long. so that, it will store numerical integer (not decimal) and this can stores Negative and Positive numbers from -2147483648 to 2147483647. The above code will run successfully and shows the result in A1 cell as -2147483648. But if it was X = -2147483649, or X = 2147483648 then it would return run-time error 6 which said Overflow.

If x was X = 240.51 or X = a12, then it would return run-time error 13, "Type Mismatch".

Examples of Long variable format are country population. number of members of world wide organization.


(4) Single:

A decimal number is one that represents a fraction. As a Single variable type the negative value range is -3.402823E38 to -1.401298E-45 and for positive values the range is 1.401298E-45 to 3.402823E+38. Example:

Sub vtype1()
    Dim x As Single
    x = 142145214532.752
   Range("A1").Value = x
End Sub

I know it is difficult to read the 1.401298E-45 number format. But trust me you would love it while I clarify it. This type of number format is called Exponential Number Format in Excel. If you type in a cell more than 11 digit, then it would return "FirstDigit.2nd_4_DigitsE+(Total Digit - 1)". For Example in B3 cell, if you enter 1401298000000000000000000000000000000000000000 then it would return 1.401298E+45. Here is a sample table showing general number in exponential format:

Image 5: Exponential Format

(5) Double:

If you want to use a decimal number that requires a good deal of precision, declare a variable using the Double data type. A data type that holds double-precision floating-point numbers as 64-bit numbers in the range -1.79769313486232E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values. The number sign (#) type-declaration character represents the Double in Visual Basic. Here is an example of declaring a Double variable:

Sub vtype1()
    Dim x As Double
    x = 4521432.7525558
Range("A1").Value = x
End Sub

(6) Currency:

The Currency data type is used to deal with monetary values. A variable declared with the Currency keyword can store a value between -922337203685477.5808 to 922337203685477.5807. Don't use the commas in a number in your code. Also, when assigning a value to a currency-based variable, do not use the currency symbol. Here is an example of declaring it:

Sub vtype1()
       Dim x As Byte
       Dim y As Long
       Dim salary As Currency
            x = 100
            y = 33550
            salary = x + y
      Range("A1").Value = salary
End Sub

This will return the result in A1 cell as:

Image 6: Result of Currency Variable

Non-Numerical

There are 5 Non-Numerical Variable formats are available in Excel VBA. These formats are allowed for mathematical calculation.

Image 7: Non-Numerical Variables

(1) String:

A string is a character or a combination of characters that constitute text of any kind and almost any length. To declare a string variable, use the String data type. Here is an example:

Sub vtype1()

    Dim x As String
    x = "A Quick Brown Fox Jumps Over The Lazy Dog"
Range("A1").Value = x
End Sub

In the Code Window if you enter the above code then it will looks like below:

Image 8: String variable

(2) Date:

In Excel VBA, Date variable type can be used to store a date value. Therefore, to declare either a date or a time variables, use the Date data type. Here is an example:

Sub vtype1()

    Dim x As Date
    x = DateValue("5 Jun 2015")
Range("A1").Value = x
End Sub

The above code will return the date in A1 cell.

Image 9: Date variable

(3) Boolean:

Simply use a Boolean variable to hold the value True or False. Like a number or a string, a Boolean value can be stored in a variable. To declare such a variable, use the Boolean keyword. Here is an example:

Sub vtype1()

    Dim x As Boolean
    x = True
If x = True Then
    Range("A1").Value = "It is TRUE that you are the Boss"
If x = False Then
    Range("A1").Value = "It is FALSE that I'm the Boss"
  End If
  End If
End Sub

The example will show the result as below:

Image 10: Boolean

(4) Object:

An object in Excel VBA is anything on the workbook which you are viewing. Even a workbook also an object. For example: Workbook, Worksheet, Cell, Range, Shape, Graph, Font, Background Color etc. Object has a property like font color, background color etc. 

Object variables are the variable types that have their names written in black (right hand side) when you define them - see example image below for Range, Worksheet and Workbook. These are the most common objects used. There are two important things to know about using object variables. 2 things are important while declaring an Object:

• While declaring Object variable, it will show in Black color in Code Window.
• After declaring the Object variable use the Set command to set the values equal to objects.
• Clear the RAM of your PC by applying the Nothing command at the end.

The first important thing is, in the Code Window Object variables are in Black color not the Blue color. See Worksheets, Workbook, Range are in normal Black text not in Blue text. These are Objects variable values.

Sub vtype1()

    Dim ws As Worksheet
    Dim wb As Workbook
    Dim rn As Range
End Sub

Image 11: Object variables declaring

The Second important thing to note is that you have to use the Set command to make the variable equal anything.

Image 12: Object Variable Equals by using Set command

Now to verify that, Object variable are working or not, just type the variable name WS and put a dot on it, then a popup window will open like below image:

Image 13: Popup window after putting a dot

The third thing, you should always set the object variable to Nothing at the end of your code. This frees up RAM and stops memory leakage which can slow Excel down. The final look for Code window is as below:

Sub vtype1()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim rn As Range
   
    Set wb = ThisWorkbook
    Set ws = Sheet1
    Set rn = Range("A1:B12")
   
    rn.Value = "test"
    rn.Font.Color = RGB(245, 125, 25)
       
    Set wb = Nothing
    Set ws = Nothing
    Set rn = Nothing
End Sub

Image 14: VBA Code using few Object variables

Now if you run the code then it will show the result as below:

Image 15: Object variable result on Excel Sheet

(5) Variant:

Variant data type A special data type that can contain numeric, string, or date data as well as the special values Empty and Null. The Variant data type has a numeric storage size of 16 bytes and can contain data up to the range of a Decimal, or a character storage size of 22 bytes (plus string length), and can store any character text. The VarType function defines how the data in a Variant is treated. All variables become Variant data types if not explicitly declared as some other data type.

Enter the below codes in Excel VBA Code window and run it:

Image 16:  Variant variable can perform various things

The result is:

Image 17: Variant variable result