asked on

Out of memory/Excel crashes on form loading

I have a spreadsheet with one main userform that is shown by clicking a commandbutton in a worksheet. Occasionally, upon attempting to show the userform, Excel will provide the message indicating that it has encountered an error and needs to close, with no other error messages. I have also occasionally encountered an out of memory error that shows when the user attempts to close the workbook.

I'm trying to figure out if these two problems are related. I have some rather long macros, with the largest being about 116Kb, which resides in a module. The userform I am referring to has a .frx file of about 112Kb. Are either of these so large as to be causing these errors? I'm not worried about reducing macro sizes, but it's definitely more difficult to reduce the memory used by the userform.

The strangest thing is how sporadic this problem is. Upon encountering the fatal error that closes Excel (which just happens every so often for no apparent reason) , if I reopen, disable macros, rename the file, close and then reopen with macros enabled, it works fine. What about renaming the file is causing this error to go away? Thanks for your help!

aikimark

Sounds like you might have a memory leak or failure in garbage collection.

When these failures DO occur...
1. Has the workbook been open for some extended period?
2. Are other users/processes competing for data or resources?
3. Are there any macros that execute upon workbook/worksheet open?
4. What routines are likely to have executed prior to the command button being clickec?
5. What routines execute when the user form opens? (what do they do?)

Rich5150

ASKER

To answer your questions:
1. At times yes, but I've often received this error immediately after the workbook has been opened and the macro to load the userform has been called.
2. no
3. Yes, there is a workbook open macro, but the code that loads the userform is located in a module - it's when this is called that I've had excel crash.
4. none, except for the workbook open macro.
5. There is a userform activate macro that primarily resets the userform to show or hide certain controls (labels, textboxes, frames), and it also sets default values for some listboxes and checkboxes in the userform. Under certain conditions it calls some other short macros to help with setting the defaults and showing/hiding controls.

It sounds like you don't think that the memory used by the userform is the issue. Would you like me to post some code?

Rich5150

ASKER

I've attached the workbook open macro and the userform activate macro for review. Some quick explanation: there are three scenarios that affect how the userform is set-up on opening.

1) There's the initial opening when the workbook is open (this will be a read only file when used, so it will be in the same state every time it's opened)
2) The user can enter in data, close the userform down, and then open it again with the userform reset (all previous data cleared and defaults restored)
3) The user can enter in data, close the userform down, and then open it again without having the userform reset, thus all previously entered data is still there

Depending on which scenario is appropriate, there are some other calls to macros that assist in resetting the data or loading previously entered data. I appreciate the help in figuring this out. Please let me know if there is additional information that will help.

Sub Workbook_Open()
    Reset
    Sheets("Input").Range("j22").Value = "Start"
    Sheets("Intro").Select
    Range("A1").Select
End Sub
 
Sub UserForm_Activate()
Sheets("Bkgd").Select
Range("j16").Select
'First use of data form
If Sheets("Input").Range("j22").Value = "Start" Then
    
    'select defaults
    AreaSelect.Selected(0) = True
    InstSelect.Selected(0) = True
    ROI.Selected(1) = True
    
    'Reset visibility of captions, etc.
    RepeatFrame.Visible = False
    
        'Inst1 data
    TRefrigCostLabel.Visible = False
    TRefrigCurr.Visible = False
    TRefrigCost.Visible = False
    TACCostLabel.Visible = False
    TACCurr.Visible = False
    TACCost.Visible = False
    TAddtlAreaLabel.Visible = False
    TAddtlArea.Visible = False
    BracketSize.Visible = False
    BracketLabel.Visible = False
    BracketNote.Visible = False
    
    'Current instrument data
    CRefrigCostLabel.Visible = False
    CRefrigCurr.Visible = False
    CRefrigCost.Visible = False
    CACCostLabel.Visible = False
    CACCurr.Visible = False
    CACCost.Visible = False
    CAddtlAreaLabel.Visible = False
    CAddtlArea.Visible = False
    
    WageButton.Value = True
    SalaryButton.Value = False
    VPurchaseNo.Value = True
    TPurchaseNo.Value = True
    CPurchaseNo.Value = True
End If
    
    'if user wants to return to and reset the data form
If Sheets("Input").Range("j22").Value = "Yes" Then
    
    'select defaults
    AreaSelect.Selected(0) = True
    InstSelect.Selected(0) = True
    ROI.Selected(1) = True
    
    'reset defaults and user input data
    Reset
    
    'Reset visibility of captions, etc.
    RepeatFrame.Visible = False
    
        'Inst1 data
    TRefrigCostLabel.Visible = False
    TRefrigCurr.Visible = False
    TRefrigCost.Visible = False
    TACCostLabel.Visible = False
    TACCurr.Visible = False
    TACCost.Visible = False
    TAddtlAreaLabel.Visible = False
    TAddtlArea.Visible = False
    BracketSize.Visible = False
    BracketLabel.Visible = False
    BracketNote.Visible = False
    
    'Current instrument data
    CRefrigCostLabel.Visible = False
    CRefrigCurr.Visible = False
    CRefrigCost.Visible = False
    CACCostLabel.Visible = False
    CACCurr.Visible = False
    CACCost.Visible = False
    CAddtlAreaLabel.Visible = False
    CAddtlArea.Visible = False
    
    WageButton.Value = True
    SalaryButton.Value = False
    VPurchaseNo.Value = True
    TPurchaseNo.Value = True
    CPurchaseNo.Value = True
End If
    
'if user wants to return to and not reset the data form
If Sheets("Input").Range("j22").Value = "No" Then
    'Reset visibility of captions, etc.
    RepeatFrame.Visible = False
    
        'Inst1 data
    TRefrigCostLabel.Visible = False
    TRefrigCurr.Visible = False
    TRefrigCost.Visible = False
    TACCostLabel.Visible = False
    TACCurr.Visible = False
    TACCost.Visible = False
    TAddtlAreaLabel.Visible = False
    TAddtlArea.Visible = False
    BracketSize.Visible = False
    BracketLabel.Visible = False
    BracketNote.Visible = False
    
    'Current instrument data
    CRefrigCostLabel.Visible = False
    CRefrigCurr.Visible = False
    CRefrigCost.Visible = False
    CACCostLabel.Visible = False
    CACCurr.Visible = False
    CACCost.Visible = False
    CAddtlAreaLabel.Visible = False
    CAddtlArea.Visible = False
    
    AreaSelect_Change
    VPurchaseYes_Change
    VCollDevCheck_Change
    VConCheck_Change
    VReagentCheck_Change
    VControlCheck_Change
    VServiceCheck_Change
    RepeatYes_Change
    
    'if Inst1 was initially selected
    If InstSelect.Selected(0) = True Then
        TPurchaseYes_Change
        TCollDevCheck_Change
        TConCheck_Change
        TReagentCheck_Change
        TControlCheck_Change
        TServiceCheck_Change
        BracketYes_Change
        TRefrigYes_Change
        TACYes_Change
        TAddtlAreaYes_Change
    End If
    
    'if Other was initially selected
    If InstSelect.Selected(1) = True Then
        CPurchaseYes_Change
        CCollDevCheck_Change
        CConCheck_Change
        CReagentCheck_Change
        CControlCheck_Change
        CServiceCheck_Change
        CRefrigYes_Change
        CACYes_Change
        CAddtlAreaYes_Change
    End If
    
    'if not reset, send listbox selections back to their listbox
    If Sheets("Input").Range("g2").Value = Sheets("List").Range("a2").Value Then
        AreaSelect.Selected(0) = True
    End If
    If Sheets("Input").Range("g2").Value = Sheets("List").Range("a3").Value Then
        AreaSelect.Selected(1) = True
    End If
    If Sheets("Input").Range("g2").Value = Sheets("List").Range("a4").Value Then
        AreaSelect.Selected(2) = True
    End If
    If Sheets("Input").Range("g2").Value = Sheets("List").Range("a5").Value Then
        AreaSelect.Selected(3) = True
    End If
    If Sheets("Input").Range("g2").Value = Sheets("List").Range("a6").Value Then
        AreaSelect.Selected(4) = True
    End If
    If Sheets("Input").Range("g3").Value = Sheets("List").Range("b2").Value Then
        InstSelect.Selected(0) = True
    End If
    If Sheets("Input").Range("g3").Value = Sheets("List").Range("b3").Value Then
        InstSelect.Selected(1) = True
    End If
    If Sheets("Input").Range("m21").Value = Sheets("List").Range("d2").Value Then
        ROI.Selected(0) = True
    End If
    If Sheets("Input").Range("m21").Value = Sheets("List").Range("d3").Value Then
        ROI.Selected(1) = True
    End If
End If
    DataPages.Value = 0
    UpdateControls
End Sub

Open in new window

Rich5150

ASKER

I guess I should include these two macros as well since they run when the workbook is opened or when the userform is reset. I'll confess right now that my understanding of memory leakage and garbage collection isn't too good, so any explanation will help. I'm curious if it has anything to do with one phenomenon I've run into, where after a long period of having the workbook open and having done a lot of work in the VBA module, it appears that the link between the listboxes and the cell referenced as the controlsource is broken. Rather than showing what has been selected in the listbox, the cell will simply be empty, and even when I type one of the listbox values into the cell, it will disappear as soon as I hit enter. I run code cleaner on this workbook occassionally, and this appears to fix that problem. I am clueless as to what causes this. Thanks again for the help.

Sub Reset()
Dim Today
Dim ShiftLength As Integer
Dim NumShifts As Integer
Dim LabSpaceCost As Integer
Dim SolidWasteCost As Integer
Dim LiquidWasteCost As Integer
Dim DefPercent As Integer
Dim StaffDefault As Integer
Dim NumberofInst2 As Integer
Dim NumberofInst1 As Integer
Dim NumberofCurrent As Integer
Dim PosPercDef As Integer
Dim IncPercDef As Integer
Dim ShiftTime As String
    
    Today = Date
    ShiftLength = 8
    NumShifts = 1
    LabSpaceCost = 20
    SolidWasteCost = 1
    LiquidWasteCost = 1
    ShiftTime = "8:00 AM"
    DefPercent = 100
    NumberofInst2 = 1
    NumberofInst1 = 1
    NumberofCurrent = 1
    PosPercDef = 2
    IncPercDef = 3
    StaffDefault = 1
    
    'account info
    Sheets("Input").Range("g4").ClearContents
    Sheets("Input").Range("g5").Value = Today
    
    'staffing info
    Sheets("Input").Range("g7").Value = ShiftLength
    Sheets("Input").Range("g8").Value = NumShifts
    Sheets("Input").Range("g9:g10").ClearContents
    Sheets("Input").Range("g11").Value = StaffDefault
    Sheets("Input").Range("g12:g14").ClearContents
    Sheets("Input").Range("g16:g17").ClearContents
    Sheets("Input").Range("g18").Value = DefPercent
    Sheets("Input").Range("g20:g21").ClearContents
    Sheets("Input").Range("g22").Value = DefPercent
    Sheets("Input").Range("g24:g25").ClearContents
    Sheets("Input").Range("g26").Value = DefPercent
    Sheets("Input").Range("g28:g29").ClearContents
    Sheets("Input").Range("g30").Value = DefPercent
    Sheets("Input").Range("g32").ClearContents
    Sheets("Input").Range("g33").Value = StaffDefault
    Sheets("Input").Range("g34:g35").ClearContents
    Sheets("Input").Range("g37").ClearContents
    Sheets("Input").Range("g38").Value = DefPercent
    Sheets("Input").Range("g40").ClearContents
    Sheets("Input").Range("g41").Value = DefPercent
    Sheets("Input").Range("g43").ClearContents
    Sheets("Input").Range("g44").Value = DefPercent
    Sheets("Input").Range("g46").ClearContents
    Sheets("Input").Range("g47").Value = DefPercent
    Sheets("Input").Range("j44:j45").Value = "false"
    
    
    'sample info
    Sheets("Input").Range("j2:j7").ClearContents
    Sheets("Input").Range("j10:j11").ClearContents
    Sheets("Input").Range("j12").Value = PosPercDef
    Sheets("Input").Range("j13").Value = IncPercDef
    Sheets("Input").Range("j20").Value = ShiftTime
    Sheets("Input").Range("j29").Value = "false"
    Sheets("Input").Range("j30").Value = "false"
    Sheets("Input").Range("j9").Value = DefPercent
    
    'current workflow data
    Sheets("Input").Range("m3:m10").ClearContents
    Sheets("Input").Range("m12:m14").ClearContents
    Sheets("Input").Range("m16:m18").ClearContents
    
    'lab data
    Sheets("Input").Range("m22").Value = LabSpaceCost
    Sheets("Input").Range("m23").Value = SolidWasteCost
    Sheets("Input").Range("m24").Value = LiquidWasteCost
    Sheets("Input").Range("m25").ClearContents
    
    'Inst2 data
    Sheets("Input").Range("p3:p4").Value = "false"
    Sheets("Input").Range("p6:p13").ClearContents
    Sheets("Input").Range("q12").Value = "No"
    Sheets("Input").Range("m29:m34").Value = "false"
    Sheets("Input").Range("p5").Value = NumberofInst2
    
    'Inst1 data
    Sheets("Input").Range("p15:p16").Value = "false"
    Sheets("Input").Range("p18:p25").ClearContents
    Sheets("Input").Range("p26:p27").Value = "false"
    Sheets("Input").Range("p28").ClearContents
    Sheets("Input").Range("p29:p32").Value = "false"
    Sheets("Input").Range("p33").ClearContents
    Sheets("Input").Range("p34:p35").Value = "false"
    Sheets("Input").Range("p36").ClearContents
    Sheets("Input").Range("p37:p38").Value = "false"
    Sheets("Input").Range("p39").ClearContents
    Sheets("Input").Range("q24").Value = "No"
    Sheets("Input").Range("m36:m41").Value = "false"
    Sheets("Input").Range("p17").Value = NumberofInst1
    
    'Current instrument data
    Sheets("Input").Range("p41:p42").Value = "false"
    Sheets("Input").Range("p44:p54").ClearContents
    Sheets("Input").Range("p55:p56").Value = "false"
    Sheets("Input").Range("p57").ClearContents
    Sheets("Input").Range("p58:p59").Value = "false"
    Sheets("Input").Range("p60").ClearContents
    Sheets("Input").Range("p61:p62").Value = "false"
    Sheets("Input").Range("p63:p65").ClearContents
    Sheets("Input").Range("j16:j17").ClearContents
    Sheets("Input").Range("q50").Value = "No"
    Sheets("Input").Range("m43:m48").Value = "false"
    Sheets("Input").Range("p43").Value = NumberofCurrent
 
    'timelines
    Sheets("CurrentTL").Columns("b:az").Delete
    Sheets("CurrentTL").Rows("1:8").Delete
    Sheets("Inst1TLM").Columns("b:az").Delete
    Sheets("Inst1TLM").Rows("1:8").Delete
    Sheets("Inst2TL").Columns("b:az").Delete
    Sheets("Inst2TL").Rows("1:8").Delete
    Sheets("Inst2TLM").Columns("b:az").Delete
    Sheets("Inst2TLM").Rows("1:8").Delete
    Sheets("Inst2TLM2").Columns("b:az").Delete
    Sheets("Inst2TLM2").Rows("1:8").Delete
    Sheets("VInst2TLM3").Columns("b:az").Delete
    Sheets("Inst2TLM3").Rows("1:8").Delete
    Sheets("Inst2TLM4").Columns("b:az").Delete
    Sheets("Inst2TLM4").Rows("1:8").Delete
    DelPics
    
End Sub
 
Sub DelPics()
        'timelines
    Sheets("CurrentTL").Columns("b:az").Delete
    Sheets("CurrentTL").Rows("1:8").Delete
    Sheets("Inst1TLM").Columns("b:az").Delete
    Sheets("Inst1TLM").Rows("1:8").Delete
    Sheets("Inst2TL").Columns("b:az").Delete
    Sheets("Inst2TL").Rows("1:8").Delete
    Sheets("Inst2TLM").Columns("b:az").Delete
    Sheets("Inst2TLM").Rows("1:8").Delete
    Sheets("Inst2TLM2").Columns("b:az").Delete
    Sheets("VInst2TLM2").Rows("1:8").Delete
    Sheets("Inst2TLM3").Columns("b:az").Delete
    Sheets("VInst2TLM3").Rows("1:8").Delete
    Sheets("Inst2TLM4").Columns("b:az").Delete
    Sheets("Inst2TLM4").Rows("1:8").Delete
    Sheets("PN").Rows("6:14").Hidden = False
    Sheets("InstComp").Columns("c:e").Hidden = False
    Sheets("InstComp").Rows("4:21").Hidden = False
    Sheets("ABC").Rows("15:48").Hidden = False
    Sheets("WFComp").Range("b6:b33").ClearContents
    
  'Delete pics on output pages
    Dim TLpic As Shape
    For Each TLpic In Sheets("Inst2TLM").Shapes
        If Intersect(TLpic.TopLeftCell, _
                 Sheets("Inst2TLM").Range("a1:bb100")) Is Nothing Then
            'do nothing
        Else
            TLpic.Delete
        End If
    Next TLpic
    For Each TLpic In Sheets("Inst1TLM").Shapes
        If Intersect(TLpic.TopLeftCell, _
                 Sheets("Inst1TLM").Range("a1:bb100")) Is Nothing Then
            'do nothing
        Else
            TLpic.Delete
        End If
    Next TLpic
    For Each TLpic In Sheets("WFComp").Shapes
        If Intersect(TLpic.TopLeftCell, _
                 Sheets("WFComp").Range("a1:bb100")) Is Nothing Then
            'do nothing
        Else
            TLpic.Delete
        End If
    Next TLpic
    For Each TLpic In Sheets("Growth").Shapes
        If Intersect(TLpic.TopLeftCell, _
                 Sheets("Growth").Range("a1:bb100")) Is Nothing Then
            'do nothing
        Else
            TLpic.Delete
        End If
    Next TLpic
    For Each TLpic In Sheets("LTCC").Shapes
        If Intersect(TLpic.TopLeftCell, _
                 Sheets("LTCC").Range("a1:bb100")) Is Nothing Then
            'do nothing
        Else
            TLpic.Delete
        End If
    Next TLpic
End Sub

Open in new window

aikimark

At this point, I suspect your UNDO stack is the memory culprit.

What you need to do is purge the UNDO stack in two areas. You might invoke the do-nothing code in the snippet or something else.
* Reset() timelines -- as you delete column ranges
* DelPics() -- as you delete entire rows/columns/shapes.

Reference:
http://excel.tips.net/Pages/T002463_Clearing_the_Undo_Stack_in_a_Macro.html

Sub ClearUndo()
    Range("A1").Copy Range("A1")
End Sub

Open in new window

Rich5150

ASKER

You're reference states that if the macro doesn't make any changes, then the undo stack will not be cleared. Don't the macros you mentioned make changes and thus clear the undo stack? Even if the columns are empty they are still deleted, which is a change. OR, are you saying that the process of deleting the rows, columns, etc. is occupying too much memory in the undo stack and thus crashing excel, so I need to clear out the undo stack at the end of each of these macros. Sorry for the confusion, but I haven't really run into this problem before.

I've also had someone tell me that having macros that were greater than 60Kb could be causing this problem, but this doesn't make sense to me since the crash isn't occurring when these really large macros are running. Do you advocate smaller macros to avoid memory issues? Thanks.

aikimark

There used to be a limit on routine size. That should have been removed in your runtime environment.

I think that the Undo stack isn't cleared until you exit your routine. That is why I suggested that you invoke another routine in the middle of your routine to see if that might help.

Also, you can monitor the memory usage with the Task Manager and include code-execution pauses via MsgBox statements.

Rich5150

ASKER

"Also, you can monitor the memory usage with the Task Manager and include code-execution pauses via MsgBox statements."

Can you give me a quick example of that? I assume that if I break some of those routines up into smaller routines I will accomplish the same thing (in terms of clearing out the undo stack). I really appreciate your help.

aikimark

We shall assume you do not need help using the Task Manager. Add enough columns to your view that you can see as much memory usage as possible.

    'timelines
msgbox "before Columns("b:az").Delete"
    Sheets("CurrentTL").Columns("b:az").Delete
msgbox "before Rows("1:8").Delete"
    Sheets("CurrentTL").Rows("1:8").Delete

Open in new window

aikimark

An alternative might be to have a DeleteRange subroutine that accepts a range and invokes its Delete method.

Rich5150

ASKER

I followed your advice and noticed the following. The file starts out at a baseline of between 30 and 40Mb of memory usage. As I input data and run scenarios through it, the memory used continues to build. After running about a dozen different scenarios through it, I saw it spike as high as 110Mb while the macro that generates the outputs was running. What I don't understand is why memory usage is continuing to build as different scenarios are entered. There is code there to reset the outputs, meaning that it deletes all data, pictures, etc. that are generated with each scenario. Furthermore, when I tried to run a file save on it after running all of these scenarios, I got a path/file access error and was unable to save the file without closing and reopening. What do you think is happening, and how can I avoid having the memory usage build? Is this another issue with the undo stack?

aikimark

Could be. I would have suggested you do file save to reset the Undo stack, but they've fixed that in Excel2007.

Rich5150

ASKER

I probably should have mentioned this up front but I am working with Excel 2003 PS3.

aikimark

It would be a temporary fix until you upgrade, but you should be able to force the Undo stack flush with a save of the file.

Rich5150

ASKER

I definitely understand that that would be solution, however, when I distribute this for use it will be a read-only file that can't be saved. Is there a way to do this without saving?

aikimark

Have you tried creating a separate routine for your deletes?

Rich5150

ASKER

I'm already working on that, but I don't think it's going to address the issue with memory usage. I'm still perplexed as to why this workbook continues to use more and more memory as it's used since previously entered data is deleted and I assume that once the macros have finished running that the undo stack is cleared. Other than the undo stack I don't know what else would cause it to be using more memory.

aikimark

Several reasons are possible:
* Microsoft has a garbage collection or memory leak problem and you have discovered it.
* The Undo stack documentation is wrong and the stack isn't really cleared by a macro invocation.
* The more data that exists in the worksheet prior to the start of the Reset, the greater the load on the Undo stack. There is probably not much of a memory load when deleting columns/rows with mostly empty cells. You can probably test this hypothesis with different scenarios.

====================
6. Are you testing these changes with a read-only file?
7. Is it possible to close and reopen the file, rather than delete these columns and rows?
8. Can you substitute column/row hiding for deleting?
9. Although this is more of a performance suggestion, set the application.screenupdating = false at the top of the reset() routine and back to true at the bottom of the routine.

Rich5150

ASKER

aikimark:
Thanks very much for all of your time and suggestions on dealing with this problem. I've tried a combination of a few things we've discussed (primarily creating more subroutines) and so far so good. Regarding your last handful of questions, 6, 7 and 8 are all no, and while 9 is a really great suggestion, this will apparently cause an issue with a copy and paste command that executes when the outputs are generated (at least in excel03). The one other thing I've wondered about has to do with declaring variables. I know that it's good programming to use option explicit and make declarations for all variables, but I have to confess to occassionally being lazy and simply referencing a cell value from time to time. To the best of your knowledge, does this have the potential to create memory issues? I know that some variable types use less memory, so I try to pay attention to that.

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rich5150

ASKER

Thanks very much for your time and effort on this problem.