Msgbox Crashing Excel Randomly

Question

Background

I have had this problem in many machines, different excel configurations and not linked to a specific code.
I like to provide users with a message box at the end of the code execution, like a "OK01: Performed succesfully!"

Problem

The messagebox crashes Excel instance randomly. There is no real way to debug it since when it is debugged it works as expected, but it only happens on random occasions: could be on the first run, after the second, on the fifth, on the tenth, etc; Programs on memory are the same or as many as possible to be closed; Excel instance with just one file opened or mutiple. Let us say, multiple common scenarios for a PC.

Considerations

Files are created from scratch, not even module import happens. Whenever I do a macro I use the following structure. It happens either if Call ExcelNormal is before or after the MessageBox; it happens even if it is the only messagebox on the execution too.

Code

Sub Sample()
If MsgBox("Please confirm that you want to run the following code", vbYesNo) = vbNo Then Exit Sub
Call ExcelBusy
Call Exec_CreateSheets("Sheet2")
Call Exec_ImportExcelFile("Dummy", Sheets(1).Range("A1"), True, True, True, "Sheet1", TxtPathForFile:="C:\Users\UserName\Desktop\Testfile.xlsx")
MsgBox "Ok01Exec_RoutinesToRun: Done!", vbOKOnly
Call ExcelNormal
End Sub
Sub ExcelNormal()
With Excel.Application
.Cursor = xlDefault
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
.AskToUpdateLinks = True
.StatusBar = False
.EnableEvents = True
End With
End Sub
Sub ExcelBusy()
With Excel.Application
.Cursor = xlWait
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
.AskToUpdateLinks = False
.StatusBar = False
.EnableEvents = False
End With
End Sub
Function Return_IsExcelFileLocked(ByVal TxtFile As String) As Boolean
On Error Resume Next
' If the file is already opened by another process,
' and the specified type of access is not allowed,
' the Open operation fails and an error occurs.
Open TxtFile For Binary Access Read Write Lock Read Write As #1
Close #1
' If an error occurs, the document is currently open.
If Err.Number <> 0 Then
' Display the error number and description.
MsgBox "Error01Return_IsExcelFileLocked: #" & Str(Err.Number) & " - " & Err.Description & ": If file is open please close it."
Return_IsExcelFileLocked = True
Err.Clear
End If
End Function
Sub Exec_ImportExcelFile(ByVal TxtSheetToCreate As String, ByVal RangeDataBegins As Range, ByVal IsNeededAsValuesOnly As Boolean, ByVal IsPartOfSubs As Boolean, ByVal IsImportedSheetVisible As Boolean, Optional ByVal TxtSheetToImport As String, Optional ByVal IsImportedFileNeededToBeDeleted As Boolean, Optional ByVal TxtPathForFile As String)
Dim WBToImport As Workbook
Dim WBOriginal As Workbook
Dim TxtFileToImport As String
Dim VarValueFromMsg As Variant
If TxtPathForFile = "" Then ' 2. If TxtPathForFile = ""
MsgBox "War01Exec_ImportExcelFile: If the file for the " & TxtSheetToCreate & " is opened, please close it before importing", vbExclamation
VarValueFromMsg = Application.GetOpenFilename(Title:="Please choose the " & TxtSheetToCreate & " file", fileFilter:=TxtSheetToCreate & " (*.xls;*.xlsx;*.xlsm;*.csv),*.xls;*.xlsx;*.xlsm;*.csv", ButtonText:=TxtSheetToCreate, MultiSelect:=False)
On Error GoTo Err01Exec_ImportExcelFile
If VarValueFromMsg = False Then Call ExcelNormal: End
Err01Exec_ImportExcelFile:
Else ' 2. If TxtPathForFile = ""
VarValueFromMsg = TxtPathForFile
End If ' 2. If TxtPathForFile = ""
If IsPartOfSubs = False Then Call ExcelBusy
Set WBOriginal = ThisWorkbook
TxtFileToImport = CStr(VarValueFromMsg)
If Return_IsExcelFileLocked(TxtFileToImport) = True Then Call ExcelNormal: End
Call Exec_CreateSheets(TxtSheetToCreate)
On Error GoTo Err02Exec_ImportExcelFile
Set WBToImport = Workbooks.Open(Filename:=TxtFileToImport, ReadOnly:=True)
If TxtSheetToImport = "" Then TxtSheetToImport = WBToImport.ActiveSheet.Name
Call Exec_ShowAllDataInSheet(TxtSheetToImport, WBToImport)
With WBToImport
Application.CutCopyMode = False
If IsNeededAsValuesOnly = True Then ' 1. If IsNeededAsValuesOnly = True
.Sheets(TxtSheetToImport).Range(.Sheets(TxtSheetToImport).Cells(RangeDataBegins.Row, RangeDataBegins.Column), .Sheets(TxtSheetToImport).Cells(.Sheets(TxtSheetToImport).Cells.SpecialCells(xlCellTypeLastCell).Row, .Sheets(TxtSheetToImport).Cells.SpecialCells(xlCellTypeLastCell).Column)).Copy
'.Sheets(TxtSheetToImport).Range(.Sheets(TxtSheetToImport).Cells(RangeDataBegins.Row, RangeDataBegins.Column), .Sheets(TxtSheetToImport).Cells(.Sheets(TxtSheetToImport).Cells.SpecialCells(xlCellTypeLastCell).Row, .Sheets(TxtSheetToImport).Cells.SpecialCells(xlCellTypeLastCell).Column)).Copy
WBOriginal.Sheets(TxtSheetToCreate).Cells(1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Else ' 1. If IsNeededAsValuesOnly = True
.Sheets(TxtSheetToImport).Range(RangeDataBegins.Address).CurrentRegion.Copy Destination:=WBOriginal.Sheets(TxtSheetToCreate).Cells(1, 1)
End If ' 1. If IsNeededAsValuesOnly = True
End With
WBToImport.Close False, False
DoEvents
Application.CutCopyMode = False
If IsImportedFileNeededToBeDeleted = True Then Kill (VarValueFromMsg)
WBOriginal.Activate
Sheets(TxtSheetToCreate).Visible = IsImportedSheetVisible
DoEvents
'trying to address memory leaks when called by subs
Set WBToImport = Nothing: Set WBOriginal = Nothing
If IsPartOfSubs = False Then Call ExcelNormal
If 1 = 2 Then ' 99. If error
Err02Exec_ImportExcelFile:
MsgBox "Err02Exec_ImportExcelFile: Excel could not find the file at '" & TxtFileToImport & "'. Make sure the file exists!" & Chr(10) & "Further Details: " & Err.Description, vbCritical: Call ExcelNormal: End
End If ' 99. If error
End Sub
Sub Exec_ShowAllDataInSheet(ByVal TxtSheet As String, Optional ByVal WBParent As Workbook)
If WBParent Is Nothing Then Set WBParent = ThisWorkbook
On Error GoTo Err01Exec_ShowAllDataInSheet
WBParent.Sheets(TxtSheet).Visible = True
On Error Resume Next
WBParent.Sheets(TxtSheet).ShowAllData
WBParent.Sheets(TxtSheet).EntireRow.Hidden = False
WBParent.Sheets(TxtSheet).EntireColumn.Hidden = False
'trying to address memory leaks when called by subs
Set WBParent = Nothing
If 1 = 2 Then ' 99. If error
Err01Exec_ShowAllDataInSheet:
MsgBox "Err01Exec_ShowAllDataInSheet: Sheet " & TxtSheet & " does not exists!", vbCritical: Call ExcelNormal: End
End If ' 99. If error
End Sub
Sub Exec_CreateSheets(ByVal NameSheet As String, Optional ByVal Looked_Workbook As Workbook)
If Looked_Workbook Is Nothing Then Set Looked_Workbook = ThisWorkbook
Dim SheetExists As Worksheet
On Error GoTo ExpectedErr01CreateSheets
Set SheetExists = Looked_Workbook.Worksheets(NameSheet)
SheetExists.Delete
ExpectedErr01CreateSheets:         'this means sheet didn't existed so, we are going to create it
With Looked_Workbook
.Sheets.Add After:=.Sheets(.Sheets.Count)
ActiveSheet.Name = NameSheet
'trying to address memory leaks when called by subs
Set Looked_Workbook = Nothing
End With
End Sub

There is no real consistent code that provokes this behaviour, so I insert an image to ilustrate it.

Question

I am not quite sure if a UserForm instead will address it, I am suspecting on some memory issue related to show the messagebox; is there a way to clean it somehow or to prevent his behaviour? I have tried to look for documentation in this regard but I have not found anything to this specific scenario.

Based on Cristian Buse Solution

I changed the End on the first message box but having the same results. I found a way for others to reproduce the behaviour, I edited my original code.

By logging "Manually" I am still unable to get a log on what is happening (although as I stated on comments, my guess as now is that it tries to display "out of memory" and crashes), I share my findings by using this neat solution

Log when crashed first run
[2021-11-05 11:43:17][Exec_CreateSheets] Before calling ExcelBusy
[2021-11-05 11:43:19][Exec_ImportExcelFile] After Importing Excel
[2021-11-05 11:43:27][Before messagebox] Before messagebox
[2021-11-05 11:43:27][ConsoleLog] 
Log when crashed on 7th run (as I said, it could happen on the nth run, the most annoying one is the first one)
[2021-11-05 11:43:32][Exec_CreateSheets] Before calling ExcelBusy
[2021-11-05 11:43:33][Exec_ImportExcelFile] After Importing Excel
[2021-11-05 11:43:35][Before messagebox] Before messagebox
[2021-11-05 11:43:35][ConsoleLog] 
[2021-11-05 11:43:35][ExcelNormal] 
[2021-11-05 11:43:36][Exec_CreateSheets] Before calling ExcelBusy
[2021-11-05 11:43:38][Exec_ImportExcelFile] After Importing Excel
[2021-11-05 11:43:39][Before messagebox] Before messagebox
[2021-11-05 11:43:39][ConsoleLog] 
[2021-11-05 11:43:39][ExcelNormal] 
[2021-11-05 11:43:40][Exec_CreateSheets] Before calling ExcelBusy
[2021-11-05 11:43:41][Exec_ImportExcelFile] After Importing Excel
[2021-11-05 11:43:43][Before messagebox] Before messagebox
[2021-11-05 11:43:43][ConsoleLog] 
[2021-11-05 11:43:43][ExcelNormal] 
[2021-11-05 11:43:44][Exec_CreateSheets] Before calling ExcelBusy
[2021-11-05 11:43:45][Exec_ImportExcelFile] After Importing Excel
[2021-11-05 11:43:47][Before messagebox] Before messagebox
[2021-11-05 11:43:47][ConsoleLog] 
[2021-11-05 11:43:47][ExcelNormal] 
[2021-11-05 11:43:48][Exec_CreateSheets] Before calling ExcelBusy
[2021-11-05 11:43:49][Exec_ImportExcelFile] After Importing Excel
[2021-11-05 11:43:50][Before messagebox] Before messagebox
[2021-11-05 11:43:50][ConsoleLog] 
[2021-11-05 11:43:50][ExcelNormal] 
[2021-11-05 11:43:51][Exec_CreateSheets] Before calling ExcelBusy
[2021-11-05 11:43:52][Exec_ImportExcelFile] After Importing Excel
[2021-11-05 11:43:54][Before messagebox] Before messagebox
[2021-11-05 11:43:54][ConsoleLog] 
[2021-11-05 11:43:54][ExcelNormal] 
[2021-11-05 11:43:55][Exec_CreateSheets] Before calling ExcelBusy
[2021-11-05 11:43:56][Exec_ImportExcelFile] After Importing Excel
[2021-11-05 11:43:57][Before messagebox] Before messagebox
[2021-11-05 11:43:57][ConsoleLog]

Basically when ConsoleLog ends on the last line is when it crashed, to be clear, on the first log it crashed on the 1st run for the code [2021-11-05 11:43:27][ConsoleLog] and on the second log crashed after the 7th run on the line [2021-11-05 11:43:57][ConsoleLog].

Sub Sample()
    If MsgBox("Please confirm that you want to run the following code", vbYesNo) = vbNo Then Exit Sub
    Call ExcelBusy
    Call Exec_CreateSheets("Sheet2")   
    LogTextToFile "Exec_CreateSheets", "Before calling ExcelBusy"
    Call Exec_ImportExcelFile("Dummy", Sheets(1).Range("A1"), True, True, True, "Sheet1", TxtPathForFile:="C:\Users\UserName\Desktop\Testfile.xlsx")
    LogTextToFile "Exec_ImportExcelFile", "After Importing Excel"
    LogTextToFile "Before messagebox", "Before messagebox"
    LogTextToFile "ConsoleLog", Err.Description
    MsgBox "Ok01Exec_RoutinesToRun: Done!", vbOKOnly
    Call ExcelNormal
    LogTextToFile "ExcelNormal", Err.Description
End Sub

@BigBen Yes, I do on most machines, some may have the Excel related ones (PowerPivot for example), Anaconda, some work related ones (not on all are the same since they are different machines, different workflow/access) — Sgdva, Nov 04 '21 at 19:55
I am not able to read the second messagebox in your "image" - what is stated there? — Ike, Nov 04 '21 at 19:56
@Ike It is the same routine so there are only 2 message boxes MsgBox("Please confirm that you want to run the following code", vbYesNo) = vbNo and then after execution "Ok01Exec_RoutinesToRun: Done!", vbOKOnly — Sgdva, Nov 04 '21 at 20:00
@Sgdva I agree with Ike, what is that "Ask" in the second `MsgBox`? — dwirony, Nov 04 '21 at 21:10
The 1st MsgBox say: "Ask01Exec_RoutinesToRun: Please confirm that you want to run the following: *-Exec_ExchangeVariation" | The 2nd MsgBox says: "Ok01Exec_RoutinesToRun: Done!" | @Sgdva: That means it's not exactly the same code as shown. Can you confirm (as suggested in your text) that the code as shown will evoke the crash? — Spinner, Nov 04 '21 at 21:42
@Spinner As stated, this happens in most code executions and as I explained I always start with those lines (it is not a specific code per say happens on odd basis to my codes), however, I found what is causing it. It seems it tries to display ["out of memory"](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/error-messages/out-of-memory-visual-basic-compiler-error) but the message itself makes it crash, I have researched about it and it seems more complex than I originally thought. — Sgdva, Nov 04 '21 at 22:33
I found a lot of overcomes/opinions to ["out of memory"](https://stackoverflow.com/questions/14396998/how-to-clear-memory-to-prevent-out-of-memory-error-in-excel-vba) solutions, none of them have worked. As for now, I have tried most of the solutions provided: Set objects to nothing, erase arrays on every sub that uses an array before exiting it, there is no real way to track it, or at least I haven't been able to track it (I checked memory usage and it happens even on files that roughly use 200k). Needless to say I do not use Selects ever and prioritize memory optimization. — Sgdva, Nov 04 '21 at 22:44
@Sgdva, I don't see any debugging steps to confirm it is the messagebox causing the issue and not the following 'procedure call'. I would want to stick in a 'debug.print' or the like on the line following just to confirm it is the messagebox causing the issue. — dra_red, Nov 04 '21 at 23:11
@dra_red as it can be shown, it is impossible to debug it because excel just crashes and exits without warning on random (the SS shown on the second execution, but it could happen on the first, on the second, on the fifth, etc.) I was able to get a messagebox after some attempts with a Userform and it seems it is trying to display out of memory, I left two comments in that regard. — Sgdva, Nov 04 '21 at 23:21
@Sgdva, I realise you have done your own troubleshooting and have come to the conclusion you have but I persist because it is a weird issue that I cannot remember seeing in the past... how about using something like: debug.print "made it this far" Application.Wait (Now() + TimeValue("00:00:10")) If Excel still shuts down before you can see the error message, then it is the message box. If it waits and then shuts down, then you can start looking at the procedure call. — dra_red, Nov 05 '21 at 01:05
Can you reproduce this If you create a new excel file, with only 1 sub and just `MsgBox "Test"`? — Raymond Wu, Nov 05 '21 at 01:45
@dra_red I do agree with you and have followed as you stated previously: I have tried to log it to the console; set a break point just before the message box, tried to log an err.description, but I'm unable to see the results due to the FC — Sgdva, Nov 05 '21 at 14:29
@RaymondWu It happens, this was a new file, I'm starting to think it's related to a sub that I have that handles [workbook open](https://www.google.com/search?q=workbooks+open+crashes+excel+on+multiple+calls&rlz=1C1GCEA_enUS968US968&oq=workboo&aqs=chrome.0.69i59j69i57j69i59l2j69i60l2.1553j0j7&sourceid=chrome&ie=UTF-8) but then again, everything is cleaned to optimize memory and it only does it for one workbook but I am unable to track other than that. — Sgdva, Nov 05 '21 at 14:32
@Sgdva Why don't you then try without having that `Workbook_Open` sub?? If the new file works well without it then you just found the culprit? (I'm assuming you have tried the answer below and it did not resolve your issue) — Raymond Wu, Nov 05 '21 at 14:37
@RaymondWu I tried it and didn't work, but yeah, as I dig I think that is the issue and probably could be related to Excel itself, for now the kind of workaround that works is [this one](https://stackoverflow.com/questions/57605613/out-of-memory-error-upon-closing-a-workbook-excel-vba), specifically the one that states to hide the IDE (even when it's not showed): it still crashes time to time but not that often — Sgdva, Nov 05 '21 at 18:13
@Sgdva, you still don't have the 'LogTextToFile' after the messagebox. If the messagebox displays crashes before you select 'ok', then it is definitely message box causing issue, but if the user selects click on the message box and then it crashes, it could still be the following routine causing the error. Stick a 'LogTextToFile' after the message box to be sure. — dra_red, Nov 06 '21 at 01:00
@dra_red thank you, I'm the user at the moment, and the messagebox doesn't show up, it just crashes so no ok can be clicked. — Sgdva, Nov 08 '21 at 14:41

Cristian Buse · Answer 1 · 2021-11-05T09:43:30.607

First of all you should not use End on it's own. It clears the whole state (all variables lose value throughout the project) and might just be the cause of all your issues. Instead use Exit Sub or Exit Function. Replace:

If MsgBox("Please confirm that you want to run the following code", vbYesNo) = vbNo Then End

with:

If MsgBox("Please confirm that you want to run the following code", vbYesNo) = vbNo Then Exit Sub

If that does not fix your issue then the following code will help you with debugging:

Option Explicit

'Rudimentary Logging
Const LOG_FILE_PATH As String = "C:\Users\<yourUserName>\Desktop\RudLog.txt"


Public Sub LogTextToFile(ByVal procName As String, ByVal textToLog As String)
    Dim fileNumber As Long: fileNumber = FreeFile
    '
    On Error Resume Next
    Open LOG_FILE_PATH For Append Access Write Lock Write As fileNumber
    Print #fileNumber, "[" & Format$(Now(), "yyyy-mm-dd hh:mm:ss") & "][" & procName & "] " & textToLog
    Close fileNumber
    On Error GoTo 0
End Sub

Public Sub ClearLogFile()
    Dim fileNumber As Long: fileNumber = FreeFile
    '
    On Error Resume Next
    Open LOG_FILE_PATH For Output Access Write Lock Write As fileNumber
    Close fileNumber
    On Error GoTo 0
End Sub

Just replace the LOG_FILE_PATH value with a valid path. The text file name can be anything. The file will be created for you as long as the folder is valid and there are valid characters in the file name.

Your Sample procedure can then become:

Sub Sample()
    If MsgBox("Please confirm that you want to run the following code", vbYesNo) = vbNo Then Exit Sub
    LogTextToFile "Sample", "Before calling ExcelBusy"
    Call ExcelBusy
    LogTextToFile "Sample", "After calling ExcelBusy"
    'code
    LogTextToFile "Sample", "Before MsgBox"
    MsgBox "Ok01Exec_RoutinesToRun: Done!", vbOKOnly
    LogTextToFile "Sample", "After MsgBox"
    'code
    LogTextToFile "Sample", "Before calling ExcelNormal"
    Call ExcelNormal
    LogTextToFile "Sample", "After calling ExcelNormal"
End Sub

On my computer, the above write the following to the text file:

[2021-11-05 09:05:56][Sample] Before calling ExcelBusy
[2021-11-05 09:05:56][Sample] After calling ExcelBusy
[2021-11-05 09:05:56][Sample] Before MsgBox
[2021-11-05 09:05:57][Sample] After MsgBox
[2021-11-05 09:05:57][Sample] Before calling ExcelNormal
[2021-11-05 09:05:59][Sample] After calling ExcelNormal

Of course, you can add as many logging lines in the other methods to see exactly what is the last line that works before crashing.

Edit #1

As suggested by @Ike in the comments section, long lines of code impact readability and make issues harder to spot.

A good replacement for:

If MsgBox("Please confirm that you want to run the following code", vbYesNo) = vbNo Then Exit Sub

could be:

Dim res As VbMsgBoxResult
res = MsgBox(Prompt:="Please confirm that you want to run the following code" _
           , Buttons:=vbYesNo _
           , Title:="Please confirm")
If res = vbNo Then Exit Sub

or even:

If MsgBox(Prompt:="Please confirm that you want to run the following code" _
        , Buttons:=vbYesNo _
        , Title:="Please confirm") = vbNo Then Exit Sub

Well spotted: "End" - again an example why one should avoid long code lines. — Ike, Nov 05 '21 at 09:13
@Ike Indeed. I try to avoid long lines for the sake of readability and maintenance. I once saw ```End``` crashing someone's VBA project but only after running ```End``` exactly 5 times. As it was weird, I offered to investigate and later found that he had some code in another module that was copying some memory for some objects but he was doing it in an unmanaged way so eventually a GPF error was occurring. He was getting random crashes all the time and only by chance he figured out the 5 time End case which lead us to the issue. In the Op's case might be something similar. — Cristian Buse, Nov 05 '21 at 09:17
Propably you should edit your example - and break the msgbox line accordingly - to show the better readability :-) — Ike, Nov 05 '21 at 09:21
@IKe Correct. I initially had ```res = MsgBox(...) = VbNo```. Good catch — Cristian Buse, Nov 05 '21 at 09:42
First of all, nice and creative way of debugging for this scenario. I took your advice on changing end to Exit Sub but the behaviour still happens. The way to log it saddly didn't fix it nor let me to results/other clues. See my updated question. — Sgdva, Nov 05 '21 at 16:10
@Sgdva I could not crash Excel using your updated code. Since I cannot replicate on my side, it is very difficult to pinpoint the issue. I saw that you don't have a log call after the ```Call ExcelNormal``` line. Could you try to add one and then crash to see if ExcelNormal finishes execution before crashing? Something like ```LogTextToFile "Sample", "After calling ExcelNormal"``` right before ```End Sub``` — Cristian Buse, Nov 05 '21 at 17:27
@Sgdva It looks like ```[ExcelNormal]``` was not logged on the first crash but it was on the 7th. Is that correct or did you delete the line by mistake? — Cristian Buse, Nov 05 '21 at 20:54
@CristianBuse It was not logged because it crashed before reaching there, as it did on the first one after the ConsoleLog the ExcelNormal should occur, but it didn't. — Sgdva, Nov 05 '21 at 22:04
@Sgdva Interesting. Then I suggest you should add log lines inside the ExcelNormal method after each statement. Maybe one of them is causing issues. — Cristian Buse, Nov 06 '21 at 08:30

Msgbox Crashing Excel Randomly

1 Answers1