none
Limitations on Arrays RRS feed

  • 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
    Moderator
  • 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
    Moderator
  • 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
    Moderator
  • 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
    Moderator
  • hi Ron,
     
    with your example,
    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