EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Error Handling Functions VBA OverFlow Error
 

VBA OverFlow Error

Madhuri Thakur
Article byMadhuri Thakur

Excel VBA OverFlow Error

Introduction to VBA Overflow Error

We encounter many types if error while working with VBA. Errors are called runtime errors when they have encountered an error while executing the code. Also, every code has some certain code assigned to it in the programming of VBA. In excel one such error is the VBA Overflow error. The code for this error is run time error 6 which means overflow in the programming of VBA. We will learn about this error in this article.

 

 

Now we know what that overflow error is a type of error now let us understand what this error means. If we declare any variable as a certain data type and the value of the variable is exceeding the limit of the data type of the variable we get the error for overflow. For example, if we define a variable as integer and we know that integer can hold values up to 32767 for positive numbers and -32768 for negative numbers. So if we provide any input beyond that range we will encounter overflow error in VBA.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Overflow error in layman terms means we overload a data type with the values it can hold. Such cases will encounter us to this error. So to avoid this error we need to be aware of what data type we are using so that we can prevent it.

We will see through various examples that how in different types of data types we will possibly encounter this error.

How to Use VBA Overflow Error in Excel?

We will learn how to use a VBA Overflow Error Function with few examples in excel.

You can download this VBA OverFlow Error Excel Template here – VBA OverFlow Error Excel Template

Example #1 – Overflow Error

For the first example let us use Integer data type. Let us try to overflow the values so that variables cannot store it and see the error we encounter.

Follow the below steps to use VBA Overflow Error function in Excel:

Step 1: To basically start with VBA first thing we need to do is enable our developer’s tab and then click on it to open VB Editor from Visual Basic option as shown in the screenshot below,

VBA OverFlow Error

Step 2: Click on it and insert a module as follows, Once we click on the module. Double click on it and it will open another window for us where we will write our code.

Module Error

Step 3: We will see a blank window on the right-hand side of us, declare a subfunction and that is how we start a macro as follows,

Code:

Sub Sample()

End Sub

VBA OverFlow Error

Step 4: Declare a variable as an integer so that it can hold integer value for us,

Code:

Sub Sample()

Dim A As Integer

End Sub

OverFlow Error Example 1.1

Step 5: Now in Variable A store such value which will overflow the data type as follows,

Code:

Sub Sample()

Dim A As Integer
A = 4896 * 5000

End Sub

OverFlow Error Example 1.2

Step 6: Now display the value of A using msgbox function,

Code:

Sub Sample()

Dim A As Integer
A = 4896 * 5000
MsgBox A

End Sub

OverFlow Error Example 1.3

Step 7: Run the above code and see what result we get,

VBA Overflow Error 1

We received this error because 4896*5000 is over the positive number limit for the integer data type to hold and variable A is overflowed by this value so we encounter this error.

Example #2 – Overflow Error

Now in this example let us BYTE data type. We know that byte data type can hold values from 0 to 255 but any values other than that range will give us an error. Let us find out.

Step 1: We already have our module inserted, we can work on the same one or create a new one. But let us work on the same module we inserted. Double click on the module to enter the code window again,

OverFlow Errror module 2

Step 2: Declare another sub-function as follows,

Code:

Sub Sample1()

End Sub

VBA OverFLow Error 2.1

Step 3: Declare a variable as data type BYTE as follows,

Code:

Sub Sample1()

Dim A As Byte

End Sub

OverFlow Error Example 2.2

Step 4: Now in Variable A store value above than 255 as follows,

Code:

Sub Sample1()

Dim A As Byte
A = 266

End Sub

Error example 2.4

Step 5: Use a msgbox function to display the value of A,

Code:

Sub Sample1()

Dim A As Byte
A = 266
MsgBox A

End Sub

Error example 2.6

Step 6: Let us run the above code by pressing F5 and see the result,

OverFlow Error 2

Step 7: Now let us try and change the value of A to 244 and rerun the code to see the result,

Code:

Sub Sample1()

Dim A As Byte
A = 244
MsgBox A

End Sub

VBA Example 2.7

Step 8: When we run the code again we see the following result,

Overflow 3

When we first run the code variable A has values more than the range a BYTE data type can hold, but in the second instance, the variable A has data in its data type range so we didn’t encounter the overflow error.

Example #3 – Overflow Error

Now let us use LONG data type as an example as it is the most used data type among programmers.

Step 1: We will again work in the same module we inserted earlier. We just need to double click the module and we are in it.

OverFlow Errror mdule 3

Step 2: Declare a sub-function as shown in the screenshot.

Code:

Sub Sample2()

End Sub

VBA OverFLow Error 3.1

Step 3: Declare a variable as a LONG data type as follows.

Code:

Sub Sample2()

Dim A As Long

End Sub

Example Error 3.2

Step 4: Now similar to above examples let us overflow this variable by making it hold values above its range as follows.

Code:

Sub Sample2()

Dim A As Long
A = 2000 * 365

End Sub

Example Error 3.3

Step 5: Use a msgbox function to display the value of A as follows.

Code:

Sub Sample2()

Dim A As Long
A = 2000 * 365
MsgBox A

End Sub

Example Error 3.4

Step 6: Click on the above Run Button and see that we encounter overflow error.

VBA OverFlow 4

Step 7: Now there is a method to overcome this error in long data type by using CLNG function as follows.

Code:

Sub Sample2()

Dim A As Long
A = CLng(2000) * 365
MsgBox A

End Sub

Example Error 3.5

Step 8: Now if we again run the code we can see the following result.

VBA OverFlow 5

Now, what did the CLNG Function do? It converted the value to a long integer which the variable can hold.

How to Overcome Overflow Error in VBA

When we encounter overflow error in VBA that means any one of our variables, not more is having some values which it cannot hold. We need to identify the variable and rectify it. Also, we have CLNG function for long data types to help us. But knowing our data type actually helps.

Things to Remember

There are certain things which we need to remember about overflow error in VBA:

  • Overflow error is a run time error.
  • The error code for overflow error is 6.
  • To overcome overflow error we must know what data type can hold how much values.
  • CLNG function helps in overflow error for long data types.

Recommended Articles

This is a guide to VBA Overflow Error. Here we discuss how to use Excel VBA Overflow Error Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Copy Paste
  2. VBA RGB
  3. VBA Subscript out of Range
  4. VBA XML

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

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

EDUCBA

Download VBA OverFlow Error Excel Template

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW