Range .Count in Excel 2007

[Harlan Grove]

I don't have XL2007 yet, but I've been thinking about how I might need
to rewrite code to cope with the new worksheet grid size. Since XL2007
has 2^20 rows and 2^16 columns, it has 2^36 cells per worksheet.
That's more than can be stored in a long integer (which can store only
up to 2^31-1). Has the return type for the .Count property of the
Range class become Double type, or will .Count property calls for
ranges with more than 2^31-1 cells throw errors or (this'd be fun!)
return incorrect long integer values, e.g., returning unsigned longs
from the underlying Excel OM implementation that would be interpreted
as negative signed integers by VBA?

I did search Google Groups, but I found nothing on this.

 

[Bob Phillips]

Harlan,

Actually the grid is 2^20 x 2^14, not 2^16.

Count is still a Long, but to accommodate this, Range has a new property,
CountLarge, which is type Variant.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

xl2007 added range.countlarge

From:
http://msdn2.microsoft.com/en-us/library/bb242638.aspx

Range.CountLarge Property
Excel Developer Reference

Counts the largest value in a given range of values. Read-only Variant.
Version Information
Version Added: Excel 2007

Syntax
expression.CountLarge
expression A variable that represents a Range object.

======
Range.count still returns a long in xl2007 and a long is still: -2,147,483,648
to 2,147,483,647

Yes:
msgbox activesheet.cells.count
will cause an error.

 

[Harlan Grove]

Actually the grid is 2^20 x 2^14, not 2^16.

Count is still a Long, but to accommodate this, Range has a new
property, CountLarge, which is type Variant.

....

So there's an OM incompatibility between XL2007 and prior versions. So
we face the same sort of fun as we did with the VBA5 to VBA6
transition from XL97 to XL2000? Need to add conditional compilation
blocks to check XL version? What fun!

Thanks.

 

[Bob Phillips]

Exactly, but at least there is a way, better than none at all I guess.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)