What is rule for array size limit of WorksheetFunctions?

[gifariz]

Hi VBA masters,

I want to know if there is good rule of array size limit of WorksheetFunctions. Because sometimes WorksheetFunctions return incorrect result because I dont know the limitation of use.
I saw some previous posts here and other sites, but it is quite old, wondering if there is update also especially after dynamic-array-enabled excel update.
Apparently, if we pass 1d array input, it is limited by max number of columns in excel (2^14=16384).
And, if we pass 2d array input, then there is no limit of array input.
Is this correct?

 

[James006]

Hi,
Currently, the main limitation is : your PC available memory
Excel specifications and Limits :

Excel specifications and limits - Microsoft Support

In Excel 2010, the maximum worksheet size is 1,048,576 rows by 16,384 columns. In this article, find all workbook, worksheet, and feature specifications and limits.

support.microsoft.com

 

[Mario_R]

Hello gifariz.
Clearly explain what you want to do because using Excel functions in a macro doesn't always make sense.

 

[gifariz]

Hi,
Currently, the main limitation is : your PC available memory
Excel specifications and Limits :

Excel specifications and limits - Microsoft Support

In Excel 2010, the maximum worksheet size is 1,048,576 rows by 16,384 columns. In this article, find all workbook, worksheet, and feature specifications and limits.

support.microsoft.com

Do you refer to "worksheet arrays" in the your link?

But it seems that 1d array input for VBA WorksheetFunctions is limited by something.
For example,

Sub xx()

    Dim x, y1, y2, y3
    Dim i As Long
    
    '2d is okay
    ReDim x(1 To 1100000, 1 To 1)
    For i = 1 To UBound(x)
        x(i, 1) = i
    Next i
    y1 = WorksheetFunction.Max(x)    'returns 1100000
    
    '1d seems limited by something
    ReDim x(1 To 100000)
    For i = 1 To UBound(x)
        x(i) = i
    Next i
    y2 = WorksheetFunction.Max(x)    'returns 34464
    y3 = WorksheetFunction.Unique(x) 'returns (1 to 34464) array size

End Sub

Outside VBA, in worksheet, array larger than number of rows seems to not work.
For example, =MAX(SEQUENCE(1100000)) returns error.
While =MAX(SEQUENCE(1,1000000)) still works, even the row-array input size is larger than number of columns.

 

[gifariz]

Hello gifariz.
Clearly explain what you want to do because using Excel functions in a macro doesn't always make sense.

I do many things. So I want to have clear understanding of how WorksheetFunctions work and its limitations.
Like, so far, because WorksheetFunction.Max, .Unique, .Sort are unpredictable (e.g. sometime returns incorrect results when array input is one-dimension but large, not sure if this is the reason), I need to make my own function for this tasks, which is burdensome. Mostly I work on zero-based 1d arrays, so converting to one-based 2d arrays for using WorksheetFunctions is additional work, but better than making my own function, only if I can make sure WorksheetFunctions won't return incorrect results by me knowing the rules.

 

[Peter_SSs]

For example, =MAX(SEQUENCE(1100000)) returns error.
While =MAX(SEQUENCE(1,1000000)) still works

But those two arrays vary by number of elements. If you give the second formula the same size array =MAX(SEQUENCE(1,1100000)) then it also returns an error.
I think the issue with these particular examples is not a general limit with worksheet functions but specifically with the SEQUENCE function. It works with 1,048,576 elements (no. of excel rows) but fails with 1,048,577 elements.

 

[gifariz]

But those two arrays vary by number of elements. If you give the second formula the same size array =MAX(SEQUENCE(1,1100000)) then it also returns an error.
I think the issue with these particular examples is not a general limit with worksheet functions but specifically with the SEQUENCE function. It works with 1,048,576 elements (no. of excel rows) but fails with 1,048,577 elements.

I see. So maybe there is no general rule of limitations, at least for my question.

Btw, I was originally asking specially about WorksheetFunctions in VBA, while our discussion extended to functions in sheet.
Okay maybe conclusion is, for WorksheetFunctions in VBA, inputting 1d array need to be avoided due to unknown limitation, while 2d array input has no limit other than memory resource.
Hopefully inputting 2d array WorksheetFunctions will never return incorrect result unlike in 1d array input.

Thank you all.