0

I am working on big amount of data (1.5 mln rows) and I'am wondering if there will be noticeable time span between working on rows, and working on array. What i am doing is going through every row and compare string values, sometimes modify it, copying etc. I'am not doing any calculations.

And seccond question: Let say, that there is no Dates. cells.Value2 will be much faster than cells.Value? Or this will not be noticable to user?

titol
  • 999
  • 13
  • 25
  • It will vary somewhat, but I believe `Value2` is generally reckoned to be around 20% faster. Using an array should be a lot faster than reading and writing individual cells, though there will be memory implications with that much data (I assume multiple sheets). – Rory Mar 16 '15 at 13:23
  • 1
    Benchmark it and find out for yourself. http://stackoverflow.com/q/7103552/3198973 – RubberDuck Mar 16 '15 at 13:33
  • Seems like this question could be self-answered by trial & error or some debugging. See the links in comments above :) – David Zemens Mar 16 '15 at 13:49
  • I now, that i could do test by myself, and I will probably do this. I asked a question, because i thought that maybe someone have a best way to do this. I am new to excel macros, my work before always was done on arrays or lists in c#. – titol Mar 16 '15 at 19:23

1 Answers1

0

Based on the one project I've undertaken with something approaching the amount of data that you're talking about, I don't think the difference will be dramatic, but it may be worth trying a simple test using YOUR data and YOUR computations and see which approach works best in your specific project.

I suspect that the more computations you're performing on the data, the more advantageous the array would be; if it's a lot of data with significant searching but few changes, leaving it in the worksheet(s) may well be more more efficient.

In my project (your mileage may vary), I found that putting a huge array back into the range of cells could actually become a bottleneck, although this was on an older version of Excel where I think I was starting to approach a limit of memory and addressing capability. If the processing had changed nearly everything in the data, you're writing it back to the cells one way or another, so better to do so as a Range. But if the changes are few, you may be better off reading from the spreadsheet and writing back to it, rather than spreadsheet to array, get and put values from/to the array, and then writing the array back to the spreadsheet.

But at the end of the day, my experience may be different from yours, and the best answer is to do a test, even a simple test, on your data, and see what runs better. Along the way, you may find other reasons to prefer one solution or the other, and those may end up being more important than any performance benefit you derive.

Ralph J
  • 478
  • 10
  • 18
  • Thanks. I now, that i could do test by myself, and I will probably do this. I asked a question, because i thought that maybe someone have a best way to do this. I am new to excel macros, my work before always was done on arrays or lists in c#. – titol Mar 16 '15 at 19:23