Screenupdating false access
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.
I have used several methods when writing simple VBA macros that have turned spreadsheet macros from horribly slow to sometimes very fast.
A few of these things can be applied to COM access.
So before we get to COM Interop, some general tips about dealing with Excel programatically are in order.
For this we will use VBA inside of Excel to demonstrate a couple issues. Suppose you have a sheet in which you have values in column A and column B.
If we run it we would find that in our example it will run in about 3.5 seconds.
Our simple code might look like this It takes a very long time...The simple ways to avoid the first two items above is to turn the updating off near the beginning of your code.This can be done in the example above by adding two simple lines Code with these changes is significantly faster.In our example we noted that the screen updating, calculation, and working with each cell individually adds a tremendous amount of time.Taking that knowledge and applying it when we access Excel will make a huge difference in the time required to run our code.