Excel VBA Variable Types

To code efficiently, declaring variables and assigning data types to those declared variables are key to going a long way in VBA codingVBA CodingVBA 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.  To code efficiently, declaring variables and assigning data types to those declared variables are key to going a long way in

As the name says, the variable will vary from time to time, and we store some value in those variables. To understand this better, let’s remember our “mathematics” classes, where we assume the variable “x = something,” so whenever we use the “x” variable, it would be equal to the value we have assigned.

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 Types (wallstreetmojo.com)

What is Data Type?

The data type is the restriction we put on hold the variable. For example, for the declared variable, we can restrict it to hold only “Date Values,” “Integer Values,” “Long Values,” “String Value,” etc.

The data types that a variable may hold are called “Data Type” in VBA.

It has many types. It is important to understand what each data type can hold in coding. We can classify the data types in two ways:

#1 – Non-Numerical Data Types

These data types can hold only non-numerical data. These are common non-numerical data types: String, Boolean, Variant, and Object.

  • String: This can hold two kinds of string values, i.e., a String with a fixed and variable length.Boolean: Booleans in VBABooleans 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 are logical values: either TRUE or FALSE.Variant: It can hold both numerical and non-numerical dataObject: Object variables are products of Microsoft. For example, in Excel, objects are “Worksheet,“ “Workbook,“ and “Range.” In addition, Microsoft objects are “MS Word,“ “MS PowerPoint,“ and “MS Outlook.”

#2 – Numerical Data Types

How to Define Variable & Assign Data Type in VBA?

The most important thing to know is to define the variable during coding. We can define the variable types differently: Implicitly and Explicitly.

#1 – Implicitly

We can declare the VBA variableDeclare The VBA VariableVariable declaration is necessary in VBA to define a variable for a specific data type so that it can hold values; any variable that is not defined in VBA cannot hold values.read more implicitly, i.e., without using the “DIM” word. Dim stands for “Dimension.” For example, look at the below image.

Code:

Sub Data_Type() k = 45 End Sub

#2 – Explicitly

It is a proper way of declaring a variable. We would call it an official and professional way. To declare a variable, we have to use the word “DIM” and assign a data type to the variable.

Sub Data_Type() Dim k As Integer k = 45 End Sub

We have defined the variable “k,” as shown in the above image, and assigned the data type as “Integer.”

Rules to Define Variable

  • A variable cannot contain any space character.The variable should not contain any special characters except “underscore” (_)The variable should not start with a numerical character.The variable should not directly contain any VBA keywords.

Examples

Example #1

To define any variable, we need first to use the word “Dim” followed by a variable name.

Sub Data_Type() Dim var End Sub

Next, we need to assign a data type once given the variable name. As we discussed above, we can assign any data type.

Sub Data_Type() Dim var As Integer End Sub

We have assigned the data type as an Integer. So, now you need to remember the limitations of the Integer variable. It can hold values between -32768 to 32768.

Sub Data_Type() Dim var As Integer var = 25000 End Sub

In the above image, we have assigned 25000, which is well within reach but entering the value more than the limit will cause an overflow error in VBAOverflow Error In VBAVBA Overflow Error or “Run Time Error 6: Overflow” occurs when the user inserts a value that exceeds the capacity of a specific variable’s data type. Thus, it is an error that results from the overloading of data beyond the desired data type limit of the variable.read more.

Sub Data_Type() Dim var As Integer var = 35000 End Sub

You can run this code using shortcut key F5 or manually to see the result.

Overflow is the assigned value of a data type that is more than its capacity.

Example #2

Similarly, we cannot assign different values also. For example, we can not assign the “String” value to the integer data type variable. If assigned, we will get a “Type Mismatch ErrorType Mismatch ErrorWhen we assign a value to a variable that is not of its data type, we get Type mismatch Error or Error code 13. For example, if we assign a decimal or long value to an integer data type variable, we will get this error (Error Code 13) when we run the code.read more.”

Sub Data_Type1() Dim var As Integer var = “Hii” End Sub

Now, run this code through shortcut key F5 or manually to see the result.

Things to Remember

  • We must always use the DIM word to define the variable.Before assigning data type, ensure what kind of data you will store.Assigning more than the capacity value to the data type causes an overflow error, and assigning a different value to the data type causes a “Type Mismatch Error.”

This article has been a guide to VBA Variable Types. Here, we discuss how to define the variable and assign data type in Excel VBA with the help of practical examples and a downloadable Excel template. Below you can find some useful Excel VBA articles: –

  • VBA SolverPublic Variables in VBAGlobal Variables in VBANamed Range in VBA