Percentile_Inc function with an internal VBA array

[BFrassek]

Dear experts,
with EXCEL 2010 there is this nice percentile_inc function that gives the k-percentile value of a part of this worksheet.
This works fine - also for a 2-dimensional table, and even the values must not be sorted.

Now, I want to use this function within a VBA program.
A maximum of 160,000 negative values (at least 2000) are calculated and put into an array neg_lambda.
The 0.01-percentile should be calculated and put to cell [OM161]:

Dim neg_lambda(160000) As Integer
Erase neg_lambda
calculation ...
[OM161] = Application.WorksheetFunction.Percentile_Inc(neg_lambda, 0.01)

This code generates a "type mismatch error".

Is it possible to use the percentile function in this way - what is wrong?

Of course, after calculating the values it is possible to store the internal VBA array into a new sheet and then using the "outer" EXCEL function on that sheet.
But perhaps it is possible to use the Application.WorksheetFunction.Percentile_Inc without doing this ...???

Thank you for your help and best regards from
the German EXCEL fan Bernd Frassek

 

[shg]

I think when you use worksheetfunction.xxx with arrays, you're limited to 64K (65536) entries.

 

[BFrassek]

Thank you very much, shg!
You were right with the Limitation. Since I Need the big Array, I will put the internal Array into a sheet and do the percentile calculation outside of VBA ...
Best regards
Bernd Frassek

PS: Since the Problem is solved, must I do anything now, e.g. delete this thread?

 

[shg]

You're welcome.

Nope, nothing else, you're done.