Array limit

[confusion123]

I read the contents of some data into an array using:

Dim MyArray() As Variant

MyArray = Cells(1, 1).CurrentRegion.Value

which usually works, unless the data is too "big", which results in the message:

Run-time error '7': Out of memory

Does anyone know what the limit is, ie number of rows / cols before I see this message?

Also, how can I get round this?

I'm using Excel 2010 32 bit, so is limited to 4GB of Ram. Would a 64 bit Excel help?

Thanks

 

[RoryA]

There isn't a fixed limit - it depends on how much memory you have available. 64bit Excel should help, although I'm always a little surprised if people actually need that much data in the array - do you need all the columns?

 

[confusion123]

There isn't a fixed limit - it depends on how much memory you have available. 64bit Excel should help, although I'm always a little surprised if people actually need that much data in the array - do you need all the columns?

My stakeholders haven't a clue.

At present, my data is 80,000 rows and 300 columns!

 

[RoryA]

But why do you need all of that in an array?

 

[confusion123]

Let's assume the cols are HousePrice, Metric1, Metric2, ..... , Metric299

If one of the requirements were to find those where HousePrice > £1m, I would need to apply a filter to column 1 but also return the results of the remaining columns.

I could use autofilter but it's slow.

 

[RoryA]

You should store the data in a separate file (preferably a database) and query it, rather than loading the whole lot into memory.

 

[confusion123]

Thanks.

I've done that in the past using ADO, grabbing the data either stored as a csv on the network or saved in Access / SQL.

 

[confusion123]

You should store the data in a separate file (preferably a database) and query it, rather than loading the whole lot into memory.

Access can only hold 255 fields.

Would SQL Server help?

 

[RoryA]

I think you should rearrange your data so you have a metric type in one field rather than separate fields for each metric.

 

[confusion123]

I think you should rearrange your data so you have a metric type in one field rather than separate fields for each metric.

Afraid it's not up to me to change the data.

Also, I've just noticed using ADO to bring back the data, "only" brings back the first 255 fields.

I have 300 fileds and the rest is truncated. Is that true or have I done something wrong?