CODEWITHSUNDEEP
arraysvbaexcel
dadazhu
dadazhu

Reputation: 3

VBA array size reset once hitting 32767 (Excel 2010)

I use a 1D dynamic array to hold values that total numbers of them will be 60K plus. This seems like an easy task but I noticed that once the size of the array reaches 32767, it started from 0 again. Any suggestion will be appreciated.

My code:

Sub GetHours()

Dim R As Long, i As Long, N As Long, var, vRaw, v

R = LastUsedRow(Sheet1)
With Sheet1
      vRaw = .Range(.Cells(2, 1), .Cells(R, 22)).Value
End With

For i = 1 To R - 1
    var = vRaw(i, 12)
    If IsNumeric(var) Then
        If IsArrayEmpty(v) Then
            ReDim v(0)
            v(0) = i
        Else
            N = UBound(v) + 1
            ReDim Preserve v(N)
            v(N) = i
        End If

    End If
Next

End Sub

Function LastUsedRow(ByVal ws As Worksheet) As Long

Dim lastrow As Long

On Error GoTo errHandler
lastrow = ws.Cells.Find(What:="*", After:=[A1], _
                         SearchOrder:=xlByRows, _
                         SearchDirection:=xlPrevious).Row

LastUsedRow = lastrow
Exit Function

errHandler:
    LastUsedRow = 0

End Function

Function IsArrayEmpty(anArray As Variant)
Dim i As Integer
On Error Resume Next
i = UBound(anArray, 1)

If Err.Number = 0 Then
    IsArrayEmpty = False
Else
    IsArrayEmpty = True
End If
End Function

Upvotes: 0

Views: 436

Answers (1)

Tim Williams
Tim Williams

Reputation: 166531

Note you have Dim i As Integer in that last IsArrayEmpty function...

That will trigger an out of bounds error above ~32k which will then trigger your array to be reset.

Upvotes: 2

Related Questions