This is a cross post from r/excel.
I intended to write a long post and then realized we are all too busy for my editorializing. So bottom line up front: If you use macros, and specifically a Personal.XLSB, find a way to back them up frequently! If any easy way doesn't exist or you are too lazy (like me) to do it manually then make 1, before you lose your precious macros.
I started writing macros last year and it has quickly become an obsession for work. Then 1 day last week my computer refused to boot up, a corrupted file. IT said they were unable (or unwilling) to recover any data (queue wailing and gnashing of teeth followed by heavy drinking). Thankfully I had shared a few of macros with folks on my team so not all was lost. But I needed a way to backup my macros, windows auto-backup wasn't an option and I wasn't going to do it manually.
To this end, I found and modified a Workbook macro that will automatically make a backup of my Personal.XLSB every time I save it:
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'use Before Save event as Before Close event will not be triggered by Personal
'location to backup Personal.XLSB to (network drive, etc.)
Const sBak As String = "(drive):\(filepath)\Personal.xlsb"
Application.DisplayAlerts = False
With Workbooks("Personal.xlsb")
.SaveCopyAs sBak & Format(Now(), "_yyyymmdd.bak")
.Save
End With
Application.DisplayAlerts = True
End Sub
If you use this, place it in ThisWorkbook in your Personal.XLSB and edit where you want the backup to save to. To restore the backup is quite easy: copy the .bak file into your XLSTART folder and rename the file replacing ".bak" with ".XLSB".
If my mistake can save even 1 person out there from the same fate than I will find solace in that knowledge.