Variable Declaration in VBA

Before moving to the VBA variable declaration, let us first understand what variables are, the use of variables, and when we should use them.

What are the variables in VBA?

The word “variable” defines variables as the name of memory in your location, which holds some value. You can pass a value in a code based on the variable type. The value will be used while executing the code, and you will get the output.

What is the use of Variable?

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, selecting a variable name that relates to data is advisable so that other users can understand it 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 a string value, you can name that variable like strName.

One can declare variables 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.read more. However, the coder must declare them at the start of the code so that every user can understand the code very easily. The variable should be declared using Dim.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Variable Declaration (wallstreetmojo.com)

Examples of VBA Variable Declaration

Here are some examples for you to understand the VBA data typeVBA Data TypeData type is the core character of any variable, it represents what is the type of value we can store in the variable and what is the limit or the range of values which can be stored in the variable, data types are built-in VBA and user or developer needs to be aware which type of value can be stored in which data type. Data types assign to variables tells the compiler storage size of the variable.read more. Then, 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.read more. Then, copy and paste the below codes one by one to see the result.

VBA Variable Declaration Example #1 – Integer

One may need the 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.read more when one needs to store the whole numbers. For example, integers can store values between 32,768 to 32,767. If you need to pass values beyond this, use the Long Data type 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.read more.

Code:

Sub VBA_Code1()

Dim Score As Integer

Score = 101

MsgBox “Sachin Scored " & Score

End Sub

The above code’s 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.read more 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.read more can store data like the date. Therefore, 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 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.read more 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, the result in the A1 cell will be “Female.”

VBA Variable Declaration Example #5 – Long

The Data type Long is also used to store numbers. They can store numbers between -2,147,483,648 to 2,147,483,647. Here, you must have a question: if Integer and Long can store numbers, why do we use Integer?

Here is the answer. Integer takes two bytes of space. However, Long needs 8 bytes of space. So, you should not use Long when you know one can store a number as an Integer. Else, your program running time will increase.

Suppose you need to show the distance between the North Pole and the South Pole in “Meter.” You know that the distance in the meter is outside the range of -32,768 to 32,767. So, you will use the 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. The South Pole is 13832000 Meter.

If you use an Integer as a data type in the above code, then this will go through an 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. Instead, we can use the number or underscore in the middle of the variable name.VBA variable declaration cannot be an Excel keyword like Sheet, Range, etc.VBA variable declaration does not contain special characters.

This article is a guide to VBA Variable Declaration. Here, we discuss how to declare a variable in VBA and when you should use it, along with examples and a download template. Below are some useful Excel articles related to VBA: –

  • Comment using Apostrophe in VBA BlockWait Function in VBAVBA Call SubVBA CSTR