In Microsoft Excel 5/95 VBA, you addressed most objects by referring to their collection. For instance, if you needed to get the value of a range on a specific worksheet, you would use the Worksheets collection to address that worksheet, as follows:
iNumber = ThisWorkBook.Worksheets("Sheet1") _
.Range("A1").Value
In Microsoft Excel 97, the new VBE object model allows you to reference objects directly by their Name (CodeName) property. In the case of a worksheet, the CodeName property is independent of the name the user sees on the sheet tab. Therefore, you can now uniquely identify worksheets with the CodeName property and not worry about the tab name provided for the user.
Assume that you set the CodeName property of your worksheet to wksMySheet. You can now refer to this worksheet in the following manner:
iNumber = wksMySheet.Range("A1").Value
If you need to refer to objects in another workbook by name, you must create a reference to that workbook using the Tools/References menu item in VBE. In order to do this, you must give a unique name to the VBProject object for each workbook.
By default, each new workbook you create contains its own VBProject object named VBAProject. You cannot create a reference between two projects whose names are the same. To change the project name, simply select the project in the Project Explorer and type a new name in the name property for that project shown in the Properties window. The following figure shows two VBProjects that have been given unique names.
When addressing VBComponents by name in a single-workbook project, the current VBProject name is assumed as a qualifier. For instance, if your VBProject is named vbaMyProject and it contains a VBComponent (in this case, a UserForm) named frmMyForm, the following two lines of code are equivalent:
vbaMyProject.frmMyForm.Show
frmMyForm.Show
When referring to a VBComponent in a workbook to which you have created a reference, you may use the name of the VBComponent directly if that name is unique (in other words, if there is no VBComponent in the referencing workbook with that name).
If the referencing and the referenced VBProject both have a VBComponent with the same name, then you must use the name of the referenced VBProject as a qualifier. For instance, if the referenced VBProject is named vbaReferenced and both projects contain a worksheet with the CodeName Sheet1, from which you want to get the value in cell A1, you should do it like this:
iNumber = vbaReferenced.Sheet1.Range("A1").Value