ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   5461 element limit (https://www.excelbanter.com/excel-programming/326785-5461-element-limit.html)

Alan Beban[_2_]
5461 element limit
 
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[_5_]
5461 element limit
 
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 wrote:

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

Alan Beban[_2_]
5461 element limit
 
Thanks, Dave,

INDEX, MATCH and VLOOKUP were other affected functions.

Alan Beban

Dave Peterson wrote:
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 wrote:

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[_5_]
5461 element limit
 
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 wrote:

Thanks, Dave,

INDEX, MATCH and VLOOKUP were other affected functions.

Alan Beban

Dave Peterson wrote:
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 wrote:

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

Alan Beban[_2_]
5461 element limit
 
Dave Peterson wrote:
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


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 wrote:

Thanks, Dave,

INDEX, MATCH and VLOOKUP were other affected functions.

Alan Beban

Dave Peterson wrote:

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 wrote:


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[_5_]
5461 element limit
 
I guess I was always lucky enough to use ranges in that kind of
application.vlookup() in earlier versions.



Alan Beban wrote:

Dave Peterson wrote:
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


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 wrote:

Thanks, Dave,

INDEX, MATCH and VLOOKUP were other affected functions.

Alan Beban

Dave Peterson wrote:

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 wrote:


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


All times are GMT +1. The time now is 10:39 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com