and see how the Range method works. To begin learning about objects, a beginning VBA programmer might want to count the number of cells in a range the user selected, then display a message if the user selected only one cell. Try typing in the statement:
If Application.Range.Selection.Count = 1 Then MsgBox("Only one cell")...
then, run it and there will be an error message "Range method of Application class failed." Isn't VBA supposed to be easy? Well, it will seem easier in a moment. Let's take some time to study how objects work. Start by drawing a diagram of what this line tries to do. One way to do this is to look the key words up in the VBA reference manual or Excel Help and note the definitions.
Count Property: Returns the number of items in a collection. Read-only.
Range Method:
Returns a cell or range of cells
If Application.Range.Selection.Count = 1 Then ...
Selection Property: Returns the current selection in the active Window of the Application object. Read-only.
Example 2. Invalid object reference
Selection Property: Returns the object that is currently selected in the active window of the Application object. Read-only.
If Application.Selection.Cells.Count = 1 Then...
Cells Method: Returns a collection of cells as a Range.
Example 3. Valid object reference
From Example 2 above, it's easy to see why the first attempt failed. To understand an object reference, always read it from right to left—this seems backwards—but remember that VBA parses it from left to right. The code in Example 2 is trying to "count the number of items in the selection in the range in the application". So, why did the Range method fail? Let's bring up the Object Browser and see how the Range method works.
As Figure 1 below shows, it's easy to bring up Help on the Range method by finding it under the Application object (remember, the error message told us this is how it parsed the code). Then click on the question-mark button. Help for the Range method says it returns a cell or a range of cells (a Range Object). Now, let's check Help for the Selection property. The first thing the help information shows is that the Selection property applies to the Application and Window objects. It does not apply to a Range. This is important! We have learned two things. First, it's OK to say Application.Range("A1"), but it's completely invalid to use anything besides a cell reference with the Range method. Second, it's not possible to ask for a selection from a range, code must ask for a selection from a window or the application.
Now, on to fixing the problem. Since VBA is parsing from left to right, it looked in the application for a range, then tried to find a selection in that range, which it can't do. To get to the cells in the user's selection, the reference should look like Application.Selection.Something. Referring to Help again, the Selection property returns the object currently selected in the active window, which is a Range object. This means the code has to work with the Range object, not the Range method.
Use the Object Browser again to look at the properties and methods for the Range object. Now it becomes clear that "Cells" is the Something. It is a method that returns a collection of cells as a Range object. Since a collection of cells is returned, the Count property will work to tell how many cells were selected. Now, where do the cells come from? That's the easy part, the user just selected them. So, to take the user's selection, return it as a range, and count the cells in that range, use the code in Example 3, Application.Selection.Cells.Count.
Figure 1. Using the Object Browser to check a reference
But it's not over yet, it's time for the advanced lesson. If the user selected a range, isn't the Cells method just returning a range as another range? Yes, it is! Help for the Cells method says that applying the syntax object.Cells to a Range returns the same Range object (in other words, it does nothing). Hmmm. So try the code
If Selection.Count = 1 Then MsgBox("Only one cell")
Try this by selecting a single cell and running the macro. It works! This brings to mind two great truisms of VBA:
Truism 1: There are many ways to accomplish the same result in VBA.
Truism 2: It's easier to write complex code than it is to write simple code.
One final tip on the Object Browser. Most new users will try to search for a keyword in the left-hand pane of the browser (titled "Objects/Modules" in Figure 1 above.) If the keyword is a method or property it will not appear in this pane. For this reason, the browser works best when the programmer knows the correct object in advance. If this isn't the case, searching Help first for the keyword will return its parent object. Also remember that the most frequently-used methods and properties are children of the Application object. For these, just select "Application" on the left and then look in the right-hand pane of the browser to find the keyword of interest.