Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Excel VBA (Excel2000) - call stack

    I have multiple Excel Dialog screens (forms) that are interlinked and displayed according to which option buttons are clicked.
    In Lotus, I seem to recall a feature which allowed you to 'reset the call stack' i.e. if routine A calls routine B which calls routine C etc, then you could reset the stack when routine C runs i.e. on completion it does not return to B etc.
    Can you do this in Excel VBA???
    Any help would be very much appreciated.
    zeddy
    zzz@sunbeach.net

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel2000) - call stack

    There is no direct way to do that in VBA that I know of. The closest I can come would be to use OnTime to run the procedure in one or two seconds, but the ontime will still return to the calling procedure.

    <pre> Application.OnTime Now + TimeValue("00:00:01"), "MyProcedure"
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel2000) - call stack

    Could you set a flag on the return that would prevent code from running in the previous routines?

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: Excel VBA (Excel2000) - call stack

    All of the routines basically do some housekeeping tasks, capturing data, posting data etc before calling the next routine triggered by selection of a particular option on a form. So each routine essentially ends by calling another routine which will display another form. These routines may be in other Excel files which are loaded appropriately on demand. There is a 'final exit' from the system which will 'end' all routines. My problem is that a User can bounce around between forms which will cause the subroutine stack to grow accordingly.
    Does anyone know how deep the subroutine stack can get??? I haven't seen any specs which tell me.

    zeddy

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: Excel VBA (Excel2000) - call stack

    I tried a quick and dirty test - it seems that the call stack can get as large as 2,245 entries before the 'out-of-stack-space' error is triggerred - i.e. a subroutine can call another subroutine which calls another subroutine.. etc 2,245 times. - so I'm probably OK with my intelinked forms - a User will more than likely complete the task within this limit. But if anyone knows how to reset or clear the stack I wouold still be very interested.

    zeddy

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Excel VBA (Excel2000) - call stack

    > But if anyone knows how to reset or clear the stack I wouold still be very interested

    A bit extreme, but there's always <font color=blue>Application.Quit</font color=blue>

    StuartR

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel2000) - call stack

    << a bit extreme>>

    Indeed.

    'End' would be better, but that resets all variables. It does empty the call stack though.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: Excel VBA (Excel2000) - call stack

    Unfortunately this does not address the problem - what we have is a series of Forms which are displayed to capture various User inputs - each Form is displayed by running a routine and the sequence of Forms is determined from User responses. So a command button on say Form1 may trigger a routine (in another Excel file) to unload itself and then display say Form2, and a button on Form2 will trigger a routine to unload itself and then load Form3 and so on. What we want is a method of cancelling the 'return' back to the previous calling routines in Form2 and Form1 etc.

    Unfortunately, we can't use 'End' when going from say Form1 ...Form7 to Form8 as this will stop Form8 from being displayed. All VBA commands after an 'End' statement are ignored. Ditto for commands after an 'Application.Quit'
    What I was hoping for was a simple 'Reset' command that would clear the environment (I don't mind losing all variables because I can easily regenerate them e.g. by saving to sheet and then retrieving etc) but would allow VBA statements following such a 'Reset' command to execute. I guess there is no 'Pop Stack' either.

    zeddy

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel2000) - call stack

    OK. So your answer lies in post number

    http://www.wopr.com/cgi-bin/w3t/showthread...d&sb=5&o=0&vc=1

    You should define a public boolean variable (e.g. bOK as Boolean) that you can set to true (or false) so later on you can determine whether remaining (calling) code should be executed or not.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: Excel VBA (Excel2000) - call stack

    I understand the thinking behind this.
    But the point is there is NO code in the calling routine to be executed on return!
    i.e. the last line to be executed in routineA is a call to routineB and the last line in routineB is a call to routineC etc.
    This isn't' a major problem other than the call stack just gets larger and larger as each routine calls another routine.
    The limit seems to allow up to 2,245 entries before the stack blows. It's just that I seem to recall you could clear the stack when doing similar calls with Lotus macros, i.e. when routine25 is called from routine24, we want to 'forget' all about the calling routine24 (and anything prior) and just start routine25 as if nothing previously existed.
    Unless the Users of my application continue to 'bounce' backwards and forwards between my Forms all day it seems the stack limit will allow a reasonable limit and not crash ungracefully.

    Many thanks for taking the time to review all of this.

    zeddy

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel2000) - call stack

    Then why not use OnTime to schedule the routines?
    Legare Coleman

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: Excel VBA (Excel2000) - call stack

    Hi Legare,

    I can't use OnTime because the sequence of what routines run and in which order is entirely determined by the User.
    For example, if a Form has several buttons on it which will each subsequently display a different Form, we don't know in advance which ones the User will pick. And each subsequent Form displayed may also have several option buttons which will also display additional Forms and so on. So the subrutine call stack grows as the User makes their selections.

    Regards
    zeddy

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel2000) - call stack

    You don't need to know in advance. You just replace your current Call with OnTime. OnTime will schedule the next routine to run in whatever time you set, and return to the calling routine which can end and clear the stack before the next routine starts.
    Legare Coleman

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel2000) - call stack

    You can even set the time to the current time, no need for any delay:

    Application.OnTime Now, "TheSubToRun"
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: Excel VBA (Excel2000) - call stack

    Hi Legare,
    The only way I know of clearing the stack (other than completion of all routines back to the very first calling routine) is to use an 'End' statement which I thought would immediately cease all further VBA execution.
    Are you saying I could use the OnTime in routineA to schedule say, routineB to run in 2seconds and have the next VBA command line in routineA clear the stack with an 'End' statement and then still have routineB run in 2 seconds???

    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

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