What is quicker in VBA: Cells(1,1) or Range(A1)

[jimojimo]

Hi all,
In general, if I had a bit of vba code that was looping through many rows and columns, and I was both reading and writing *single cells*, is it quicker to refer to the cell via reading the Cells() or the Range() properties?
Thanks,
--Jim

[romperstomper]

I am not aware of a speed difference between the two properties per se. You would be better off reading the whole range into an array and looping through that though. It may also be marignally quicker to use Cells if you are using row and column numbers, rather than having to do string concatenation, but I doubt it would be noticeable.

[broro183]

hi all,

I agree with RomperStomper's suggestion of reading to an array(s) rather than looping cell by cell. When looking at the "bigger picture", would it be possible to complete your task using Excel's "native functionality"?
If so, you may find some types of Excel's "native functionality" are faster, examples include autofilter, advanced filter, formulae or even Find & Replace.


At risk of hijackiing the thread... Personally, when reading to an array, I'm most likely to use ".value2" based on my interpretation of comments on Charles William's site (Note, I haven't done my own testing!). Others interpret the comments differently & relate it (".value2" vs ".value") to the type of information being referenced. Edit: RomperStomper, what are your thoughts?

hth
Rob

[shg]

Value2 just avoids the conversion of cells formatted as Dates and Currency to those VBA variable types, and instead just passes them verbatim as Doubles.

[jimojimo]

Thanks everyone very much. I am using an array for one of the sets of values I read, while another value is just a row-by-row single cell i need so I just read it once as I loop through the row. But the writing part is where most of the time is spent so I thought I'd try and shave a few seconds.
--Jim

[broro183]

Thanks Shg
Can we take that (no conversion) as providing potential for slight improvement?
(perhaps in the same "doubtful of being noticeable" realms as the string conversion in "range(..."?)

Jim,
How long is your code currently taking?
If you are able to upload a stripped down sample file with layout & your current code, someone (I'm off to bed now) may be able to make suggestions to help speed it up.
Here are some general suggestions, although, without seeing the code I don't know if you are already using them already:
- turn off screenupdating
- turn off calculation (if interim results aren't relied on)
- remove any ".select" or ".activate" statements & act directly on the objects.
- etc...
-turn the calculation & screenupdating on (in this order) at the end of the macro.

hth
Rob

[jimojimo]

Broro,
Thanks very much, wohoo!

It was the calculation that did it. I should have noticed that, but I'm relatively new to Excel. The screenupdate is a minor thing, but that will help some. But toggling the calc in the procedure was the magic bullet--it was a 20 minute process and now it's 10 seconds. Most of the cells in the sheet have formulas so it was adding up to a big delay...doing a calc every time the program wrote to a cell.

Thanks again,
--Jim

[broro183]

hi Jim,

Thanks for the feedback - I'm pleased we could help
If you're happy with the result can you please Edit your first post to mark the thread as Solved?
(see the FAQ for instructions)

If you are interested in learning more about how to optimise your coding read this thread:
http://www.excelforum.com/excel-prog...id-in-vba.html

hth
Rob