runtime error 6 overflow error - vba excel program
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are VBA Arrays not 1 based?
Did you try GLArray(ArrayCount,1) ?
Did you try GLArray(ArrayCount,1) ?
CLng cannot perform a narrowing conversion. You will need to declare your array as a double.
HugoHiasl,
It's an overflow error, not an array indexing error.
Kevin
It's an overflow error, not an array indexing error.
Kevin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No, VBA will round out the decimal portion without incurring an overflow.
Kevin
Kevin
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
Kevin
Haha.
If its the value in the screenshot (Watch Window) it would cause an overflow.
If its the value in the screenshot (Watch Window) it would cause an overflow.
Yep.
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
Dim GLArray() As Double
GLARray(arraycount, 0) = activeworkbook ... .value
Chris
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
Make that four!
Change this:
Dim GLArray() As Long
to:
Dim GLArray() As Double
Kevin
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
Chris
Oh ya!
Most excellent call!
Most excellent call!
ASKER
Thanks everyone! :-)
Kevin