?
Solved

runtime error 6 overflow error - vba excel program

Posted on 2010-01-08
16
Medium Priority
?
1,065 Views
Last Modified: 2013-11-25
Hello experts,

I am stuck on an error (runtime error 6 - overflow) and hoping for some insight.   This is a VBA program running in Excel.

Please see the screenshot attached.    The error occurs when I try to assign a value retrieve from a spreadsheet to an array that was declared as a Long data type.
 - Dim GLArray() As Long

If you have any questions or if further clarification is needed, please let me know.
Long.png
0
Comment
Question by:lcha
  • 8
  • 3
  • 2
  • +3
16 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26208024
What is in the cell?

Kevin
0
 
LVL 10

Assisted Solution

by:bromy2004
bromy2004 earned 668 total points
ID: 26208029
Long can only go up to 2,147,483,647
Change it to Double
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 332 total points
ID: 26208035
A long data type variable can hold any integer value from -2,147,483,648 to 2,147,483,647.

Kevin
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:HugoHiasl
ID: 26208036
Are VBA Arrays not 1 based?

Did you try GLArray(ArrayCount,1) ?
0
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26208045
CLng cannot perform a narrowing conversion.  You will need to declare your array as a double.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26208065
HugoHiasl,

It's an overflow error, not an array indexing error.

Kevin
0
 
LVL 10

Accepted Solution

by:
bromy2004 earned 668 total points
ID: 26208077
Kevin,
if there is a decimal in the Cell, that is less than 2,147,483,647, would it still cause an Overflow (on the Decimal Part)?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26208085
No, VBA will round out the decimal portion without incurring an overflow.

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26208089
I'm sure Asker has a rather large number in that cell. Why the number is so large is beyond me. Maybe he works for the US debt department.

Kevin
0
 
LVL 10

Expert Comment

by:bromy2004
ID: 26208105
Haha.
If its the value in the screenshot (Watch Window) it would cause an overflow.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26208143
Yep.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 26208176
Note the typing of the array will need to be double rather than long and the conversion to long in the snippet, (clng(...)) needs to be removed to leave

Dim GLArray() As Double
GLARray(arraycount, 0) = activeworkbook ... .value

Chris
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26208190
OK, how many of us are going to tell the Asker to change the type to Double?

Make that four!

Change this:

Dim GLArray() As Long

to:

Dim GLArray() As Double

Kevin
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 26208229
Indeed Kevin but the point I was making was that not only the definition needs to change but also the type conversion needs to be removed.

Chris
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26208239
Oh ya!

Most excellent call!
0
 

Author Closing Comment

by:lcha
ID: 31674455
Thanks everyone! :-)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question