vba - Which is faster: Array or working on cells in Excel -

I am working on large amounts of data (1.5 mln rows) and I'am wondering if in between Work on noticeable time period rows, and work on the array. What I am doing is going through every line and it compares the string value, sometimes it modifies, imitates etc. I'am is not doing any calculations.

And seccond question: Suppose there are no dates. Cells Value will be much faster than cells 2. Value? Or will it not be remarkable for the user?

Based on a project I have done something closely with the data you are talking about I do not think the difference will be dramatic, but it can try to do a simple test using your data and your computations and can see which approach works best in your specific project.

I suspect that you are doing more computations on the data, then it will be more beneficial; If there is a lot of data with this important search, but some changes, it can be more efficient by leaving it in the worksheet.

In my project (your mileage could be different), I found that the large array back into the cell range can actually become an obstacle, although it was on the older version of Excel, where I think That I was starting to reach a limit of memory and was addressing the capacity. If almost everything had changed in processing data, then you are writing it back in cells from either one or another, it is better to do this as a range but if the changes are few, then you can read from the spreadsheet And instead of writing it back, you can get better value for the array than the spreadsheet, get value and / into the array, and then writing the array back on 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 works better Used to be. Along the way, you may find other solutions to choose a solution or other, and which can be more important than the performance benefits you receive.