Microsoft Office Tutorials and References
In Depth Information
Some Useful Worksheet Functions
In the lingo of object-oriented programming, a Range object’s parent is the Worksheet object
that contains it. A Worksheet object’s parent is the Workbook object that contains the
worksheet, and a Workbook object’s parent is the Application object.
How can you put this information to use? Examine the SheetName VBA function that follows.
This 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(ref) As String
SheetName = ref.Parent.Name
The next function, WorkbookName , returns the name of the workbook for a particular cell.
Notice that it uses the Parent property twice. the first Parent property returns a Worksheet
object, and the second Parent property returns a Workbook object.
Function WorkbookName(ref) As String
WorkbookName = ref.Parent.Parent.Name
The AppName function that follows carries this exercise to the next logical level, accessing the
Parent property three times (the parent of the parent of the parent). This function returns the
name of the Application object for a particular cell. It will, of course, always return
Microsoft Excel .
Function AppName(ref) As String
AppName = ref.Parent.Parent.Parent.Name
Counting cells between two values
The following function, named CountBetween , returns the number of values in a range (first
argument) that fall between values represented by the second and third arguments:
Function CountBetween(InRange, num1, num2) As Long
‘ Counts number of values between num1 and num2
If num1 <= num2 Then
CountBetween = .CountIfs(InRange, “>=” & num1, _
InRange, “<=” & num2)
CountBetween = .CountIfs(InRange, “>=” & num2, _