VBA Long

VBA Long

In the old days when memory was really expensive, we had to manage by using Integers for numbers. But after the technology evolution, the need for larger number variable came into the picture. With the help of an Integer variable, we could only store the numbers from -32768 to +32768. But now we have variable LONG which is used for storing and using the numbers of any range and any length. For long and lengthy code, it is recommended to use the Long variable.

How to Use Excel VBA Long?

We will learn how to use a VBA Long with few examples in excel.

VBA Long – Example #1

In this example, we will see, basic use of how VBA Long can work for numbers. For this, we need a Module.

Step 1: Go to VBA window and from Insert menu list select Module as shown below.

Step 2: Once we do that we will get a blank page of Module. Now there, write the subcategory in any name, but better it should in the name of a performed function, like VBA Long.

Code:

Sub VBALong1()

End Sub

Step 3: Now define a dimension DIM as A or any other alphabet and letter can be used.

Code:

Sub VBALong1()

  Dim A

End Sub

Step 4: And assign the variable Long to dimension A.

Code:

Sub VBALong1()

  Dim A As Long

End Sub

Step 5: Now we will assign a number, let’s consider 2 digit number say 10 as shown below.

Code:

Sub VBALong1()

 Dim A As Long
 A = 10

End Sub

Step 6: Now we will use a message box to print the value stored in variable Long with dimension A.

Code:

Sub VBALong1()

    Dim A As Long
    A = 10
    MsgBox A

End Sub

Step 7: Now compile the written code and run it by clicking on the play button.

As we can see the value stored in dimension A is now printed in a message box. This shows that variable Long cab used for low digit numbers as well.

VBA Long – Example #2

In this example, we will number with multiple digits in it. We use approx. 9 to 10 digit figure to understand whether variable Long works in the same manner as it did for two digit number. For this:

Step 1: Open a new module again and add a subcategory in the name of sequential VBA Long name as shown below.

Code:

Sub VBALong2()

End Sub

Step 2: Now define a dimension DIM as A or any other alphabet or word can be used.

Code:

Sub VBALong2()

  Dim A

End Sub

Step 3: After that, we will assign variable function Long to dimension A.

Code:

Sub VBALong2()

  Dim A As Long

End Sub

Step 4: Now assign a numerical value to dimension A of any length. For testing consider 9 to 10 digit number or any combination. We have considered 123123123 for it as shown below.

Code:

Sub VBALong2()

  Dim A As Long
  A = 123123123

End Sub

Step 5: Now we will use the message box to print stored valued in A as shown below.

Code:

Sub VBALong2()

  Dim A As Long
  A = 123123123
  MsgBox A

End Sub

Step 6: Now we will compile and run the code. We will see, even a lengthy numerical value got printed in a message box.

VBA Long – Example #3

In this example, we will see whether variable function Long can be used for negative numbers or not. For this, a new module will be needed. Open a new module

Step 1: Insert subcategory in any name or better in the name of VBA Long as shown below.

Code:

Sub VBALong3()

End Sub

Step 2: Consider the same dimension DIM A and assign it the function Long as shown below.

Code:

Sub VBALong3()

  Dim A As Long

End Sub

Step 3: Now we will use the same number as we did in the previous example which is 123123123 and give it a negative sign. And assign this number to Long dimension A as shown below.

Code:

Sub VBALong3()

  Dim A As Long
  A = -123123123

End Sub

Step 4: And at last, use a message box to print the value stored in dimension A.

Code:

Sub VBALong3()

  Dim A As Long
  A = -123123123
  MsgBox A

End Sub

Step 5: Compile the code and run it after that. We will see, even if the number the long and negative but still VBA Long has printed it without any error.

VBA Long – Example #4

In this example, we will see whether we can use decimal numbers with VBA Long or not. For this, we need a module.

Step 1: Open a new module, create the subcategory for VBA Long as shown below. You may keep the sequence of code as we did in below.

Code:

Sub VBALong4()

End Sub

Step 2: Now we will use the same dimension DIM as A as we used in the above examples.

Code:

Sub VBALong4()

  Dim A

End Sub

Step 3: Now assign this dimension to Long as shown below.

Code:

Sub VBALong4()

  Dim A As Long

End Sub

Step 4: Now we use the same numbers as we used in the above example, but here we will use decimal digit. So 123.123123 will be the number here as shown below.

Code:

Sub VBALong4()

  Dim A As Long
  A = 123.123123

End Sub

Step 5: At last we can use the message box to print the value stored in A as shown below.

Code:

Sub VBALong4()

  Dim A As Long
  A = 123.123123
  MsgBox A

End Sub

Step 6: Now compile the code and run. We will see that VBA Long has returned only whole numbers which are before decimal as shown below.

This shows that VBA Long cannot be used for decimal numbers. It will give any integer or whole number in return.

VBA Long – Example #5

In this example, we will see if we can use VBA Long for works, alphabets or test or not. Open a module

Step 1: Write subcategory of function for VBA Long or in any other preferred name as shown below.

Code:

Sub VBALong5()

End Sub

Step 2: In the next line of code, use dimension DIM A as Long as shown below.

Code:

Sub VBALong5()

  Dim A As Long

End Sub

Step 3: Now assign any text or word to defined dimension A in inverted commas. We have used VBA Long here as text.

Code:

Sub VBALong5()

  Dim A As Long
  A = "VBA Long"

End Sub

Step 4: Now use the message box to print to value store in dimension A as shown below.

Code:

Sub VBALong5()

  Dim A As Long
  A = "VBA Long"
  MsgBox A

End Sub

Step 5: Once done with coding, compile the code or run directly. We will see an error message box with an error message “Run-time error 13 – Type Mismatch” will appear.

This means that there is a mismatch of used variable and values stored in it and that is not supported. Hence, it shows VBA Long doesn’t support text and alphabets.

Pros of VBA Long

• Any length numeric values can be used in a Long function.
• Long in VBA makes the use for number free from any constraints.

Things to Remember

• Long cannot be used for texts and decimal numbers.
• For decimal number, using Double over Long and Integer are preferred.
• If the code length is short then no need to compile the data step-by-step.
• Remember to save the file as Macro Enable Excel format so that created code will remain safe.

Recommended Articles

This has been a guide to VBA Long. Here we have discussed how to use Excel VBA Long along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Working with VBA Active Cell
2. Deleting a Row in VBA
3. How to Use Excel VBA Transpose?
4. How to Fix 1004 Error Using VBA