I have an interesting puzzle! And I haven't a clue how to debug the problem we're observing. Any ideas very much welcomed..
By way of background, we have a fairly large and complicated Excel addin that's function is to automate a long series of tasks for users. In essence each row on the spreadsheet is a list of parameters that defines 'a task' and the addin simply goes down that list and carries out each task in sequence. Example tasks might be: opening another spreadsheet, recalculating it and saving it; opening an Access database, running a query and saving the result set as a csv file; displaying an input box on the screen for the user to enter some info which is then used to send an email to someone; running another program etc etc.
The way it's used is that it's located on a remote PC and users log into that PC, click a button to start the series of tasks, and then the addin goes to work for quite for a number of hours until all the tasks have finished.
The specific problem is that it works just fine as long as it's being "watched", by which I mean the connection to the remote desktop hasn't been closed (or hasn't been automatically locked by Windows through inactivity of the user). What we observe is that sometimes (ie not every time, even when exactly the same collection of tasks is executed) when the remote connection is closed or Windows locks the desktop - the vba code just pauses - and as soon as we return to the remote PC or unlock it then the code resumes. We're able to tell it has really paused because the addin writes out a text file containing a runlog of its progress every so often and when we inspect that file at the end we can see it wasn't doing anything duing the period it appeared to "paused". We can't see anything common in terms of the particular part of the code / type of task its processing when pauses occur - they seem correlated only with the moment the PC is locked or the connection to teh remote PC is closed.
The possible candidates for problems that I can see from the code are:
- time message boxes
- use of application.wait
- doevents
- use of application.screenupdating
... but they're just the one that look related to me.
(I realise it might help if I posted the code, but it's literally huge and not well enough written to be followed!)
The biggest issue is debugging the issue, as even when we can see it's paused, the moment we try to debug it then of course we're back on the remote PC in unlocked mode and it start resumes again!!!!
Any pointers are to what on earth might be doing it, or how to go about debugging it.
Regards
Simon