VBA Long

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:

Excel VBA LONG Function Example 1

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.

Excel VBA LONG Function Example 1-1

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.

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.

VBA Long data type Example 1

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

VBA Long data type Example 1-1

Alternatively, you can select the “View” tab.

VBA Long data type Example 1-2

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

VBA Long data type Example 1-3

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

VBA Long data type Example 1-4

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.

VBA Long data type Example 1-5

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

VBA Long data type Example 1-6

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

VBA Long data type Example 1-7

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.

VBA Long data type Example 1-8

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.

VBA Long data type Example 1-9

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

VBA Long data type Example 1-10

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

VBA Long data type Example 1-11

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:

VBA Long data type Example 1-12
VBA Long data type Example 1-13

If you entered it correctly:

VBA Long data type Example 1-14
VBA Long data type Example 1-15

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.

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.

VBA Long Example 2

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.

VBA Long Example 2-1

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

VBA Long Example 2-2

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

VBA Long Example 2-3

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

VBA Long Example 2-4

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

VBA Long Example 2-5

vbCrLf – It is a VBA constant used to print it in different lines.

Step 7: Create a for-loop to find the sums odd

VBA Long Example 2-6

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

VBA Long Example 2-7

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

VBA Long Example 2-8

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.

VBA Long Example 2-9
VBA Long Example 2-10

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)

1. What is the difference between VBA long and integer?

difference between VBA long and integer

2. What is the difference between VBA long and double?

difference between VBA long and double

3. What is the limit of long data type in VBA?

The limit of the VBA Long data type is -2,147,483,648 to 2,147,483,647.

4. What is the size of long data type in VB?

In VB (Visual Basic), the size of the Long data type is 4 bytes.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *