Screenupdating false access

Posted by / 02-Aug-2020 07:30

Screenupdating false access

You want to add them up and put the result in column C.

Granted this is a very simple example that you could argue someone should not use code for but it is an easy way to demonstrate the concept. lets consider a sheet with values in A, B, C and a formula in D. We can do some simple things to avoid some of the Excel overhead.

I wrote an answer to one but thought it would be a good a time as any to document the issue a little better and share a few tips that I have used in the past to speed things up substantially.

If you have written applications that interact with Excel using COM or just written VBA macros inside the provided Visual Basic editor you may have noticed that it can be slow due to overhead involved.

So, this is all great and fine but what does this have to do with accessing Excel from COM?

The overhead associated with COM can be much higher than the method we were using.

To do this we are going to alter the code to only get the values and put them back when we are done.

In our example the messagebox gives odd numbers sometimes. It runs so fast that our timing method sometimes does not record it properly.

Taking that knowledge and applying it when we access Excel will make a huge difference in the time required to run our code.Right now, we are still accessing each cell on each iteration.In order to address it we will need to minimize the interaction between the code we are running and Excel.This is a pretty serious performance change from such a simple addition to the code that it is a very worthwhile thing to do.To make this really fast though, we need to limit the amount of time spent working our way through the Excel objects.

screenupdating false access-29screenupdating false access-1screenupdating false access-58

The simple ways to avoid the first two items above is to turn the updating off near the beginning of your code.