Excel: Force recalculate on save

Question

I have a simple macro that returns the date of the last save of the current document:

Function LastSavedTimeStamp() As Date
  LastSavedTimeStamp = INT(ActiveWorkbook.BuiltinDocumentProperties("Last Save Time"))
End Function

This works fine when I manually force the cell to re-calcutate, but the cell doesn't re-calculate when I save the sheet - which is what I want. Is there a way to ensure that the macro above is run everytime the document is saved, such that the date of last save is automatically updated.

PS. This will only work when macros are enabled. That is fine.

---

I.e. this is what I want

---

Try 2: Inspired by Skin

Answer 1

The problem that you have, as I see it, is that you want to save the document with the last saved value. This a chicken and egg issue though. You won't get the last saved value until it's saved ...!

To trick it, firstly, I suggest adding Application.Volatile to your current function ...

Function LastSavedTimeStamp() As Date
    Application.Volatile
    
    LastSavedTimeStamp = Int(ActiveWorkbook.BuiltinDocumentProperties("Last Save Time"))
End Function

.. that way, it will force the recalculation when the document is saved and anytime a calculation runs.

Then, in your ThisWorkbook object, add the below code ...

Public DO_NOT_CALCULATE As Boolean

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    Dim objWorksheet As Worksheet

    If Not DO_NOT_CALCULATE Then
        For Each objWorksheet In ThisWorkbook.Worksheets
            objWorksheet.Calculate
        Next
        
        DO_NOT_CALCULATE = True
        
        ThisWorkbook.Save
    Else
        DO_NOT_CALCULATE = False
    End If
End Sub

That will save the workbook again after the initial save forcing the recalculation to make sure your cell has the correct value.

It's a bit ugly but it should work for you.

Answer 2

You just need to put a call to this function in the BeforeSave event sub procedure of the work book module.

Here's the code to paste in:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    LastSavedTimeStamp
End Sub