VBA Long

Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What is a Long Data Type in VBA?

Long is a data type in VBA used to store the numeric values. We know that integer also holds numeric values, but Long differs from integers as the data storage range is very big. In the case of the Long data type, we can hold decimal values too. So, it is a built-in data type.

“Long,” as the name says, should hold the value of something big. “Long” is a numerical data type in VBA Excel.

The Long data type in Excel VBA can hold the values from 0 to 2, 147, 483, 647 for positive numbers, and the negative number can hold from 0 to -2, 147, 483, 648.

VBA Long data type requires 4 bytes of memory storage of your computer. It is the double Integer data type, variable memory (2 bytes), and half of the double data type variable memory (8 bytes).

In my short career, we have never seen a scenario where we want to utilize the limit of a VBA Long data type fully. But, we will show you some examples to better understand it.

VBA Long

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

Examples of  VBA Long Data Type

Below are the examples of excel VBA Long data types.

You can download this VBA Long Data Type Excel Template here – VBA Long Data Type Excel Template

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

VBA Long Example #1

As soon as you declare the variable data type as “Long,” you can assign the values from  -2, 147, 483, 648 to 2, 147, 483, 648.

For example, declare the variable as a Long data type.

Code:

Sub Long_Example1()

Dim k As Long

End Sub
vba long example 1.1

Let us assign the value as a total row count of the worksheet.

For getting the total count of rows in the excelCount of Rows In The ExcelThere are numerous ways to count rows in Excel using the appropriate formula, whether they are data rows, empty rows, or rows containing numerical/text values. Depending on the circumstance, you can use the COUNTA, COUNT, COUNTBLANK, or COUNTIF functions.read more worksheet, the code is “Rows. Count.”

Code:

Sub Long_Example1()

Dim k As Long

k = Rows.Count

End Sub
vba long example 1.2

Now, show the value in the message box.

Code:

Sub Long_Example1()

Dim k As Long

k = Rows.Count
MsgBox k

End Sub
vba long example 1.3

Run this code and see what the total rows count in the worksheet is.

vba long example 1.4

It says we have more than 1 million rows on the worksheet.

For better understanding, we will change the data type from LONG to INTEGER.

Code:

Sub Long_Example1()

Dim k As Integer

k = Rows.Count

MsgBox k

End Sub
vba long example 1.5

If we run the VBA codeRun The VBA 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, we get the error message “Overflow.”

vba long example 1.6

We got this error in VBAError In VBAVBA error handling refers to troubleshooting various kinds of errors encountered while working with VBA. read more because the data type “Integer” can only hold the values from -31768 to 32767. In this case, “Rows. Count” will return the number exceeding the limit of the “integer” variable.

Assigning the value of over 1 million to the variable which can hold only 32767 causes the overflow error here.

VBA Long Example #2

Find the Last Row Using Long Variable

Finding the last used row of the worksheet is the most important of the coding. To find the last used row of the worksheet requires a variable to be declared. While declaring the variable and assigning a data type requires some common sense.

As shown in the image below, assume you have data that ends at 25,000 rows.

vba long example 2.1

Now, we know the last used row number is 25,000. So, for this, we do not need the “LONG” data type because the “INTEGER” data type can give me the last row.

Look at the below code for your information.

Code:

Sub Long_Example1()
Dim k As Integer

k = Cells(Rows.Count, 1).End(xlUp).Row

MsgBox k

End Sub
example 2.2

If we run this code, we will get the last used row number of the worksheet we are working on now.

example 2.3

Knowing the size of the data you will have in the future is important as a coder. Because at the moment, data may end at the 25,000th row, but if the data increases beyond the “Integer” limit, i.e., 32767, it causes an overflow error.

For example, I will increase the data to 32768th rows.

vba long data type example 2.4

We will not get the value if I run the same code again. Rather, we will get the error below.

vba long data type example 2.5

Remember, we have increased the limit by just 1, beyond the limit of the “Integer” value, so we got an “Overflow” error.

So, it is important to know the size of your data before you assign a data type to the variable. Therefore, it is always better to declare the variable as “LONG” without considering your future data size.

Alternatives to Excel VBA Long Variable

You must already be thinking about what if we want to hold the value more than the limit of a long variable. For this, we need to use different data types: VBA “String” or “Variant.”

Remember, when it crosses the number 2147483647, we will get 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 with the LONG data type. So, we need to use the “String” or “Variant” data type to store more than this number.

For String

Code:

Sub Long_Example2()

Dim k As String

k = 2147483648

MsgBox k

End Sub
vba long data type example 3.1

For Variant

Code:

Sub Long_Example2()

Dim k As Variant

k = 2147483648

MsgBox k

End Sub
Example 3.2

When we run the above codes, it will show the mentioned number.

example 3.3

Recommended Articles

This article has been a guide to VBA Long data type in Excel. Here, we learn how to use the VBA Long data type, practical examples, and a downloadable Excel template. Below are some useful Excel articles related to VBA: –