In VBA Variable declaration are necessary to define a variable for a certain data type so that It can hole values, any variable which is not defined in VBA cannot hold values, there is an option to enable require variable declaration and variable declaration is done by the DIM keyword in VBA.
Variable Declaration in VBA
Before moving to the VBA variable declaration, let us first understand what variables are, what is the use of variables, and when should we use them.
What are the variables in VBA?
Word Variable itself defined variables are basically the name of memory in your location, which holds some value into that. You can pass a value in a code based on the type of variable. The value will be used while executing the code, and you will get the Output.
What is the use of Variable?
When you are creating a program or a code, it consists of some instructions that pass the information to the system about what to do with data. The data consist of two types of values, fixed and variable. Fixed values are also called as constant. Variables are defined by certain data types, i.e., Integer, Byte, string, etc. This helps us to identify the nature of the data we are entering, i.e., Text, Number, Boolean, etc.
How to Declare a Variable?
To declare a variable in code, you should assign a name to that variable. You can assign any name to a variable. However, it is advised to select a variable name which relates to data so that other user can also understand easily. For example, if you need to pass Integer data in the code, then the name variables like i_count or out. If you need to pass string value, then you can name that variable like strName
Variables can be declared anywhere in the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.. However, the coder is advised to declare them in starting of the code so that every user can understand the code very easily. The variable should be declared using Dim.
Examples of VBA Variable Declaration
Here are some examples for you to understand the VBA data type. You can try it on your computer.
Add a module in VBA EditorVBA EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.. Copy and paste the below codes one by one to see the result.
VBA Variable Declaration Example #1 – Integer
VBA IntegerVBA IntegerIn VBA, an integer is a data type that may be assigned to any variable and used to hold integer values. In VBA, the bracket for the maximum number of integer variables that can be kept is similar to that in other languages. Using the DIM statement, any variable can be defined as an integer variable. Data type is used when you need to store the whole numbers. Integer can store value between 32,768 to 32,767. If you need to pass value beyond this, then you need to use Long Datatype in VBALong Datatype In VBALong is a data type in VBA that is used to store numeric values. We know that integers also store numeric values, but Long differs from integers in that the range for data storage is much larger in the case of long data type..
Sub VBA_Code1() Dim Score As Integer Score = 101 MsgBox "Sachin Scored " & Score End Sub
When you run the above code, the result will show Sachin Scored 101. See below
VBA Variable Declaration Example #2 – String
The VBA string data typeVBA String Data TypeVBA SubString is a crucial function used for splitting the data by dividing a VBA string into different substrings. There are three types of substring functions available in VBA, i.e., left-right, mid and split functions. can store data as text.
Sub VBA_Code_String() Dim strName As String strName = "Ram" Range("A1:A10") = "Ram" End Sub
When you run the above code, this will enter Ram in Every cell between Range A1:A10.
VBA Variable Declaration Example #3 – Date
The date data type in VBADate Data Type In VBAVBA Date is a date and time function. It returns only the current date as per the system date you are using and has no arguments whatsoever. This function returns the current system date. can store data like the date. It will be in the format of MM/DD/YYYY.
Sub VBA_Code_Date() Dim DOB As Date DOB = "04/04/1990" MsgBox "I was born on " & DOB End Sub
When you run the above code, this will show the result as below.
VBA Variable Declaration Example #4 – Boolean
Boolean Datatype in VBABoolean Datatype In VBABoolean is an inbuilt data type in VBA used for logical references or logical variables. The value this data type holds is either TRUE or FALSE and is used for logical comparison. The declaration of this data type is similar to all the other data types. has only two values True or False.
Sub VBA_Code_Boolean() Dim bgender As Boolean bgender = False If bgender = True Then Range("A1") = "Male" Else Range("A1") = "Female" End If End Sub
When you run the code that the result in A1 cell will be Female.
VBA Variable Declaration Example #5 – Long
Data type Long is also used to store numbers. They can store number between -2,147,483,648 to 2,147,483,647. Here you must have a question if Integer and Long both can store numbers, then why do we use Integer?
Here is the answer, Integer takes two bytes of space. However, Long needs 8 bytes of space. So when you know that number can be stored as an integer, you should not use long else your program running time will increase.
Suppose you need to show the distance between the North Pole to the South Pole in Meter, and you know that the distance in the meter is outside the range of -32,768 to 32,767. So you will use data type Long.
Sub VBA_Code_long() Dim distance As Long distance = 13832000 MsgBox "Distance between the North Pole and the South Pole is " & distance & " Meter" End Sub
The result will be “Distance between the North Pole, and the South Pole is 13832000 Meter
If you use an integer as a data type in the above code, then this will go through error. You can try it.
Points to Remember
You need to remember some points while declaring the variables.
- A variable name should not be more than 255 characters.
- Variables are not case sensitive.
- A Variable should not start with a number. You can use the number or underscore in the middle of the variable name.
- VBA variable declaration cannot be named as an Excel keyword like Sheet, Range, etc.
- VBA variable declaration did not contain special characters.
This has been a guide to VBA Variable Declaration. Here we discuss how to declare a variable in VBA and when should you use it along with examples and download template. Below are some useful excel articles related to VBA –