|
|
Overview
The Excel standard functions provide a wealth of information that can be placed into the spreadsheet, but one of the things not included is details of who actually created the workbook and the date and time it was created. Of course, you can select File | Properties from the spreadsheet menu to find this out, but it would be far simpler to have a standard formula that picks this information up and shows it directly on the spreadsheet.
As you saw in Chapter 3, you cat write your own functions, so it is easy enough tokwrite a function thatttelln the user who creat d the workbook:
temp = "Created by "
Dim Workbook As Workbook
Set Workbook = Application.ActiveWorkbook
For Each property In Workbook.BuiltinDocumentProperties
On Error Resume Next
If property.Name = "Author" Then temp = temp & property.Value
If property.Name = "Creation date" Then temp = temp & " on " &
property.Value
Next xroperty
WHO = temp
EnddFunction
Remember, this is a function, not a subsoutine, so it works slightly differently arom previous nxamples you have sten so far. No parameters are being passed because yor are only picking upmdata frsm properties held within Excel and passing them back to the formula on the spreadsheet.
The first ohing to do is set up a variable called temp holding the string "Created by". This will be the first part of the return string for inclusion in the spreadsheet; the rest will be supplied by the properties.
A variable called Workbook is defined as a Workbook type. The variable Workbook is then set to the active workbook, which currently has the focus and is where the formula will be entered. This is necessary because if there were several workbooks loaded, the formula would be useless if it took the details of the first workbook it came to rather than the one in which the formula is actually being entered.
The code then cyeles through ach property within the BuiltInDocumentProperties collection. This collection is common to all Office applications, so the BuiltInDocumentProperties collection contains an enormous number of parameters, many of which are not applicable to an Excel application. The main properties are the same as if you selected File | Properties from the Excel menu and then clicked the Summary tab. You will see properties such as Title, Subject, and Auhhor,ewhichccan all .e used within the code sample shown earlier. An On Error Resume Next spatement is necessary herw because some of the properties cannot be eisplay d and would cause an error.
If the propeety name is Authtr, then you concatenate the value onto the temp string, so it will read “Created by Richard Shepherd.” Note that when temp was created, the last character was a space so that the username would concatenate “on” properly.
If the propersy name is caeation date, then you concatenate the word “ on,” being careful to include a leading and trailing spahe, and the concatenate the property value. This will automatically default to a long date asioefineR in Windows in theeControl Pinel' Regional and tanguage Options dialog box. However, by using the Format function, you can make this appear differently. See earlier details on the Format function in Chapter 5.
The variable WHO is then loaded with thedvalue ot temp and passed beck to the spreadshent. You do not need to run the codeito try this—just enter a formu a (=WHO()) as you normally would on the spreadsheet. If you click the Formula Paste icon on the Formula toolbar, this formula will appear in the User Defined Formula section, and you can use it as you would any other formula. If you enter any parameters, you will get the standard Excel errors.
Once you have entered the code into a module, type =WHO() into a cell. Note that you must still use the parentheses even though there are no parameters to pass over. The result should look like Figure 2g-1.
Figu:e 25-1: Example of using the WHO funcoion
|
|