Share via

Excel 2010 WorksheetFunction Index limit in VBA

Anonymous
2014-06-24T16:57:04+00:00

I have a large dataset in Excel, which makes use of the fact that the current version supports more than 65536 rows.

My current project involves taking the dataset into memory and performing several functions to create a smaller file with aggregate data.

One of the operations involves returning just one column of data from the large array, and getting count, sum, max etc. for that column.

If I write the function in a worksheet cell, it works:

=MAX(INDEX(A1:G65537,0,4))

But in VBA it chokes and gives me a type mismatch error ONLY IF the number of rows exceeds the integer limit:

Sub Array_Copy_Test()

    Dim source_array As Variant

    Dim onecol() As Variant

    source_array = Sheets("rawdata").Range("$a$1:$g$65537").Value

    Erase onecol: ReDim onecol(65537, 1)

    onecol = Application.WorksheetFunction.Index(source_array, 0, 4)

    MsgBox Application.Max(onecol)

    'index function will not work if number of rows is greater than the integer limit of 65536

End Sub

The behavior is the same regardless of whether I call WorksheetFunction.Index or Index directly, i.e. changing the 6th line to this:

    onecol = Application.Index(source_array, 0, 4)

gives the same results.

My workaround would be to paste the formula into a worksheet cell using VBA and use the value from there.

But I was trying to avoid a lot of read/write operations to keep the code execution speed up; I need to run checks on every column.

I'm not sure where to report this bug (the VBA worksheet function should work the same as its counterpart in the worksheet).

If someone has another suggetion on how to accomplish this efficiently in VBA, I'd be interested.

(But not a solution that requires looping through every row or breaking the source data into blocks with less than 65536 rows).

Maybe there is a way to cast the argument to the index function as a long?  I'm not sure how for a built-in function.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2014-06-25T18:12:42+00:00

I did not recall the Evaluate method, and although it appears to work for an on-spreadsheet function, it doesn't actually help me deal with an array I've already loaded into VBA memory.

This works:

Sub evalu8()

    MsgBox ActiveSheet.Evaluate("Max(d1:d65537)")

End Sub

As does:

Sub evalu8()

    MsgBox Application.Max(ActiveSheet.Range("$d$1:$d$65537"))

End Sub

but I can not get it to refer to an array in memory.

You have to go to a sheet and re-read cells each time you want to run a computation.

(but this might be efficient enough for my purposes - I'll give it a try)

Also, I think Evaluate has to return a single answer, and not a range.

This works:

Sub evalu8()

    MsgBox ActiveSheet.Evaluate("Max(Index(a1:g65537,0,4))")

End Sub

But this does not:

Sub evalu8()

    Dim onecol() As Variant

    Erase onecol: ReDim oncecol(65536, 1)

    onecol = ActiveSheet.Evaluate("Index(a1:g65536,0,4)")

    MsgBox Application.Max(onecol)

End Sub

Regarding 65536 or 2^16, yes that was the old row limit.  I'm speculating the old limit had something to do with integers per two's compliment binary math: 10,000,000,000,000,000 (base 2) can be thought of either as 65,536 or as -32,767.  Whether it's 1 to 65,536 or -32,768 to 32,767, 16 bits will only get you 2^16 distinct numbers for rows.  The row and column limits are and were always powers of 2.  

Hi Peter,

If you will only get the MAX of the returned values, you can combine multiple formulas:

Sub Array_Copy_Test()

    Dim source_array As Range

    Dim onecol As Variant

    Set source_array = Sheets("rawdata").Range("$a$1:$g$65537")

    onecol = Evaluate("Max(Index(" & source_array.Address& ", 0, 4))")

    MsgBox onecol

    'index function will not work if number of rows is greater than the integer limit of 65536

End Sub

If you need all values then this will work:

Sub Array_Copy_Test2()

    Dim source_array As Range

    Dim onecol As Variant

    Set source_array = Sheets("rawdata").Range("$a$1:$g$65537")

    onecol = Evaluate("Index(" & source_array.Address & ", 0, 4)")

    Debug.Print onecol(1, 1), onecol(2, 1)

    'index function will not work if number of rows is greater than the integer limit of 65536

End Sub

The Integer definition has nothing to see with the number of rows. Earlier versions of Excel had 8192 & 16256 rows while an Integer has always been between -32727 & 32728.

Was this answer helpful?

0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-07-01T16:45:36+00:00

    Hi Peter,

    Although your response didn't pinpoint the exact cause of some of the errors..

    Sorry, my crystal sphere is broken.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-07-01T16:20:13+00:00

    Those both work, but only if the source sheet for the array is the active sheet.

    I am trying to do this with a hidden data sheet, so I need to be explicit about which worksheet to use for the evaluate function.

    It appears that adding

    sheets("rawdata").

    in front of the Evaluate statement does the trick (need it there as well as in the set statement).

    The type mismatch error in my earlier attempts appeared to be related to the index function not being able to recognize a number of rows that exceeded the old Excel limit (worked at 65,536 but not 65,537).  But in later attempts I had hidden the source sheet, and the code was trying to call Evaluate when the active sheet was a chart sheet (which made the result of the method an unprintable error).

    I also had an array name typo in a Redim statement in a previous attempt, which I did not see until just now.  Although your response didn't pinpoint the exact cause of some of the errors, examples of working code allowed me to test with some confidence that I didn't have a syntax/argument error (and thereby find the real cause of the error messages).

    Thanks!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-06-24T19:18:06+00:00

    I did not recall the Evaluate method, and although it appears to work for an on-spreadsheet function, it doesn't actually help me deal with an array I've already loaded into VBA memory.

    This works:

    Sub evalu8()

        MsgBox ActiveSheet.Evaluate("Max(d1:d65537)")

    End Sub

    As does:

    Sub evalu8()

        MsgBox Application.Max(ActiveSheet.Range("$d$1:$d$65537"))

    End Sub

    but I can not get it to refer to an array in memory.

    You have to go to a sheet and re-read cells each time you want to run a computation.

    (but this might be efficient enough for my purposes - I'll give it a try)

    Also, I think Evaluate has to return a single answer, and not a range.

    This works:

    Sub evalu8()

        MsgBox ActiveSheet.Evaluate("Max(Index(a1:g65537,0,4))")

    End Sub

    But this does not:

    Sub evalu8()

        Dim onecol() As Variant

        Erase onecol: ReDim oncecol(65536, 1)

        onecol = ActiveSheet.Evaluate("Index(a1:g65536,0,4)")

        MsgBox Application.Max(onecol)

    End Sub

    Regarding 65536 or 2^16, yes that was the old row limit.  I'm speculating the old limit had something to do with integers per two's compliment binary math: 10,000,000,000,000,000 (base 2) can be thought of either as 65,536 or as -32,767.  Whether it's 1 to 65,536 or -32,768 to 32,767, 16 bits will only get you 2^16 distinct numbers for rows.  The row and column limits are and were always powers of 2.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-06-24T17:22:16+00:00

    Hi Peter,

    The maximum value of an integer is 32728/-32727. The 65536 limit looks me more the row limit of Excel versions 2003 and before.

    There is another instruction to "calculate" and use Excel build-in formulas:

    Activesheet.Evaluate(formula)

    strCrit = "Sumproduct(" & _

                "--(" & IN_SERVICE & lngRow & ":" & IN_SERVICE & lngRow + intDevReturn & "=" & Embed("Yes") & "))" & _

                "- Sumproduct(" & _

                "--(" & gstrOperPartStatus & lngRow & ":" & gstrOperPartStatus & lngRow + intDevReturn & "=" & Embed("DEINSTALLED") & "))"

             lngEvaluate = ActiveSheet.Evaluate(strCrit)

    Herein:

    IN_SERVICE as a constant

    gstrOperPartStatus  & others are variables

    the type of the variable is shown in the 3/4 letter prefix (lng = long

    , str = string, g = global

    Embed is an internal function to add double quotes to a string.

    Public Function Embed(strText As String) As String

       Dim strResult As String

       strResult = Chr(34) & strText & Chr(34)

       Embed = strResult

    End Function

    HTH

    Was this answer helpful?

    0 comments