Workbook slow to close - any obvious issues with the code?

[Ironman]

Hi

This is my workbook_before close event code.

I'd be grateful to know if the 20 seconds or so it takes to close can be expected with the below code or if there's something obvious that can be amended to speed it up?

Many thanks!

Private Sub workbook_beforeclose(Cancel As Boolean)

Dim MsgResult As Integer

Application.EnableEvents = False

MsgResult = MsgBox("Are you SURE you want to overwrite the master Exercise Log file?   ", vbYesNoCancel + vbExclamation, "WARNING")

Select Case MsgResult

    Case vbNo
        'a) If you select No, you get a second dialog box saying "Existing file unchanged" and the workbook as well as Excel will close.
        
        'checks to see if other workbooks are open.  It will not shut down the application if there are other workbooks open
        If Application.Workbooks.Count < 2 Then
            MsgBox "Master file unchanged - data NOT saved" & vbNewLine _
            & "" & vbNewLine _
            & "Exercise Log will now close", vbInformation, "Master File Unchanged "
            'the following line does not save the workbook but sets a bit that tells Excel that any changes have already been saved, even if the changes were not actually saved
            ThisWorkbook.Saved = True
            Application.Quit
        Else
            'other workbooks are open.  Leave the application alone and
            'simply close this workbook.
            MsgBox "Other workbooks open - data NOT saved!" & vbNewLine _
            & "" & vbNewLine _
            & "Exercise Log will now close!", vbInformation, "Master File Unchanged "
            Me.Close False
        End If
        
    Case vbCancel
        'b) If I select Cancel then there's no action, the dialog box closes and the workbook remains open as if nothing had happened.
        
        Cancel = True
        'simply cancel the closing of this workbook
         
    Case vbYes
        'c) If I select Yes, then the new data is saved, a second dialog box appears + vbconf with existing data overwritten.
        '   The file and Excel then close.
   
'Bernie Dietrick
Dim bdFileName As String
Dim FullFileName  As String

Application.DisplayAlerts = False

FullFileName = ActiveWorkbook.FullName
bdFileName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)

If LCase(Application.UserName) <> "jsullivan" Then
   ActiveWorkbook.SaveCopyAs Filename:="E:\BACKUPS\Exercise Log\" & _
      bdFileName & " Backup - " & Format(Now, "dddd dd mmmm yyyy, h.mmam/pm") & _
      ".xlsm"

   'Backup to 128gb USB drive as well
   ActiveWorkbook.SaveCopyAs Filename:="Y:\DOCUMENTS\EXERCISE LOG\Exit Backups\" & _
      bdFileName & " Backup - " & Format(Now, "dddd dd mmmm yyyy, h.mmam/pm") & _
      ".xlsm"

End If

ActiveWorkbook.SaveAs Filename:=FullFileName, AddToMru:=False

Application.DisplayAlerts = True

        Worksheets("Training Log").[H1:H9] = vbNullString
        'Worksheets("Analysis").[G1:Z1] = vbNullString
        'Worksheets("Iron Man Log").[G1:Z1] = vbNullString
        Worksheets("Daily Tracking").[CI1:CZ1] = vbNullString
        Worksheets("Indoor Bike").[I1:I2] = vbNullString
        
        'Application.EnableEvents = True 'commented out 20.09.2021
        MsgBox "New backup files created in" & vbNewLine & vbNewLine _
        & "E:\BACKUPS\EXERCISE LOG    " & vbNewLine & vbNewLine _
        & "Y:\DOCUMENTS\EXERCISE LOG\EXIT BACKUPS" & vbNewLine & vbNewLine _
        & "Exercise Log will now close", vbInformation, "Master File Overwritten"
        
         ThisWorkbook.Saved = True
         'Application.EnableEvents = True
        
End Select

Application.EnableEvents = True

End Sub

 

[Gokhan Aycan]

What's the size of the workbook that's being saved? Depending on the user, it is being saved 3 times.

 

[Ironman]

Hi again Gokhan!

It's 41mb.

I intentionally create 2 backups (I'm an eternal pessimist ) - is the other instance the file itself?

Why do you think this line was created for me? Isn't that the Excel default?

ActiveWorkbook.SaveAs Filename:=FullFileName, AddToMru:=False

 

[Ironman]

Also, are the "Application.EnableEvents = True" statements in the correct places?

 

[Michael M]

I hate to say "I told you so"....but I think you are flirting with it's limit at the moment.
Why not put in a breakpoint at the 1st MsgBox, then step through using F8.....that will at least give you some idea how long each step is taking

 

[Ironman]

Thanks as ever Michael - I don't have an issue with the tasks Excel is performing on close; it takes as long as it takes. I was more wanting to know if the coding was as efficient as it could be, which is beyond my knowledge.

 

[Michael M]

As @Gokhan Aycan has already metioned, multiple saves will take a little longer !
Why are these being NullStrings....if they are simply text / numbers in the cells, why not clear them...is it a formatting issue
Otherwise looks OK to me

 

[Michael M]

Sorry, I meant...

Worksheets("Training Log").[H1:H9] = vbNullString
        'Worksheets("Analysis").[G1:Z1] = vbNullString
        'Worksheets("Iron Man Log").[G1:Z1] = vbNullString
        Worksheets("Daily Tracking").[CI1:CZ1] = vbNullString
        Worksheets("Indoor Bike").[I1:I2] = vbNullString

 

[Ironman]

Yes, I understand, those lines clear any '1's generated in those ranges when a value is exceeded (and I'm then alerted with a msgbox). Although I will have amended the code to the next relevant value, the nullStrings clear any 1's that I might forget to do manually, which would otherwise make the code fire again because there's still a 1 in the cell.

Great, that's really all I needed to know; the delay is down to what I'm asking Excel to do rather than the way the code is written - thanks a lot Michael.

 

[Michael M]

You could get rid of some MsgBoxes....but we have had that discussion previously.....