Mike Alexander: What useful purpose is served by having both .Count and .CountLarge properties of the Range class
I suspect there are a myriad of reasons for having both (backwards compatibilty, memory issues, etc...)
What backwards compatibility issues?
Try to come up with a scenario in which it NECESSARY to have both .Count and .CountLarge properties.
- Loading .XLS files in older versions.
- Loading .XLS{M|B} files in older versions.
- Loading .XLS files in Excel 2007 and later versions.
- Loading .XLS{M|B} files in Excel 2007 and later versions.
1 and 2 can't use .CountLarge anyway, and 2 would require nontrivial manual conversion if any worksheets use more than 65536 rows or 256 columns. Anyway, .CountLarge is a nonissue. As for .Count, assigning it to a Double would use 4 additional bytes of memory.
3 could be trouble if Excel 2007 assumes every worksheet in any file uses the big grid, but if it assumes .XLS files use the 65536-by-256 grid, then no problem assigning .Count to a Long.
4 needs to return values that exceed Long's domain. But if .Count returned a Double rather than a Long, that problem disappears.
Other than using 4 more bytes per variable, try to think of anything that won't work the same if .Count returned a Double rather than a Long. If you don't need your code to work under Excel 2007, nothing to do anyway. If you do need your code to work under both Excel 2003 and Excel 2007, you now have to add conditional compilation blocks to assign cells.Count to variables under Excel 2003 and cells.CountLarge to variables under Excel 2007. It'd be easier to assign both properties to the same variables, and to accomodate .CountLarge, those variables would need to be type Double anyway. At that point, it would have been easier for VBA programmers if Microsoft had made .Count return a Double and dispense with .CountLarge
Try to think of a backwards compatibility issue. Existing code assigning .Count to anything is ALREADY BROKEN because .Count could easily overflow in Excel 2007. Fixing this requires BOTH changing .Count to .CountLarge AND possibly (probably) changing the type of the variable to which one had assigned .Count and would now assign .CountLarge from Long to Double. That is, fixing the code already requires more than just switching from .Count to .CountLarge. And once you need to change the type of the variable, what purpose is served by leaving .Count as-is rather than making it return a Double?
For those of you who still don't get it,
Existing Excel 2003 code
Dim n As Long
':
n = ActiveSheet.UsedRange.Cells.Count
Current Excel 2007 recoding - 1st alternative (necessary if code would be used in both 2003 and 2007)
#If Excel2007 Then
Dim n As Double
#Else
Dim n As Long
#End If
':
#If Excel2007 Then
n = ActiveSheet.UsedRange.Cells.CountLarge
#Else
n = ActiveSheet.UsedRange.Cells.Count
#End If
Current Excel 2007 recoding - 2nd alternative (easiest if code would only be used in 2007)
Dim n As Double
':
n = ActiveSheet.UsedRange.Cells.CountLarge
Alternative Excel 2007 recoding (if MSFT had done it right - code could have been used in all versions)
Dim n As Double
':
n = ActiveSheet.UsedRange.Cells.Count
Those of you who still don't get it (i.e., understand what I mean), why not try something different and think about this issue?
Mike Alexander:
After all, why have integer, long, double, etc...
Much easier to answer. Just look at the conditional compilation syntax, using # as initial character. Just like C. And all of C's numeric types, even down to calling short integers Short and long integers Long. Just a guess, but it looks like the C programmers who wrote VBA in the first place included all of C's types (even char though in VBA it's called Byte).
Now, if you can't figure out on you own why there are separate integer (Short, Integer, Long) and floating point (Single, Double) types, I'm not going to tell you. You'll have to figure that out on your own.