VBA Long

What is Long Data Type in VBA?

“Long” as the name says, it 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 for the negative number it can hold from 0 to -2, 147, 483, 648.

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

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

Examples of  VBA Long Data Type

Below are the examples of excel VBA Long data type.

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 long data type.

Code:

Sub Long_Example1()

Dim k As Long

End Sub

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

To count the total number of rows in the worksheet code is “Rows. Count”

Code:

Sub Long_Example1()

Dim k As Long

k = Rows.Count

End Sub

Now show the value in the message box.

Code:

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 in the worksheet.

Now for better understanding, I will change the data type from LONG to INTEGER.

Code:

Sub Long_Example1()

Dim k As Integer

k = Rows.Count

MsgBox k

End Sub

If I run the VBA code I will get the error message as “Overflow”.

The reason why we got this error in VBA because the data type “Integer” can hold the values from -31768 to 32767, only. In this case “Rows. Count” will return the number which is more than the limit of “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. In order to find the last used row of the worksheet requires variable to be declared. While declaring the variable and assigning a data type requires some common sense.

Assume you have data ends at 25000 rows as shown in the below image.

Now I know last used row number is 25000, for this, we don’t need “LONG” data type because “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

If I run this code I will get the last used row number of the worksheet I am working on at the moment.

As a coder, it is important to know the size of the data you will have in the future. Because at the moment data may be ending at 25000^th 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 32768^th row.

Now if I run the same code again I will not get the value rather I will get the error as below.

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

So it is important to know the size of your data before you assign a data type to the variable. It is always a better option to declare the variable as “LONG” without thinking of your data size in the future.

Alternatives to Excel VBA Long Variable

You must be already 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 i.e. either VBA “String” or “Variant”

Remember the moment it crosses the number 2147483647 we will get overflow error with a VBA LONG data type. To store more than this number we need to use “String” or “Variant” data type.

For String

Code:

Sub Long_Example2()

Dim k As String

k = 2147483648

MsgBox k

End Sub

For Variant

Code:

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 has been a guide to VBA Long data type in excel. Here we learn how to use VBA Long data type along with practical examples and downloadable excel template. Below are some useful excel articles related to VBA –

• VBA Assign WorkBook to Variable
• Split String Function in Excel VBA
• VBA Tutorial to Record Macros
• VBA End Function Examples
• VBA Call Sub Procedure
• SubString in VBA
• Subscript Out of Range in VBA
• Select Cell in VBA
• CDBL VBA