macro timing issue

[Terry P]

We use a series of large workbooks to simulate business performance over
time. These workbooks and VBA code have perfomed well, i.e. 2-4 seconds per
time iteration, using Excel 2003. Running the system using Excel 2007 on a
new, high powered, dual core PC, using Vista takes 6-7 minutes per time
iteration. On an XP machine it's taking 10-11 minutes.

Looking where time is lost throughout our systems, we've come upon a simple
piece of code that takes 15 seconds in 2007 vs. <1 second in 2003
(essentially instantly). This same Procedure placed in a new workbook
running in 2007 also runs instantly, however not so in the full production
system.

Enabled Events is set to True, Screenupdating is False, Application
Calculation is xlCalculationManual, ActiveWorksheet.EnableCalculation is
False (these are all newly added to the code, not required in Excel 2003)

The problem occurs in a For loop with 130 insertions of data into specific
cells of an activeworksheet. This worksheet has no links to any other
worksheet:

For i = 1 to 13
For j = 1 to 10

( 'tvalue' and 'target_column' (below) are simple functions of i and
j )

ActiveSheet.Cells(1,target_column).Value = tvalue '<<<< DATA INSERTION

Next j
Next i

The entire delay takes place in the data insertion statement; also, 'tvalue'
can be set to "1" with no change in response.

I'd appreciate any thoughts about what we're missing. Solving this typical
type of code problem is necessary for us to continue with Excel 2007 at this
time. Many thanks...

 

[The Dude]

Hello Terry,

I doubt that it comes from the code as it performs well on another computer
with 2007.
Have you checked the antivirus? or any realtime protection software?
I had that problem once with a proactive protection...

Other than that I don't see. I guess you have already tried to close the PC
and restart it.

 

[Terry P]

Thanks for your reply. Let me clarify...

The problem occurs on ALL our Office 2007 computers when running our
application - a 12MB main module and a series of linked workbooks. We have
turned off the Norton AntiVirus with no change. Restarts have made no
difference.

We have extracted the same code and only this code and installed it in a new
workbook made with Office 2007 as .xls. In this test case the code segment
ran with no delay.

There's something operating within the full application code and sheets of
our system - which runs fine using Office 2003 - that Office 2007 doesn't
handle correctly (the function of simply inserting data in an unlinked
worksheet - storing the data - no save involved). We've spent days, now
weeks looking at different Excel Options to find how this could happen: no
luck. Even though we've supposedly prevented recalculation, the target
worksheet appears to be undergoing some kind checking or calculation. We've
also tried inserting a new worksheet, given it a different name from the
original target sheet, changed the code accordingly, and get the same result.


Unfortunately, this is only one of many problems we see with Office 2007.

Again, any hunch is appreciated.

Thanks to "The Dude"...

 

[atledreier]

Thanks for your reply. Let me clarify...

The problem occurs on ALL our Office 2007 computers when running our
application - a 12MB main module and a series of linked workbooks. We have
turned off the Norton AntiVirus with no change. Restarts have made no
difference.

We have extracted the same code and only this code and installed it in a new
workbook made with Office 2007 as .xls. In this test case the code segment
ran with no delay.

There's something operating within the full application code and sheets of
our system - which runs fine using Office 2003 - that Office 2007 doesn't
handle correctly (the function of simply inserting data in an unlinked
worksheet - storing the data - no save involved). We've spent days, now
weeks looking at different Excel Options to find how this could happen: no
luck. Even though we've supposedly prevented recalculation, the target
worksheet appears to be undergoing some kind checking or calculation. We've
also tried inserting a new worksheet, given it a different name from the
original target sheet, changed the code accordingly, and get the same result.

Unfortunately, this is only one of many problems we see with Office 2007.

Again, any hunch is appreciated.

Thanks to "The Dude"...
--
Terry Priebe
Decision Support Associates, Inc
DeSA








- Show quoted text -

I had this on an XL97 system once. There was some code running on the
sheet that ran everytime the sheet updated. try enablevents.false and
see what happens...

 

[The Dude]

atledreier made a very good suggestion.

I guess the links must be updated in your new workbook? if not then I
suggest to change the 'Workbook.UpdateLinks' setting (or open the workbook
from a macro with a False attribute to [update links]).

You could also try to add 'Workbook.UpdateRemoteReferences = False' before
the code and '...= True' after the code so that it does not update the links
while running.

 

[Terry P]

Hello and sorry it took so long getting back. Thanks for the idea but
unfortunately it didn't solve the problem. I have tried similar "logical"
commands, and none have worked.

Please see my note responding to 'the dude' today.

Best Regards...

 

[Terry P]

Hi and appreciaste your ideas. Unfortunately they didn't solve the problem.
I also thought of the linking issue so I used Excel Options to disable
linking. This failed.

We then disabled every Excel Option in 2007 that somehow could be related.
This failed.

I Copied and Pasted Values over all the linked Worksheets. That failed.

What we've found is that running our application in Office 2007 now requires
0.12 seconds to place a value on any Worksheet.... i.e.
targetsheet.cell.value=x

Okay... what's left. atledreier mentioned some unexpected issues with XL97.
I also remember there were some Worksheets I had at that time when moved
into XL97 contained some hidden characteristics XL97 didn't like - requiring
rebuilding of those sheets. So what we plan to do - pending other ideas - is
to begin to remove older worksheets one section at a time, resave the
remaining system, open again and running a smaple code until we find the
solution.

Again, thanks for your help.

Best Regards, Terry




--
Terry Priebe
Decision Support Associates, Inc
DeSA

atledreier made a very good suggestion.

I guess the links must be updated in your new workbook? if not then I
suggest to change the 'Workbook.UpdateLinks' setting (or open the workbook
from a macro with a False attribute to [update links]).

You could also try to add 'Workbook.UpdateRemoteReferences = False' before
the code and '...= True' after the code so that it does not update the links
while running.

I had this on an XL97 system once. There was some code running on the
sheet that ran everytime the sheet updated. try enablevents.false and
see what happens...