When you handle a worksheet, you can handle a worksheet using Index
property and Name
property like Sheets(1).Select
. But if users will change the name of worksheet or change the sorting of the worksheets, you cannot access to the
appropriate worksheet with the name property or the index property.
Another way to access to the worksheet is using an Object Name.
An object name can be seen in the Project window in Visual Basic Editor. In the
picture below, the object name of Sheet3 is wks_main. In the main window of
Excel (not in VBE), when you change the worksheet name of Sheet3 form Sheet3 to
MySheet, in VBE, Sheet3 will be changed as MySheet. But wks_main as an Object
Name will not be changed.
The way of changing the Object Name of worksheets
is, changing it via Properties Window in VBE or you can change it from code line.
The following code would add a worksheet and change the Object Name. So here after, you can access to the appropreate worksheet using the Object Name.
Place the following in a standard module.
Note: When you compile VBA project, VBE returns error if the object wks_main does not exist as yet.
Dim wks As Worksheet
Dim sPrevCodeName As String
Dim sNewCodeName As String
'set new object name
sNewCodeName = "wks_main"
'add a worksheet
Set wks = Sheets.Add
'get an object name of the added worksheet
sPrevCodeName = wks.CodeName
'change an object name of the added worksheet
Properties("_CodeName") = sNewCodeName
wks_main.Range("A1").Value = "This is it!"