Yesterday, I was writing about how you can access worksheet functions in different ways. And I was going on and on about it to the point where I’m now recreating a slimmed down Excel Object Model in VBA. How did this topic get away from me so fast?
Anyway, I set up the CCell and CRange classes. I’m kind of working backward using this line of code
Next in line is the CWorksheet object. The CWorksheet object will have a Name property and a Range property. The Range property will return a CRange object (just like the Range property of the Worksheet object returns a Range object in Excel).
Private mclsRange As CRange
Public Property Get Name() As String
Name = msName
End Property
Public Property Let Name(ByVal sName As String)
msName = sName
End Property
Private Sub Class_Initialize()
Set mclsRange = New CRange
End Sub
Public Property Get Range(sAddress As String) As CCell
Set Range = mclsRange.Item(sAddress)
End Property
It looks pretty simple, and it is. Whenever a new CWorksheet object is created, it creates a new CRange object in the Initialize event. You’ll remember from yesterday’s post that when a CRange object is created, 40 CCell objects are created. We now have cascading Initialize events: Create a CWorksheet and you automatically get a CRange. To get access to that CRange object, we have a Range property. That property takes the Address argument and passes it to the CRange variable (mclsRange) which will then return a CCell with that Address. Whew.
Next we need a Worksheets collection object to hold all of the CWorksheet objects.
Private Sub Class_Initialize()
Dim clsWorksheet As CWorksheet
Dim i As Long
Set mcolWorksheets = New Collection
For i = 1 To 3
Set clsWorksheet = New CWorksheet
clsWorksheet.Name = “Sheet” & i
mcolWorksheets.Add clsWorksheet, clsWorksheet.Name
Next i
End Sub
Public Property Get Item(vItem As Variant) As CWorksheet
Set Item = mcolWorksheets.Item(vItem)
End Property
Whenever a CWorksheets object is created, the Intialize procedure automatically creates three CWorksheet objects named Sheet1, Sheet2, and Sheet3. Sound familiar? That’s how it works in Excel. When you create a new workbook, it creates three worksheets and bunch of cells.
We have problem, though. We need to do some fancy maneuvering if we want to use code like Worksheets("Sheet1") rather than Worksheets.Item("Sheet1"). We’ll have the same problem with the Workbooks object, so I’ll explain it when we get there.
Next on the list is the CWorkbook class. Every CWorkbook will have a Name property and a Worksheets property. The Worksheets property will give us access to a CWorksheets collection object.
Private mclsWorksheets As CWorksheets
Public Property Get Name() As String
Name = msName
End Property
Public Property Let Name(ByVal sName As String)
msName = sName
End Property
Private Sub Class_Initialize()
Set mclsWorksheets = New CWorksheets
End Sub
Public Property Get Worksheets() As CWorksheets
Set Worksheets = mclsWorksheets
End Property
When this object is created, it creates a new CWorksheets object. CWorksheets then creates three CWorksheet objects. And each of those CWorksheet objects creates a CRange object, which in turn creates 40 CCell objects. It’s a lot of power in a only a few procedures.
To hold all the CWorkbook objects, we now need a CWorkbooks collection object.
Private Sub Class_Initialize()
Set mcolWorkbooks = New Collection
End Sub
Private Sub Class_Terminate()
Set mcolWorkbooks = Nothing
End Sub
Public Function Item(vItem As Variant) As CWorkbook
Set Item = mcolWorkbooks.Item(vItem)
End Function
Public Sub Add()
Dim clsWorkbook As CWorkbook
Set clsWorkbook = New CWorkbook
clsWorkbook.Name = NextWorkbookName
mcolWorkbooks.Add clsWorkbook, clsWorkbook.Name
End Sub
Private Function NextWorkbookName() As String
Dim i As Long
Dim clsWorkbook As CWorkbook
Dim lMax As Long
Dim lTemp As Long
For i = 1 To mcolWorkbooks.Count
Set clsWorkbook = mcolWorkbooks.Item(i)
lTemp = Val(Replace(clsWorkbook.Name, “Book”, “”))
If lTemp > lMax Then lMax = lTemp
Next i
NextWorkbookName = “Book” & lMax + 1
End Function
One thing that’s different here is that I have a function to get the next valid workbook name. I based it on what Excel does, very loosely. That is, I call the next workbook Bookn, where n is a integer one higher than the highest in use.
I also have an Add method. Every object we’ve done so far gets added automatically when it’s parent object is created (Workbooks create Worksheets create Ranges). But I made the Workbook addable.
Finally, we have a CApplication object.
Private Sub Class_Initialize()
Set mclsWorkbooks = New CWorkbooks
End Sub
Public Property Get Workbooks() As CWorkbooks
Set Workbooks = mclsWorkbooks
End Property
All it does is create an new CWorkbooks object with nothing in it. We can use the Add method of the CWorkbooks collection object to fill it when we’re ready.
Before we cap it off, let’s look at the CWorkbooks collection object and how I made the Item the default property so I could use Workbooks(1) instead of Workbooks.Item(1). I right clicked on the CWorkbooks module and choose Remove CWorkbooks. I answered ‘Yes’ when it asked if I wanted to export it first. I opened CWorkbooks.cls in Notepad and added an Attribute line to the Item property.
A couple of things to note. First, the Attribute line must come directly after the Public Property Get line. Second, the Attribute line must be all the way to the left – no spaces or tabs. I also added Public Property Get NewEnum even though I’m not using it for this example. Save and Close Notepad, back to the VBE. I right click in the project and choose Import and import CWorkbooks.cls. You can’t see the Attribute line, but VBA knows what to do with it. I go through the same exercise with CWorksheets.
The last piece I need to add is a Range property to the CApplication class.
Dim clsWorkbook As CWorkbook
Dim clsWorksheet As CWorksheet
Set clsWorkbook = mclsWorkbooks.Item(1)
Set clsWorksheet = clsWorkbook.Worksheets(1)
Set Range = clsWorksheet.Range(sAddress)
End Property
This works differently than in Excel. Excel keeps track of which Workbook and Worksheet are active and using Application.Range will get a range from the Activesheet. In my mock object model, I get it from the first sheet of the first workbook. It’s a cop out, I know.
At long last, I return to Module1, run my test code, and get this thrilling result.
That’s a really long explanation just to say that you can get to a Range object directly from the Application object. But now, hopefully, you have a better understanding of how the Excel Object Model is put together and why you can use certain shortcuts. In my opinion, Excel’s object model is the best of all the Office apps. Compared to Word or Access, it’s intuitive and logical. If you’re creating an application using custom class modules, you could do worse that mimicking some of Excel’s characteristics. Characteristics like plural collection object names (Workbooks hold Workbook) and shortcut properties (Application.Sum(…)).
If you’ve come this far without jabbing a pencil in your eye, then you’ll probably want to download the workbook and F8 your way through the test procedure to see how it all fits together.
You can download ReproObjectModel.zip
Dick, I’m curious how you learned so much about the behind-the-scenes of Excel’s object model. Having read you excellent posts I’d love to read more, especially if it’s as clear as this. Do you have any recommendations?
Also, I learned early on not to use Excel reserved words for variable names, but have wondered if the same applied to class properties. Reading this the answer looks like “no.” I’m curious when exactly the reserved name restriction applies.
A lot of experimenting, but… I tech edited a book called Excel 2007 VBA by Green, Bullen, Bovey, and Alexander. I had just submitted the final chapters and was feeling pretty good. The Wrox guy emailed me and said something like “Looks great. We only have to edit Appendix A and we’ll be done. It’s about 300 pages.” I emailed him back and said that he must have had a typo in his email, but that a 30 page appendix would be no problem.
There was no typo. Appendix A is entitled “Excel 2007 Object Model”. In its 329 pages, there is an entry for every object, collection object, property, method, and event in Excel’s object model, including examples for many of them. I had just edited ~600 pages and I was only 2/3 done. But I learned a lot about the OM. Did I say a lot? I meant a f**k load.
The Application object started on page 642 and ended on 664. The Range object: 852-867.
Here are a few objects that you’ve probably never used: AutoCorrect object, CubeField object, SmartTag object, Treeview Control object, XMLSchema object.
That’s my sob story. Thanks for letting me get that off my chest.
Re Reserved Names: When the text in the VBE turns red, that’s how I know I can’t use that word. But I’ve found for properties of custom objects, that it doesn’t complain very often.
Yikes! I have the 2002 version of that book. I haven’t looked at it for a while, but it has a similarly long Appendix A, followed by the VBE and Office XP object models. I have to admit I prefer reading blogs :).
I’ve never seen a variable turn red because it was a reserved word. I just created a subroutine and declared Application, Property, Range, Name, SpecialCells, ActiveSheet and some others as doubles and assigned them values. Not a peep from the IDE and it ran fine. I did get a couple of compile errors for declaring Application and ActiveSheet as public variables in the ThisWorkbook module, but that’s it.
I try to avoid using existing “words” and object/method/property- names like the plague, because they tend to confuse me if I revisit the code later on. It can also lead to unexpected problems:
I once did a search and replace in a file where someone used an object variable called “Public CodeBook As Workbook”. It was used like so:
Set CodeBook = ThisWorkbook
So since we already have ThisWorkbook available I did a global Search and replace through the code, but made a small mistake: I also replaced the declaration, which became:
Public ThisworkBook As Workbook.
All of a sudden, I started to get Object variable not set errors on each line that addressed the Thisworkbook object.
Not realising I changed that declaration line, I thought I had corrupted the VBA project of the file. It took me over an hour to find the problem.
The punch line: Don’t use reserved words or existing names. More importantly: be careful when doing S&R in code :-)
That’s my experience Doug. I can declare variables badly with no problem, and I can use that happily (or unhappily) thereafter. many times I have declared something like
Dim row As Long
and later using the row property shows as
Range(“G1?).row
that is, row is not proper case, it takes my variable case.
Jan Karel –
I’ve learned to use the “entire word” option when doing a global search and replace on my code.
The “Find” feature in MZ Tools actually lists all instances of a search term, and it’s helpful to be able to see where and how the term is used.
Jon: I do too, but “entire word” didn’t help in the example I gave :-)
Thanks for the MZ tip, I need to look at that!