  |
|
Use VBA Functions to Return
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| "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. |
July, 2006
Patrick,
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. Because of space limitations, the second instruction is displayed in two lines, using the " _" line-continuation symbols of space and underscore.
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.
Hope this helps,
Charley Kyd
June, 2006
Copyright © 2004 - 2008 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.
Excel DashboardsLearn how to create top-quality dashboard reports with Excel. |
