What's the impact of running a Macro all day?

[dalesea]

Hi All,

We have a spreadsheet where live financial market data is streamed on one tab [Sheet1]. On a separate tab [Sheet2], the user can setup trigger alerts that send an e-mail if the trigger criteria is true (e.g. if Sheet1!J20 > 10000000 then send an e-mail). The user can set cell-specific alerts or check all cells within a given range. The user can also set custom e-mail headers for each trigger using custom commands/functions.

Our problem: a macro that checks for triggered events and sends e-mail is currently scheduled (Application.OnTime) to run every 5 seconds. However, if left for a few hours - MS Excel seems to close down without giving an error.

Details of the macro: The macro takes less than a few milliseconds to run. The macro uses three public variables (a date/time for the new scheduled run, a boolean to enable or disable trigger monitoring and the user's sender e-mail address if their SMTP account doesn't use authentication). The macro loops through each complete row on Sheet2 to check if an e-mail needs to be sent for each trigger. All successfully sent e-mails are recorded in Sheet3. The task is actually split up into approx. 5 procedures.

I'm wondering, what's the impact of running a macro all day (8am - 6pm, maybe longer, every 5 seconds)? Is it possible? What isn't possible? Why does Excel close without an error? Is it a buffer issue or processing power issue? Does it matter if certain variables aren't emptied before the macro finishes?

Any help would be much appreciated.
Thanks!

 

[chuckchuckit]

Welcome to the board!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have numerous Excel automated programs that run all day long. Looking for certain things to happen with the financial markets and once they do any number of coded things may happen.<o></o>
<o></o>
The first “possible clue” that comes to mind is that you may have too many connections running that are increasing over time due to emails or queries that are not being deleted after their use is completed.<o></o>
<o></o>
You can check this through the menu “Data, Connection” where you can look at that occasionally throughout the day, and the number of connections should not be increasing if you are deleting each connection after its use is finished. Coding to delete the particular connection no longer needed when others are still being used can get a bit tricky to do properly.<o></o>
<o></o>
To get all the different various programs to run I had to weed through a lot of things. Finding the intermittent things are not easy. One thing I did to find a particular stubborn one that was crashing my whole system after some hours, was I loaded the "Windows Task Manager" to watch the memory allocation as time progressed. On one program I saw over time that it was slowly but surely using more and more memory until it maxed out my memory after some hours and crashed. This was a 2nd party piece of software poorly written that I was using inside my Excel that I then stopped using.<o></o>
<o></o>
Also inside the "Windows Task Manager" in Windows 7 is the "Resource Monitor" which can also help narrow down individual programs a bit better sometimes too.<o></o>
<o></o>
My approach on stuff like this is I look for a clue of “something different or out of the ordinary”. As for me it usually is these “clues” that point me in the direction of the problem while it may not always be the answer directly at first.<o></o>
<o></o>
Also I found for my situation since I can have so many programs running at one time on one computer, I split up the chores between many different instances of Excel. So one program is not doing "all" the work and possibly causing unforseen conflicts. Since much of my programs are observing and alerting or just data gathering and processing, they do not need to integrate with each other, so this approach works OK in that situation.<o></o>
<o></o>
Hope that is of assistance.<o></o>
<o></o>
Chuck<o></o>

 

[chuckchuckit]

A bit more thought on that is: if it is crashing around the same time every day, that is when I might compare the "Windows Task Manager" data and the "Data Connections" info with the start of the day levels.

My "clue hunting" tends to look for consistency in crashing too as that might be easier to find due to it always happens the same. Then it means something consistent is causing it so it should not be as hard to find.

Just more thoughts...