integer overflow in vba even though I'm using long data type

Question

I wrote a simple vba macro. I'm checking each cell in the one column and whenever it starts back to 1 again, I copy the previous value (e.g. 4) into the corresponding slots:

So the output would look like this:

It seems to work fine when covering the range of a signed integer. However, as soon as I hit cell 32,769, it starts producing the same number over and over, making me assume it overflowed.

In fact, the debugger throws error:

run-time error '1004':
Application-defined or object-defined error

It highlights this line of code:

Range("L" & start).Resize(iCounter) = curVal

According to the debugger, start is value 32769, iCounter is value 32771, and curVal is 3.

But rather than doing this:

1 3
2 3
3 3

It does this (5 was the previous large value):

1 5
2 5
3 5

As you can see, this is incorrect, even though my program said the top value should have been 3. I assume some type of overflow occurred. However, I am using long values, so I am not sure why overflow is occurring:

Sub calculateLargest()
  Worksheets("parlam2010_resumen").Activate
  Range("K9").Activate

  Dim curVal As Long
  Dim nextVal As Long
  Dim iCounter As Long
  Dim start As Long

  iCounter = 9
  start = 9
  Do
    If ActiveCell.Value = "" Then Exit Do

    curVal = ActiveCell.Value
    nextVal = ActiveCell.Offset(1, 0).Value

    If curVal > nextVal Then
      Range("L" & start).Resize(iCounter) = curVal
      start = iCounter + 1
    End If

    ActiveCell.Offset(1, 0).Activate
    iCounter = iCounter + 1
  Loop
End Sub

Any ideas?

Your question title is confusing. This is about integer overflow, not stack overflow. — paddy
– paddy, Commented Sep 1, 2015 at 0:44
@paddy I corrected it. I was thinking of this website when writing lol. — Daniel Viglione
– Daniel Viglione, Commented Sep 1, 2015 at 0:47
Your code is not doing what you think it is. Try single stepping through a small data set and you will see that resized range is not what you thought. If you are using an .xls with max rows of 65536 you will get the error you describe — chris neilsen
– chris neilsen, Commented Sep 1, 2015 at 1:44
FYI, this can be coded to run much faster (several orders of magnitude) by using Variant array's. (Search that term on SO to see how it's done) — chris neilsen
– chris neilsen, Commented Sep 1, 2015 at 2:09

score 1 · Accepted Answer · 2015-09-01 02:49:43Z

1

There is no integer overflow. You are simply not resizing the target of curVal correctly.

Range("L" & start).Resize(iCounter - start + 1) = curVal

At K32771 iCounter is 32771 and start is 32769. Your Range.Resize property should not be 32771. It should be 32771 - 32769 + 1. The 5's are not coming from calculating the 1, 2, 3 at all. They are left over from writing too many cells with a 5 in the previous iteration.

As iCounter grows and start moves down, eventually you are going to try to write into a cell that is off the lower boundary of the worksheet. This is what is generating the Run-time error '1004'.

Collectives™ on Stack Overflow

integer overflow in vba even though I'm using long data type

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related