Excel VBA Macro Programming

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 can write your own functions, so it is easy enough to write a function that tells the user who created the workbook:

Function WHO() 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 property WHO = temp End Function

Remember, this is a function, not a subroutine, so it works slightly differently from previous examples you have seen so far. No parameters are being passed because you are only picking up data from properties held within Excel and passing them back to the formula on the spreadsheet.

The first thing 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 cycles through each 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 Author , which can all be used within the code sample shown earlier. An On Error Resume Next statement is necessary here because some of the properties cannot be displayed and would cause an error.

If the property name is Author , 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 property name is creation date , then you concatenate the word ‚“ on, ‚½ being careful to include a leading and trailing space, and then concatenate the property value. This will automatically default to a long date as defined in Windows in the Control Panel's Regional and Language 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 the value of temp and passed back to the spreadsheet. You do not need to run the code to try this ‚ just enter a formula ( =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 25-1.

Figure 25-1: Example of using the WHO function