VBA Array size limited to 65536 also in Excel 2010????

[okerikioki]

I have created a script in vba that gathers quite a lot of data in an Array. I want to be able to calculate the average and the percentiles of the outcome of the data.

It seems to be possible to write data to the array up to 65536 rows. When I extend the data to 65537 rows I get a type mismatch error.

I am using Excel 2010, and tested that I can calculate an average of 100.000 rows in a spreadsheet. But I cannot write 100.000 rows to an array and calculate the average on the array.

This does not make sense...

Anybody have a way around this (and still being able to calculate average / percentiles of the total data set), preferable without writing all the data to a worksheet?

[JieJenn]

VBA array size is limited to 65535 when the file version is prior 2007 (file type end with xls). If you're working on a pre-2007 excel file in excel 2010 environment, yes, the array will still imited to 65535 rows.

[rockclimber510]

This just means that your default save form is .xls. You can change your default in Excel Options under the Save tab. Just change it to the .xlsx format, at you won't have the problem anymore in the future.

For your current file, do a save as .xlsx format. Close the file, then reopen it, and you will then have access to all of rows you need.

[okerikioki]

I do not think that this is the problem. My current file format is .XLSM (saving in .XLSX will not keep the vba code) and I still have the problem.

As said, I can access more than 65536 rows in my spreadsheet! BUT I cannot do that in an array, that just exists in vba...

[shg]

You can create arrays larger than 64K, but you can't apply worksheet functions (Like Average) to them.

[okerikioki]

Ok, but is there a way around this?

It seems kind of strange that the number of rows you can do these calculations on in a workbook extends over 65k, but in an array in VBA this is limited to 65k...

Did they forget to address this in the 2010 version?

[Whizbang]

Would using EVALUATE get around this?

[111StepsAhead]

If you don't mind me asking what kind of data are you working with? It's my opinion that if you need more than 65k rows you are collecting the data wrong/inefficiently.

[Paul]

I suppose one way around the limitation is to create multiple arrays each having, for example, 65000 records, sum each array, then sum those subtotals, and finally divide by the total number of entries in all array's combined.

[PingPing]

If you're not going to be doing complex calculations (which seems to be the case) then you should explore using SQL (with ADO) instead.
With large data sets, SUMming, taking AVERAGEs, ORDERing (ie. sorting) and GROUPing (eg. into quartiles) can be performed much more efficiently in a database with SQL, than using arrays in VBA.
I don't know how much SQL you know, but from what you've described, the SQL queries to generate what you want are quite simple.

[MaczaQ]

did you consider to use refering to range whilst to refer to array ?

Check below test, I hope you will find it useful for your needs

Please Login or Register  to view this content.

in atteched file is example - result display to immediate window

[GaryAC]

I know this is an old thread but nobody appears to have addressed the real issue. An type mismatch error isn't an error about the size of the array it about the type of data you are putting in the array. If you dimension it as long and you are entering a number greater than a long data type can handle you will get that error. You may need to dimension the array as double.