Wes wants to know the last time a particular Workbook was saved. Today, in Episode #1725, Bill shows us how to add a small Macro to our Workbook that will show the Date and Time the Workbook was saved.
Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You'll discover macro techniques you won't find anywhere else and learn how to create automated reports that are amazingly powerful and useful.
For more information on Excel 2010 VBA and Macros, check out...
VBA and Macros: Microsoft Excel 2010 - The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. Using Microsoft Excel 2010 VBA scripting features, Excel users can save dozens -- or even hundreds -- of hours per year. But most Excel users have never written a VBA script: many haven't even used Excel's built-in Macro Recorder.
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You'll discover macro techniques you won't find anywhere else and learn how to create automated reports that are amazingly powerful and useful.
For more information on Excel 2010 VBA and Macros, check out...
VBA and Macros: Microsoft Excel 2010 - The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. Using Microsoft Excel 2010 VBA scripting features, Excel users can save dozens -- or even hundreds -- of hours per year. But most Excel users have never written a VBA script: many haven't even used Excel's built-in Macro Recorder.
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
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!
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!
