I know you’re thinking that class module week is over, and you’re right. This post is more about the Excel Object Model and how it is set up. It stems from a couple of comments on another post that correctly point out that
Application.WorksheetFunction.Transpose(...) do the same thing.
For this example, we’ll look at the Application object and how it relates to the Range object. The hierarchy goes like this: Application => Workbook => Worksheet => Range. Yet you can access a Range object directly from the Application object. The key point to know is that when you type
Application.Range... you’re not accessing the Range object directly, rather you’re using the Range property of the Application object. A subtle distinction, I’m sure you’ll agree. When you’re first learning the Excel Object Model, it’s much more convenient and less confusing to think of
Application.Workbooks(1).Worksheets(2).Range("A1") as a string of four objects connected by periods. But in reality, you’re getting a Range object from the Range property of a Worksheet object. You get the Worksheet object from the (default) Item property of a Worksheets collection object. You get the Worksheets collection object from the Worksheets property of a Workbook object, which comes from the Item property of a Workbooks collection object, which comes from the Workbooks property of the Application object. Well, you can see why book authors use the shortcut to explain this.
Let’s look at how having different properties that return the same object works by writing the Excel Object Model in VBA. Well, kind of. Create class modules in a new workbook named CApplication, CCell, CRange, CWorkbook, CWorkbooks, CWorksheet, CWorksheets and a standard module for testing. Or you can download the workbook at the end of this post to follow along.
Here’s the code I want to run and have it work.
Dim clsApplication As CApplication
Set clsApplication = New CApplication
clsApplication.Workbooks(1).Worksheets(“Sheet1”).Range(“D7”).Value = 37
Debug.Print clsApplication.Workbooks(1).Worksheets(“Sheet1”).Range(“D7”).Value, _
I want to create a new Application object, add a workbook to it, assign a value to a “cell”, and finally access that cell through a big hierarchy AND through the Application object.
We’ll start with the CRange object and move up to the Application object. We’re going to make an abbreviated Range object – rather than all the cells normally found in a worksheet, ours will only be A1:D10. The Range object is a bit odd as it’s really a collection object, and so it will be with our CRange object. Our CRange object will be a collection of CCell objects. Each CCell object will have two properties; Address and Value. CCell looks like this:
Private msAddress As String
Public Property Get Value() As Variant
Value = mvValue
Public Property Let Value(ByVal vValue As Variant)
mvValue = vValue
Public Property Get Address() As String
Address = msAddress
Public Property Let Address(ByVal sAddress As String)
msAddress = sAddress
CRange looks like this:
Private Sub Class_Initialize()
Dim clsCell As CCell
Dim i As Long, j As Long
Set mcolCells = New Collection
For i = 1 To 4
For j = 1 To 10
Set clsCell = New CCell
clsCell.Address = Chr$(64 + i) & j
mcolCells.Add clsCell, clsCell.Address
Private Sub Class_Terminate()
Set mcolCells = Nothing
Public Property Get Item(sAdd As String) As CCell
Set Item = mcolCells.Item(sAdd)
Whenever we create a new Range collection object, 40 cells are automatically created with addresses like A1, C8, etc. and no values. This is like when you insert a new worksheet into a workbook. In addition to creating a Worksheet object, you’re also creating millions of cells. OK, not really because Excel’s Range object is a lot more sophisticated than mine, but you get the idea. With CRange, you can only get at one CCell at a time. That’s a pretty big departure from Excel’s Range object, but I don’t have all day here.
Tomorrow we’ll code the rest of the class modules and see if we can get that test procedure to run.
You can download ReproObjectModel.zip