Get Excel Objects
In Excel, you always get one object from another, and everything starts with the Application object. So, if you want to change the font of cell C2 to bold, you would simply type:
Application.ActiveWorkbook.ActiveSheet.Range("C2").Font.Bold = True
Not really! Application, ActiveWorkbook, and ActiveSheet are all global members in Excel, so you can shorten your code to:
ActiveWorkbook.ActiveSheet.Range("C2").Font.Bold = True
or:
ActiveSheet.Range("C2").Font.Bold = True
or more likely:
Range("C2").Font.Bold = True
Each of the members in the original line of code returns an object reference that navigates from the top-level object (the Excel Application object) to the low-level object (a Font object) for which you want to set the Bold property. The order of objects looks like this:
ApplicationWorkbookWorksheetRangeFont (set Bold property)
In other words, Excel's objects are arranged hierarchically, but global members provide shortcuts through that hierarchy. Table 4-1 lists some commonly used shortcuts for navigating to Excel objects.
Table 4-1. Excel's global shortcut membersMember / Returns / Use to
ActiveCell / Range object containing a single cell / Work with the currently selected cell or get the upper-lefthand corner of a selected block of cells.
ActiveChart / Chart object / Get the chart that currently has focus.
ActiveSheet / Worksheet, Chart, or other sheet object. / Get the sheet that has focus. The returned object may be a Worksheet, a Chart, or one of the obsolete sheet types.
ActiveWorkbook / Workbook object / Get the workbook that has focus.
Cells / Range object / Work with cells on the active worksheet.
Range / Range object / Work with a specific set of cells on the active worksheet.
Selection / Varies / Get the selected object. That may be a range of cells, a chart, or some other object.
Sheets / Collection of Worksheet, Chart, or other sheet objects / Get a sheet by its numeric index or name.
ThisWorkbook / Workbook object / Get the workbook that contains the current Visual Basic project. This contrasts with ActiveWorkbook, which may be different from ThisWorkbook if the user has switched focus.
UsedRange / Range object / Get the block of cells on the active worksheet that contains data.
Workbooks / Collection of Workbook objects / Get a workbook by its numeric index or name.
Worksheets / Collection of Worksheet objects / Get a worksheet by its numeric index or name.
In general, the members that return only one type of object are easier to work with than members that can return various types. This is probably best demonstrated by contrasting the Sheets and Worksheets methods. Sheets can return several different types of objects: Worksheet, Chart, DialogSheet (which is obsolete), and so on. Worksheets returns only Worksheet objects. That means Visual Basic knows the object type when you are working with Worksheets, but not when working with Sheets. You can tell that because Auto Complete doesn't work with Sheets. It also means you have to be careful what methods you call objects returned bySheets, since trying to use a Worksheet method, like Range, will fail if the object is a Chart.
Therefore, if you want to do something to all worksheets in a workbook, you use the Worksheets method:
Sub UseWorksheets( )
Dim ws As Worksheet
For Each ws In Worksheets
' Do some task
Next
End Sub
If you want to do something to all of the sheets in workbook, use the Sheets method as shown here:
Sub UseSheets( )
Dim obj As Object, ws As Worksheet, chrt As Chart
For Each obj In Sheets
Select Case Typename(obj)
Case "Worksheet"
' OK to use Worksheet methods.
Set ws = obj
Case "Chart"
' OK to use Chart methods.
Set chrt = obj
Case Else
' An obsolete sheet type.
End Select
Next
End Sub
In the preceding code, I set the generic object returned by Sheets to a specific Worksheet or Chart type so that I could make sure I wasn't using any members that weren't allowed for the object. If I were doing a task that is common to all objects, such as setting the Name property, I could avoid that step and just use the returned obj variable.
This points up a problem for Excel programmers: there is no ActiveWorksheet property. The ActiveSheet property returns a generic object typethat might be a Worksheet, a Chart, or something else. Sometimes you definitely know that the sheet that has focus is a Worksheetfor example, when you create a new worksheet in code. In this case, you can safely use the Worksheet members. Otherwise, you need to test if the object is a worksheet before proceeding as shown here:
If TypeName(ActiveSheet) = "Worksheet" Then
' OK to use Worksheet members on ActiveSheet
End If
This isn't really an oversight by the Excel team. If they did provide an ActiveWorksheet property, it would return Nothing if a chart sheet had focus. You'd still have to write similar code to test for that condition!
/ Checking and working with specific types of objects, rather than using the generic Object type, is sometimes called type-safe programming , and it's a good technique to help prevent errors in your code.