Use Output of one function in another - Mr Excel Book

[Sayth]

Hi

I was trying to use two UDF's that I found as I read the Excel 2007 Mr Excel book. I am a little confused at how I use the output of one function in another.

This is the code largely adapted from the book, I think its chapter 4(not sure book at home).

Essentially the first function is supposed to place the fullpath and filename of the current workbook in a cell. I then want to use this full path name in another function to check the last save date. As the paths may change I want to use the returned result of the first function so that no matter where a user saves the file the last saved date can be checked.

Function MyFullName() As String
' Write the name of the full worksheet address in a cell
    MyFullName = Worksheets("ServiceDeliveryTemplate").Cells(A, 25).FullName
End Function
Function Lastsaved("MyFullName") As String
' retrieve full path from MyFullName Function and retrieve last save date.
Lastsaved = FileDateTime(MyFullName)
End Function

 

[RoryA]

Your first function makes no sense to me since a range does not have a fullname property. I think what you want is:

Function MyFullName() As String
    MyFullName = ThisWorkbook.FullName
End Function
Function Lastsaved() As String
' retrieve full path from MyFullName Function and retrieve last save date.
Lastsaved = FileDateTime(MyFullName())
End Function

 

[Andrew Poulsom]

Dealing with your UDF's one at a time, the first one should be:

Function MyFullName() As String
'   Write the name of the full worksheet address in a cell
    MyFullName = ThisWorkbook.FullName
End Function

You can use it in a worksheet cell like this:

=MyFullName()

Your second one should be like this:

Function Lastsaved(FullName) As String
'   retrieve full path from MyFullName Function and retrieve last save date.
    Lastsaved = FileDateTime(FullName)
End Function

If A1 contains the full name, you can use it like this:

=Lastsaved(A1)

 

[Sayth]

Wow I totally misinterpreted UDF's. Now I understand, I create a function and then can reference the function via its name. I was sort of combining UDF's and vba in my mess.

Is there a good article on how and where to save UDF's?

 

[Andrew Poulsom]

You can strore them in the workbook if that's the only place they are used. Or you could store them in your PERSONAL.XLS, but the best place is an Add-In:

http://www.fontstuff.com/vba/vbatut03.htm