Use VBA Functions to Return Excel Document Properties

Excel tracks many worksheet properties that formulas don't return. But you can fix the problem in VBA by writing a UDF, a User Defined Function.

2016

Excel tracks many worksheet properties that formulas don't return. But you can fix the problem in VBA by writing a UDF, a User Defined Function.“I would like to add a small formula-driven reference in my Excel worksheets that: (1) Automatically notes the original author/user (or last modifier) of the worksheet. (2) Automatically notes when the worksheet was last modified. Any tips would be much appreciated.” — Patrick V.

Unfortunately, there’s no way to do what you want using Excel’s built-in worksheet functions. However, you can return information like this using two simple VBA macro functions:

Public Function LastSaveTime() As String
Application.Volatile
LastSaveTime = ThisWorkbook.BuiltinDocumentProperties(“Last Save Time”)
End Function

Public Function LastAuthor() As String
Application.Volatile
LastAuthor =ThisWorkbook.BuiltinDocumentProperties(“Last Author”)
End Function

Each function consists of two instructions. The first tells Excel to recalculate the function each time you recalculate your spreadsheet. The second line returns the information you want.

To use these functions, copy them to a module in your workbook. Then, in a spreadsheet, enter the formulas:

=LastSaveTime()
=LastAuthor()

When you enter these formulas, be sure to include the open and close parentheses as shown.

You can return other information about Office documents with other arguments. To see the complete list of arguments, along with information about the BuiltinDocumentProperties property, select the property in your code and press F1.

Note: The help topic lists property names that apply to all Office products. Less than half of those listed work with Excel. Those that don’t return an error message.