Excel 2007 - Slow & can't run macros

[Roger]

I have been running an involved spreadsheer for a number of years. Couple of
macros (not automatic) and lots of formula. Ran with few if any problems
under Excel 97 and 2003.
I am trying to run the same spreadsheet in 2007 and it just doesn't work.
I have gone to Trust Center and enabled macros ( The wording could lead one
to believe that Excel should not have macros)
Open the original 4MB .xls file
If I try and save it as xlsx I get warning about not being able to save in
macro-free file because of the VB project.
I try to save as macro-enabled xlsxm
When I re-open the xlsxm file every task such as row incert or cell copy
takes over a minute to complete.
I note that this xlsxm file is only 800k so do not know what happened to the
3.2MB worth of information.

How can I run macros under 2007?

 

[Roger]

I have no idea what happened. I just kept trying to open and save the file. I
did view the macros but made no changes.
It will not run and I can make entries.
However the Macro seen below takes over a minute to run so I still am having
problems.
Macro recorded 11/20/99 by Roger
'
'
Range("A8:s168").Select
Selection.Sort Key1:=Range("d8"), Order1:=xlDescending,
Key2:=Range("e8") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
ActiveWindow.ScrollRow = 5
Range("a1").Select
End Sub

I have been running an involved spreadsheer for a number of years. Couple of
macros (not automatic) and lots of formula. Ran with few if any problems

see original post

 

[Shane Devenshire]

Hi,

You have created a new file type xlsxm you should be using xlsm (four
digit last name.)

Then close the file and reopen it, below the ribbon you should see the
Security Bar, click Options, and choose Enable this content...

 

[Shane Devenshire]

Hi,

There really is nothing in your code that should take a long time to run. I
have modified your code a little. If you have a title row then change
xlGuess to xlYes.

Range("A8:s168").Sort Key1:=Range("d8"), Order1:=xlDescending,
Key2:=Range("e8") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Range("A1").Select

 

[Roger]

Sorry for typo.
Yes I am working in xlsm type file.

Example
Saved & closed the file.
Started Excel
Opened the file
Clicked a single row and hit delete
Did nothing else
1:35 to delete the row.

 

[JLatham]

Make a copy of your original Excel 2003 .xls file.

How does that copy perform when opened in Excel 2007 and is running in
'Compatibility Mode'?

I suggest this just as a benchmark between the two files (.xls and .xlsm).
If the times are significantly different, then the attempt to convert it to
2007 format may have corrupted it.

If the times are similar, it may be that the added time to complete is being
caused by Excel 2007 recalculating the other formulas in the workbook - in
which case there may not be much to be done to help other than trying to make
the workbook more efficient. There may be help/guidance in doing that at
www.decisionmodels.com and other sites. Be sure you work with a copy of your
workbook in making any such changes so that if bad things happen you have an
easy road to recovery.

 

[Roger]

Thanks for suggestion and reply.

The spreadsheet tracks finances so I have a saved copy for every month.
Went back to pre-2007 Office install and opened a workbook from Nov 08
Opened Excel, opened the 2003 xlx file and saved as XLS, closed Excel
Opened Excel, opened the 2003 xlx file and saved as XLSM, closed Excel
Opened Excel, opened the 2003 xlx file and saved as XLSX, closed Excel
Opened Excel and in an empty cell typed = then went to a different
worksheet in this workbook, selected a cell and hit enter.
Attempted to do this extremely simple "calculation" for each file format. In
each case I gave up after 1:15 wait.
(Comment when you do this, are we supposed to see the formula appear while
still at the sheet I am copying the cell from?)

After waiting over a minute I attempted to shut the program down. In each
case the program locked up so it took a forced close in each case.

After doing this I attempted to open IE to return to this site but now my PC
was running very slow. Did a re-boot. Had problems starting after re-boot.
Did second re-boot and now the PC seems back to normal.

If I cna't get this "Improved" version of Excell to work I will probably do
complete un-install and offer the license to some unsuspecting fool.

 

[JLatham]

To respond to your (Comment... ) first, yes, you should see the formula being
built up in the formula bar as you go from sheet-to-sheet and cell-to-cell.

Now for the next step - which due to the nature of your workbook(s) you may
not be able to take; you might give consideration to submitting it through
this page:
http://blogs.msdn.com/excel/archive/2009/01/06/vba-excel-performance-your-feedback-requested.aspx
Now, while they're specifically looking into performance issues with 2007
workbooks using VBA, they may take an interest in yours or point you in
another direction. While it may not solve your problem, it may help improve
the product in the long run. To be honest and frank about it, performance
issues of one type or another have been a topic of discussion at this board
and many others since about day 2 of the release of 2007 - right after people
got over grumbling about the ribbon interface. If you do go this route, at
least you will be able to say "well, I tried to help where I could".

As for uninstalling and reverting back to an older version of Excel, that's
your call and I know people that have done it and I'm sure who ever you
donate the copy of 2007 to may actually probably appreciate it as it does
perform well for many people. Although you might want to reconsider - as it
would be the basis for an upgrade in the future rather than having to
purchase a full copy when the time comes that performance does move up to
your definition of acceptable.

One last question and I'll let it go - are these workbooks doing any
charting or graphing with the data? That is definitely one area that Excel
2007 fails to meet expectations of most and is measurably less responsive
than earlier versions. And if you've got that going on, the slowdown in that
area would definitely affect the perceived responsiveness in all others.

 

[Roger]

Went to the link. Actually got a reply from a human being at MS. Followed the
instructions. Results of the "Fix"
Current performance of Excel 2007 under WinXP 2GB Ram 1.8Ghz cpu
vs Excel 2003 under Vista 2GB ram 1.8 Ghz cpu

In both cases I had the file on my desktop and no other programs were running

Open the file by dble click

Excel 07 22 sec
Excel 03 11 sec including click on warning to enable macros

Change the value of a single cell to 566
Worksheet FID IRS
Cell G12

Excel 03 <1 sec
Excel 07 Gave up after two minutes.
Attempted to close Excell which caused crash of program


Bottom line Excell 2007 appears not to be out of Beta.

 

[JLatham]

Said by Roger "Bottom line Excell 2007 appears not to be out of Beta."

ROFLMAO!!