StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Agree. </p> <p>I have found that when you turn off <code>ScreenUpdating</code>, <code>Calculation</code>, it's best to think about how to do as much work (writes,reads,events,...) as possible for as few <code>ScreenUpdating</code> calls in return. This will speed up operations while also providing the user with a better and more tolerable experience. Say, for example that you want to write some data to a sheet as fast as possible. You could do this:</p> <pre><code>For Each row In rowDic.Keys() ' turn off updating for item in rowDic.Key(row) ... do some writes Next ' turn on updating Next </code></pre> <p>or to go faster you could do this:</p> <pre><code>' turn off updating For Each row In rowDic.Keys() for item in rowDic.Key(row) ... do some writes Next Next ' turn on updating </code></pre> <p>Similarly, when writing data, it's quickest to write larger chunks, fewer times. so the ideal number of writes, if any, is one. You can do this by treating a <code>Range</code>as a 2D <code>array[rows,cols]</code>. I have found the following to be effective: </p> <pre><code>' turn off updates ' Organise data in ram so that it fits the range for which it is meant Dim two_d_arr (rows,cols) loadDataFromSource two_d_arr Dim destinationRange as Range destinationRange = Sheets(someSheet).Range(someRange).Value = two_d_arr Redim two_d_arr(0,0) ' !!! RELEASE MEMORY ' turn on updates </code></pre> <p>Here, there are no loops, this optimises each individual task's time in the CPU which results in quicker processing times and in turn seems to make excel work normally (not crash). </p> <p>HTH,</p> <p>F </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload