execution speed comparrision

Originally Posted by jakopak

-list can be array of strings or numbers, non empty range of cells
-array or range is constructed during loop (if condition)
-things to apply: text size or color, number or string manipulation

Generally, it is better to iterate once and do as much as possible in each iteration.

This is especially true if you are modifying cell properties (like formatting).

Each access to an Excel cell or range is very expensive because it involves "interprocess" (really interthread) communication. Not only does the interprocess communication take a lot more time than a VBA function call, for example, but also it involves a process context switch, which allows other processes on the computer to take control of the CPU.

(The context switch issue might be less of a concern with modern multiple-core computers. I still use a single-core single-CPU computer. But the interprocess communication overhead is still an issue.)

For that reason, if you are manipulating cell values, not properties, it is usually best to read a range into a VBA variable, do all the manipulation, then write the VBA variable out to the Excel range. For example:

Dim v As Variant, r As Long, c As Long
v = Range("A1:Z1000")
For r = 1 To 1000: For c = 1 To 26
    .... access v(r,c) ....
Next c, r
Range("A1:Z1000") = v

That said, there are situations where it might be better to iterate on each VBA array separately, namely: if you have very large VBA arrays, each with many thousands of elements that you are accessing sequentially.

In that case, the benefit of iterating each VBA array separately arises from the "locality" of VBA address references. Each time you access a single element in a VBA array, the CPU actually loads many neighboring elements into the cache as well, which takes some amount of time. If the next reference is to a neighboring array element that is already in the cache, it is faster for the CPU to access it.

Usually, I do not lose sleep over "cache optimization". But I have encountered situations where that makes a significant difference in overall performance.