Is there a limit to array/variant?

[mailcmc282]

Hi,

I have code that is supposed to call the first column in an array, then the second, etc. It works unless the length of the array is > 80,000 (or approx that value). Is there a limit to how large the column length in an array can be? I cannot find anything online about this. If this is the case any help would be appreciate.

dim output_dta () as double
dim statrangeds_ as variant

statsrangeds_ = Application.Index(output_dta, 0, i)

Thank,
CMC

 

[Jerry Sullivan]

Hi CMC and Welcome,

The limitation you are hitting isn't with the Array size or Variant type,
rather it is a limitation of Application.Index()

I encountered a similar problem a few months ago trying to apply Application.Min() to a large range.

Through trial and error I found that it would generate an error when the range had more than 65,536 rows.

That is also the maximum number of Rows in a worksheet prior to xl2007 which probably isn't a coincidence.

A simpler way to accomplish your intent that doesn't have this limitation is to replace:

statsrangeds_ = Application.Index(output_dta, 0, i)

With this...

statsrangeds_ = output_dta(0, i)

Hope this helps!