Thread: Data Viewer now runs very slow using Excel 2007

Hi.
I have a Data Viewer written using VBA and Excel. Basically, it reads a binary file and presents the data in rows and columns in worksheets. I developed it using Excel 2003, but we've recently upgraded to Excel 2007 and it now runs as fast as a lame dog.

I've performed various tests to find the root cause of the problem and have found that reading and manipulating the data takes no time at all, but writing the data into individual cells is what is causing the massive slow down. I've looked online for solutions to this problem, but all I've come across is information stating that operations like writing to cells directly now seem to take up to 20x longer in Excel 2007 than in 2003.

To give an example of my issue, a data file that contained 90 days worth of data used to load in about 2 minutes - now it takes nearly 30 minutes which is making it pretty much unuseable. I've seen comments that say try and process blocks of data rather than individual cells/rows/columns, but I'm populating the cells, so I need to write to each one individually.

Does anyone have any ideas how I could get around this problem as I can't think of anything, but I'm by no means an expert user of Excel or VBA.

Cheers.

Originally Posted by neilelph

but I'm populating the cells, so I need to write to each one individually.

Can you clarify what you mean by that?

I assume you are already turning off calculation and screenupdating when writing to the sheet?

Are you actually selecting the cells in the code?

To try and answer both posts above:

I am turning off calculation and screen updating using the below bit of code:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

The way I write to cells is as below:

For k = 1 To numSheets
  .......
  .......
  For i = 1 To sheetsWorth
   ......
   ......

                'Sample Number
                ActiveCell.Value = ((k - 1) * recordsPerSheet) + i
       
                For j = 2 To 6
                    ActiveCell.Offset(0, j + 2).Value = instruments(j - 2)
                Next j
                For j = 7 To (numInstruments + 1)
                    ActiveCell.Offset(0, j + 3).Value = instruments(j - 2)
                Next j
                
                'Point to next row
                ActiveCell.Offset(1, 0).Select
  Next i
Next k

The above section of code is within a larger loop that populates the instuments() array before writing the values to the worksheet at the end of the loop as shown.

Please ask if you need more info.
Cheers.

Selecting cells is unnecessary and inefficient so you should avoid that - just write to the cell directly using an address or row and column number.
It would also be faster to put the values into an array and then write that array to the worksheet in one go. We'd need to see the full code for that and, if possible, a sample workbook.

Note: please use code tags whenever you post code. I've added them for you on this occasion.

OK. I'll take a look later on if no-one beats me to it.

Bit rough and ready, but see if this is faster.

Glad to help. Please don't forget to mark the thread 'Solved'.