在不同版本的Excel中计数单元格时，值不正确 [英] Incorrect value when counting cells in different versions of Excel

问题描述

当我尝试使用 Cells.Count 的一些变体时，我看到一些SO用户遇到问题。在某些情况下，VBA代码会引发溢出错误。

I have seen some SO users run into an issue when trying to use some variation of Cells.Count; the VBA code throws an overflow error in some cases.

有关参考，请参阅此答案的评论：

For reference, see comments on this answer:

我认为这可以工作，但是我得到一个溢出错误，它指向
代码如果Master.Cells 。特殊号码（xlCellTypeVisible）.Count> 0
然后---它似乎不过滤任何东西
- user1556069

I think this will work, but I get an "overflow" error and it points me to the code "If Master.Cells.SpecialCells(xlCellTypeVisible).Count > 0 Then" --- it seems like it's not filtering for anything in particular – user1556069

和此答案：

这个onyl工作（和Cells.Count没有工作），因为后者
使用一个整数，16位，最大值65,536和整个
电子表格返回numbr更大？ - fast_code

Is this onyl working (and Cells.Count didnt work) because the latter used an integer, 16 bits, max value of 65,536 and the whole spreadsheet returned a numbr greater? – fast_code

我假设在幕后的地方，VBA试图强制
细胞计数一个小整数（16位）或长整数（32位）。
Excel 2007工作表的单元格计数将溢出这两个
数据类型。不幸的是，我现在无法隔离它，因为我没有
有一个Excel 2007的副本方便，不能实际重现你的
错误。 - mwolfe02

I'm assuming that somewhere behind the scenes VBA is trying to coerce the cell count to a small Integer (16-bit) or Long integer (32-bit). The cell count of an Excel 2007 worksheet would overflow both of those datatypes. Unfortunately I can't isolate it right now because I don't have a copy of Excel 2007 handy and cannot actually reproduce your error. – mwolfe02

试图理解这一点，我试图重现自己，并尝试分配单元格时溢出.Count 作为整数。这对于整数数据类型的值太大是有道理的。

Trying to understand this, I tried to reproduce myself and got an overflow when trying to assign Cells.Count as an Integer. This makes sense as the value is too large for the Integer data type.

在Excel 2003和2010两者中使用下面的代码，尝试使用分配为长或变量。

Using the code below in both Excel 2003 and 2010, I was given a numeric result when trying to assign as a Long or Variant.

Option Explicit

Sub testInteger()
    Dim i As Integer
    i = Cells.Count 'Overflow
    Debug.Print i 'Doesn't get this far...
End Sub

Sub testLong()
    Dim l As Long
    l = Cells.Count
    Debug.Print l 'Prints 16777216 in both versions
End Sub

Sub testVariant()
    Dim v As Variant
    v = Cells.Count
    Debug.Print v 'Prints 16777216 in both versions
End Sub

正如您在评论中看到的那样， Cells.Count 值为 16777216 （对于2003年是正确的），但是对于这两个版本，它们是相同的 ，这对我来说没有意义。从以上链接答案中引用 mwolfe02 ：

As you can see in my comments, the Cells.Count value is 16777216 (which is correct for 2003), but it is the same for both versions, and that doesn't make sense to me. To quote mwolfe02 from one of the above-linked answers:

Excel 2007工作表共有1,048,576行和16,384列，总共17,179,869,184个单元格。

Excel 2007 worksheets have 1,048,576 rows and 16,384 columns for a total of 17,179,869,184 cells.

哪个告诉我2010年印刷的价值至少应该是（我相信应该是一样的） 17,179,869,184 。

Which tells me the value printed in 2010 should be at least (I believe it should really be the same) 17,179,869,184.

那么为什么这个数字不能正确打印/为什么在2010年返回的2003年的价值？

推荐答案

计算此类大数时，请使用 .Countlarge 属性。

When calculating such large numbers use .Countlarge property.

例如

Sub CellsCount()
    Dim l As Double
    l = ActiveSheet.Cells.CountLarge
    Debug.Print l
End Sub

也永远不要使用 Cells.Count 或 Cells.CountLarge 而不指定工作表对象。这是为了确保在兼容模式下我们不会收到错误的计数/错误。同样也不要使用 Rows.Count 。始终使用 ws.Rows.Count 。这是人们在尝试找到excel中最后一行时最常见的错误。例如

Also never use Cells.Count or Cells.CountLarge without specifying the worksheet object. This is to ensure that we don't get incorrect count/error in compatibility mode. Similarly never use Rows.Count. Always use ws.Rows.Count. This is the most common error people make while trying to find the last row in excel. For example

这个

lRow = ws.Range("A" & Rows.Count).End(xlUp).Row

和

lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

可能永远不会给您相同的结果。

might not give you the same results always.

我还会建议进一步阅读此。

I would also recommend further reading of this.

这篇关于在不同版本的Excel中计数单元格时，值不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！