Microsoft Office Tutorials and References
In Depth Information
Some Useful Worksheet Functions
The following function is similar to LastSaved , but it returns the date and time when the
workbook was last printed or previewed. If the workbook has never been printed or previewed, the
function returns a #VALUE error.
LastPrinted = ThisWorkbook. _
BuiltinDocumentProperties(“Last Print Date”)
If you use these functions in a formula, you might need to force a recalculation (by pressing F9)
to get the current values of these properties.
Quite a few additional built-in properties are available, but Excel doesn’t use all of
them. For example, attempting to access the Number of Bytes property will generate
an error. For a list of all built-in properties, consult the Help system.
The preceding LastSaved and LastPrinted functions are designed to be stored in the
workbook in which they’re used. In some cases, you may want to store the function in a different
workbook (for example, personal.xlsb ) or in an add-in. Because these functions reference
ThisWorkbook , they won’t work correctly. Following are more general-purpose versions of
these functions. These functions use Application.Caller , which returns a Range object that
represents the cell that calls the function. The use of Parent.Parent returns the workbook
(that is, the parent of the parent of the Range object — a Workbook object). This topic is
explained further in the next section.
LastSaved2 = Application.Caller.Parent.Parent. _
BuiltinDocumentProperties(“Last Save Time”)
Understanding object parents
As you know, Excel’s object model is a hierarchy: Objects are contained in other objects. At the
top of the hierarchy is the Application object. Excel contains other objects, and these objects
contain other objects, and so on. The following hierarchy depicts how a Range object fits into