Excel 2007. BUG. Count is defined as a long. Cells.Count results in error Overflow

[keepITcool]

in Excel 2007 try: ?Activesheet.Cells.Count

The actual number of rows = 1.048.567 (2^20)
The actual number of columns = 16.385 (2^14)

The actual number of cells = 17.179.869.184 (2^34)

The maximum number in a long = 2.147.483.647 (2^31-1)

result... Error 6 Overflow


I'm pretty sure this may present some serious problems in the lifespan
of Excel 2007, also with selection .count etc.

You cannot convert it using CDbl as the error is generated internally
when the number is assigned to the count property return value.


Solution:
the Count property must be redefined to return a variant (or double.)

 

[Nick Hodge]

KeepITCool

To reference the large grid and to not break existing code, Excel have added

Cells.CountLarge

Returns
17179869184

This may change before RTM, but the feeling is, as it appears next to .count
in intellisense it will be better as CountLarge

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Nick Hodge]

You would also use application.Version to determine the grid size in cross
version applications

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

KeepITCool

To reference the large grid and to not break existing code, Excel have
added

Cells.CountLarge

Returns
17179869184

This may change before RTM, but the feeling is, as it appears next to
.count in intellisense it will be better as CountLarge

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[keepITcool]

thx.

Countlarge... hmmm

My existing code does break. If user selects All cells and my code
tries to determine the selection size...

I hope they'll add a simple conditional constant to determine
Excel version. #if xl12 then


application.version doesn't equate gridsize, as xl12 can open both
large and small grids... u could look at the compatibility mode
(conditional compile..) etc. but i think i'll stick to
Worksheet.Rows.count

 

[Nick Hodge]

Believe me... I believe this is the least of our issues with the large grid.
I believe we will get many users asking why their formulae take so long to
calculate, etc

Time will tell...

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)