Macro array maximum?

Posted by George on July 27, 2001 7:13 AM

I'm writing a macro that has an array, which is filled using a loop. At the end of the loop, I want to find the 10th percentile of the items in the array.

It works just fine when I use the following code:

Dim Sim_Loss_1st(1 To 5000) As Single
For Count = 1 To 5000
Application.StatusBar = Count
Calculate
Sim_Loss_1st(Count) = Range("a8").Value
Next Count
Application.WorksheetFunction.Percentile(Sim_Loss_1st, 0.1)

However, if instead of having 5000 items in the array, I have 6000,then when it gets to the last line of the code, it crashes and gives me a "Run time error 13; Type mismatch" error.

Is there some limit to the number of items in an array when you use a worksheet function in a macro? Is there any easy way around this -- I need to be able to run with at least 10,000 items, and preferably up to 100,000?

Thanks.

Posted by Dax on July 27, 2001 9:25 AM

It's not the array that's causing your problems but the size of the array that the Percentile function can handle. In Excel 97 (which I presume you're using) the limit is 5461 items and in Excel 2000 the limit is 8191 items. You'd need a custom function for anything bigger.

Regards,
Dax.

Posted by George on July 27, 2001 10:38 AM

Dax,
Thanks, I would never have thought that the limit is with the worksheet function, but, of course, when I checked the on-line help, it confirmed what you said.

Now, here's the strange part:
1) I'm using Excel 2000, not 97, so 6,000 data points should work.
2) I could have sworn that I've used the percentile function in a worksheet with more than 8191 elements, so I tried it: I filled in the numbers from 1 to 12,000 in the first column of a sheet, and used the percentile function -- it worked perfectly, contrary to what the help screen would have one believe.

Do you have any other suggestions, or do I need to treat this as a bug in the program?

Thanks!

Posted by George on July 27, 2001 10:53 AM

Just 1 further note: I tried running it with 5461 items, and it worked; with 5462 it crashed. So apparently the Excel 97 limit applies VBA, but the (supposed) Excel 2000 limit can be exceeded in spreadsheets!