Listing Using the Parent Property to Obtain a Reference to an Objects Parent Object

Sub MeetMySing1eParent()

' Declare a worksheet variable named ws Dim ws As Worksheet

' Set ws to refer to sheet 1

Set ws = ThisWorkbook.Worksheets("Sheet1")

' Please meet my parent - Mrs. Workbook Debug.Print ws.Parent.Name

Set ws = Nothing End Sub

TIP Keep the Parent property in mind as you work with the Excel object model. For example, when you write pro-cedures that require parameters that are Excel objects, use a Range object and the Parent property to get the worksheet to which the range belongs, and use the Parent property again to get the Workbook with which the range is associated. This can dramatically reduce the number of parameters that you have to pass between procedures.

Another way to refer to a Worksheet object is using its code name. For example, take a look at the Project Explorer window and Properties window shown in Figure 7.1.

Figure 7.1

You can set a worksheet's code name in the VBE and refer to the worksheet in your code via its code name.

Figure 7.1

You can set a worksheet's code name in the VBE and refer to the worksheet in your code via its code name.

Vba Excel Ribbon

In the Project Explorer window shown next, you can see that after you set the code name, the worksheet appears under Microsoft Excel Objects using its code name with its real name, or the name the user sees, in parentheses.

"jê Microsoft Visual Basic - Hook?


1 : Ete Edit iitew Insert Format Qebug 1 : Run lools £dd-Ins Window bfeip




Project - VBAProject



a. MJ0 [


- Microsoft Excel Objects ^)Sheet2 (Sheets) © sheets (5heet3) Q ThisWorkbook em

Properties - wsMenu

wsMenu Worksheet


Alphabetic categorized |



pisplayPaoeSreaks False

DlsplayftghtToLeft Fatee

EnableAutoFllte* False

■EnableCalcuVation True

¡EnableOutfining False

EnablePivotTafcile False

■EnableSelection 0 - idNoRestrictions

Maine Sheet L

1 Scroll^rea

1 [standa-dWIdth S.43

You can programmatically distinguish between the code name and the real name using the properties CodeName and Name respectively as the following code snippet demonstrates.

' prints out name & code name ' assumes a worksheet has been named ' in the VBE as: wsMenu Sub WhatsMyName()

On Error Resume Next

Debug.Print "-----------------------------------------------"

Debug.Print "The name on my worksheet tab is " & wsMenu.Name & ", " Debug.Print "but you can call me " & wsMenu.CodeName & "."

Debug.Print "-----------------------------------------------"

End Sub

The output of this procedure is:

The name on my worksheet tab is Test, but you can call me wsMenu.

So which way should you use to refer to worksheets in code? Each way has its pros and cons, and you'll run across situations that will eliminate one of the methods as an option. For example, often your code operates on workbooks that you have no control over—you can't know or set the worksheet code names ahead of time. On the other hand, it's much more likely that users will change the name of a worksheet rather than the code name of a worksheet (though users can goof with that also unless you have locked the project).

Performance-wise, it's quicker to use the code name. Unless you're setting a reference to a worksheet object thousands of times in a row though (highly unlikely), it really doesn't matter. If you're using the standard way to refer to an item in a collection, such as setting a worksheet variable to refer to a specific worksheet, you usually only do this a few times at most in a given procedure, and the process happens so quickly that you won't experience a relevant performance difference between the two.

0 0

Post a comment