+ Reply to Thread
Results 1 to 26 of 26

Seeing all the current CALLs in memory

  1. #1
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Seeing all the current CALLs in memory

    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
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: Seeing all the current CALLs in memory

    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.
    Chip, if you can read this, you're STILL making the world a better place with your immeasurably valuable gifts. Every week, you continue to help me (and untold numbers of others) tremendously. Practically no alternative outlets can match your effectiveness, even over the vast number of topics you demystify and empower usage of. You were, and still are, amazing.

  4. #4
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: Seeing all the current CALLs in memory

    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.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Seeing all the current CALLs in memory

    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.

  6. #6
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: Seeing all the current CALLs in memory

    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)
    Last edited by Oppressed1; 06-26-2015 at 01:55 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: Seeing all the current CALLs in memory

    How about queued now called pending

    Some thoughts on self-managing the OnTime inventory of invocations are shown in
    http://stackoverflow.com/questions/4...events-in-exce
    but, as you might sadly suspect, it does not indicate a way to list the pending events.

    A stronger example is in Di ck's
    http://dailydoseofexcel.com/archives...ontime-method/
    Search for the string
    Harry
    Again, no one at Di ck's indicated any way to list pendings other than self-management like this.

    Yes, ****'s is considered a 4 letter word - maybe I have the parental lock set on excelforum.com !

  8. #8
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Seeing all the current CALLs in memory

    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.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Seeing all the current CALLs in memory

    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
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Seeing all the current CALLs in memory

    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.

  11. #11
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Seeing all the current CALLs in memory

    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



  12. #12
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: Seeing all the current CALLs in memory

    snb, perhaps also delete the named range when the event fires; you show deleting it when the "request" is "cancelled." (Maybe that's on your website and I missed that.) Then you could just walk through the defined names, rather than have to test each one against Now to see if they're pending.

  13. #13
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Seeing all the current CALLs in memory

    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".

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Seeing all the current CALLs in memory

    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.

  15. #15
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: Seeing all the current CALLs in memory

    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
    Last edited by Oppressed1; 06-27-2015 at 02:23 AM.

  16. #16
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Seeing all the current CALLs in memory

    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.

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Seeing all the current CALLs in memory

    Hi Jerry,

    I've been thinking about your issue and I ran across this with the THE ObjPtr.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  18. #18
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Seeing all the current CALLs in memory

    Entia non sunt multiplicanda sine necessitate

  19. #19
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Seeing all the current CALLs in memory

    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.
    Remember what the dormouse said
    Feed your head

  20. #20
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Seeing all the current CALLs in memory

    Thanks all, I'll close this off.


    Quote Originally Posted by xladept View Post
    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.

  21. #21
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Seeing all the current CALLs in memory

    Oops! Tee Heee this

  22. #22
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Seeing all the current CALLs in memory

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

  23. #23
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Seeing all the current CALLs in memory

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

  24. #24
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Seeing all the current CALLs in memory

    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)

  25. #25
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Seeing all the current CALLs in memory

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

  26. #26
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Seeing all the current CALLs in memory

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Set PivotTable Filter to Current Day, Current Week, Current Month, or Current Year
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2014, 08:31 AM
  2. Replies: 1
    Last Post: 12-08-2011, 08:52 AM
  3. Need VBA to find out memory and virtual memory used by thread
    By Yozhik in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-07-2011, 08:31 AM
  4. Out of Memory:Is there any way in which I can restore the memory that got used up dur
    By c.vaibhav in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2009, 07:15 AM
  5. Out of Memory:Would it make any difference if I changed from Excel97 to the current v
    By Bill Martin -- (Remove NOSPAM from address) in forum Excel General
    Replies: 4
    Last Post: 05-31-2005, 09:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1