Why is VBA only giving an overflow error when assigning arrays over a certain size

Question

I have a code that needs to analyze a very large amount of data that has new data added regularly, and as such, the array size increases each week. The codes overall purpose is to compare the new data, to the previous weeks data and do some analysis of those items.

It has been working fine for weeks, but this week I am getting an overflow error when I try to assign the array from the excel sheet range. I tried running the same code on last weeks array, which is slightly smaller, and it still works fine. It seems that the reason for the overflow, is because there is more data this week.

Last weeks array had about 1.16 million elements, and the assignment worked fine. This week, the array has about 1.28 million elements, and I get an overflow error.

Below is a small snippet of the code. The line that causes the error is the line where oldArr is assigned. Note that oldArr is not declared prior to this line. I have tried assigning it with a "DIM" statement, but that did not change the result. I assume this is a data size issue, but according to some searching, VBA has had no limit on array size for many years.

The code works on last weeks data, where lastRow_old = 10734. However, this week it equals 11847, and now I get an overflow. Why am I getting this error now and how can I fix it?

Dim oldWS As Worksheet 
Set oldWS = ThisWorkbook.ActiveSheet
firstRow = 3
lastRow_old = oldWS.Cells(Rows.Count, 4).End(xlUp).Row 

oldArr = oldWS.Range("A" & firstRow & ":EE" & lastRow_old)

I'd suggest checking stackoverflow.com/questions/26146939/… or stackoverflow.com/questions/65580481/… — BigBen
– BigBen, Commented Feb 28 at 16:30
Try just loading the range for the last week's data and see if that gives the same error — Tim Williams
– Tim Williams, Commented Feb 28 at 16:59
@TimWilliams, I have tried doing this. To be a little more clear, what is not working, is when I try to run the code to compare this weeks data with last weeks data (so oldArr / oldWS refers to last weeks data, and last week has 11,847 rows). However, if I go back a week, and try to compare the data from 2 weeks ago (so oldArr and oldWS are the data from 2 weeks ago, and has 10734 rows), to the data from this week, then it works fine. — ChrisW
– ChrisW, Commented Feb 28 at 19:01
Ok well you need to identify where the problem is in your latest dataset, so maybe try looping over that and loading blocks of rows until you hit the error? Seems like (based on @BigBen's links) there may be some problems with some of the cell values in your latest dataset. A range that same size works fine for me.... — Tim Williams
– Tim Williams, Commented Feb 28 at 19:18

CDP1802 · Accepted Answer · 2025-02-28 19:52:15Z

0

This code may take some time but should find a problem cell.

Option Explicit
Sub dbug()
    Dim cel As Range
    For Each cel In ActiveSheet.UsedRange
        If Left(cel.Text, 10) = "##########" Then
            MsgBox cel.Address & vbLf & cel.Text & vbLf & cel.NumberFormat
            Exit Sub
        End If
    Next
    MsgBox "done"
End Sub

answered Feb 28 at 19:52

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FunThomas Mar 1 at 19:54

If the column width is not large enough to display 10 #-characters, the code will not find the cell. Range.Text will return exactly what is displayed.

CDP1802 Mar 1 at 21:12

@funthomas Yes, surprisingly Cells(1).ColumnWidth = 1: Cells(1) = "##########": Debug.Print Cells(1).Text doesn't but Cells(1).NumberFormat = "d/m/y":Cells(1) = 123456789: Debug.Print Cells(1).Text does. I guess ActiveSheet.UsedRange.ColumnWidth = 20 would fix it allowing for font size.

ChrisW Aug 25 at 18:30

Have not worked on this in a while and just came back to it. I did not use your code, but I believe it would have worked, as the problem cell did in fact have "##########" displayed. I wrote a code that assigned an entire row to an array at a time to reduce the number of iterations needed, and then just visually scrolled through the row to find the error. It looks like the issue, is that somehow a cell was formatted as a date, when it should not have been, and it came up as a negative date.

Collectives™ on Stack Overflow

Why is VBA only giving an overflow error when assigning arrays over a certain size

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related