Ian Bennett just sent me details of a bug in build 501 of FastExcel: the error message he got is –
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 …