Excel 2007/2010 range.count too many cells bug

Ian Bennett just sent me details of a bug in build 501 of FastExcel: the error message he got is –

VBA Error 6 is Overflow and line 1630 says

nCells = Worksheets(j).UsedRange.Count

nCells is dimmed as a Long and the maximum number a Long can hold is 2,147,483,647, and in the Excel 2007/2010 big grid you can have 16384 columns x 1048576 rows = 17,179,869,184 cells.
So it won’t fit in a Long!
No problem I thought, I will dim nCells as Currency (Currency data type can hold 922,337,203,685,477.5807).

Well it does not work: still gives overflow. The reason is a fundamental problem in the Excel Object model:
Range.Count is defined as a long so it will always overflow (before it returns the answer to your variable) if the Range is too large .

So it seems that the only answer is to roll your own function:

EDIT: Eric points out that you need to cope with multi-area disjoint ranges. Originally I was thinking of the UsedRange which is always rectangular. But Eric is right, so here is a revised function that handles multi-area ranges.

Function RangeCount(theRange As Range) As Currency
Dim nRows As Currency
Dim nCols As Currency
Dim rng As Range
Dim UnionRange As Range
If Not theRange Is Nothing Then
' eliminate area overlaps
Set UnionRange = theRange.Areas(1)
If theRange.Areas.Count > 1 Then
For Each rng In theRange.Areas
Set UnionRange = Union(UnionRange, rng)
Next rng
End If
' count each area separately
For Each rng In UnionRange.Areas
nRows = rng.Rows.Count
nCols = rng.Columns.Count
RangeCount = RangeCount + nRows * nCols
Next rng
End If
End Function

This works! So now all I have to do is fix all the places in FastExcel where this problem might occur, create a new build, test it and upload it …

This entry was posted in UDF, VBA. Bookmark the permalink.

8 Responses to Excel 2007/2010 range.count too many cells bug

  1. Eric says:

    This assumes the range is rectangular and contiguous, doesn’t it? So, if we have a range that isn’t guaranteed to be that, is the only way to defeat this issue to count every cell using some sort of loop?

    • fastexcel says:

      Thanks for pointing that out: I was thinking of the used range whcih is always rectangular. But you are correct – you need to loop on the areas of the range (and also eliminate any overlap between the ranges).
      I have revised the function to do this.

  2. Eric says:

    Nice method for figuring that out… I was thinking of looping through all the cells, this is soooo much more elegant!

  3. Jim Cone says:

    I don’t believe there is an easy solution that eliminates double counting of overlapping cells in a multi-area range.
    I usually just check for an overlap and tell the user to try again.
    Function HasOverLap(ByRef AllCells As Excel.Range) As Boolean
    Dim AreaCnt As Long
    Dim N As Long
    Dim i As Long

    AreaCnt = AllCells.Areas.Count
    If AreaCnt > 1 Then
    For N = 1 To AreaCnt – 1
    For i = (N + 1) To AreaCnt
    If Not Application.Intersect(AllCells.Areas(N), _
    AllCells.Areas(i)) Is Nothing Then
    HasOverLap = True
    Exit Function
    End If
    Next ‘i
    End If
    End Function

  4. Jim Cone says:

    For some reason my mileage varies .
    Calling the RangeCount function on an overlapping Excel selection returns the sum of the cell counts in each selection.
    With “C5:D14,B7:E8” selected,
    MsgBox RangeCount(Selection) returns 28 not 24.

    What also might be of interest is the count returned from the use of
    SpecialCells(xlCellTypeAllFormatConditions) in xl2003 vs. xl2007.
    Overlapping ranges are double counted in xl2007, but not in xl2003.

    • fastexcel says:

      You are right, I was thinking of 100% overlaps (and that only works between 2 ranges).
      Anyway I think the RangeCount function works fine when you pass it the usedrange or non-overlapping ranges, which is where i started!

  5. fastexcel says:

    Ian Bennett points out that you can use the new in Excel 2007 property Range.CountLarge (which I had forgotten about). Help incorrectly says this returns the largest value in a range: actually it returns the count of cells in the range inside something weird: a variant containing an unsupported variant type which VBA can somehow convert to a double or a currency datatype.
    And to use CountLarge and be compatible with Excel 2003 you would need to have code that checked application.version and used .Count or .Countlarge according tgo version.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s