Q:

What does the save button in the editor do?

I could not find a simple answer to this question, which I thought was odd. I was making changes to an Add-in I wrote and I was pressing the save button ever so often in the excel VBA editor. When I came back to make some more changes I found that all my changes over the last few days are gone. Is there any hope of recovering my work? More importantly, if I am making changes what is a better way of saving the project. I usually work out of my add on and only save a new copy when I need to send it out to a teammate.

A:

I remember having this bad surprise at some point, but then I started to look closely at the save button before clicking and I noticed that VBA is telling you which workbook will be saved if you click on the floppy disk.

The reason why it is important to have a look at this is because the file that will be saved depends on what is currently selected in the Project Explorer and not what is currently visible in the VBA Code Editor Window.

You can also make sure that your add-in is properly saved by selecting one of its elements and running the following command in the Immediate Window (which should replicate exactly what the VBE save button does):
Thisworkbook.Save

Finally, unlike for other types of workbooks, Excel won't tell you when you close the application without saving changes to an add-in. For that reason, you can take extra precautions by adding the following code to the object Thisworkbook of your add-in:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If ThisWorkbook.Saved = False Then
        Dim strMsg As String

        ' Specify the message to display.
        strMsg = "Want to save your changes to '" & ThisWorkbook.Name & "'?"

        Dim Decision As Boolean, ireply As Variant
        ireply = MsgBox(prompt:=strMsg, Buttons:=vbYesNoCancel)
        If ireply = vbYes Then
            Decision = True
        ElseIf ireply = vbNo Then
            Decision = False
        Else 'They cancelled (VbCancel)
            Cancel = True
            Exit Sub
        End If

        If Decision = True Then
            ThisWorkbook.Save
        End If

    End If

End Sub

Which will prompt you with a message allowing you to save your add-in if it has an unsaved status when the Excel application is closing.

