VBA Variables, Data Types & Declare VBA Constants in Excel

Variables are used in almost all computer program and VBA is no different. It’s a good practice to declare a variable at the beginning of the procedure. It is not necessary, but it helps to identify the nature of the content (text, data, numbers, etc.)

In this VBA tutorial, you will learn-

• VBA Variables
• VBA Data Types
• Constant in VBA

VBA Variables

Variables are specific values that are stored in a computer memory or storage system. 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 of the Excel VBA variable types must be given a name.

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

• It must be less than 255 characters
• No spacing is allowed
• It must not begin with a number
• Period is not permitted

Here are some example for Valid and Invalid names for variables in VBA.

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

In VBA, we need to declare the variables before using them by assigning names and data type.

In VBA, Variables are either declared Implicitly or Explicitly.

• Implicitly: Below is an example of a variable declared Implicitly.
• label=guru99
• volume=4
• Explicitly: Below is an example of variable declared Explicitly. You can use “Dim” keyword in syntax
• Dim Num As Integer
• Dim password As String

VBA variable is no different than other programming languages. To declare a variable in VBA you use the keyword “Dim.”

Syntax for VBA Variable,

To declare a variable in VBA, type Dim followed by a name:

Sub Exercise ()
        Dim <name>
End Sub

Before we execute the variables we have to record a macro in Excel. To record a macro do the following –

Step 1): Record the Macro 1

Step 2) : Stop Macro 1

Step 3): Open the Macro editor, enter the code for variable in the Macro1

Step 4): Execute the code for Macro 1

Example, for VBA Variable

Sub Macro1()
	Dim Num As Integer
	Num = 99
	MsgBox " Guru " & Num
End Sub

When you run this code, you will get the following output in your sheet.

Excel VBA Data Types

Computer cannot differentiate between the numbers (1,2,3..) and strings (a,b,c,..). To make this differentiation, we use Data Types.

VBA data types can be segregated into two types

• Numeric Data Types

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

Data Type	Bytes Used	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
Boolean	2 bytes	True or False
Date	8 bytes	January 1, 100 to December 31, 9999
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

In VBA, if the data type is not specified, it will automatically declare the variable as a Variant.

Let see an example, on how to declare variables in VBA. In this example, we will declare three types of variables string, joining date and currency.

Step 1) Like, in the previous tutorial, we will insert the commandButton1 in our Excel sheet.

Step 2) In next step, right-click on the button and select View code. It will open the code window as shown below.

Step 3) In this step,

• Save your file by clicking on save button 
• Then click on Excel icon  in the same window to return the Excel sheet.
• You can see the design mode is “on” highlighted in green

Step 4) Turn off design mode, before clicking on command button

Step 5) After turning off the design mode, you will click on commandButton1. It will show the following variable as an output for the range we declared in code.

• Name
• Joining Date
• Income in curreny

Constant in VBA

Constant is like a variable, but you cannot modify it. To declare VBA constants, you can use keyword Const.

There are two types of constant,

• Built-in or intrinsic provided by the application.
• Symbolic or user defined

You can either specify the scope as private by default or public. For example,

Public Const DaysInYear=365

Private Const Workdays=250

Download Excel containing above code

Summary:

• Variables are specific values that are stored in a computer memory or storage system.
• You can use VBA Dim types keyword in syntax to declare variable explicitly
• VBA data types can be segregated into two types
• Numeric Data Types
• Non-numeric Data Types
• In VBA, if the data type is not specified. It will automatically declare the variable as a Variant
• Constant is like a variable, but you cannot modify it. To declare a constant in VBA you use keyword Const.