VBA中的范围大小是否有限制？

Question

我创建了一个用户定义的函数来显示公司匹配的程度。 只要我使用的范围小于或接近第2800行，这个函数就能很好地工作。 我有一个包含4000家公司的列表，但是当我尝试包含在2800行以上的行时，该函数不起作用。 范围只能这么大吗？ 有没有办法解决这个问题？

这是代码：

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

- user2543319

---

范围限制与Excel表格大小限制相同。如果您可以将其放入工作表中，则可以将其映射到范围。当您说“它不起作用”时，这是什么意思？ - RBarryYoung

它会产生一个 #Value 错误。 - user2543319

不是特别相关，但使用str作为变量名可能会有问题，因为VBA内置了一个名为Str的函数（用于将数字转换为字符串）。除非您的str变量是一个数组，否则这不太可能成为问题 - 在这种情况下，str(1)将是一个数组访问而不是函数调用。 - barrowc

Answer 1

请参考网页 Excel 规格和限制。

该页面包括以下部分： 工作表和工作簿规格和限制 计算规格和限制 图表规格和限制 数据透视表和数据透视图报表规格和限制 共享工作簿规格和限制

我相信一个范围可以达到整个工作表的大小。工作表的大小限制为 1,048,576 行乘以 16,384 列。

在“计算规格和限制”部分中，所选范围的限制为 2048。

- user3791402

Answer 2

如果您尝试首先转储数组中的所有值，会发生什么？

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

我已经尝试过使用超过4000个单元格，没有问题。

- John Alexiou

Answer 3

对于 Range 对象的子字符串存在限制，这取决于您使用的 Excel 版本。但是从我记得的来看，无论您使用哪个版本，它肯定小于 4000。

为了解决这个问题，您应该将其分成几个部分并使用 Union。

- Philippe K

---

只是一个提示：你可以将这行代码 str = cell.Value 和其后面的三行代码移动到循环之外（在 For..Each 代码行之前），因为它们只需要被评估一次。 - Andy G