Microsoft Office Tutorials and References
In Depth Information
On the CD
Using the functions in this chapter
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.
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 _
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 worksheet object that contains the Range object.
Function SHEETNAME(rng As Range) As String
‘ Returns the sheet name for rng
SHEETNAME = rng.Parent.Name
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