Controlling Environmental Parameters During Procedures Calls

  • While building a moderate sized Excel application where many macros called each other in sequences determined by user actions, I learned that my habit of setting Excel's environment back to its "normal" state before exiting a macro can cause problems to a macro that has called another one. That is if the calling macro has set calculations to manual and the sub it calls returns this to automatic as it finishes, then the calling sub has to set the reset parameters back to the state it needs them to be in for its subsequent execution.


    The need to reinitialize the changed parameter settings again in the calling macro frequently was signaled by the code producing unexpected results (sometimes referred to as "errors" by users). :)


    A solution is to capture the current state of commonly modified environmental parameters at the start of each macro and then reset them to these values when the macro finishes. This way a calling macro will no have its settings changed by a macro that it calls.


    Since Excel's VBA variables declared inside of a procedure have a scope limited to that procedure, the same code can be used for all macros and Excel will keep track of each set separately.


    Inserting the Dim statements adn first five lines of the following code at the start of each macro and the final five lines immediately prior to macro exit should return Excel to the environmental condition it was in when the macro was called. If you never use one of these parameters (e.g, IgnoreRemoteRequests) then simply omit its code. Hope you find it useful.


    P.S. As Reafidy noted in his thread of a week ago in this forum, if your macro can exit in multiple ways, make sure the "return to macro entry" code runs prior to any of the macro exit possibilites.


    http://www.ozgrid.com/forum/showthread.php?t=92290

  • Re: Controlling Environmental Parameters During Procedures Calls


    Quote

    The need to reinitialize the changed parameter settings again in the calling macro frequently was signaled by the code producing unexpected results (sometimes referred to as "errors" by users).


    Hee hee hee - my laugh for the day :grin:

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Controlling Environmental Parameters During Procedures Calls


    I used to avoid this problem with a mix of not resetting some of these things at the end of my macros (lazy a$$) and by unsetting and resetting others immediately around the relevant coding (slightly code heavy, but then, I would say it's better not to leave something like 'display alerts' false indefinitely).


    Of the mentioned parameters, though, would it be viable to remove the screen updating reset and just have screen updating stay false if it had been set false at any stage?


    Part of me rejects the concept of not resetting this kind of parameter to true at the end of macros... but screen updating is reactivated both at the end of macros and in break mode... what could possibly go wrong? (ominous laugh)

  • Re: Controlling Environmental Parameters During Procedures Calls


    I agree that ScreenUpdating is borderline for needing to reset. For one thing, if returned prematurely to True it generally (never?) will cause errors, it simply may slow down code execution. It's updating state also will be very obvious. And you are correct that it sets back to True automatically when macro execution ends (although I think this is true only at the end of the "main" macro, not when each called sub finishes execution).


    Since I sometimes intentionally turn it back on and then back off during a sub (to show an intermediate result during a long routine) I think I just prefer to know that when I call a sub that it will not change my current envoronment. That way I don't have to "Study" the called sub to refresh my memory on what settings I did or did not change.


    I certainly do not feel the approach I suggest should be manditory. I just find it useful and wanted to share.

  • Re: Controlling Environmental Parameters During Procedures Calls


    Mmm, v. good point about showing intermediate points. My macros tend to end up a bit of a black box - you know, stuff goes in, indeterminate wait, may or may not have worked... I really should look at giving some kind of update process!


    I know the status bar can be a less processor-intensive way of showing progress than some big ol' display meters I've seen on here...


    Re your last point, I wasn't criticising the idea, I think it's brilliant and very 'conscientious' programming, I was just thinking out loud around it.


    Thanks for sharing,
    Neil

  • Re: Controlling Environmental Parameters During Procedures Calls


    Quote

    Of the mentioned parameters, though, would it be viable to remove the screen updating reset and just have screen updating stay false if it had been set false at any stage?


    My opinion is that for professional programmers not turning screenupdating back to true is not good practise.


    For users that are using VBA for their on use: "neglect at your own risk".


    It is more specific to some versions of excel. From memory some versions atually require you to turn screenupdating back on. I think its office XP??


    And also I believe in some versions of excel if you turn off screen updating then display a dialog box and the user the moves the dialog box then the background wont repaint properly.


    For the sake of a single line I say do it.


    I have also myself encountered the problem created by not reseting screenupdating in this thread:


    http://support.microsoft.com/kb/143452


    Quote from Microsoft

    Remember to set the ScreenUpdating property back to True when your macro ends.

  • Re: Controlling Environmental Parameters During Procedures Calls


    Neat! Thanks for the elaboration. I love threads like this that that exlaborate and expand on a theme. Oz at its best.


    And to quote the professor yet again: "Everyday I learn something new about Excel."