skip to content »

Screen updating excel

screen updating excel-20

In fact, it pays (in speed) not to mention unnecessary objects, because every object you mention has to be resolved by VBA. Below is an example based on reading in a datafile.

screen updating excel-58screen updating excel-13screen updating excel-80

We cannot access that data once the thread is running and must get the data to to the Do Work process.The only thing to note that initially the Cancel button and progress bar visibility is set to hidden.Here is the code where we launch the Do Work event operation, but more importantly we pass in data which the Do Work can use to perform its operations.There is substantial overhead in communications between worksheets and VBA. So if you have a custom VBA function which you call from hundreds of worksheet cells, it could be s-l-o-w.Equally, if you use VBA to read in lots of cells to memory, or write lots of data back to cells, it can be real slow. We read in 125 records at once (that's 125x20 = 2500 cells) into an array, then looped through and calculated the 14 output cells for each of these records, and stored them in a memory array.Apart from this, you will generally find that it pays to avoid VBA functions in big spreadsheets.

Personally, I try to use either VBA or worksheet formulae, rather than a mixture.

(Don't use this in Excel 8, it is faster to say Range = Array). This can be done because VBA keeps variable values in memory once they are set (unless they are dimensioned within a subroutine or function, in which case they disappear when the function ends) until either you close the worksheet or make any change to the VBA code.

Sub Super Blast Array To Sheet(The Array As Variant, The Range As Range) With The Range. So you can set the variables once and use them forever.

When working in VBA, the trick is to read/write data in chunks. Then we read the next batch of 125 records, and so on, until we hit empty cells.

We had a real life example, where we needed to read in 1000 records off a worksheet, each of 20 cells, do some calculations, and write back 14 cells at the end of each record. So far we've only made 8 (input) hits on the worksheet.

The code method is about 150 times faster (on my PC, anyway).