CODEWITHSUNDEEP
vbaexcel
Daniel Viglione
Daniel Viglione

Reputation: 9407

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

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:

1
2
3
4
1
2
3

So the output would look like this:

1 4
2 4
3 4
4 4
1 3
2 3
3 3

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?

Upvotes: 2

Views: 996

Answers (1)

user4039065
user4039065

Reputation:

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

Upvotes: 1

Related Questions