Geek asks and answers

WorksheetFunction array size limit

I am trying to calculate 99.5% percentile for a dataset of 100,000 values ​​in an array (arr1) in VBA using the percentile function as follows:

Pctile = Application.WorksheetFunction.Percentile(arr1, 0.995)
Pctile = Application.WorksheetFunction.Percentile_Inc(arr1, 0.995)

Nothing works, and I keep getting type mismatch (13).

The code works fine if I limit the size of the array to a maximum of 65536. As far as I know, the calculation is limited by available memory since Excel 2007, the size of the array when switching to a macro limited by available memory with Excel 2000.

I am using Excel 2010 on a high performance server. Can anyone confirm this problem? Assuming this, I believe that my parameters are to build the vba function to calculate the percentile "manually" or output to the worksheet, count it and read it. Are there any alternatives and what will be faster?

+3
source share
2 answers

The error occurs if arr1 is one-dimensional and has more than 65536 elements (see Charles's answer in Array size limits that pass array arguments to VBA ). Dimension arr1 as a two-dimensional array with one column:

Dim arr1(1 to 100000, 1 to 1)

This works in Excel 2013. Based on Charles's answer, it looks like it will work with Excel 2010.

+1
source

VBA, Excel Percentile.

(Excel-VBA)

. :

, QuickSelect, , .

:

+1

More articles:


All Articles