Home / VBA / Using Long Data Type in VBA

Using Long Data Type in VBA

- Written by Puneet

Contents hide
What is the Long Data Type in VBA?
How to Declare Long Data Type in VBA
Benefits of using Long Data Type
Using Long Data Type with a Constant

In VBA, there are many data types that you can use to assign values to a variable and a constant. In this tutorial, we are going to learn about Long data type.

What is the Long Data Type in VBA?

Long is a numeric data type that can hold numbers. As the name suggests, it can hold a long range of numbers, ranging from -2,147,483,647 to 2, 147, 483, 647. It consumes 4-byte memory in your system, less if you compare it with the double data type. You can also hold decimal values in it too.

How to Declare Long Data Type in VBA

To declare a variable or a constant with the Long data type, follow the same steps you use to declare a variable with any other data type.

declare-long-data-type-in-vba
  1. First, you need to use the DIM keyword. You need to DIM to declare a variable, it stand for declare.
  2. After that, enter the name “myLNG” or use any you want.
  3. Next, type the keyword “As”. When you type As, it shows you a lint of the data types, properties, methods.
  4. Finally, select “Long” form the list, or you can type it from the keyboard.
Sub long_data_type()
Dim myLNG As Long
End Sub

Once you declare the variable, you can assign a value to it. As we have discussed, long can store a value from a large range of numbers, so you might not get to use its limit in the real world. However, using the Long data type is still a good idea, especially when you compare it with the integer data type.

Let me show you an example. Let’s say you have more than 50000 values in a worksheet; now, you need to count those values. As you expect values above 50000, you must use the Long data type.

Sub long_data_type()
Dim myLNG As Long
myLNG = WorksheetFunction.CountA(Range("A:A"))
Debug.Print myLNG
End Sub

We have used the COUNTA function to count the values. When you run this code, it returns the value count in the immediate window.

returns-the-value-count

Now, to understand why using Long is helpful, let’s change the data type from Long to Integer. And then re-run the code. Remember that the range of numbers to store in an Integer is less than the Long.

Sub long_data_type()
Dim myLNG As Integer
myLNG = WorksheetFunction.CountA(Range("A:A"))
Debug.Print myLNG
End Sub
change-data-type

Once you run the code, it returns the Overflow Error, meaning the value is out of the range of data type. Here, we know that the number of values is above 50000, but when you are not sure how many values you will have, it’s better to use the long.

Benefits of using Long Data Type

  • Larger Numeric Range – One of the best reasons to use Long is to have a large number range to save in the variable.
  • Avoid Overflow Errors – As we have seen, using other variables can cause the Overflow Error.

Using Long Data Type with a Constant

The long data type can be used for a constant like a variable. In the example below, we have declared “myLNG” constant to store the value 1000000.

Sub long_data_type()
Const myLNG As Integer = 100000
End Sub
long-data-type-with-constant