For business users of Microsoft Excel Free guides and templates
Home >  Excel Help Questions > 

VBA Ideas

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.

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
"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
   LastSaveTime = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function

Public Function LastAuthor() As String
   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:


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.


Free Excel Dashboards

Charley's SwipeFile charts