Out of memory error

[Brian.Crawford]

In many of my subs I turn off one or more of Screenupdating, Calculation (mode) and EnableEvents. To simplify this I have a single sub
.
Public Sub SetAppItems(Optional tSState As Variant, _
Optional tCState As Variant, _
Optional tEState As Variant)

' screen updating enabled ?
If Not IsMissing(tSState) Then Application.ScreenUpdating = tSState
' calculation mode ?
If Not IsMissing(tCState) Then Application.Calculation = tCState
' Events enabled ?
If Not IsMissing(tEState) Then Application.EnableEvents = tEState
End Sub
.
This sets the conditions on entry and exit of appropriate SUBS (such as)
.
SetAppItems(false,xlcalculationmanual,false)
.
If I run the app more than once without restarting EXCEL (ie testing) I get an "Out of Memory" error on the second test. Also in the above call to SetAppitems it seems within the SUB screenupdating does get set to FALSE but on returning back to calling sub it is still TRUE
.
If I use the appropriate "Application....." commands directly within the calling SUB (ie don't use SetAppItems at all) it works fine.
.
Any suggestions
Related, is there a particular order these functions should be called in.

 

[Brian.Crawford]

OK, first I compiled the App. I got 2 errors on a unused form button that had been deleted & not the code but the Sub involved has never been called (btn_Activate).
I then eliminated all the code in GetInfo except SetAppitems and gradually re-introduced all the code & Calls. No errors at least so far .. but there are occasions where it has been exercised for a while before the error has eventually shown up. Almost like a stack space problem
I'll keep testing.
But... in SetAppItems

' screen updating enabled ?
If Not IsMissing(prevScreen) Then prevScreen = Application.ScreenUpdating
If Not IsMissing(tSState) Then Application.ScreenUpdating = tSState
>>
Where .Screenupdating comes in as True and where tSState is False and after executing the code to the >> point .screenupdating is still True

I though it might be that a variant didn't work here so I set a Dim'd Boolean Var. to tSState and used it instead of tSState, also tried using =False instead of =tSState, I kept .EventsEnabled=True and kept .Calculation =auto...still didn't work. But I can stop before exiting SetAppItems and set it to False ok in the Immediate Window ... go figure
Thoughts (does this happen when you try it?)
Thanks
Brian

 

[Jerry Sullivan]

But... in SetAppItems

' screen updating enabled ?
If Not IsMissing(prevScreen) Then prevScreen = Application.ScreenUpdating
If Not IsMissing(tSState) Then Application.ScreenUpdating = tSState
>>
Where .Screenupdating comes in as True and where tSState is False and after executing the code to the >> point .screenupdating is still True

Brian, I see the effect that you are describing, but I believe that is just related to the timing of when the change in property is displayed in the VB Editor. If you add a Watch on the expression Application.ScreenUpdating and check the option to break when the value changes, it breaks at the next statement after the line:
If Not IsMissing(tSState) Then Application.ScreenUpdating = tSState

That internal behavior aside, is the function giving you the result you want?

 

[Brian.Crawford]

It's hard to tell as there are only a couple of cells that get changed in that particular SUB. In the other SUBs where I have tried it in the past it seems to be OK. In fact the only reason I discovered it here was when stepping through the code trying to figure out the "Out of Memory" problem.

The O-o-M error is quitely hiding away right now and I won't get back to testing it more for a few days. I will re-post if it reoccurs.
Appreciate all your help on this

Thanks
Brian