P
PO
Excel 2003, SP2
Hi,
I have a workbook that uses an "Import" procedure to copy a sheet from
another (open) workbook and inserting it into the current workbook. In the
importing workbook there is a chart, the series have to be updated to show
the new worksheet, and also there is some formatting done to the new
imported worksheet (merging cells, lines, bgcolors). Everything works fine -
it takes around 2 sec to import a worksheet.
In the new version of the workbook however updating the chartseries takes
8-10 seconds and doing the simple formatting takes around 5 seconds (I used
a timer to clock each part of the code). The only difference between the new
and the old version is that the new version contains offset formulas (around
50 formulas) instead of text (the offset formulas are used to return the
text in the users native language since the workbook is used by our company
in different countries).
During code execution I have set Screenupdating and Enableevents to false
and I've also set Calculation to manual. This has no effect on the amount of
time it now takes to execute the code.
I suspect the problem has something to do with the offset formulas but I
don't know how to solve this.
Any suggestions?
Regards
Pete
Hi,
I have a workbook that uses an "Import" procedure to copy a sheet from
another (open) workbook and inserting it into the current workbook. In the
importing workbook there is a chart, the series have to be updated to show
the new worksheet, and also there is some formatting done to the new
imported worksheet (merging cells, lines, bgcolors). Everything works fine -
it takes around 2 sec to import a worksheet.
In the new version of the workbook however updating the chartseries takes
8-10 seconds and doing the simple formatting takes around 5 seconds (I used
a timer to clock each part of the code). The only difference between the new
and the old version is that the new version contains offset formulas (around
50 formulas) instead of text (the offset formulas are used to return the
text in the users native language since the workbook is used by our company
in different countries).
During code execution I have set Screenupdating and Enableevents to false
and I've also set Calculation to manual. This has no effect on the amount of
time it now takes to execute the code.
I suspect the problem has something to do with the offset formulas but I
don't know how to solve this.
Any suggestions?
Regards
Pete