vba 中的范围大小有限制吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17430203/
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
Is there a limit to the size of a range in vba?
提问by user2543319
I created a user-defined function to show the degree to which companies are matchable. This function works really well, as long as the range I use as thisRange is less than or close to row 2800. I have a list of 4000 companies and when I try to include those in rows over 2800, the function doesn't work. Can the range only be so big? Is there any way to fix this problem?
我创建了一个用户定义的函数来显示公司的匹配程度。只要我用作 thisRange 的范围小于或接近第 2800 行，此函数就非常有效。我有一个 4000 家公司的列表，当我尝试将那些公司包含在 2800 行以上时，该函数不起作用。范围只能这么大吗？有没有办法解决这个问题？
Here's the code:
这是代码：
Function bestMatch(company As String, thisRange As Range) As String
Dim min As Double, nextMin As Double
Dim cell As Range
Dim score As Double
Dim best As String, str As String, nextBest As String
min = 99999
nextMin = 99999
For Each cell In thisRange.Cells
str = cell.Value
substr1 = Left(str, 1)
substr2 = Left(company, 1)
score = Leven(company, str)
If score < min And substr1 = substr2 Then
min = score
best = str
ElseIf score = min And substr1 = substr2 Then
min = score
best = str + " && " + best
ElseIf score > min And score <= nextMin And substr1 = substr2 Then
nextMin = score
nextBest = str
min = min
best = best
End If
Next
If min > 15 Then
bestMatch = "No Match Found"
ElseIf min <= 15 Then
bestMatch = "1. " + best + " 2. " + nextBest
End If
End Function
回答by user3791402
Refer to the web page Excel specifications and limits
请参阅网页Excel 规格和限制
The sections of this page include: Worksheet and workbook specifications and limits Calculation specifications and limits Charting specifications and limits PivotTable and PivotChart report specifications and limits Shared workbook specifications and limits
此页面的部分包括： 工作表和工作簿规范和限制 计算规范和限制 图表规范和限制 数据透视表和数据透视图报告规范和限制 共享工作簿规范和限制
I believe a range can be as large as an entire worksheet. A worksheet's size limit is listed as 1,048,576 rows by 16,384 columns.
我相信范围可以与整个工作表一样大。工作表的大小限制列为 1,048,576 行 x 16,384 列。
In the "Calculation specifications and limits" section, the limit of a SelectedRange is listed as 2048.
在“计算规范和限制”部分，选定范围的限制列为 2048。
回答by ja72
What if you try to dump all the values in an array first
如果您尝试先转储数组中的所有值会怎样
Function bestMatch(company As String, start_cell As Range, int count) As String
Dim vals() as Variant
vals = start_cell.Resize(count,1).Value2
For i=1 to count
substr1 = Left(vals(i,1), 1)
...
Next i
...
End Function
I have tried this with >4000 cells and there is no problem.
我已经尝试了 >4000 个单元格，没有问题。
回答by Philippe K
There is a limitation of the substring for Range object and it is varying depending on the version of Excel you are using. But from what I remember, I am pretty sure it is less than 4000, whatever version you have.
Range 对象的子字符串有一个限制，它会因您使用的 Excel 版本而异。但是根据我的记忆，我很确定它少于 4000，无论您使用的是什么版本。
In order to turn this thing around, you should divide it into some parts and use Union.
为了扭转这个局面，你应该把它分成几个部分并使用Union。
