Excel 03 to 07 VBA Execution VERY SLOW...Need Help!!!

[M1lrtym]

This is my first post and I am hoping someone here can help us out as we can't seem to figure this out. Thanks in advance.

Our company is slowly migrating everyone to Office 07. This isn’t a problem for most people but we have some other power Excel users that are experiencing extremely slow performance with 07 when their worksheet has a lot of VBA code. Some of the Excel applications they have put together are very impressive and really take Excel to is limits. The apps have also evolved and become a huge part to some users daily job so the slow performance has a huge effect.

I been doing some testing trying to strip out different things from the sheets or code but still, the performance is almost a 10:1 ratio between 03 and 07. A simple loop that used to be instantaneous could take 10 seconds now and when you have to do that hundred times a day I can see where it would get old. I picked out one of the Excel workbooks that has been most affected and I first stepped through the entire routine/procedure to see if it would get hung in one particular place but it doesn’t. It just seems to be slower all around. This particular workbook I am testing just uses basic loops to read data, do basic calculations and move it to a user interface sheet. There are no graphics or shapes being manipulated other than graphs. There also aren't any ODBC connections being made. All ranges being read are written to arrays before it is spit out to the worksheet. Below are some of the things I have tried with no luck:

- Disabling sheet calculation until macro/procedure completes (minor help)
- Striping out user created VBA functions being called by a worksheet
- Removing object.select methods
- Improving variable declaration
- Sorting the raw data better

We have tested this on multiple machines (older vs. newer) all with just about the same results. I have searched all over Microsoft’s website with very little luck. It looks like 07 definitely caters to the 95% of users that don’t even know what VBA is and maybe they don’t care about the slower performance. All I know is we will have to figure it out or try to talk our IT director into supporting two versions of Excel.

 

[FastExcel]

A major cause of slow VBA in Excel 2007 is that the overhead of selecting, reading or writing an excel cell has gone up by a factor of up to 10 compared to previous versions.
The bypass is:
- do not select
- do not loop on Cells one-by-one
- read cells from a range in large blocks into a variant and then loop on the variant, write back in large blocks

Dim vArr as variant
dim j as long
dim k as long
vArr=Range("A1:Z200").Value2
for j=1 to ubound(varr,1)
for k=1 to ubound(varr,2)
varr(j,k)=varr(j,k)*2
next k
next j
Range("A1:Z200")=vArr