Date Modified Function

Question

I set up a User Defined Function for Last Date MOdified in Excel 2007 via Visual Basic:

Public Function ModDate()

ModDate = Format(FileDateTime(ThisWorkbook.FullName), "m/d/yy h:n ampm")

End Function

When I input the function ModDate() into the spreadsheet, the date doesn't update automatically. I have to reinput the function or refresh the spreadsheet to make the function work.  Is there a better (yet easy) way to set up the last date modified in a spreadsheet so that I don't have to refresh the sheet each time?

Answer 1

You could change the function to

Public Function ModDate()

    Application.Volatile

    ModDate = Format(FileDateTime(ThisWorkbook.FullName), "m/d/yy h:n ampm")

End Function

Whenever the worksheet is recalculated, the function will be updated.