What is Excel VBA LONG Function?
In VBA (Visual Basic for Applications), the Long Datatype is a numeric data type used to store integer values that range from -2,147,483,648 to 2,147,483,647. It is a 32-bit signed integer data type, which means it can store both positive and negative whole numbers within this range.
Consider the following example:

In the above subroutine, initialize a VBA Long Integer and assign a large value. Then, print the value. The output is printed in the immediate tab, as shown below.

The value is successfully stored in the variable as a Long data type and printed.
NOTE: As mentioned earlier, the VBA Long Max Value ranges from -2,147,483,648 to 2,147,483,647. Any value greater than these values will throw an error.
Table of Contents
Key Takeaways
- VBA’s Long data type is designed for storing integers within a specific range. It is well-suited for most integer-based calculations due to its extensive range.
- To convert VBA Long values to strings, the CStr function is commonly used.
- Compared to the Double data type, Long is more memory-efficient when working primarily with integers.
- However, Double is the preferred option when dealing with decimal or floating-point numbers and broader numeric scopes.
- In extremely large integers or heightened precision, considering alternative data types or custom solutions beyond Long and Double may be necessary.
VBA Long Declaration
The declaration of a VBA Long integer variable is shown below:
Dim var As Long
Where var is the variable typecasted to VBA Long.
Now that you’ve seen how to use VBA Long Function let’s see some Examples of how to use it.
Examples
Let us view some practical examples of how to use the VBA Long Data type below.
Example #1
Consider an example where you need to find the factorial of any number given. Due to the increasing product values, we use the VBA Long data type in this example. To do so, follow the steps mentioned below.
Step 1: Open the Excel Workbook. Once it is opened, click the “Developer” tab in the title bar. In that, select the “Visual Basic” option.

In the VBA Module, click the “Insert” button in the title bar and select the “Module” option.

Alternatively, you can select the “View” tab.

Then go to the “Macros” and select “View Macros”.

If you’ve created subroutines or Macros earlier, a window will open, and click the “Edit” button to open the VBA tab.

You can edit, add, and delete subroutines once you click the button.
Now, you are ready to start coding in Excel VBA.
Step 2: Initialize a sub-procedure to calculate the factorial for any number the user gives.

Step 3: Initialize two Long variables; one gets the user’s input, and the other receives the resultant factorial value.

Step 4: Using the InputBox function in Excel VBA, get the number input from the user.

Step 5: Use an If-Else function to check whether the given value is greater than 0. If not, it returns a message and terminates the sub-procedure.

The VBA Long data type can also accept negative values.
Step 6: Declare the product variable as 1. Since we’re multiplying numbers, if we don’t initialize the variable as 1, all the product values will become 0.

Step 7: Initialize a FOR-loop to the number defined by the user, multiply it by the product variable, and update it.

Step 8: Print the product variable in a MsgBox function.

Code:
Sub CalculateFactorial()
Dim num As Long
Dim result As Long
num = InputBox(“Enter a positive integer:”)
If num < 0 Then
MsgBox “Please enter a positive integer.”
Exit Sub
End If
result = 1
For i = 1 To num
result = result * i
Next i
MsgBox “The factorial of ” & num & ” is ” & result
End Sub
Step 9: Run the above VBA code by pressing the Green Button on the VBA Toolbar or pressing “F5.”
If you entered a negative integer:


If you entered it correctly:


Example #2
As a practice example, you want to create a VBA Program that prints a sequence of Fibonacci numbers till the number you input.
A Fibonacci Series is a series of numbers in which the following number is the sum of the last two variables.
For example:
0, 1, 1, 2, 3, 5,…..
In this case, we start counting from the 3rd digit. The first two values are 0 and 1 always.
In the 3rd digit,
1 = 0+1
2 = 1+1
And so on.
For this, we can use the Long Datatype to handle large values.
Step 1: Declare a subroutine to generate Fibonacci numbers until the preferred range.

Step 2: Declare 5 VBA Long Data type variables, the number until the Fibonacci series will print. Declare two variables to declare the first two variables. Then, declare another variable to find the sum of the two variables and a looping iterative variable.

Step 3: Check whether the current year’s birthdate has passed. If not, subtract one from the difference in years gotten.

Step 4: Check whether the given number is positive or not.

Step 5: Initialize the first two numbers of the series.

Step 6: Print the Fibonacci sequence. Declare the first two values.

vbCrLf – It is a VBA constant used to print it in different lines.
Step 7: Create a for-loop to find the sums odd

Step 8: Print the sum of the previous two values 0 and 1.

Step 9: Assume the second element is in this series and substitute them.

Code:
Sub GenerateFibonacci()
Dim n As Long
Dim fib1 As Long
Dim fib2 As Long
Dim fibNext As Long
Dim i As Long
n = InputBox(“Enter the number of Fibonacci numbers to generate:”)
If n <= 0 Then
MsgBox “Please enter a positive integer.”
Exit Sub
End If
fib1 = 0
fib2 = 1
Debug.Print “Fibonacci Sequence:” & vbCrLf & “0” & vbCrLf & “1”
For i = 3 To n
fibNext = fib1 + fib2
Debug.Print fibNext
fib1 = fib2
fib2 = fibNext
Next i
End Sub
Step 10: To compile and run the above program, press “F5” to run the program. We will get the output printed in the immediate tab.


We can print the VBA Long Data type to work in a Fibonacci series.
Alternatives To Excel VBA Long Variable
Let us look at some alternatives to the Long variable when all the options are exhausted:
- Double Data Type: The Double data type can store floating-point numbers, including integers and decimals. It has a much larger range than Long and can handle very large and very small numbers. However, it may not be suitable for precise integer arithmetic due to potential rounding errors.
- Currency Data Type: Use the Currency data type to work with currency values. It is a fixed-point data type that is suitable for financial calculations. It can handle large values with high precision.
- BigInt Libraries: If you need to work with extremely large integers, you can use external libraries such as the “BigInteger” library, which supports arbitrary precision arithmetic.
- Variant Data Type: A Variant can store values of different data types, including numeric types like Long, strings, objects, and more. When you use a Variant to store a numeric value, VBA performs implicit type conversion when you use it in arithmetic operations.
Important Things To Note
- Be aware of the Long data type’s range limitation, and if you need to work with numbers outside this range, consider using an alternative data type like Double or custom handling.
- Long can contribute to memory consumption. Be mindful of memory usage in your VBA projects.
- Always initialize your Long variables before using them in calculations. Uninitialized variables may contain unpredictable values.
- Be aware of the possibility of overflow errors if you perform arithmetic operations that result in a value outside the range of the Long data type.
You can trap and handle errors by using VBA error handling techniques (e.g., On Error Resume Next).
Frequently Asked Questions (FAQs)
The limit of the VBA Long data type is -2,147,483,648 to 2,147,483,647.
In VB (Visual Basic), the size of the Long data type is 4 bytes.
Recommended Articles
This has been a guide to VBA LONG. Here we explain alternatives to Excel VBA Long variable with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply