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
Sim_Loss_1st(Count) = Range("a8").Value
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?