Is there a limit to the size of a range in vba?

Question

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?

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

Answer 1

Refer to the web page Excel specifications and limits

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.

In the "Calculation specifications and limits" section, the limit of a Selected Range is listed as 2048.

Answer 2

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.