WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Long

VBA Long

What is a Long Data Type in VBA?

Long is a data type in VBA which is used to store the numeric values, we know that integer also holds numeric values but Long is different from integers as the range for the storage of data is very big in case of long data type also in long data type we can hold decimal values too, this is an inbuilt data type.

“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.

vba long

Examples of  VBA Long Data Type

Below are the examples of excel VBA Long data type.

You can download this VBA Long Data Type Excel Template here – VBA Long Data Type Excel Template

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

vba long example 1.1

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

To get total count rows in the excel worksheet code is “Rows. Count”

Code:

Sub Long_Example1()

Dim k As Long

k = Rows.Count

End Sub

vba long example 1.2

Now show the value in the message box.

Code:

Sub Long_Example1()

Dim k As Long

k = Rows.Count
MsgBox k

End Sub

vba long example 1.3

Run this code and see what the total rows count in the worksheet is.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

vba long example 1.4

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

vba long example 1.5

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

vba long example 1.6

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 the “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.

vba long example 2.1

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

example 2.2

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

example 2.3

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 25000th 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 32768th rows.

vba long data type example 2.4

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

vba long data type example 2.5

Remember, I have increased the limit by just 1 beyond the limit of the “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 already be 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 an overflow error in VBA with the LONG data type. To store more than this number, we need to use the “String” or “Variant” data type.

For String

Code:

Sub Long_Example2()

Dim k As String

k = 2147483648

MsgBox k

End Sub

vba long data type example 3.1

For Variant

Code:

Sub Long_Example2()

Dim k As Variant

k = 2147483648

MsgBox k

End Sub

Example 3.2

When we run the above codes, it will show the mentioned number.

example 3.3

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 a downloadable excel template. Below are some useful excel articles related to VBA –

  • VBA Row Count
  • VBA End
  • VBA Call Sub Procedure
  • CDBL VBA
4 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Long Data Type Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More