Unorthidox error messages in macros

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

I have a strange problem with Excel. I have a few users
creating macro's that aren't doing anything out of the
ordinary. The layman's terms of the process are: they
will go to a path and open a file, copy it in the
template, close the file, do some formatting to the
template, save the template as something else and go on
to the next one. Some will have 5 or 6 files to open.
Some will only have one. Other will have quite a few.

They run them one time...and Excel bombs with a basic
error. Run it again right away and it works fine. Run
it and it fails after creating a few reports, or
sometimes it will run without a problem at all. It is
completely random. This is not just local to one
machine...any machine that you attempt to run this macro
from results in the same random problems. So my question
is: what could cause such instability in Excel?

1) We are running Office XP with both service packs.
2) Windows XP with SP and all critical updates
 
Gary said:
I have a strange problem with Excel. I have a few users
creating macro's that aren't doing anything out of the
ordinary. The layman's terms of the process are: they
will go to a path and open a file, copy it in the
template, close the file, do some formatting to the
template, save the template as something else and go on
to the next one. Some will have 5 or 6 files to open.
Some will only have one. Other will have quite a few.

They run them one time...and Excel bombs with a basic
error. Run it again right away and it works fine. Run
it and it fails after creating a few reports, or
sometimes it will run without a problem at all. It is
completely random. This is not just local to one
machine...any machine that you attempt to run this macro
from results in the same random problems. So my question
is: what could cause such instability in Excel?

1) We are running Office XP with both service packs.
2) Windows XP with SP and all critical updates
Click to expand...

If you were to tell us

1) What the errors actually are
2) What code is being called at the time

We might be able to help

It sounds as if you are getting run time errors opening
the files which could be because they are locked by
someone else editing them or some other process which
exclusively locks them but without further details ....

Keith
 
Gary,

You can go into the VB editor and step through the code with the F8 key.

Open the VB editor. Open the module. Click the mouse anywhere within
the code. Hit F8 to activate each code line.

If that doesn't isolate the problem area - you can set breaks in your code
by clicking on the left side (grey) border next to some of the lines. Click
the
mouse anywhere within the code. Hit F5 to run the macro. It will run to
the
break and stop. Hit F5 again to run to the next break. Set the breaks
after
each 'major' block of code in your module.

Another thing to do is to put Option Explicit at the top of your modules
(above all the code). Go to the Debug menu and select Compile. Excel
should point out major problems with your code. Repeat this until you
don't get anymore.

Also go to this site and download the code cleaner. It helps clean up your
projects
http://www.appspro.com/
 
Wow, thanks alot Steve! I'm going to go through this
today with this code.

Thanks again

Gary
 
Back
Top