What is a Long Data Type in VBA?
“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.
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 Long (wallstreetmojo.com)
Examples of VBA Long Data Type
Below are the examples of excel VBA Long data types.
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
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.”
Sub Long_Example1()
Dim k As Long
k = Rows.Count
End Sub
Now, show the value in the message box.
Sub Long_Example1()
Dim k As Long
k = Rows.Count MsgBox k
End Sub
Run this code and see what the total rows count in the worksheet is.
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.
Sub Long_Example1()
Dim k As Integer
k = Rows.Count
MsgBox k
End Sub
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.”
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.
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.
Sub Long_Example1() Dim k As Integer
k = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox k
End Sub
If we run this code, we will get the last used row number of the worksheet we are working on now.
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.
We will not get the value if I run the same code again. Rather, we will get the error below.
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
Sub Long_Example2()
Dim k As String
k = 2147483648
MsgBox k
End Sub
For Variant
Sub Long_Example2()
Dim k As Variant
k = 2147483648
MsgBox k
End Sub
When we run the above codes, it will show the mentioned number.
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: –
- VBA Row CountVBA EndVBA Call Sub ProcedureCDBL VBA
