Microsoft Office Tutorials and References
In Depth Information
Some Useful Functions for Use in Your Code
Another way to write this function follows. This version attempts to create an object variable
using the name. If doing so generates an error, then the name doesn’t exist.
Private Function RangeNameExists2(nname) As Boolean
‘ Returns TRUE if the range name exists
Dim n As Range
On Error Resume Next
Set n = Range(nname)
If Err.Number = 0 Then RangeNameExists2 = True _
Else RangeNameExists2 = False
End Function
Testing for membership in a collection
The following function procedure is a generic function that you can use to determine whether an
object is a member of a collection:
Private Function IsInCollection(Coln As Object, _
Item As String) As Boolean
Dim Obj As Object
On Error Resume Next
Set Obj = Coln(Item)
IsInCollection = Not Obj Is Nothing
End Function
This function accepts two arguments: the collection (an object) and the item (a string) that
might or might not be a member of the collection. The function attempts to create an object
variable that represents the item in the collection. If the attempt is successful, the function
returns True ; otherwise, it returns False .
You can use the IsInCollection function in place of three other functions listed in this
chapter: RangeNameExists , SheetExists , and WorkbookIsOpen . To determine whether a
range named Data exists in the active workbook, call the IsInCollection function with this
MsgBox IsInCollection(ActiveWorkbook.Names, “Data”)
To determine whether a workbook named Budge t is open, use this statement:
MsgBox IsInCollection(Workbooks, “budget.xlsx”)
To determine whether the active workbook contains a sheet named Sheet1 , use this statement:
MsgBox IsInCollection(ActiveWorkbook.Worksheets, “Sheet1”)
Search JabSto ::

Custom Search