Temporarily set default file type in Save As dialogue Excel

Question

I'm making a template for macro addins in Excel. Addins have this weird thing that when you close them Excel won't prompt you to save. So I want all my addins to inherit from a template with this code:

Private Sub Workbook_BeforeClose(ByRef Cancel As Boolean)
    'check for save because Excel doesn't prompt for addins
    'but save will fail if this is a new file, so fall back to default prompt then
    If Me.Path <> vbNullString And Not Me.Saved Then
        Select Case MsgBox("Wanna save before you quit?", vbQuestion + vbYesNoCancel, "Unsaved Changes")
            Case VbMsgBoxResult.vbYes
                Me.Save
            
            Case VbMsgBoxResult.vbCancel
                Cancel = True
                
        End Select
    End If
End Sub

Now I would also like all files made with this template to be set to be addins:

'one shot ideally
Private Sub Workbook_Open()
    'only run on new files
    If Me.Path = vbNullString Then Me.IsAddin = True
End Sub

The problem is though for files created from macro enabled templates .xltm, Excel defaults to saving as .xlsm - not .xlam addin files which is required for IsAddin to be True

So I can add:

Private Sub Workbook_Open()
    If Me.Path = vbNullString Then
        Me.IsAddin = True
        Application.DefaultSaveFormat = xlOpenXMLAddIn
    End If
End Sub

But that sets the default for all files in Excel. I only want to temporarily change the default until this file is either saved or discarded without saving. Workbook_BeforeSave runs too late, after the ui is shown with xlsm not xlam.

Any ideas? I don't want to save immediately on creation if that can be avoided, since I may want to discard a file without saving.

Answer 1

We don't want to be running code in the Open or BeforeClose events (or any other) while editing the actual .xltm template. This should help:

Public Function IsTemplate() As Boolean
    IsTemplate = (ThisWorkbook.FileFormat = xlOpenXMLTemplateMacroEnabled)
End Function

We would simply add If IsTemplate() Then Exit Sub at the start of each event method.

Once a new file is created using the template, there are 2 ways to save. Since the file is turned into an AddIn, the user can't really click the Save button in Excel itself but it can save from within VBE or by closing Excel.

Saving from VB Editor

The user might press the Save button in the VBE or simply Ctrl+S from keyboard while editing code. For this we can use a BeforeSave-AfterSave pair of events. Something like this:

Option Explicit

Private m_appFileFormat As XlFileFormat

'Utility for avoiding unwanted changes while trying to edit the actual template
Private Function IsTemplate() As Boolean
    IsTemplate = (Me.FileFormat = xlOpenXMLTemplateMacroEnabled)
End Function

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If IsTemplate() Then Exit Sub
    If m_appFileFormat <> 0 Then
        Application.DefaultSaveFormat = m_appFileFormat
        m_appFileFormat = 0
    End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If IsTemplate() Then Exit Sub
    If SaveAsUI Then
        If m_appFileFormat = 0 Then m_appFileFormat = Application.DefaultSaveFormat
        Application.DefaultSaveFormat = xlOpenXMLAddIn
    End If
End Sub

Private Sub Workbook_Open()
    If IsTemplate() Then Exit Sub
    If Not Me.IsAddin Then Me.IsAddin = True
End Sub

Closing Excel

The user might click the Excel close button. This is where your question actually points to.

A way of dealing with this is to change the Application.DefaultSaveFormat from the BeforeClose event and that works well with the downside that we now need a way to restore the app format. And that's made even worse by the fact that the user might press Cancel or Don't Save.

Unfortunately there is no event that signals that the application is closing or that the close was cancelled. The only thing I can think of is to trap a state loss. To do that you could use your own subclassProc method but that would require too much boilerplate. Instead I suggest we use a fake object and take advantage of IUnknown::Release.

In a standard 'bas' module add this code:

Option Explicit

#If Mac Then
    Private Declare PtrSafe Function CopyMemory Lib "/usr/lib/libc.dylib" Alias "memmove" (Destination As Any, Source As Any, ByVal Length As LongPtr) As LongPtr
#Else
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
#End If

Private m_appFileFormat As XlFileFormat

Private Sub Release(ByVal instancePtr As LongPtr)
    'Do not press Reset while in this method as that will "nuke" the application
    If m_appFileFormat <> 0 Then Application.DefaultSaveFormat = m_appFileFormat
    m_appFileFormat = 0
End Sub

Private Property Let MemLongPtr(ByVal memAddress As LongPtr, ByVal newValue As LongPtr)
    #If Win64 Then
        Const PTR_SIZE As Long = 8
    #Else
        Const PTR_SIZE As Long = 4
    #End If
    CopyMemory ByVal memAddress, newValue, PTR_SIZE
End Property

Public Sub RestoreAppFileFormatAtStateLoss(ByVal appFileFormat As XlFileFormat)
    If m_appFileFormat <> 0 Then Exit Sub
    
    Static o As Object
    Static vtbl(0 To 2) As LongPtr
    Static vtblPtr As LongPtr
    
    'We only need Release, QueryInterface and AddRef and not useful
    vtbl(2) = VBA.Int(AddressOf Release)
    
    'Point to vTable
    vtblPtr = VarPtr(vtbl(0))
    MemLongPtr(VarPtr(o)) = VarPtr(vtblPtr)
    
    m_appFileFormat = appFileFormat
End Sub

The final code in the ThisWorkbook module can become:

Option Explicit

Private m_appFileFormat As XlFileFormat

'Utility for avoiding unwanted changes while trying to edit the actual template
Private Function IsTemplate() As Boolean
    IsTemplate = (Me.FileFormat = xlOpenXMLTemplateMacroEnabled)
End Function

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If IsTemplate() Then Exit Sub
    If m_appFileFormat <> 0 Then
        Application.DefaultSaveFormat = m_appFileFormat
        m_appFileFormat = 0
    End If
End Sub

Private Sub Workbook_BeforeClose(ByRef Cancel As Boolean)
    If IsTemplate() Then Exit Sub
    If Me.Saved Then Exit Sub
    
    If Me.Path = vbNullString Then
        RestoreAppFileFormatAtStateLoss Application.DefaultSaveFormat
        Application.DefaultSaveFormat = xlOpenXMLAddIn
    Else
        Select Case MsgBox("Wanna save before you quit?", vbQuestion + vbYesNoCancel, "Unsaved Changes")
        Case VbMsgBoxResult.vbYes
            Me.Save
        Case VbMsgBoxResult.vbCancel
            Cancel = True
        End Select
    End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If IsTemplate() Then Exit Sub
    If SaveAsUI Then
        If m_appFileFormat = 0 Then m_appFileFormat = Application.DefaultSaveFormat
        Application.DefaultSaveFormat = xlOpenXMLAddIn
    End If
End Sub

Private Sub Workbook_Open()
    If IsTemplate() Then Exit Sub
    If Not Me.IsAddin Then Me.IsAddin = True
End Sub

I've tested a couple of scenarios and it seems that the Application.DefaultSaveFormat is restored correctly. Apologies if I missed any.