0

Pretty much the title. The code goes forward until it hits a line, and then acts as though it ran into a Stop command, except there is no such line. 

StartDate = DateAdd("d", -1 * NDays + 1, EndDate) 'the immediately preceding line
'NDays is an integer, EndDate is a date

With Excel.Application 'the line that it stops on
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlAutomatic
End With 'I put these here because the next lines save the workbook
    'if I put them before the excel.application stuff it asks if I want to save when closing

Why is it stopping on that second line? Does anybody know?

kumquatwhat
  • 315
  • 1
  • 4
  • 12
  • Graham, looking to this code I'm assuming you posted just a part of it? If not, there's a End With missing there... – Paulo Henrique Jun 02 '17 at 13:42
  • Anyway, I think we need the whole code inside the With statement because it's just a wrapping for what's linked to Excel.Application. And maybe explain a little more what's the code does with the newly opened Excel instance and if it waits for any operation concludes in Excel.Application – Paulo Henrique Jun 02 '17 at 13:44
  • I just posted the two problem lines. Absent the weird stopping, the whole thing works great (ie no missing bits), it's just the stopping. I can post more if needed. – kumquatwhat Jun 02 '17 at 13:45
  • By stopping you mean, like a breakpoint in the debugger? – Paulo Henrique Jun 02 '17 at 13:46
  • Yeah. Stop as in the line you put in so you can step through. – kumquatwhat Jun 02 '17 at 13:48
  • Could the project be somehow corrupt? Try exporting and re-importing all modules. – Mathieu Guindon Jun 02 '17 at 15:46
  • It seems to have stopped happening, at least for now. I ran it, probably 4 or 5 times when I posted the question and it did the same thing every time, but running it twice just now and it went past that code with no issue. If it occurs again I'll try that though. – kumquatwhat Jun 02 '17 at 16:01

1 Answers1

0

Try it without the with block and see what happens. So something like:

Application.Screenupdating = True
Application.DisplayAlerts = False
Application.Calculation = xlCalculationAutomatic

I am at a loss for the why behind this (and if anyone knows, please do share), but I have encountered issues with instancing new Applications, and as a result the code exhibits the same behavior where it goes to use the new instance and simply shuts off.

If I had to hazard a guess, I would assume it has something to do with how Excel is managing the VBA processes. It is possible (though I could be entirely mistaken) that the new Application process assumes control, and that this causes the first application to (in layman's terms) assume it is finished, and simply quit. That, or it pauses its own process (like a DoEvents command) but can't resume. Again, these are guesses.

Also, why the 'Excel.Application'? Are you creating a new instance called Excel? If you intend to just use the application the code is running in just us 'Application'.

Brandon Barney
  • 2,382
  • 1
  • 9
  • 18
  • It's going to be hard to test because now it's mysteriously stopped giving me the issue. I ran it several times when I posted the question, and each time it gave me the same problem, but running it twice just now and it went through no issue (if it pops up again, I'll try it and see what happens). As to Excel.Application, I use it because...well, I just always have. There's no reason to put the extra bit in front, I suppose. Does it slow down the code or otherwise potentially cause issues if I do put it there? – kumquatwhat Jun 02 '17 at 15:59
  • To answer your question about if it slows down the code or causes issues...*technically* (and that's a huge technically)...yes. The reason being that the Excel has to be resolved and then it resolves the application. By default, the implicit Application is the Excel application. Should it cause any issues? Probably not. Can it? Anything is possible. I have not seen a single other developer use Excel.Application though (outside of C#) so I would recommend going with Application. This way your code is easier to read and maintain (and potentially avoids these issues). – Brandon Barney Jun 02 '17 at 16:03