Excel crashes on workbook.close?

Question

I have a VBA function that will crash excel when I run it ("Microsoft Excel has stopped working"), but works fine if I step through each line in the editor. I want to open another workbook with the function, change the slicers on a given sheet, find a value, and then close the new workbook without saving it. The function I have is as follows:

Function ValueLookup()

Dim Check As Boolean
Dim NewBook As Workbook

Check = IsWorkBookOpen("C:\Location\Filename")
If Check = False Then
    Set NewBook = Workbooks.Open("C:\Location\Filename")
Else
    Set NewBook = Workbooks("Filename")
End If

NewBook.Activate
Worksheets("Sheetname").Activate

ActiveSheet.PivotTables("pivottable1")ManualUpdate = True

With ActiveWorkbook.SlicerCaches("Slicer_SlicerName")
    .ClearManualFilter
    .VisibleSlicerItemsList = Array("[Tablename].[Columnname].&[MyValue]")
End With

ActiveSheet.PivotTables("pivottable1").ManualUpdate = False

ValueLookup = ActiveSheet.Cells.Find(What:="*", _
                                     After:=Range("A1"), _
                                     LookAt:=xlPart, _
                                     LookIn:=xlFormulas, _
                                     searchorder:=xlByRows, _
                                     searchdirection:=xlPrevious, _
                                     MatchCase:=False)

If Check = False Then NewBook.Close savechanges:=False

End Function

(IsWorkBookOpen() is taken from Detect whether Excel workbook is already open, and doesn't seem to present any issues).

It seems that trying to close the new workbook is what's causing the issue, and if the workbook is open before I run the function there is no issue. The subroutine that calls the function will normally execute the line after the function right before excel crashes (opening a MsgBox for user input). I have tried adding Sleep 1000 after each line and adding DoEvents after the last line with no change in results. I've tried changing

NewBook.Close savechanges:= False

to

Application.Windows("NewBook").Close

without any difference. I've also tried replacing the end with

If Check = False Then
    Cancel = True
    Application.OnTime Now, "Close_Xls"
End If

End Function

Sub Close_Xls()
    ThisWorkBook.Close savechanges:=False
End Sub

but this closes the wrong workbook (the original one I'd like the macro to be run from) after my subroutine has finished. However, it does seem to execute the code successfully before closing the wrong workbook, and does not cause excel to crash.

Any suggestions?

Edit:

It now appears to work without issues. I changed the following:

If Check = False Then NewBook.Close savechanges:=False

End Function

To

NewBook.Saved = True
    If Check = False Then
        Cancel = True
        Application.OnTime Now, "Close_Xls"
    End If

End Function

Sub Close_Xls()

    Workbooks("Filename").Close savechanges:=False

End Sub

Thanks!

I had what I think may be a similar issue and found that I had to re-create the file of the workbook being opened (and crashing on close) to solve it after exploring SOOO many other possible solutions. — Scott Holtzman, Sep 14 '17 at 15:36
While I am not sure what may be causing the problem, you might try DoEvents before closing the file. It sounds like you have some sort of timing issue. DoEvents will turn over control to the operating system to complete any pending tasks. There is much debate over using DoEvents, but it might help you here. — Fred, Sep 14 '17 at 15:36
Try `NewBook.Saved = True` before closing. Also try `Workbooks("Filename").Close savechanges:=False` in your `Close_Xls()` sub. — robinCTS, Sep 14 '17 at 15:58
@robinCTS I added both changes and it now appears to work without any issues, thanks! — Jack, Sep 14 '17 at 17:12
Can you try with only one change at a time and let me know if any work by themselves? — robinCTS, Sep 14 '17 at 17:58
@robinCTS Yes, I removed `NewBook.Saved = True` and it still worked. However, it did not work without the second change. — Jack, Sep 15 '17 at 14:17
Yep. Just as I expected. Thanks.This is just a workaround, though. It's still unclear *why* the function is crashing Excel. I may try to see if I can recreate the issue on my computer. Any chance of uploading the example workbooks, to save me some time? — robinCTS, Sep 15 '17 at 14:52
@robinCTS Unfortunately, the workbooks I'm working with are confidential and I'm hesitant to upload anything. Both workbooks are stored on shared drives and use PowerPivot tables, if that's of any help. — Jack, Sep 15 '17 at 15:57

score 1 · Answer 1 · answered Sep 14 '17 at 17:27

Changing the Close_Xls() sub to the following will target the correct sheet to close:

Sub Close_Xls()
    Workbooks("Filename").Close savechanges:=False
End Sub

Another way to close the workbook without saving that might work is to use:

NewBook.Saved = True

before closing.

Excel crashes on workbook.close?

1 Answers1