vba 以编程方式获取工作表的最大允许列数和行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22483192/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Programmatically getting the maximum allowed number of columns and rows of a Sheet
提问by TheBlastOne
In a sick Excel (I use Office 10) VBA macro, i need to trap the case where the user managed to create a sheet with the maximum number of columns or rows by accident.
在病态的 Excel（我使用 Office 10）VBA 宏中，我需要捕获用户设法意外创建了具有最大列数或行数的工作表的情况。
I don′t remember how that happens, nor would I care; but most of us have seen Excel sheets that had an active sheet size of 16384 cells in width and/or 1048576 cells in height -- even though there were only a handful of nonempty cells on the sheet.
我不记得那是怎么发生的，我也不会在意；但我们大多数人都看到 Excel 工作表的活动工作表大小为 16384 个单元格的宽度和/或 1048576 个单元格的高度——即使工作表上只有少数非空单元格。
I simply want to detect this situation in VBA code.
我只是想在 VBA 代码中检测这种情况。
A sheet′s Rows.Countand Columns.Countof course return the figures, and I could check if they equal 16384or 1048576, respectively.
一张纸Rows.Count和Columns.Count当然返回数字，我可以分别检查它们是否相等16384或1048576。
However, those limits are version-dependent.
但是，这些限制取决于版本。
So my question is:
所以我的问题是：
How can I get the maximum limit for the number of rows and columns in a given Excel worksheet in VBA without coding version-dependent if-statements?There might be a constant for each limit value, but I yet failed to find it.
如何在 VBA 中获得给定 Excel 工作表中行数和列数的最大限制，而无需编写与版本相关的 if 语句？每个极限值可能都有一个常数，但我还没有找到它。
Note I am not looking for the size of Worksheet.UsedRange. I want to determine if UsedRangehas been extended to the whole available "sheetspace", which usually happens only by accident, but -- it happens, and I want to detect that situations.
注意我不是在寻找Worksheet.UsedRange. 我想确定是否UsedRange已扩展到整个可用的“工作表空间”，这通常只是偶然发生的，但是——它发生了，我想检测这种情况。
I am also not looking for the limits per Excel version. (This is easily googleable.) I don′t want to hardcode those values and version numbers.
我也不是在寻找每个 Excel 版本的限制。（这很容易在 google 上搜索到。）我不想对这些值和版本号进行硬编码。
回答by John Bustos
The easiest way to accomplish this is by comparing your UsedRange's Row and Column count to your Sheet's Row and Column count.
完成此操作的最简单方法是将您的 UsedRange 的行数和列数与工作表的行数和列数进行比较。
For example:
例如：
If ActiveSheet.UsedRange.Rows.Count = ActiveSheet.Rows.Count Then
... However you want to deal with this scenario ...
End If
And similarly with column count.
与列数类似。
