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 …

### Like this:

Like Loading...

*Related*

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?

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.

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

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

Next

End If

End Function

Jim,

I use Union() to eliminate the overalps between ranges. Seems to work OK.

Charles

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.

Jim,

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!

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.