# Limitations on Arrays

• ### Question

• Earlier versions of Excel had some limits on on the number of cells you could assign from a VBA array  (passing the results from the VBA array to the worksheet).  How have these changed in later versions of Excel?

I know that there seems to be a limit in VBA when using worksheetfunction.transpose of 2^16 elements in the array.  This is an increase from the previous 5,000 or so.

Ron
Sunday, January 15, 2012 1:40 PM

### All replies

• Can't find official documentation, but you can at least fill an entire column in Excel 2007:

Sub dural()
Dim BigGuy(1 To 1048576, 1 To 1) As Long
Dim I As Long
For I = 1 To 1048576
BigGuy(I, 1) = I
Next
Range("B:B") = BigGuy
End Sub

Since I am going 2D -> 2D, I am not using Transpose()

gsnu201201
Sunday, January 15, 2012 2:14 PM
• Actually, you can go further than that.  With similar testing I found a limit of about 2^24 for that feature (crashed on 2^25).

The issue came up in trying to process a large database in Excel -- about 500,000 elements; and do it in some reasonable amount of time.  Transferring data using arrays seemed efficacious.

In Excel 2007, the AdvancedFilter, which should have been the way to go, seems flakey under certain conditions.  I used dictionaries and that worked well to filter out items in one column that were present in another column.

But then when transferring data back to the worksheet, I ran into the TRANSPOSE limit.  That was an easy workaround by setting up a 2D array instead of a 1D array and then transposing.  So I started to wonder about array limits in general -- hence my question.

There are some limits with string arrays that are documented.

Ron
Sunday, January 15, 2012 2:29 PM
• I rummaged thru MSDN, but I could find nothing "official"
gsnu201201
Sunday, January 15, 2012 2:32 PM
• hi Ron,

i do not understand what you mean by a limitation (limitation to 65535 rows?).
the following macro works well on XL2002
Sub Test()
Dim a(1 To 2, 1 To 65536)
For i = 1 To 65536
a(1, i) = "a" & i
a(2, i) = "b" & i
Next i
Range("A1").Resize(UBound(a, 2), 2) = Application.Transpose(a)
End Sub
--
isabelle

Le 2012-01-15 08:40, Ron Rosenfeld a écrit :
> Earlier versions of Excel had some limits on on the number of cells you could assign from a VBA array (passing the results from the VBA array to the worksheet). How have these changed in later versions of Excel?
> I know that there seems to be a limit in VBA when using worksheetfunction.transpose of 2^16 elements in the array. This is an increase from the previous 5,000 or so.
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Ron

Sunday, January 15, 2012 2:53 PM
• Hi Ron,

There was indeed a 5461element limit which affected lots of array stuff in xl97 and some scenarios in xl2000. That was removed in later versions.

I'm sure there is some element limit now for dumping a VBA array to cells but in practice probably not worth trying to get near it. The optimum size will likely vary with system specs,  IOW, it might be faster to dump 4x100k than 1x400k even if it means populating a temporary 100k array each time from the main array. So try testing and timing different size batches. By size I mean total elements, ie rows x columns.

Peter Thornton

Sunday, January 15, 2012 3:08 PM
• Array Formula that try to return more than 65535 rows from a UDF fail with #Value even in Excel 2010.
Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/
Sunday, January 15, 2012 5:44 PM
• Isabelle,

The limitation I am writing about can be seen if you try, using VBA, to transpose a variant array that has more than 2^16 elements, a type mismatch error occurs.  You can also see that in your example by changing 65536 to 65537

=======================
Sub ArrayTester2()
Dim v()
Dim i As Long, j As Long

For i = 0 To 20
ReDim v(1 To 2 ^ i + 1)
For j = 1 To 2 ^ i + 1
v(j) = j
Next j
Debug.Print "i", i, 2 ^ i, "Elements: ", UBound(v)
v = WorksheetFunction.Transpose(v)
Next i
End Sub
===============================

== Ron

Ron
Sunday, January 15, 2012 6:23 PM
• Charles,

After you wrote that I did some testing (as I had been only working with Macros, and not UDF's). So I can confirm that happens in Excel 2007 UDF's also.

But, in a macro (Sub), I can dump the full 2^20 rows (Excel 2007) to a range without an error.

In the routine below, when i=26 an out of memory error occurs on the ReDim line; but for i=20 or more, all of the rows are populated.

===========================
Option Explicit
Sub ArrayTester()
Dim v()
Dim i As Long, j As Long
Dim rg As Range
For i = 0 To 30
ReDim v(1 To 2 ^ i, 1 To 1)
Set rg = Range(Cells(1, 1), Cells(WorksheetFunction.Min(2 ^ i, 2 ^ 20), 1))
For j = 1 To 2 ^ i
v(j, 1) = j
Next j
Debug.Print "i", i, Format(2 ^ i, "#,###")
rg.ClearContents
rg = v
Next i
End Sub
=====================================

Ron
Sunday, January 15, 2012 6:41 PM
• Array Formula that try to return more than 65535 rows from a UDF fail with #Value even in Excel 2010.

I think Ron was asking about the limits for dumping an array to cells from VBA but I might have misunderstood.

Peter Thornton

Sunday, January 15, 2012 7:04 PM
• hi Ron,

when i = 16 then UBound(v) = 65537
so it is not possible with XL2003 and less

--
isabelle
Le 2012-01-15 13:23, Ron Rosenfeld a écrit :
> Isabelle,
>
> The limitation I am writing about can be seen if you try, using VBA, to transpose a variant array that has more than 2^16 elements, a type mismatch error occurs. You can also see that in your example by changing 65536 to 65537
>
> =======================
> Sub ArrayTester2()
> Dim v()
> Dim i As Long, j As Long
>
> For i = 0 To 20
> ReDim v(1 To 2 ^ i + 1)
> For j = 1 To 2 ^ i + 1
> v(j) = j
> Next j
> Debug.Print "i", i, 2 ^ i, "Elements: ", UBound(v)
> v = WorksheetFunction.Transpose(v)
> Next i
> End Sub
> ===============================
>
> == Ron
>
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Ron

Monday, January 16, 2012 1:56 AM
• hi Ron,

try this new test,

Sub ArrayTester3()
Dim v()
Dim i As Long, j As Long

For i = 0 To 16
ReDim v(1 To 2 ^ i)
For j = 1 To 2 ^ i
v(j) = j
Next j
Debug.Print "i", i, 2 ^ i, "Elements: ", UBound(v)
v = WorksheetFunction.Transpose(v)
Next i
End Sub

--
isabelle

Monday, January 16, 2012 3:02 AM
• Isabelle,

Apparently it is a limitation of the worksheetfunction.Transpose method, which persists in XL2007 and possibly 2010.

Ron
Monday, January 16, 2012 3:06 AM
• I'm not sure what point you are trying to make, Isabelle.

My point has been that worksheetfunction.transpose has a LIMIT of 2^16 elements.

Your ArrayTester3 seems to show that it works OK up to that limit.

Or am I missing something?

-- Ron

Ron
Monday, January 16, 2012 3:17 AM
• no Ron, this is to confirm that worksheetfunction.transpose has a limit of 2 ^ 16 elements to excel 2003 and less.
for Excel2007 and +  i do not know
--
isabelle
Le 2012-01-15 22:17, Ron Rosenfeld a écrit :
> I'm not sure what point you are trying to make, Isabelle.
>
> My point has been that worksheetfunction.transpose has a LIMIT of 2^16 elements.
>
> Your ArrayTester3 seems to show that it works OK up to that limit.
>
> Or am I missing something?
>
> -- Ron
>
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Ron

Monday, January 16, 2012 3:50 AM
• On Mon, 16 Jan 2012 03:50:54 +0000, isabelleV wrote:

>no Ron, this is to confirm that worksheetfunction.transpose has a limit of 2 ^ 16 elements to excel 2003 and less. for Excel2007 and +  i do not know

Well, that routine works in xl2007 but will crash on 2^16+1 elements.

Ron
Monday, January 16, 2012 4:13 AM
• I have also seen that  worksheetfunction.transpose(rng) does not return more than 2^16 rows from a range to an array.

More generally it returns      n mod 2^16 rows     where n is the number of rows in the range.

There is no error when assigning the array, merely wrong number of rows (I am not sure which ones)

Going the other way  from the array to a range   (worksheetfunction.transpose(array)), there is an error-13, mismatched type if array contains more than 2^16 rows.

dbraithwaite

Sunday, February 26, 2012 4:27 AM