Results 1 to 5 of 5

Thread: Separating out test code from main code

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,709
    Location
    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.
    Please take the time to read the Forum FAQ

  2. #2
    VBAX Regular
    Joined
    Mar 2017
    Posts
    23
    Location
    Quote Originally Posted by SamT View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •