Seeing all the current CALLs in memory

I have a client project that is sort of getting out of control with all the "timers" that we have going at once. I've Googled unsuccessfully on this topic and maybe I just don't know what the keywords to use really need to be.

The question - is there an easy way to see a list of all the current "Calls" that are running in memory?
Here are some examples of the numerous timers these guys want set and reset all the time...

Please Login or Register  to view this content.

... and as part of my debugging a live workbook, I'd love to see a list of the current calls like these that are currently running in the background. Is there one that would list all these?

Not sure if this will help, and its an old thread, but, if you haven't already looked here, maybe it can get you closer to what you want....
http://www.excelforum.com/excel-prog...by-thread.html

I believe you will benefit from the excellent article http://www.cpearson.com/excel/OnTime.aspx

It seems that he understands your problem:
"There is no way to tell Excel to cancel the next OnTime event or to cancel all the pending OnTime events. Therefore, you need to store the time at which the procedure is to run in a Public variable and use that variable's value in calls to OnTime."

Sounds like you have a mess. It's even worse:
"Note that if the workbook containing the procedure to be executed by OnTime is closed before the procedure is run, Excel will open the workbook before running the procedure and will leave the workbook open after the procedure is complete."

That part sounds creepy. You are not lost, though. Read his section
Stopping A Timer

Basically, you reissue the OnTime commands with everything the same as before, but False as the last argument.

Since you have a mess of many of them, it sounds like you need a disciplined collection of variables. If you follow the above link carefully, all the way through, you should find an escape from your mess.

The previous reply was to solve your problem with "runaway timers flying around." As far as "seeing" them - have you looked at the Call Stack? Control -L, or alt-V-K in VBA window.

Tom, that is an interesting post, but it lists information about Windows processes, not the current Excel "calls to macros in the future" that I'm looking for.

Oppressed, I'm already storing the CountDown and NextDB datestamps in public variables and using them to set and stop the timers. I'm looking for a way to list the currently running timers in memory, hopefully the macro name and trigger time.

Okay, then as I suspected you don't want the call stack; you want to know what OnTimes are "queued up" so to speak.

My only suggestion is to manage them as they're queued up, and keep your own inventory. Sounds like you're using good practices so you should be equipped. Sorry, I don't know a way to list them otherwise.

I will offer, as you likely know, that closing Excel and reopening clears them, if you just want the "voices to go away!", whereas just closing the workbook "doesn't intimidate" the OnTime events because "Excel will [re]open the workbook."

EDIT: changed "fired off" to "queued up" (twice)

Sorry about that Jerry. I thought it would show other processes as well as Windows processes. I was also thinking that it might get you "on the right track" and that you might have been able to alter the code to do what you wanted. I don't know enough about VBA code to know which ones can be altered or which codes can't be....yet.

A thought, not thoroughly explored (I'm on holiday, no computer, no Excel, stuff to do and visit, and a wife who's not right happy about me spending time on the forum). But maybe you could set up an array/table and allocate an ID to each timer. Then, rather than have a simple one liner to set or unset the timer, you have a function that sets the timer and updates its entries, earliest/next time, routine, and scheduled or not, possibly even a comment. Then you could have another function that just loops through the array and lists the status of each entry.

Probably going to need some thinking through and a little re-work but, if there's no built in functionality, I guess you need to build it yourself.

Regards, TMS

Yes, I have that on my project log to create if I can't get a positive answer to this question (Create OnTime Call Log). I have all the workarounds from Googling OnTime method itself.


To all who might want to consider this question, all I'm looking to discover is if there is some method or process that will uncover and list for me all the currently running OnTime scheduled procedures.... macroname, time, you get the idea.

You might consider to combine both approaches: storing the time at which the macro has to be executed publicly and storing the pending ontime procedures.
Instead of a public variable use a 'named constant'

Please Login or Register  to view this content.

More on this:
http://www.snb-vba.eu/VBA_Application.OnTime_en.html

Thanks, all, I guess by refusing to focus on my actual question and keep returning to variations on workarounds, you are answering my question with a "no".

I don't think it should be considered as refusing to focus on the actual question. My, albeit, brief research seemed to indicate that the answer you are looking for is not there. Hence the suggestions you have been given.

Yes, people are saying, there's no direct solution. I do apologize for making least common denominator suggestions because you quickly explained that you know how to write a "logging" workaround. FWIW I did spend 25+ minutes Googling and reading articles, reading dailydose, and reviewing Chip's, and everyone says: none is known, do a workaround. That said, my very first answer was especially remedial, and somewhat off point. :-O Your question was quite explicit, so sorry for the drift there.

I do "get" your desire for a straight answer because I constantly get "level 1" suggestions when I ask questions like yours, and actually admire your restraint as a thread piles up with yada yada. However it looks very bleak, especially considering that no one solved it at dailydose. Perhaps the only one(s) with a solution are actual XL developers, who seem to delight in ignoring the pain that is plainly evident on forums by never jumping in, explaining or helping out

Yeah, after hours of Googling on my own prior to asking here I was hoping I just didn't know how to ask the question, that there was a way to see this "collection" in memory somewhere if I could figure out what it was called, but that seems to not be the case.

Thanks to all for considering the topic with me.

Hi Jerry,

I've been thinking about your issue and I ran across this with the THE ObjPtr.

Don't think it's possible, JB: http://www.mrexcel.com/forum/excel-q...scheduled.html

There is no way I know of. It's possible that there's something stored in the application namespace (though I doubt it to be honest) but I don't know how you'd access it even if it were.

Thanks all, I'll close this off.

Originally Posted by xladept

Hi Jerry,

I've been thinking about your issue and I ran across this with the THE ObjPtr.

..hehe, that seems to link back to this thread.

Oops! Tee Heee this

Hmm, ok, I've read that over and don't see the connection between the DesignMode property of forms and my original question.

I was thinking that the ObjPtr could somehow give you controlling access to the OnTime procedures in your Project

All ObjPtr does is give you the address of a pointer to an object, but there is no object returned by OnTime to get a pointer to. (If there were, you wouldn't need a pointer anyway)

I feel that some sort of pointer is germaine to this issue - Perhaps VarPtr to CountDown or NextDm???

Pointer to what? There's no relevant object exposed.