Reading large data into Excel using arrays or otherwise
Can someone please give me some pointers with regard to working with large data (say 1 million rows, 200 columns) in Excel 2010?
Having the data on the worksheet from cell A1 onwards, I am trying to read it in memory via an array.
QUESTION 1. What is the difference between these two methods?
Method 1:
dim myarray()
myarray = cells(1,1).currentregion.value
Method 2:
dim myarray
myarray = cells(1,1).currentregion
The second method seems to be able to read "more data" than the first, though both fail if the data is "too big", with an out of memory message.
I have also tried this:
Method 3:
dim rng as range
set rng = thisworkbook.cells(1,1).currentregion
This method seems to be able to read the large data EVERY time and I can retrieve any element like an array, ie:
dim a as integer
a = rng.cells(10000,50) would return a value
QUESTION 2. Why use arrays if Method 3 ALWAYS enables one to read large data?
- dugdugdug's blog
- Login or register to post comments
- 5435 reads
If the exact location of the
If the exact location of the data is known like in your case cells(10000,50), then there is no point using arrays. Array would be useful if you want to perform any calculation on large amount of data. Say, if you have 2 columns and you want to display the concatenated value of 2 columns in 3rd columns then you can use arrays.
Arrays are faster as compared to reading from excel cell or range because array is like any other variable in the memory.