Macro causes Excel to "not responding"

  • Thread starter Thread starter Mick
  • Start date Start date
M

Mick

I have a macro that has been working fine on W98 with Excel 97 SP1. Now
that I have upgraded to Win and Office XP it gets to a point (yet to be
identified) when excel hangs giving the message that excel is not
responding. I have been lead to believe that macros and spreadsheets
written in excel 97 are compatible with excel XP. Can anyone advise please?

Mick
 
Yes, they generally are ( in my experience). Although not the other way
round.

If there was something that didn't actually work in the macro you'd get
a runtime error/

You may be a victim of Windows XP's impatience. It tends to say a
programme is not responding quicker than previous versions.

Or it could be a different environmental setting in your new
configuration that doesn't agree with your macro. When you write one to
deal with a specific circumstance it's difficult to take account of all
other circumstances it may be run in.

Does the workbook that the macro is operating on take a long time to
recalculate?
 
Hi

I have left excel running for several hours and it still ends up not
responding. There doesn't seem to be any delay as far as calculation is
concerned.

I'm actually at a complete loss now, if you or anyone has any further ideas
you will make my day.

Thanks

Mick
 
Since xl ends up not responding, I bet debug.print's would be pretty useless (to
help find where the bottle neck is).

Maybe you could add a routine that serves the same kind of purpose.

Option Explicit
Sub testme01()

Call writeSoFar(where:="here")
'do lots of stuff
Call writeSoFar(where:="overhere")

End Sub

Sub writeSoFar(where As String)

Dim myFileNum As Long
Dim myFileName As String

myFileName = "C:\test.log"

myFileNum = FreeFile
Close #myFileNum

Open myFileName For Append As #myFileNum
Print #myFileNum, Format(Now, "mm/dd/yyyy--hh:mm:ss") & "--" & where
Close #myFileNum

End Sub

Pepper your code with lots of calls to this routine--and pass it a nice unique
string so you can find the problem.

(You do turn calculation to manual, do your stuff, and then set it back to the
way it was??)

It never hurts to empty the temp folder and you may want to take a look at these
two sites (Charles Williams and David McRitchie):

http://www.decisionmodels.com
http://www.mvps.org/dmcritchie/excel/slowresp.htm

Maybe you'll see something that can help.
 
Back
Top