The Caller property of the Application object returns a reference to the object that called or executed a macro procedure. It had a wide range of uses in Excel 5 and Excel 95, where it was used with menus and controls on dialog sheets. From Excel 97 onward, command bars and ActiveX controls on user forms have replaced menus and controls on dialog sheets, and the Ribbon and Quick Access Menu have now replaced command bars. The Caller property does not apply to these new features.

Caller still applies to the Forms toolbar controls, drawing objects that have macros attached and user-defined functions. It is particularly useful in determining the cell that called a user-defined function. The worksheet in Figure 2-3 uses the WorksheetName function to display the name of the worksheet in B2.

■ D«

ri *?

^ a •

App 1 i cation ..-¡Ism - Microsoft E-l.,.



l'* Home i insert Page La | Forniul : Data | Re lira ViaM | DeJeloi | @

_ en







B C.





i |calculationsj

2 i


3 1

5 |

5 1

7 !

8 1


H Input;

Calculations Results

3 mi



Ready <]


Figure 2-3

When used in a function, Application.Caller returns a reference to the cell that called the function, which is returned as a Range object. The following WorksheetName function uses the Parent property of the Range object to generate a reference to the Worksheet object containing the Range object. It assigns the Name property of the Worksheet object to the return value of the function. The Volatile method of the Application object forces Excel to recalculate the function every time the worksheet is recalculated, so that if you change the name of the sheet, the new name is displayed by the function:

Function WorksheetName() Application.Volatile

WorksheetName = Application.Caller.Parent.Name End Function

It would be a mistake to use the following code in the WorksheetName function:

WorksheetName = ActiveSheet.Name

If a recalculation takes place while a worksheet is active that is different from the one containing the formula, the wrong name will be returned to the cell.

0 0

Post a comment