Excel crashes when opening multiple documents

drnaphtali · Nov 8th 2010

Hello all,

I wrote a macro in a "Summary" workbook, which loops through design documents (where the name of the design document is written in a column of the summary workbook, and each document is denoted by

Code

Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 1).Value

in my code) and fetch some data from each workbook and inserts the value in a cell. Each of the design documents have the same format.

My problem is that when the macro tries to open the 6th document, excel crashes. I've tried doing this on a seperate machine and the same happens (i think on the other machine it crashed after the 8th document). I then tried adding a button which replaces the loop. Instead of looping, each time the button is pressed it adds the information of the next document, but to no avail it still crashed.

I tried looking into methods of clearing memory since I suspect that this is the problem.

My code to follow. The code for the button is the same, less the for loop. I created a class which fetches the relevant data from each document, please let me know if you would like this included.

Code

Option Explicit
Public workbookname As String
 
Sub update_Click()
 
    workbookname = ThisWorkbook.name
 
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim myworkbook As Range
 
    Dim i As Integer
 
    Set sh2 = Workbooks("AI HVL AHU Summary v1").Sheets("Sheet1")
    Application.ScreenUpdating = False
 
    Dim curRoom As sumRoom
    Set curRoom = New sumRoom
 
    For i = 5 To 7 'sh2.UsedRange.Rows.Count
 
        If Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 1).Value <> Empty Then
 
            Set myworkbook = Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 1)
 
            Workbooks.Open ThisWorkbook.Path & "\" & Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 1).Value
 
            'Set sh1 = Workbooks(myworkbook.Value).Sheets("Detail RDS")
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 3).Value = curRoom.Area(i, myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 4).Value = curRoom.Volume(i, myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 5).Value = curRoom.Temp(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 6).Value = curRoom.RH(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 7).Value = curRoom.SA(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 8).Value = curRoom.RA(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 9).Value = curRoom.FA(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 10).Value = curRoom.EA(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 11).Value = curRoom.Cooling(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 12).Value = curRoom.TCL(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 13).Value = curRoom.BP(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 14).Value = curRoom.PreHeating(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 15).Value = curRoom.CW(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 16).Value = curRoom.HW(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 17).Value = curRoom.AHUType(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 18).Value = curRoom.AHUFilters(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 19).Value = curRoom.AHUPA(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 20).Value = curRoom.AHUkW(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 21).Value = curRoom.AHUlenght(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 22).Value = curRoom.AHUwidth(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 23).Value = curRoom.AHUheight(myworkbook.Value)
 
            Workbooks(myworkbook.Value).Close SaveChanges:=False
            'Workbooks(workbookname).Activate
        End If
    Next i
 
End Sub

Display More

Below is the code for the button which includes an attempt to clear the memory:

Code

Sub insertData(i As Integer)
    
    Application.ScreenUpdating = False
    
    workbookname = ThisWorkbook.name
    
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim myworkbook As Range
    
    Set sh2 = Workbooks("AI HVL AHU Summary v2").Sheets("Sheet1")
    
    Dim curRoom As sumRoom
    Set curRoom = New sumRoom
     
        If Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 1).Value <> Empty Then
        
            Set myworkbook = Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 1)
            
     '       On Error GoTo ending
            Workbooks.Open ThisWorkbook.Path & "\" & Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 1).Value
    
            'Set sh1 = Workbooks(myworkbook.Value).Sheets("Detail RDS")
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 3).Value = curRoom.Area(i, myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 4).Value = curRoom.Volume(i, myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 5).Value = curRoom.Temp(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 6).Value = curRoom.RH(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 7).Value = curRoom.SA(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 8).Value = curRoom.RA(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 9).Value = curRoom.FA(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 10).Value = curRoom.EA(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 11).Value = curRoom.Cooling(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 12).Value = curRoom.TCL(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 13).Value = curRoom.BP(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 14).Value = curRoom.PreHeating(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 15).Value = curRoom.CW(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 16).Value = curRoom.HW(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 17).Value = curRoom.AHUType(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 18).Value = curRoom.AHUFilters(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 19).Value = curRoom.AHUPA(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 20).Value = curRoom.AHUkW(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 21).Value = curRoom.AHUlenght(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 22).Value = curRoom.AHUwidth(myworkbook.Value)
            Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 23).Value = curRoom.AHUheight(myworkbook.Value)
            
            Workbooks(myworkbook.Value).Close SaveChanges:=False
            'Workbooks(workbookname).Activate
        End If
        
    Application.ScreenUpdating = True
    
    Set sh1 = Nothing
    Set sh2 = Nothing
    Set myworkbook = Nothing
'    Exit Sub
'ending:
    
'    MsgBox ("Error opening; Make sure the source file is in the same folder as the summary sheet")
    
End Sub

Display More

Rob Xaos · Nov 8th 2010

Re: Excel crashes when opening multiple documents

User classes are a common source of memory leaks so would be an idea to include the class.

Is there any error message when Excel crashes?

drnaphtali · Nov 8th 2010

Re: Excel crashes when opening multiple documents

Hello Rob, thank you for the reply mate.

Here is my class

Code

Option Explicit
Function Area(row As Integer, name As String) As Double
 
    Dim Sheet As Worksheet
    Set Sheet = Workbooks(name).Sheets("Detail RDS")
    Dim sumRange As Range
    Set sumRange = Sheet.Range(Cells(6, 6).Address, Cells(Sheet.UsedRange.Rows.Count, 6).Address)
 
    Area = WorksheetFunction.Sum(sumRange)
 
    Set Sheet = Nothing
    Set sumRange = Nothing
End Function
Function Volume(row As Integer, name As String) As Double
    Dim Sheet As Worksheet
    Set Sheet = Workbooks(name).Sheets("Detail RDS")
    Dim sumRange As Range
    Set sumRange = Sheet.Range(Cells(6, 9).Address, Cells(Sheet.UsedRange.Rows.Count, 9).Address)
 
    Volume = WorksheetFunction.Sum(sumRange)
 
    Set Sheet = Nothing
    Set sumRange = Nothing
End Function
Function Temp(name As String) As Double
 
    Temp = Range(Workbooks(name).Names("Tdb_rm")).Value
End Function
Function RH(name As String) As Double
 
    RH = Range(Workbooks(name).Names("RH_rm")).Value
End Function
Function SA(name As String) As Double
 
    SA = Range(Workbooks(name).Names("SA")).Value
End Function
Function RA(name As String) As Double
 
    RA = Range(Workbooks(name).Names("PLRA")).Value
End Function
Function FA(name As String) As Double
 
    FA = Range(Workbooks(name).Names("FA")).Value
End Function
Function EA(name As String) As Double
 
    EA = Range(Workbooks(name).Names("FA")).Offset(1, 0).Value
End Function
Function Cooling(name As String) As Double
 
    Cooling = Range(Workbooks(name).Names("SAF")).Offset(-1, 1).Value
End Function
Function TCL(name As String) As Double
 
    TCL = Range(Workbooks(name).Names("TCL")).Value
End Function
Function BP(name As String) As Double
 
    BP = Range(Workbooks(name).Names("BP")).Value
End Function
Function PreHeating(name As String) As Double
    Dim cell As Range
    Dim sh As Worksheet
 
    Set sh = Workbooks(name).Sheets("Load_Calc")
 
    Dim i As Integer
 
    For i = Range(Workbooks(name).Names("TBP")).Rows.row To sh.UsedRange.Rows.Count
 
        If Workbooks(name).Sheets("Load_Calc").Cells(i, 1).Value = "ADDITIONAL PRE-HEAT (to reduce terminal re-heaters)" Then
 
            PreHeating = Workbooks(name).Sheets("Load_Calc").Cells(i, 5).Value
 
        End If
 
    Next i
 
    Set sh = Nothing
 
End Function
Function CW(name As String) As Double
    Dim cell As Range
 
    Dim i As Integer
 
    i = Range(Workbooks(name).Names("SAF")).Rows.row
 
    CW = Workbooks(name).Sheets("Load_Calc").Cells(i, 13).Value
 
End Function
Function HW(name As String) As Double
    Dim cell As Range
    Dim sh As Worksheet
 
    Set sh = Workbooks(name).Sheets("Load_Calc")
 
    Dim i As Integer
 
    For i = Range(Workbooks(name).Names("TBP")).Rows.row To sh.UsedRange.Rows.Count
 
        If Workbooks(name).Sheets("Load_Calc").Cells(i, 15).Value = "Hot water" Then
 
            HW = Workbooks(name).Sheets("Load_Calc").Cells(i, 18).Value
 
        End If
 
    Next i
 
    Set sh = Nothing
 
End Function
Function AHUType(name As String) As String
 
    AHUType = Workbooks(name).Sheets("AHU").Cells(17, 8).Value
 
End Function
Function AHUFilters(name As String) As Double
 
    AHUFilters = Workbooks(name).Sheets("AHU").Cells(57, 3).Value
 
End Function
Function AHUPA(name As String) As Double
 
    AHUPA = Workbooks(name).Sheets("AHU").Cells(24, 8).Value
 
End Function
Function AHUkW(name As String) As Double
 
    AHUkW = Workbooks(name).Sheets("AHU").Cells(29, 8).Value
 
End Function
Function AHUlenght(name As String) As Double
 
    AHUlenght = Workbooks(name).Sheets("AHU").Cells(80, 8).Value
 
End Function
Function AHUwidth(name As String) As Double
 
    AHUwidth = Workbooks(name).Sheets("AHU").Cells(81, 8).Value
 
End Function
Function AHUheight(name As String) As Double
 
    AHUheight = Workbooks(name).Sheets("AHU").Cells(82, 8).Value
 
End Function

Display More

The program physically crashes "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience ..."
It then closes, opens back up and displayes a recovered document.

I forgot to add, when I run the button 5 times, and restart the document, I can add another 5 sets of documents.

Thank you so much for the help, this proplem is very frustrating.

drnaphtali · Nov 8th 2010

Re: Excel crashes when opening multiple documents

Maby the leak is in the class methods which loops through the columns of the external sheets? I do not know why though. I know for a fact the program crashes AS SOON AS THE workbookname.open command is run. It does not even try to enter the rest of the code.

Leon

Rob Xaos · Nov 8th 2010

Re: Excel crashes when opening multiple documents

This may be very hard for us to diagnose, given the nature of the problem. If there are more class methods then you really need to upload all of them. Don't worry about the adverts they are always on the first and last posts of a page.

One thing I did notice is that in functions: PreHeating, CW and HW you are declaring Cell as a Range but not setting it to Nothing at the end. Technically Cell is an object so this is probably a small memory leak. If you have anything similar but inside a loop then this may be the problem. If you want to be 100% safe then every declared variable should be set to Nothing before leaving a function/procedure.

Something else to try is to set the VBE to break on all errors: Tools > Options > General > Error Trapping. That may enable you to catch an error in the class module that is causing a problem but not being highlighted until it has killed Excel.

drnaphtali · Nov 9th 2010

Re: Excel crashes when opening multiple documents

In the insertData sub I set the ranges equal to nothing. Maybe its best to ignore the update_click sub, since I only updated my code in the button for debuggin purposes.

That is all the code I have on this workbook. The problem has to be there somewhere. Thank you for all the help Rob.

Regards,
Leon

Rob Xaos · Nov 9th 2010

Re: Excel crashes when opening multiple documents

Try my suggestion above about changing the error trapping and see if that gives further insight. Report back if that raises errors and where they occur.

drnaphtali · Nov 9th 2010

Re: Excel crashes when opening multiple documents

Hey Rob,

Thanks again for your time. I did as you suggested. The code did not break anywhere.

What I did do though, is to put a break on the line

Code

Workbooks.Open ThisWorkbook.Path & "\" & Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 1).Value

and when i press "F8" (the 6th time) it doesnt move to the next line, it just CRASHES.

Mind boggling stuff...

Regards,
Leon

drnaphtali · Nov 9th 2010

Re: Excel crashes when opening multiple documents

It might be worth noting that, the design documents where the summary workbook gets its information from, is quite large with several complex calculations each. I do not have a problem opening each individual document though. Its just that if I have a few open at the same time, my computer becomes extremely slow.

Leon

Excel crashes when opening multiple documents

Participate now!

Share

Users Viewing This Thread