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

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
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!

VBA Code:
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
What's the size of the workbook that's being saved? Depending on the user, it is being saved 3 times.
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
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?
VBA Code:
ActiveWorkbook.SaveAs Filename:=FullFileName, AddToMru:=False
 
Last edited:

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
Also, are the "Application.EnableEvents = True" statements in the correct places?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

I hate to say "I told you so":cool:....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

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Sorry, I meant...
VBA Code:
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

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
You could get rid of some MsgBoxes....but we have had that discussion previously.....;);)
 

Forum statistics

Threads
1,147,958
Messages
5,744,043
Members
423,841
Latest member
barren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top