Code Execution Degrades Over Time

Hello all. I signed up here to ask for some help with the code I am going to post. The code works, but I sure it is not the most efficient way to do what I am doing and there are some problems that arise after many hours of execution that have baffled me to the point I need fresh eyes and more excel knowledgeable people to look at it.

In a nutshell, this code is behind a report that has to print each morning at 7am or desired time and generate a .csv file for storage backup of the data. It is also triggering database queries once an hour for new data. I give the user input boxes to set the Print time, view minutes and seconds to next update of data, I display current time to be compared with Next Print Time and give them input boxes to give a file path where the csv file will be written. It all works fine.....for a while.

I think my problem stems from keeping the time displayed accurately in a cell for viewing and using it for the compare to know when to print and save.

What I have noticed is that the longer the code runs, the more it loads up on memory and processor time to the point it will eventually crash. I have tried to slow the code down with WAIT commands, Sleep commands and experimented with some For/While looping but all of my attempts have affected either the time updating or paused function of the spreadsheet while waiting or sleeping.

I don't do a lot of excel vba so my methodology may be laughable, but for better or worse, here it is. Any help with this will be much appreciated.

With the exception of a couple of buttons coming from Sheet1 writing to Global bits, all code is in this one module. A screenshot of the user input area is also attached. Thank you.

Please Login or Register  to view this content.

Hi KD. I could not find any recursion and I was also unable to test your code. Evenso, this is worth a try as I suspect that you are stacking up calls to OnTime.

Please Login or Register  to view this content.

Thanks for the response Tom, but it does not appear to work. I copied verbatim what you had after exporting my original module and the code works but within 10 seconds it will wind up to what appears to be a somewhat infinite loop that eats about 50% of my process resources and requires an End Task to close.

I think you are right about loading up OnTime calls though. I just don't understand how to clear them between calls and I think that is what you are trying to do, but I will admit that don't completely understand you code does to unload the calls.

Thanks

Tom I kept playing with the code and reading to see what you were doing with it and I stumbled onto the fix of what you posted. Basically adding the Schedule:=True/False to your code, it is working with no wind up. I notice my Mem Usage on Excel dropped 12mb as well. Processor load is mostly 0 bouncing to 1 occasionally. I am going to let this run and I will know by tomorrow if this was the fix. Thanks again.

Please Login or Register  to view this content.

The code above seems to have fixed the problem with the CPU getting loaded up. It has now been running some 20 hours without locking up. But I have been watching my Memory Usage in Task Manager and from 3pm yesterday until now, it has risen from 22 mb (on startup of the app) to 96 mb as of the time of this post. I know this is still stemming from the time display I am doing. I have removed all calls and experimented to see what the mem usage does and it only ratchets up when the time functions are being used. With the fix from above it has extended the run time of this but at the rate the mem usage is going up, it will surely run my pc out of memory within 3 or 4 days.

This is the code that is being used for time and apparently causing the memory load. I re-ordered things a little and remove some junk that I really didn't need.

Are there any other things that can be done to unload memory usage.

Please Login or Register  to view this content.

You can try an API timer instead of OnTime. I don't know what resources OnTime uses but I do know that SetTimer uses very little...

Please Login or Register  to view this content.