VBA Runs a Fixed Number of Lines of Code and then Stops

[dflak]

First of all this only happens with this code if the code is being run from the windows task scheduler - I am not sure what that has to do with it, but it might be relevant. Here is a related post: https://www.excelforum.com/excel-pro...scheduler.html. I can run this code in real time and it runs without error to completion.

In the code below, there are two DoEvents with the comment "Added just because." These lines of code are highlighted in green. According to the log, when these lines are included the code stops on

Please Login or Register  to view this content.

If I remove these two lines, the code stops on

Please Login or Register  to view this content.

Which is exactly two non-comment instructions later.

In other words, the code appears to execute a fixed number of steps and then stops. No error message. Excel is still running, but using no CPU.

Just for grins, I set and unset break points. To see if "toggling" breakpoints would clear the issue. Nope!

I am wondering if anybody has an idea as to why this is happening and why only if launched from the task scheduler. I have other tasks that run successfully from the task scheduler.

Please Login or Register  to view this content.

[CK76]

I'm guessing that the issue isn't with VBA, but with Task Scheduler.

Check Conditions or Settings tab in Task Scheduler and see if there is any that would stop code to run to completion.

Also check Task Scheduler's log and see if you can find any flag raised.

[dflak]

This program takes about an hour to run even with a heavy server load. I am running it on off hours when the server load is low. I do have the task scheduler terminate the program if it has been running longer than 8 hours and that is the error message I get on the task scheduler: The last run of this task was terminated by the user. I use the same settings with other programs that do run.

I have the program set up to run at 6:30 PM. That's the time stamp on the log file when it fails. Here is the result of yesterday's run. As you can see the program stopped well before the 8 hours was up.

"03/07/2019 18:30:52 Open Executive"
"03/07/2019 18:30:52 Call Query 1"
"03/07/2019 18:30:57 Call Query General 1"
"03/07/2019 18:30:57 Query General 1 called"
"03/07/2019 18:30:57 Calling SubSQL with qry_crw_codes"
"03/07/2019 18:30:57 Running query: qry_crw_codes"
"03/07/2019 18:31:20 Query, qry_crw_codes, is complete."
"03/07/2019 18:31:20 SubSQL Complete with qry_crw_codes"
"03/07/2019 18:31:20 Do Event 1 start"
"03/07/2019 18:31:20 Do Event 1 End"
"03/07/2019 18:31:20 Do Event 2 Start"
"03/07/2019 18:31:20 Do Event 2 End"
"03/07/2019 18:31:20 Saving Workbook - commented out"

[dflak]

Also, what do you mean by checking the task scheduler's log? I can see the listings of my tasks and their status. Are additional details available elsewhere?

[dflak]

Disregard my last question - I went poking around now that I know that additional information could be found and found that information is logged in the systems log.

I found two entries: one at program start and the other at program stall. There is mention of a potentially malicious Macro script, but it says no action was taken. At least I have a new thread to unravel.

A potentially malicious Macro script was Detected, and no action was taken by policy
Device: WS3546; IP: 172.30.218.163; MAC: 18-60-24-7C-E5-51
File path: c:\users\dflak\local\0000 dpr\dpr model detail.xlsb
Process Id: 7160
Occurrences: 1

A potentially malicious Macro script was Detected, and no action was taken by policy
Device: WS3546; IP: 172.30.218.163; MAC: 18-60-24-7C-E5-51
File path: c:\users\dflak\local\0000 dpr\dpr model detail.xlsb
Process Id: 7160
Occurrences: 1

[CK76]

How do you execute the macro in excel? Do you use Workbook_Open?
What happens if you replace part of your code that takes long time, with smaller task? Does it run to completion?

Also, you may want to trace network status during code execution. If there was network interference and/or downtime, then that could impact code execution.

Typically, if and when I need to run Excel Macro on schedule. I'd write VBScript to open Excel file and execute the sub and close workbook.
Task Scheduler is used to run VBScript file.

However, without knowing your full process. It's hard to pinpoint the cause. I'd also recommend posting on MS support and see if they can shed more light on this. Since, code does execute when manually run.

[dflak]

I use a three-tier system to run my scheduled tasks - similar to yours except instead of a VB Script, I use another excel workbook. The reason for this is that the script knows where it is and what it is named and I generate the string to copy and paste into the task scheduler when it says "Run a program." It tells the task scheduler to use excel to execute the script.

The task scheduler can open a file, but it cannot run a macro. So I have it open the script excel file. The script file has an on open event that opens the target workbook and runs a specified macro. The script not only closes the target workbook, but also closes itself and ends Excel. This system does work with other Excel script / target combinations. A copy of this script is attached.

I stubbed out the part of the code that does the actual querying. Under this condition, the workbook takes less than a minute to complete. It still hangs when the task scheduler kicks it off.

[Melvosh]

This is outside of my normal realm, so I apologize if this has nothing to do with the problem. Could the script be dying at the DoEvents lines for some reason? It seems in both scenarios, it stops at the 2nd DoEvents in the code. Do any of your other scheduled tasks have more than one DoEvents line?

[dflak]

I threw in the DoEvents because they were "harmless." I started to suspect that the code was executing a fixed number of commands based on other debugging code I put in there. In the real world there should be no reason to put two DoEvents in a row.

Another factor to consider. The system that is failing is Windows 7 Professional, 64-bit, Office 2016. On my laptop Windows 7 Professional, 32-bit, Office 2013, the task scheduler for this file works fine.

[CK76]

Tested on my machine. Win 10, 64 bit. Office 365 ProPlus, Excel 64 bit.

Ran fine to completion.

0.JPG

My guess, is that issue lies either in Task Scheduler, or Trust setting.

Check if you have following checked in the Trust Center.
Macro Settings - > Trust access to the VBA project object model.

[dflak]

Ahead of you on one of them Both my scripts and target workbooks are in trusted locations.

I do believe you are right based on your and other evidence - it probably is the task scheduler.

Since last post, I ran a sfc /scannow and got some disk errors that the scan could NOT fix. So my IT department has a ticket on that one.

Also I tinkered with some of the settings such as Run with highest privileges and Compatibility. I won't hold out a lot of hope for that since I did not have to set them for the scripts that do run.

Also I noticed when I run good tasks, that I don't get the information (it's not even an error) about a potentially malicious macro. I think I might pass that information onto my IT people too. They have our machines screwed down tight and maybe they are being too restrictive on some of the checks they do. This might explain why the task scheduler works for some applications but not others.

So I have to play a game of Sesame Street "One of these things is not like the others." What in this code, makes the task scheduler think that the macro is malicious that isn't in other code that runs OK? It gets through at least one query but craps out before even calling for the next. I use the same logic to run queries in many of my applications.

I'm glad you keep tossing these pitches. It gets me thinking about other things.

[dflak]

This would be less frustrating if it flat didn't run or just failed ... but the task gets started. I would think that the task scheduler would then stand by and wait for it either to finish or time out. The task scheduler doesn't get an "I'm done" from the program and it does time out, but the log files indicate that the program stalls after X number of code executions which is about a minute after it kicks off and then idles until killed. So in this regard, the finger gets pointed to the macro.

[CK76]

Both my scripts and target workbooks are in trusted locations.

No, you misunderstood me. It's Developer Macro Settings and not Trusted location settings.

If that box isn't checked. Certain operation will fail on task scheduler.
0.JPG

[dflak]

From the "Dark Side" - AKA Microsoft Community.

There was a discussion on DoEvents. One of the posters had a similar issue with the task scheduler except he was running an MS-Access application. He replaced the DoEvents with a short timer and his code worked.

I always thought that a DoEvent was "harmless" however, in the context of the task scheduler it may cause an issue. One possible issue is that it gets confused and doesn't know what it is waiting for a response from. It just sits there and waits indefinitely. That would explain a program freeze.

Another could be that DoEvents spawn sub-processes and if you have enough of them, you overrun the stack.

I'm just guessing here.

Someone also suggested that I always run with the highest privileges. I'm not so sure I need that.

Another person suggested that I change the compatibility from Windows Vista, Windows server 2008 to Windows 7, Windows Server 2008 R2. That does not seem to have an effect.

Still another suggested running this script with elevated status: netsh winsock reset - now this did do something. I sometimes get to the point where I can't even launch the scripts manually. I launch them, they run for about 30 seconds and then quit with an error. There is no evidence that they had even ran the script they were supposed to call. If I close the task scheduler and run this script and reopen the task manager, I can run manually. I used GP_Edit.msc to set up running this script on shutdown. Since I reboot every night before leaving my desk, this should be in force over night.

I am at the point where I managed to get all my scripts running manually. (I commented out most of my DoEvents). I will know tomorrow if I have success on some of them and have to wait until the weekend for the rest.