Separating out test code from main code

[SamT]

The only remedy seems to be to go to an earlier version of the file - saved before you tried to add the reference.

If you do enough coding to use a third party code tester, and need to save many "micro" versions, here's what I use in My Personal.xls

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'If I forget to save before closing. Provides a warning, in case I really don't want the latest changes.
    If Not Me.Saved Then Me.Save
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
'Everytime the File is saved
    Me.SaveCopyAs ("E:\COMPUTING\Programming\_VBA\MyPersonal\" & CDbl(Now) & "- Personal.xls")
End Sub

When I actually want to SaveAs a new version, I just do that normally from the Menu.

[neilt17]

If you do enough coding to use a third party code tester, and need to save many "micro" versions, here's what I use in My Personal.xls

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'If I forget to save before closing. Provides a warning, in case I really don't want the latest changes.
    If Not Me.Saved Then Me.Save
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
'Everytime the File is saved
    Me.SaveCopyAs ("E:\COMPUTING\Programming\_VBA\MyPersonal\" & CDbl(Now) & "- Personal.xls")
End Sub

When I actually want to SaveAs a new version, I just do that normally from the Menu.

Thank you SamT - that's a great idea.

I found that I needed to modify this slightly for PowerPoint as when you call .SaveCopyAs from the equivalent PresentationBeforeSave event handler I get an error "Presentation (unknown member): Failed". A solution involving timers (posted here: https://www.pcreview.co.uk/threads/e...aveas.2839232/) did not work for me. So instead I call .SaveCopyAs from within a PresentationSave event handler - with a check for a recent save to prevent looping:

MyEventsModule:

Option Explicit

Public m_oMyEvents As CMyEvents

Private Sub Auto_Open()
  If m_oMyEvents Is Nothing Then
    Set m_oMyEvents = New CMyEvents
  End If
  Set m_oMyEvents.PPTEvent = Application
End Sub

CMyEvents class:

Option Explicit

Public WithEvents PPTEvent As Application
Private m_strSaveMinute As String

Private Sub PPTEvent_PresentationSave(ByVal Pres As Presentation)
  Dim strNowMinute As String
  strNowMinute = Format(Now, "yyyy-mm-dd-hh-nn")
  If strNowMinute <> m_strSaveMinute Then
    ' This required to prevent an infinite loop as the SaveCopyAs event also
    ' triggers the PresentationSave event.
    Application.ActivePresentation.SaveCopyAs Environ("HOMEPATH") & _
      "\Documents\FileBackups\PowerPoint\" & "mybackup" & "-" & _
      Format(Now, "yyyy-mm-dd-hh-nn-ss") & ".pptm", _
      ppSaveAsOpenXMLPresentationMacroEnabled
  End If
  m_strSaveMinute = Format(Now, "yyyy-mm-dd-hh-nn")
End Sub