Hi,

I'm trying to get this UDF to work in my Excel 2002 workbook. I've followed
the instructions closely and sometimes it works. For example, I created a
new workbook, book1, and inserted the UDF module as instructed. It worked.
Then I tried putting the same code into a module in my Personal.xls workbook
so that I can run the UDF from any open workbook. It seems to run OK if I
stay inside the Personal.xls workbook, but not when I try to use it in a new
workbook. I've tried using Insert Function and accessing the UDF from the
user-defined functions and I've tried just keying in the =DocProps("last time
save") and neither works (the function returns either #VALUE or #NAME).

I know I'm copying and pasting correctly because the function works
sometimes.

Any idea what I'm doing wrong?

Ted

"Frank Kabel" wrote:

Hi
a UDF is a user defined function written in VBA (visual Basic for
Applications'). For getting started with this see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Pringles." schrieb im Newsbeitrag
...
sorry to intrude, but... what exactly is a UDF and how do i make one?

"Frank Kabel" wrote:

Hi
use the following UDF:

Function DocProps(prop As String)
application.volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

and enter in a cell
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

"Ranrunr" schrieb im
Newsbeitrag
...
How do I automatically insert the date the current file was last
modified
into a cell in Excel?

Try:
=personal.xlsb!docprops("last save time")

Make sure you use the correct extension for your personal.xl* file. And watch
the order of the words!

"Last Save Time" is not the same as "last time save".



Ted M H wrote:

Hi,

I'm trying to get this UDF to work in my Excel 2002 workbook. I've followed
the instructions closely and sometimes it works. For example, I created a
new workbook, book1, and inserted the UDF module as instructed. It worked.
Then I tried putting the same code into a module in my Personal.xls workbook
so that I can run the UDF from any open workbook. It seems to run OK if I
stay inside the Personal.xls workbook, but not when I try to use it in a new
workbook. I've tried using Insert Function and accessing the UDF from the
user-defined functions and I've tried just keying in the =DocProps("last time
save") and neither works (the function returns either #VALUE or #NAME).

I know I'm copying and pasting correctly because the function works
sometimes.

Any idea what I'm doing wrong?

Ted

"Frank Kabel" wrote:

Hi
a UDF is a user defined function written in VBA (visual Basic for
Applications'). For getting started with this see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Pringles." schrieb im Newsbeitrag
...
sorry to intrude, but... what exactly is a UDF and how do i make one?

"Frank Kabel" wrote:

Hi
use the following UDF:

Function DocProps(prop As String)
application.volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

and enter in a cell
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

"Ranrunr" schrieb im
Newsbeitrag
...
How do I automatically insert the date the current file was last
modified
into a cell in Excel?





--

Dave Peterson

If the code resides in a workbook other than the workbook containing the
cell function, you need to prefix the call to the function with the
name of the (open) workbook that contains the code. E.g,.

='personal.xls'!DocProps("Last Save Time")

See http://www.cpearson.com/excel/Writin...ionsInVBA.aspx for a
discussion of User Defined Functions.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Ted M H" wrote in message
...
Hi,

I'm trying to get this UDF to work in my Excel 2002 workbook. I've
followed
the instructions closely and sometimes it works. For example, I created a
new workbook, book1, and inserted the UDF module as instructed. It
worked.
Then I tried putting the same code into a module in my Personal.xls
workbook
so that I can run the UDF from any open workbook. It seems to run OK if I
stay inside the Personal.xls workbook, but not when I try to use it in a
new
workbook. I've tried using Insert Function and accessing the UDF from the
user-defined functions and I've tried just keying in the =DocProps("last
time
save") and neither works (the function returns either #VALUE or #NAME).

I know I'm copying and pasting correctly because the function works
sometimes.

Any idea what I'm doing wrong?

Ted

"Frank Kabel" wrote:

Hi
a UDF is a user defined function written in VBA (visual Basic for
Applications'). For getting started with this see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Pringles." schrieb im Newsbeitrag
...
sorry to intrude, but... what exactly is a UDF and how do i make one?

"Frank Kabel" wrote:

Hi
use the following UDF:

Function DocProps(prop As String)
application.volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

and enter in a cell
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

"Ranrunr" schrieb im
Newsbeitrag
...
How do I automatically insert the date the current file was last
modified
into a cell in Excel?

Hi guys,

Thanks a million for the quick replies. I understand the requirement to
precede the function name with the workbook name when running the function
from another wookbook. I think I have discovered the problem. If I insert
the function into a cell in a workbook, it returns #VALUE!. But if I save
the file, close it and then reopen it the function returns the expected
value. What I was missing was the requirement to save and close the workbook
to get the function to work.

Ted





"Ted M H" wrote:

Hi,

I'm trying to get this UDF to work in my Excel 2002 workbook. I've followed
the instructions closely and sometimes it works. For example, I created a
new workbook, book1, and inserted the UDF module as instructed. It worked.
Then I tried putting the same code into a module in my Personal.xls workbook
so that I can run the UDF from any open workbook. It seems to run OK if I
stay inside the Personal.xls workbook, but not when I try to use it in a new
workbook. I've tried using Insert Function and accessing the UDF from the
user-defined functions and I've tried just keying in the =DocProps("last time
save") and neither works (the function returns either #VALUE or #NAME).

I know I'm copying and pasting correctly because the function works
sometimes.

Any idea what I'm doing wrong?

Ted

"Frank Kabel" wrote:

Hi
a UDF is a user defined function written in VBA (visual Basic for
Applications'). For getting started with this see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Pringles." schrieb im Newsbeitrag
...
sorry to intrude, but... what exactly is a UDF and how do i make one?

"Frank Kabel" wrote:

Hi
use the following UDF:

Function DocProps(prop As String)
application.volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

and enter in a cell
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

"Ranrunr" schrieb im
Newsbeitrag
...
How do I automatically insert the date the current file was last
modified
into a cell in Excel?

I think that original code has a small bug in it.

If you create two new workbooks and save them at different times, you can put:

=personal.xls!docprops("Last save time")

In A1 of Sheet1 in both workbooks.

Now arrange your windows so you can see Sheet1 of both workbooks at the same
time.
Window|Arrange|horizontal

Force a recalc (put =rand() in any empty cell). Look at the times.

I'd make a small change to get the properties from the workbook that holds the
formula.

application.caller is the cell with the formula.
application.caller.parent is the worksheet that owns the cell with the formula
application.caller.parent.parent is the workbook that owns the worksheet that
owns the cell with the formula

Option Explicit
Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = Application.Caller.Parent.Parent.BuiltinDocumentPr operties(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

Ted M H wrote:

Hi guys,

Thanks a million for the quick replies. I understand the requirement to
precede the function name with the workbook name when running the function
from another wookbook. I think I have discovered the problem. If I insert
the function into a cell in a workbook, it returns #VALUE!. But if I save
the file, close it and then reopen it the function returns the expected
value. What I was missing was the requirement to save and close the workbook
to get the function to work.

Ted

"Ted M H" wrote:

Hi,

I'm trying to get this UDF to work in my Excel 2002 workbook. I've followed
the instructions closely and sometimes it works. For example, I created a
new workbook, book1, and inserted the UDF module as instructed. It worked.
Then I tried putting the same code into a module in my Personal.xls workbook
so that I can run the UDF from any open workbook. It seems to run OK if I
stay inside the Personal.xls workbook, but not when I try to use it in a new
workbook. I've tried using Insert Function and accessing the UDF from the
user-defined functions and I've tried just keying in the =DocProps("last time
save") and neither works (the function returns either #VALUE or #NAME).

I know I'm copying and pasting correctly because the function works
sometimes.

Any idea what I'm doing wrong?

Ted

"Frank Kabel" wrote:

Hi
a UDF is a user defined function written in VBA (visual Basic for
Applications'). For getting started with this see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Pringles." schrieb im Newsbeitrag
...
sorry to intrude, but... what exactly is a UDF and how do i make one?

"Frank Kabel" wrote:

Hi
use the following UDF:

Function DocProps(prop As String)
application.volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

and enter in a cell
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

"Ranrunr" schrieb im
Newsbeitrag
...
How do I automatically insert the date the current file was last
modified
into a cell in Excel?





--

Dave Peterson

You don't need to close the workbook nor does the workbook need to be in a
saved state. All that is required is that the workbook has been saved to
disk once. The last saved property is created in the workbook when it is
saved for the first time, so a new, unsaved workbook will not have the
property. Save it once and all will be right with the world.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Ted M H" wrote in message
...
Hi guys,

Thanks a million for the quick replies. I understand the requirement to
precede the function name with the workbook name when running the function
from another wookbook. I think I have discovered the problem. If I
insert
the function into a cell in a workbook, it returns #VALUE!. But if I save
the file, close it and then reopen it the function returns the expected
value. What I was missing was the requirement to save and close the
workbook
to get the function to work.

Ted





"Ted M H" wrote:

Hi,

I'm trying to get this UDF to work in my Excel 2002 workbook. I've
followed
the instructions closely and sometimes it works. For example, I created
a
new workbook, book1, and inserted the UDF module as instructed. It
worked.
Then I tried putting the same code into a module in my Personal.xls
workbook
so that I can run the UDF from any open workbook. It seems to run OK if I
stay inside the Personal.xls workbook, but not when I try to use it in a
new
workbook. I've tried using Insert Function and accessing the UDF from
the
user-defined functions and I've tried just keying in the =DocProps("last
time
save") and neither works (the function returns either #VALUE or #NAME).

I know I'm copying and pasting correctly because the function works
sometimes.

Any idea what I'm doing wrong?

Ted

"Frank Kabel" wrote:

Hi
a UDF is a user defined function written in VBA (visual Basic for
Applications'). For getting started with this see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Pringles." schrieb im Newsbeitrag
...
sorry to intrude, but... what exactly is a UDF and how do i make one?

"Frank Kabel" wrote:

Hi
use the following UDF:

Function DocProps(prop As String)
application.volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

and enter in a cell
=DOCPROPS("last save time")
(format cell as date)

--
Regards
Frank Kabel
Frankfurt, Germany

"Ranrunr" schrieb im
Newsbeitrag
...
How do I automatically insert the date the current file was last
modified
into a cell in Excel?