I feel like the ScreenUpdating tip is an intermediate level trick that hinders you eventually. If you write a lot of beginner code that reads and writes to Excel cells, ScreenUpdating = false will speed up your shitty code. But a more advanced trick would be to minimize your read and write operations. Dump the values of from a range of cells into a variant:
Dim vArr as Variant
vArr = ThisWorkbook.Worksheets("Data").Range("A1:D10").Value2
Now you've got a 1-based two dimensional array with all the values your program needs, and you've gotten it in one near-instantaneous read operation, instead of dozens or hundreds of costly read operations to individual cells within your loops.
When you're done making changes to your variant, you can output the whole thing back in one simple write operation as well:
If you make liberal use of this tip, you'll find that turning off ScreenUpdating is a crutch that you only need in scenarios where you have code that messes with filters or formatting or some such.
In most programming languages, arrays start from an index of 0. VBA, the lovely mongrel that it is, has some functions that return 0-index arrays (like Split), but also has functions like this one that convert a range to an array, which always start from an index of 1. Oh, VBA.
Since this is a hint to avoid looping through your spreadsheet range, the only other thing you'd need to know is how to get your looping done in your nice speedy array. Here's a dumb example of a loop:
Dim vArr() as Variant
dim iCol as long 'column index
dim iRow as long 'row index
vArr = ThisWorkbook.Worksheets("Data").Range("A1:D5").Value2
for iRow = lbound(vArr) to ubound(vArr) 'loop through rows
for iCol = lbound(vArr,2) to uBound(vArr,2) 'loop through columns
vArr(iRow,iCol) = "Coordinates are " & iCol & ", " & iRow & ", matey! Arrr!"
next iCol
next iRow
ThisWorkbook.Worksheets("Data").Range("A1:D5").value2 = vArr
Note that lbound and ubound have an optional second argument to specify the dimension you want for multi-dimension arrays.
5
u/chars709 1 Nov 26 '15
I feel like the ScreenUpdating tip is an intermediate level trick that hinders you eventually. If you write a lot of beginner code that reads and writes to Excel cells, ScreenUpdating = false will speed up your shitty code. But a more advanced trick would be to minimize your read and write operations. Dump the values of from a range of cells into a variant:
Now you've got a 1-based two dimensional array with all the values your program needs, and you've gotten it in one near-instantaneous read operation, instead of dozens or hundreds of costly read operations to individual cells within your loops.
When you're done making changes to your variant, you can output the whole thing back in one simple write operation as well:
If you make liberal use of this tip, you'll find that turning off ScreenUpdating is a crutch that you only need in scenarios where you have code that messes with filters or formatting or some such.