Many Microsoft Excel VBA programming problems arise from a misunderstanding of how to correctly reference and navigate Workbook, Sheet, and Range objects using the Microsoft Excel object model. This section reviews the most common methods and their proper usage.
There are three common methods of the Application object that are used to access a workbook object from the Microsoft Excel object model.
Refers to the workbook within which the code is currently running. This is most commonly used when referring to objects within the AddIn.
Refers to the workbook that is currently active in the Microsoft Excel interface. Use this method only when your intention is to reference whatever workbook happens to be active.
Uses the Workbooks collection to identify a specific workbook by name or by index number within the collection. Workbooks referenced using this method need not be active.
Similarly, there are two common methods of the Workbook object that are used to access Sheet objects from the Microsoft Excel object model. Note that neither of these two methods is object-type – specific. Both return either a worksheet object, a chart object, or any of the Microsoft Excel 5/95 legacy sheet objects, such as DialogSheets and XLM sheets.
Refers to the sheet that is currently active in the Microsoft Excel interface.
Uses the Sheets collection to identify a specific sheet by name or by index number within the collection. Sheets referenced using this method need not be active.