0

I'm running a program that saves an excel file, generates a new workbook and then saves it and prints it out. The code runs pretty smoothly, but for some reason I'm having random errors come up. When I say random, I mean VBA stops my program from running, asks me if it wants to debug, and, when I debug, it highlights the line that needs to be corrected.

This is normal behavior, but the line usually won't proceed until the line has been fixed or changed. However, without changing anything in my code and pressing the "play" button straight away, my code works fine again.

This occurs twice: once at the end of a with command:

With newbook
    .Title = newName
    .Worksheets("Sheet1").Name = "MAIN SHEET"
    .Worksheets("Sheet2").Name = "FORMULA SHEET"
    .SaveAs (newName)
End With

And then it occurs with the line:

ActiveSheet.PrintOut

Both of these lines work fine without changing anything and just pressing play.

Is there any way to make these stops in the code not appear?

Thanks for any help!

Hugo Bassas
  • 19
  • 3
  • Did you set a flag to stop at those lines? – findwindow Jun 16 '16 at 18:55
  • 1
    @findwindow you mean a *breakpoint*? :p – Mathieu Guindon Jun 16 '16 at 18:56
  • no I do not have any flags. It's supposed to just run on its own and not stop. – Hugo Bassas Jun 16 '16 at 18:57
  • 1
    @Mat'sMug I don't need to know what it's called if I have you. – findwindow Jun 16 '16 at 18:57
  • If you didn't set any breakpoints, then we need to see more code... – findwindow Jun 16 '16 at 18:59
  • 1
    The problem is that the errors are pretty random: sometimes they stop the program, sometimes they don't. And this is really without any change to the program – Hugo Bassas Jun 16 '16 at 19:03
  • 2
    Among other things, VBA also tells you the error code and description. That would have been helpful. –  Jun 16 '16 at 19:08
  • Sometimes that happens to me, and pressing `CTRL+BREAK` twice allows me to continue without errors. Alternatively, I've heard turning on Debug Mode and off works too. See [this thread](http://stackoverflow.com/questions/2154699/excel-vba-app-stops-spontaneously-with-message-code-execution-has-been-halted) for more ideas. – BruceWayne Jun 16 '16 at 19:19
  • If that's error 91 *object variable not set*, then there's no `ActiveSheet` to `PrintOut` and your code needs to make sure there's an active sheet before it tries to work with, well, the active sheet. – Mathieu Guindon Jun 16 '16 at 19:19
  • 1
    That said `ActiveSheet.PrintOut` is not even in the code you provided. Please [edit] your question to include enough context. – Mathieu Guindon Jun 16 '16 at 19:20

1 Answers1

1

I suppose it's possible the workbook is not fully formed at the time you're tryingto print/save.

Try introducing a delay before the print/save, something like:

.Worksheets("Sheet2").Name = "FORMULA SHEET"

DoEvents

Sleep 500

DoEvents

.SaveAs (newName)

Will BeDeleted
  • 87
  • 2