Start Screen updating excel

Screen updating excel

The first thing we do is darken the button which launched the process so the user doesn’t click it twice.

Here is our event which handles the moving of the progress bar.

This is done on the GUI thread so no need to do checking of the dispatch.

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.

The data here is special because it all resides on gui controls.

We don’t want to access that data directly from our worker thread otherwise we will get this exception as reported in my previous article (C# WPF: Linq Fails in Background Worker Do Work Event ) : The calling thread cannot access this object because a different thread owns it.

In addition, if your spreadsheet loads slowly because of initialisation routines, you might consider showing a splash screen (say, a textbox attractively labelled with the name of your program, etc) for a few seconds.

this article shows how to do GUI processing and background work in WPF using C# with an eye on progress and the ability to cancel.

(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.

The only thing to note that initially the Cancel button and progress bar visibility is set to hidden.