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.

 

[Jerry Sullivan]

Brian, Try changing SetAppItems to a Function instead of a Sub.

Regarding the order of the application statements, it's probably not possible to define an order that will universally provide the best result in terms of speed and intended execution sequence.

However for the vast majority of code, the intent is to turn off these 3 properties at the beginning and turn them on at the end.

In those cases, the most important relationship is to have .enableevents after .calculation as you do. I'd lean toward putting screenupdating last to avoid the potential for a second screen update when calculation is set to automatic.

 

[Brian.Crawford]

Did that and it "seemed" to get rid of the "Out of Memory" error, Why is that ??
...............Oooops, just closed the WB again and got the Out of memory" error, but it's been opend and closed numerous times in between.
.
However the screenupdating still does not get set correctly. I call this during the WB Activate event (had it in the Open before where it also didn't work) and after executing the;
.
...If Not IsMissing(tSState) Then Application.ScreenUpdating = tSState
.
It was True coming into the Func and it is still true after executing the above line where tSState is False. While stopped in the function after the above line of code I can set it to False in the Immediate Window.
Thoughts
Thanks

 

[Jerry Sullivan]

Brian,

I've tested the function with some sample Workbook.Activate code.

It sets the ScreenUpdating correctly and I haven't experienced any memory issues yet.

Is it possible that the problem lies elsewhere in your code?

If you post, I will see if that works on my PC.

 

[Brian.Crawford]

I'll work on getting the code to you. I'm sure it's possible I'm doing something else that is influencing this but this is happening in WB Activate code and is the only change that I make for testing this.
My intention was to use this in all my SUBS where needed but for now it is in this one place only. I did put the code in various places at one point but had to remove it everywhere else as it created the same problem in various places while running the app, sometimes during some lengthy testing periods (so it is not necessairly related to just this place in the code).
And as mentioned, if I put the 3 application... commands in the main SUB (vs a called SUB or FUNCTION) it never gives me a problem (most subs have at least 2 of these; ScreenUp & Calculation). It is definitely related to doing these in their own sub.
thank
B

 

[Jerry Sullivan]

BTW, Are you locating the Public Function in a Standard Module of your Project?
That's the way I tested it, and it's probably the best place in terms of VBA Project organization.

 

[Brian.Crawford]

YEs both the calling and called are in (separate) public modules

 

[Jerry Sullivan]

YEs both the calling and called are in (separate) public modules

The calling is in the ThisWorkBook module - yes?

Will you be able to post your code?
I can post my test code if it will help you.

 

[Brian.Crawford]

here is the code that I'm currently testing. Only GetInfo uses the SetAppItems at this time which the only times it fails.

1) for the workbook activate
Private Sub Workbook_Activate()
...Application.OnKey "%{F12}", "ManageWB"
...Application.OnKey "%{F5}", "ShowAll"
...Application.OnKey "%{F7}", "ManageCCWB"
...GetInfo ' Initialize WB info
End Sub

for "GetInfo"; I've included the Application.... commands that work and commented out the "SetAppItems" that doesn't. tmp is a global variant
.
Public Sub GetInfo()
' Dim prevCalc As Variant, prevEvents As Variant, prevScreen As Variant
' tmp = SetAppItems(xlCalculationManual, False, False, prevCalc, prevEvents, prevScreen)

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False

Call ProtectWS(x, Sheets("summary"), False)
Call ProtectWS(x, Sheets("projectsummary"), False)
Call ProtectWS(x, Sheets("referencesummary"), False)
Call ProtectWS(x, Sheets("log"), False)
Call ProtectWS(x, Sheets("budgetplan"), False)

FullPath = LCase(Application.ActiveWorkbook.FullName)
WorkBookName = LCase(Application.ActiveWorkbook.Name)
DirPath = Left(FullPath, InStr(1, FullPath, WorkBookName) - 1)

UserID = Application.UserName

WBInfo = Split((Application.ActiveWorkbook.Name), "_", 4)
Sheets("summary").Range("budgetyear") = WBInfo(1)
Sheets("summary").Range("CCID") = WBInfo(0)

Call SetNamedRanges
Call BuildAcctList
Call ShowAll(False)

Call ProtectWS(x, Sheets("summary"), True)
Call ProtectWS(x, Sheets("projectsummary"), True)
' Call ProtectWS(x, Sheets("referencesummary"), True)
Call ProtectWS(x, Sheets("log"), True)

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

' tmp=SetAppItems(xlCalculationAutomatic, True, True)
End Sub

Here is the current version of SetAppItems, I'm not using the previous settings at this point.

Public Function SetAppItems(Optional tCState As Variant, _
Optional tEState As Variant, _
Optional tSState As Variant, _
Optional ByRef prevCalc As Variant, _
Optional ByRef prevEvents As Variant, _
Optional ByRef prevScreen As Variant) _
As Variant

' calculation mode ?
If Not IsMissing(prevCalc) Then prevCalc = Application.Calculation
If Not IsMissing(tCState) Then Application.Calculation = tCState

' Events enabled ?
If Not IsMissing(prevEvents) Then prevEvents = Application.EnableEvents
If Not IsMissing(tEState) Then Application.EnableEvents = tEState

' screen updating enabled ?
If Not IsMissing(prevScreen) Then prevScreen = Application.ScreenUpdating
If Not IsMissing(tSState) Then Application.ScreenUpdating = tSState

SetAppItems = True
End Function

 

[Jerry Sullivan]

There are a lot of Procedures being called there.

It sounds like you have tried, with and without calling SetAppItems and it only fails when you have SetAppItems.

Have you tried the opposite - to run this with virtually nothing but SetAppItems?

If you do that and it works, you could start to add back in the other Calls.

It could be that one of your Procedures is incompatible with SetAppItems and if you identify which one, it could be fixed.

Here is some code you could try as a starting point. It's a variation of code in xl2007 Help for ScreenUpdating.
It will hide every other column with and without screenupdating hidden and show the difference in speed.

Public Sub GetInfo()
    Dim tmp As Variant
    Dim i As Long
    Dim c As Range
    Dim startTime As Double, stopTime As Double
    Dim elapsedTime(2) As Double
    Application.ScreenUpdating = True
    Worksheets("Sheet1").Activate
    For i = 1 To 2
        If i = 1 Then
            Debug.Print i & " before: " & Application.ScreenUpdating
            tmp = SetAppItems(xlCalculationManual, False, False)
            Debug.Print i & " after: " & Application.ScreenUpdating
        Else
            Debug.Print i & " before: " & Application.ScreenUpdating
            tmp = SetAppItems(xlCalculationAutomatic, True, True)
            Debug.Print i & " after: " & Application.ScreenUpdating
        End If
        startTime = Time
        For Each c In ActiveSheet.Columns
            If c.Column Mod 2 = 0 Then
                c.Hidden = True
            End If
        Next c
        stopTime = Time
        ActiveSheet.Columns.Hidden = False
        elapsedTime(i) = Round((stopTime - startTime) * 24 * 60 * 60, 2)
    Next i
    Application.ScreenUpdating = True
    MsgBox "Elapsed time, screen updating off: " & elapsedTime(1) & _
            " sec." & Chr(13) & _
            "Elapsed time, screen updating on: " & elapsedTime(2) & _
            " sec."
End Sub

You might also want to declare all your variables explicitly, The debugger might find something prior to run-time.