Learn Excel – “Function for Last Saved Time”: Podcast #1725

Learn Excel – “Function for Last Saved Time”: Podcast #1725

MrExcel podcast is sponsored by “Easy-XL”! Learn Excel from MrExcel podcast, episode
1725 – Function for the Last Saved Time! Hey, welcome back to the MrExcel netcast,
I’m Bill Jelen. And today’s question sent by Wes, Wes is looking
for a user-defined function to give us the last time that this workbook was saved. So we’re going to switch over to VBA with
Alt+F11, Ctrl+G to show the Immediate window, I’m just going to do a couple of tests down
here. I’m sure I have the debug.Print activeworkbook.FullName,
that gives us the path and file name. And I need that path and file name for the
function called filedatetime, which would give us a date and time that this was saved. So, today at 6:26, a couple minutes ago. Alright, so we’re going to insert a Module
into this workbook, will call it function, what we call it now, maybe LastSaved. No arguments that were going to pass it, and
a user-defined function has to return the variable with the same name as the function. So LastSaved is equal to, and I want to copy
this from down here, but if I click down here, it’s going to give me a compile error. So I just put a 1 in there so that way that
line is valid for a moment, and we’ll Ctrl+C to copy that, replace the one, Ctrl+V, and
yep, it capitalizes everything. That’s good, because I didn’t misspell anything. This should be really simple, just come back
here,=LastSaved(), Enter! OK, so there’s the right answer in the wrong
format, we need to format this as a date and time, or short date, or a time. Or we can do Ctrl+1, and let’s see, I think
there’s one that shows both date and time like that, although we have to add the seconds. Alright so, create some sort of a format,
so we can see that it’s working. Alright, so there’s our Podcast1725, I will
save! OK, it’s not updating. Now, let’s see what happens if I press the
F9 key, still not updating. Alright, so that means that we have to come
back here and mark this function as volatile, application.Volatile like that. I’ll do a debug compile just to make sure
that Excel sees that is now volatile, re-enter this function. Alright and now, so we’ll do a save, still
not updating. Let’s see if it updates when I update the
worksheet. OK, so now at least, when the worksheet changes,
it’s doing a calculate, and that’s getting updated. I still don’t like that, I need to force Excel
to do a calculate after the save. See after a save, nothing has changed, so
Excel doesn’t bother to recalculate, we need to force that to happen. So Alt+F11, let’s see, here’s our workbook,
we’re going to go into Microsoft Excel Objects, you might have to expand this and go to ThisWorkbook. And then we’re going to say Workbook from
the left drop-down, and from the right drop-down, I know there’s a BeforeSave, I need the AfterSave. There it is, I have never used it before. I will just do ActiveSheet.Calculate, that
should work. Alright, so this is a little event-handler
macro, every time we save, after the save is done, it’ll come back and it’ll recalculate
the active sheet. So now we should be all set, a couple of minutes
have gone by, we’ll click Save, and the function updates, there you go. Wes, that sounded really, really easy but
there were a couple of Gotcha’s there that we had overcome. Alright hey, I want to thank Wes for sending
that question in, I want to thank you for stopping by, we’ll see you next time for another
netcast from MrExcel!

13 thoughts on “Learn Excel – “Function for Last Saved Time”: Podcast #1725

  1. After Save is not showing up in my excel 2007 VBA editor drop down. Did they add more events to the editor after 2007?

  2. Even following every keystroke =lastsaved() returned only the first date. After some exasperation discovered removing the debug.Print stuff at the bottom it worked?

  3. Hi Jelen,

    I unable to find AfterSave option in Microsoft objects > This workbook > Workbook > ??.. there are many other options except AfterSave. I'm using excel 2007.

    Looking for your assistance.


  4. Use function Workbook_BeforeSave to prevent "double saving" the file, because if you hit save button and want to close the file soon after, you will be prompted to save it again.

    Is there a good alternative to "Application.Volatile" as it prompts me to save the file, regardless if I done any changes to the file, nor hitting save button.

    Thanks for the awesome video! It helped me a lot! 🙂

  5. This has worked BUT it only updates the formula on the active sheet whereas I want to put this formula on all sheets and calculate all at once rather than going through each sheet and pressing save or f2 etc

Leave a Reply

Your email address will not be published. Required fields are marked *