Chapter 25: VBA Custom Function Examples
Download CD Content
This chapter is jam-packed with a wide variety of useful (or potentially useful) VBA custom worksheet functions. You can use many of the functions as they are written. You may need to modify other functions to meet your particular needs. For maximum speed and efficiency, these Function procedures declare all variables that are used.
Simple Functions
The functions in this section are relatively simple, but they can be very useful. Most of them are based on the fact that VBA can obtain useful information that's not normally available for use in a formula. For example, your VBA code can access a cell's HasFormula property to determine whether a cell contains a formula. Oddly, Excel does not have a built-in worksheet function that tells you this.
| On the CD | The companion CD-ROM contains the workbook |
Does a Cell Contain a Formula?
The following CELLHASFORMULA function accepts a single-cell argument and returns TRUE if the cell has a formula:
Function CELLHASFORMULA(cell As Range) As Boolean ' Returns TRUE if cell has a formula CELLHASFORMULA = cell.Range("A1").HasFormula End Function If a multicell range argument is passed to the function, the function works with the upper- left cell in the range.
Returning a Cell's Formula
The following CELLFORMULA function returns the formula for a cell as a string. If the cell does not have a formula, it returns an empty string.
Function CELLFORMULA(cell As Range) As String ' Returns the formula in cell, or an ' empty string if cell has no formula Dim UpperLeft As Range Set UpperLeft = cell.Range("A1") If UpperLeft.HasFormula Then CELLFORMULA = UpperLeft.Formula Else CELLFORMULA = "" End If End Function This function creates a Range object variable named UpperLeft. This variable represents the upper-left cell in the argument that is passed to the function.
Is the Cell Hidden?
The following CELLISHIDDEN function accepts a single cell argument and returns TRUE if the cell is hidden. It is considered a hidden cell if either its row or its column is hidden.
Function CELLISHIDDEN(cell As Range) As Boolean ' Returns TRUE if cell is hidden Dim UpperLeft As Range Set UpperLeft = cell.Range("A1") CELLISHIDDEN = UpperLeft.EntireRow.Hidden Or _ UpperLeft.EntireColumn.Hidden End Function
Returning a Worksheet Name
The following SHEETNAME function accepts a single argument (a range) and returns the name of the worksheet that contains the range. It uses the Parent property of the Range object. The Parent property returns an object-the object that contains the Range object.
Function SHEETNAME(rng As Range) As String ' Returns the sheet name for rng SHEETNAME = rng.Parent.Name End Function
 |
If you see a function listed in this chapter that you find useful, you can use it in your own workbook. All the Function procedures in this chapter are available on the companion CD-ROM. Just open the appropriate workbook (see Appendix D for a description of the files), activate the VB Editor, and copy and paste the function listing to a VBA module in your workbook. If you prefer, you can collect a number of functions and create an add-in (see Chapter 23 for details).
It's impossible to anticipate every function that you'll ever need. However, the examples in this chapter cover a wide variety of topics, so it's likely that you can locate an appropriate function and adapt the code for your own use.
|
The following function is a variation on this theme. It does not use an argument; rather, it relies on the fact that a function can determine the cell from which it was called by using Application.Caller.
Function SHEETNAME2() As String ' Returns the sheet name of the cell that ' contains the function SHEETNAME2 = Application.Caller.Parent.Name End Function
In this function, Application.Caller returns a Range object that corresponds to the cell that contains the function. For example, suppose that you have the following formula in cell A1:
=SHEETNAME2()
When the SHEETNAME2 function is executed, Application.Caller returns a Range object corresponding to the cell that contains the function. The Parent property returns the Worksheet object, and the Name property returns the name of the worksheet.
Returning a Workbook Name
The next function, WORKBOOKNAME, returns the name of the workbook. Notice that it uses the Parent property twice. The first Parent property returns a Worksheet object, the second Parent property returns a Workbook object, and the Name property returns the name of the workbook.
Function WORKBOOKNAME() As String ' Returns the workbook name of the cell ' that contains the function WORKBOOKNAME = Application.Caller.Parent.Parent.Name End Function
Returning the Application's Name
The following function, although not very useful, carries this discussion of object parents to the next logical level by accessing the Parent property three times. This function returns the name of the Application object, which is always the string Microsoft Excel.
Function APPNAME() As String ' Returns the application name of the cell ' that contains the function APPNAME = Application.Caller.Parent.Parent.Parent.Name End Function
|
Objects in Excel are arranged in a hierarchy. At the top of the hierarchy is the Application object (Excel itself). Excel contains other objects; these objects contain other objects, and so on. The following hierarchy depicts how a Range object fits into this scheme:
Application object (Excel) Workbook object Worksheet object Range object
In the lingo of object-oriented programming (OOP), a Range object's parent is the Worksheet object that contains it. A Worksheet object's parent is the workbook that contains the worksheet. And a Workbook object's parent is the Application object. Armed with this knowledge, you can make use of the Parent property to create a few useful functions.
|
Returning Excel's Version Number
The following function returns Excel's version number. For example, if you use Excel 2007, it returns the text string 12.0.
Function EXCELVERSION() as String ' Returns Excel's version number EXCELVERSION = Application.Version End Function
Note that the EXCELVERSION function returns a string, not a value. The following function returns TRUE if the application is Excel 97 or later (Excel 97 is version 8). This function uses the VBA Val function to convert the text string to a value:
Function EXCEL97ORLATER() As Boolean EXCEL97ORLATER = Val(Application.Version) >= 8 End Function
Returning Cell Formatting Information
This section contains a number of custom functions that return information about a cell's formatting. These functions are useful if you need to sort data based on formatting (for example, sorting all bold cells together).
| Caution | The functions in this section use the following statement: |
Application.Volatile True
This statement causes the function to be reevaluated when the workbook is calculated. You'll find, however, that these functions don't always return the correct value. This is because changing cell formatting, for example, does not trigger Excel's recalculation engine. To force a global recalculation (and update all the custom functions), press Ctrl+Alt+F9.
The following function returns TRUE if its single-cell argument has bold formatting:
Function ISBOLD(cell As Range) As Boolean ' Returns TRUE if cell is bold Application.Volatile True ISBOLD = cell.Range("A1").Font.Bold End Function The following function returns TRUE if its single-cell argument has italic formatting:
Function ISITALIC(cell As Range) As Boolean ' Returns TRUE if cell is italic Application.Volatile True ISITALIC = cell.Range("A1").Font.Italic End Function Both of the preceding functions have a slight flaw: They return an error (#VALUE!) if the cell has mixed formatting. For example, it's possible that only some characters in the cell are bold.
The following function returns TRUE only if all the characters in the cell are bold. If the Bold property of the Font object returns Null (indicating mixed formatting), the If statement will generate an error, and the function name will never be set to True. The function name was previously set to False, so that's the value returned by the function.
Function ALLBOLD(cell As Range) As Boolean ' Returns TRUE if all characters in cell are bold Dim UpperLeft As Range Application.Volatile True Set UpperLeft = cell.Range("A1") ALLBOLD = False If UpperLeft.Font.Bold Then ALLBOLD = True End Function The following FILLCOLOR function returns an integer that corresponds to the color index of the cell's interior (the cell's fill color). If the cell's interior is not filled, the function returns −4142. The ColorIndex property ranges from 0 to 56.
Function FILLCOLOR(cell As Range) As Long ' Returns a long integer corresponding to ' cell's interior color Application.Volatile True FILLCOLOR = cell.Range("A1").Interior.ColorIndex End Function | Note | If a cell is part of a table that uses a style, the FILLCOLOR function does not return the correct color. |
The following function returns the number format string for a cell:
Function NUMBERFORMAT(cell As Range) As String ' Returns a string that represents ' the cell's number format Application.Volatile True NUMBERFORMAT = cell.Range("A1").NumberFormat End Function If the cell uses the default number format, the function returns the string General.
EAN: 2147483647
Pages: 212