5461 element limit

[Alan Beban]

In xl2000 there is a limit of 5461 elements on the efficacy of many
worksheet functions used in VBA code. Has that limit been changed in
later versions of Excel?

Thanks,
Alan Beban

 

[Dave Peterson]

I remember that xl2002 removed the 5461 limit in application.transpose. And
it's still gone in xl2003:

Option Explicit
Sub testme()
Dim myArr As Variant

With ActiveSheet.Range("a1:a65536")
.Formula = "=row()"
.Value = .Value
myArr = .Value
End With

With Application
myArr = .Transpose(.Transpose(myArr))
End With

Debug.Print UBound(myArr)

End Sub

showed:
65536
in the immediate window.

Do you have a particular function you're concerned about?

 

[Alan Beban]

Thanks, Dave,

INDEX, MATCH and VLOOKUP were other affected functions.

Alan Beban

 

[Dave Peterson]

I don't recall those functions exhibiting this problem.

But I tried this:

Option Explicit
Sub testme()
Dim myArr As Variant
Dim res As Variant

With ActiveSheet.Range("a1:b65536")
.Formula = "=row()"
.Value = .Value

res = Application.Index(.Range("a:a"), 65500)

Debug.Print "---.index---"
If IsError(res) Then
Debug.Print CStr(res)
Else
Debug.Print res
End If

res = Application.VLookup(65500, .Range("a:b"), 2, False)

Debug.Print "---.vlookup---"
If IsError(res) Then
Debug.Print CStr(res)
Else
Debug.Print res
End If

res = Application.Match(65500, .Range("a:a"), 0)

Debug.Print "---.match---"
If IsError(res) Then
Debug.Print CStr(res)
Else
Debug.Print res
End If

End With

End Sub

and got this:
---.index---
65500
---.vlookup---
65500
---.match---
65500

And using the array:

Option Explicit
Sub testme()
Dim myArr As Variant
Dim res As Variant

With ActiveSheet.Range("a1:b65536")
.Formula = "=row()"
.Value = .Value
myArr = .Value
End With

With Application
res = .Match(65500, .Index(myArr, 0, 1), 0)
Debug.Print "---.match & .index---"
If IsError(res) Then
Debug.Print CStr(res)
Else
Debug.Print res
End If
End With

End Sub

I got:

---.match & .index---
65500

=========
Did I get close to what you were looking for?

If no, if you have some code, I can paste it in and test it out.

 

[Alan Beban]

I don't recall those functions exhibiting this problem.

Oh yes, indeed. Thanks for the code below. Another that doesn't work
in xl2000 because of the limit would be

res = .Vlookup(65500, myArr, 2, False)

Thanks again,
Alan Beban

 

[Dave Peterson]

I guess I was always lucky enough to use ranges in that kind of
application.vlookup() in earlier versions.