UDF help wanted

[henryg]

I have set up the UDF below to put the last file saved date into the active cell; and I also want to change the cell format to "dd-mm-yyyy" but it does not change whatever I do.

Here is the simple UDF:

Function LastSavedDate() As Date ' I tried "As String" too.

lastSaved = FileDateTime(ActiveWorkbook.FullName)
LastSavedDate = lastSaved

ActiveCell.NumberFormat = "dd/mm/yyyy"

End Function

I have tried various permutations, searched online and tried ChatGPT but I cannot get it to work.

Help appreciated.

Henry

 

[Micron]

Rather than trying to format the cell, format the value?

Function LastSavedDate() As Date
LastSavedDate = format(filedatetime(activeworkbook.FullName),"dd/mm/yyyy")
End Function

 

[henryg]

Rather than trying to format the cell, format the value?

Function LastSavedDate() As Date
LastSavedDate = format(filedatetime(activeworkbook.FullName),"dd/mm/yyyy")
End Function

Sadly that doesn’t work either.

I can live with manually formatting the cell, it’s only once per use, but it’s irritating me.

 

[Micron]

I put this in the immediate window
sheets("Sheet1").Range("A1") = format(filedatetime(activeworkbook.FullName),"dd/mm/yyyy")

and got this

Did you step through your code and check variables/references as you go?

 

[henryg]

This works:

Function LastSavedDate() As String
Dim lastSaved As String
lastSaved = Format(FileDateTime(ThisWorkbook.FullName), "dd/mm/yyyy")
LastSavedDate = lastSaved
End Function

I have no idea why this works and your original one didn't - it came after asking MRX.CL/GPT to review my earlier code, albeit after some miss-steps.

I hadn't used the immediate window; too much of a tyro at VBA.

It works so I'm happy. Of course, it means every file I save needs to be macro-enabled which may not be such a good idea, but then it's my own files.

Thanks for the help.

Henry

 

[Micron]

I would say it's because
a) you use undeclared variables (like LastSaved) which makes them variant data type, which means they can hold anything
b) format function changes data type to string (at least it does so in Access vba). That means
c) LastSaved is a string that you assign as the function return value, which is a date data type.

Now that you've made both of them strings, it works as intended. I once read a post that says "if you don't use Option Explicit in all of your modules, you deserve the grief you get" and I wholeheartedly agree. If you had declared LastSaved as a string you should have raised an error; probably Type Mismatch (13?). That would have led the way.