0

Two large VBA Projects that execute every hour of every day. They usually take 55 minutes to complete. They have been working perfectly for the past month or so.

All of a sudden, in the early hours of the morning, VBA suddenly stops executing the code at random points.

The files are opened via VBScript. When it fails, the VBScript is still running, waiting for the Macro to complete. I'm unable to control Excel and I can't open VBA to see where it has got stuck. I can't easily post the code as there is simply too much.

Any tips of things to try to get it working again? This is running on Windows Server 2012 R2.

I've tried restarting the server and I've also tried a Code Cleaner.

Alex Knauth
  • 8,133
  • 2
  • 16
  • 31
DARKOCEAN
  • 109
  • 3
  • 14
  • try to look into some error log on WinServer? – Luboš Suk May 10 '16 at 11:22
  • 2
    Have the macro create a logfile and create periodic calls to write a progress line into the logfile. Once the macro crashes, you'll have a good estimate of what's wrong by looking at the contents of the logfile. Repeat if necessary to narrow it down. – Vegard May 10 '16 at 11:23
  • I don't think WinServer realises that an error is occuring. As far as Windows is concerned, it is running as planned. Creating a log is a good idea though - I will work on this and see what is going on. As mentioned, I have two files that run, on two different user accounts. I believe only 1 is the actual problem, which then causes the second file to stop as well. When the second file runs on its own, it appears to do it perfectly fine. – DARKOCEAN May 10 '16 at 11:49
  • Don't forget to close the fle after each log call. Otherwise some lines may still be in the file buffer so you do not see the latest status in the log file – Paul Ogilvie May 10 '16 at 11:51
  • @MatthewJohnSymons the server may not realise there is an error - but the VBA error handler will. Proper error handling in the code would fix it or at least get it to output the error to a log file for you to see. As it stands there isn't much more that can be said without any code – SierraOscar May 10 '16 at 12:14
  • There are error handlers throughout all of the code. It creates a log of the error and imports it into a DB. However, VBA is not throwing any error - so the error log is therefore empty. VBA just seems to stop. – DARKOCEAN May 10 '16 at 12:32
  • When an automated Office application "just stops" it often means the application is displaying a message and is waiting for user input. This is a major reason why running Office applications in an server environment is not supported. The only way you can tell whether this is what's happening is to make the application instance visible on a monitor. – Cindy Meister May 10 '16 at 16:55
  • When I RDC onto the session, I see no messages on screen? Although it is a server environment, I can see the Excel files on screen when they execute. – DARKOCEAN May 10 '16 at 18:20

1 Answers1

3

This is not uncommon, it seems to happen after a debugging session and for one reason or another BreakPoints are not properly cleared. Solution is:

Press Ctrl+Pause|Break twice.

Credit:

@Stan https://stackoverflow.com/a/5823507/3451115

SlowLearner
  • 3,086
  • 24
  • 54