Now, that we know that every object in any Office document is a member of the Shapes collection, here are two twists.  Some types of objects that come quite naturally to developers and consumers actually do not exist on their own but inside of a container object.  One such entity is possibly the single most popular object in Excel, if not across all Office applications.  In Excel, when one creates a chart embedded in a worksheet it exists inside a container called a chartobject.  It is the chartobject that is directly within the worksheet and the chart is contained within it.  Consequently, it is the chartobject, and not the chart, that is a member of the Shapes collection.

The other twist is this.  While all objects are members of the Shapes collection, some may also be members of a more specific collection.  For example, a chartobject is a member of the Shapes collection and the ChartObjects collection.  Similarly, any object that is actually an OLE object (such as an embedded MSGraph chart or an embedded Office document) is a member of both the Shapes collection and the OLEObjects collection.  Similar to checking for the number of shapes on the activesheet, one can also check the number of elements in the ChartObjects and the OLEObjects collections. Use the same syntax as for the Shapes collection but replace Shapes by ChartObjects or OLEObjects as appropriate.

We now know two important pieces of information.  Every embedded object in any Office application is a member of the Shapes collection.  In VBA, the Shapes property provides access to the Shapes collection.  Also, some objects are members of another collection – something more specific to the object’s type.

Accessing an individual object

One can access a specific element in a collection with the syntax <Collection name>(<index>) where <index> is either a number or a name.  Hence, in Excel ActiveSheet.Shapes refers to an entire Shapes collection, Activesheet.Shapes(1) provides access to the first shape on the activesheet, and Activesheet.Shapes(“Rectangle 1”) refers to the shape named Rectangle 1. 

The same concept applies to those objects that belong to multiple collections such as a ChartObject.  Consider an Excel worksheet that has only one object in it– an embedded chart.  Then, Activesheet.Shapes(1) would refer to the shape that contains the chartobject and Activesheet.ChartObjects(1) would refer to the chartobject directly.

Example 2: The reader can leverage this knowledge almost immediately to learn what kind of object each of the various shapes contains.  Suppose a worksheet contains a chartobject, a comment, a rectangle, and maybe some ActiveX objects as in the previous example.  From the Visual Basic Editor’s Immediate Window, enter ?Typename(ActiveSheet.Shapes(1)).  The answer should be Shape.  Then try ?Typename(ActiveSheet.Shapes(1).OLEFormat.Object) and the response should be ChartObject.  What that tells us is that the type of the container is Shape and it contains an object whose type is ChartObject

To find the name of an object, switch back to Excel and select the object by clicking it.  The Name Box (the dropdown box at the extreme left of the formula bar) will contain the name.  Use that name to check ?Typename(ActiveSheet.Shapes(“rectangle 1040”).OLEFormat.Object).  The answer should be Rectangle.  In all likelihood, Rectangle 1040 will not be the name on your computer.  Use the name shown in the Name Box.

Note that the object contained in a shape is referenced not with <Shape>.Object but with <Shape>.OLEFormat.Object.

Working with a shape

Using a single common container (i.e., the Shape object) for every kind of object has some advantages.  One can carry out certain programmatic manipulations without even knowing the kind of object that is inside the shape.  For example, aligning objects on the worksheet (or in a PowerPoint slide) can be done by working with the shape itself.  Similarly, one can resize a shape object without knowing what the shape contains.

Example 3: Try this for yourself.  Suppose a worksheet has a chartobject and a comment as in example 1.  Make the comment visible (Right-click the cell containing the comment and select Show/Hide Comments).  Now, from the VBE’s Immediate Window, type the statement below and finish with the ENTER key:

activesheet.shapes(2).top=activesheet.shapes(1).top+activesheet.shapes(1).height

Then, enter: activesheet.shapes(2).left=activesheet.shapes(1).left

The two statements together position the objects so that their left edges are aligned and the comment textbox’s top edge is flush with the chart’s bottom edge.  Note that the VBA code did not know – or care – what kind of object was inside each shape.  Similarly, the code below aligns all objects in the activesheet so that each is left-aligned with the first object and the top of each object is flush with the bottom of the previous one.

Sub LeftAlignAllObjects()

    Dim i As Integer

    With ActiveSheet.Shapes

    For i = 2 To .Count

        .Item(i).Left = .Item(1).Left

        .Item(i).Top = .Item(i - 1).Top + .Item(i - 1).Height

        Next i

        End With

    End Sub

Code Sample 1

To learn more about that one can do with a Shape object, use the VBE’s intellisense capability or check the VBA help file for the Shape object.

Working with a specific type of object

The previous section showed how to manipulate the various shapes without knowing what the shape contained.  At other times, we want to manage the object that is inside the shape.  To do that, we need to know not only what kind of object the shape contains but also the object’s properties, methods and events.

The first question, then, becomes how does one find the kind of object that a shape contains?  We have already seen the answer in the previous section.  VBA’s TypeName() function is an important diagnostic and information tool.  Once we know the kind of object we have, we can declare a variable of that type and then leverage VBE’s Intellisense capability.  In some cases, we will have to take an extra step and add a project reference to the relevant library.  Irrespective of the specific steps, once we declare a variable of the appropriate type, we will gain a lot of additional resources in working with the object; and, of course, one can always look up VBA help for that object.

We will analyze six objects, each more complex than the previous.  By the end of the last one, the reader should have enough understanding of the analysis methodology to write code for a heretofore unknown object.

Working with a Rectangle

In all likelihood, the simplest object that can be embedded in a shape is probably a drawing object such as the rectangle in Example 1.  If we know that an object is a rectangle, we would declare a variable of type Rectangle.  Then, VBA’s Intellisense would give us a fair amount of help with the object’s properties and methods.

How do we find out we are dealing with a rectangle?  With the TypeName() function in the VBE’s Immediate Window as in Example 2.  ?Typename(ActiveSheet.Shapes(3).OLEFormat.Object) will yield Rectangle.

Once we know it is a rectangle, we declare a variable of that type.  Subsequently, VBE’s Intellisense capability will be available to us as in Figure 1.

Figure 1

The code below toggles the color of the interior of the rectangle between red and gray.

Sub toggleRectInteriorColor()

    Dim aWS As Worksheet, aShape As Shape, aRect As Rectangle

    Set aWS = ActiveSheet

    Set aShape = aWS.Shapes(3)

    Set aRect = aShape.OLEFormat.Object

    With aRect.Interior

    If .Color <> &HFF Then

        .Color = RGB(255, 0, 0)

    Else

        .Color = RGB(127, 127, 127)

        End If

        End With

    End Sub

Code Sample 2

For both learning and diagnosis, it can be very useful to declare each object encountered in the journey through the object model hierarchy starting with the worksheet and ending with the rectangle as in Code Sample 2.  At the same time, one should also keep in mind the principle of direct coding.  The code below provides the same functionality  as Code Sample 2 but doesn’t declare variables that are used only once.  Obviously, since we don’t tell VBE the kind of sheet or the kind of object in the shape, its ability to help us is limited.  It is up to the reader to decide which extreme style – or something in-between – is most appropriate for her or him.

Sub toggleRectInteriorColor2()

    With ActiveSheet.Shapes(3).OLEFormat.Object.Interior

    .Color = IIf(.Color <> &HFF, RGB(255, 0, 0), RGB(127, 127, 127))

        End With

    End Sub

Code Sample 3

Working with an embedded Forms control

Excel supports user interface controls of two different types.  The first type is from the Forms toolbar.  These are controls that were originally introduced with Excel 5 and are completely native to Excel.  The other type of controls (accessible from the Control Toolbox toolbar), introduced with Excel 97, are actually controls that are shared by all Office applications.  These are external to Excel and require a reference to the MSForms library (a reference that, luckily, is automatically established by Excel).  These are ActiveX controls (also known as OLE controls) and, since the Macintosh doesn’t support any ActiveX functionality, cannot be used by Excel on the Macintosh.

Now, we look at a control from the Forms toolbar.  Later, we look at the ActiveX variety.

Working with an embedded Excel chart

The principle that applied a rectangle object also works with an Excel chart with the additional twists mentioned in the introduction to this case study.  Remember that a chart is actually contained in a ChartObject.  So, the method we use for a rectangle will only get us to a chartobject.  In the Immediate Window ?Typename(ActiveSheet.Shapes(3).OLEFormat.Object) will yield ChartObject and Set aChartObj = ActiveSheet.Shapes(1).OLEFormat.Object refers to a variable of type ChartObject. How do we get from the ChartObject to the chart?  We use a two step process that bootstraps the development of the code.  This bootstrapping mechanism will be particularly useful for more complex object such as ActiveX components embedded in an Office document.

The first step of the bootstrap, which we have already completed, ended with the discovery that the object contained in the Shape is a ChartObject.  In the second step, we write a short routine that includes a variable of that type, i.e., ChartObject.  With this code, we use Intellisense (or VBA help) to explore this new object and discover that it provides a Chart property.

Figure 2

Since it looks promising, we can check the type of object returned by the Chart property. ?Typename(ActiveSheet.Shapes(3).OLEFormat.Object.Chart) will yield Chart.  Alternatively, step through the above code, and after the Set x=…statement, use the Immediate Window to check ?Typename(x). 

Figure 3

Once its type is established as Chart, the next step in the bootstrap is to appropriately modify the declaration of the variable.  This will place the full capability of the VBE’s Intellisense at our disposal.  The example below toggles the type of chart between a line chart and an area chart.

Sub toggleEmbeddedChartType()

    Dim aChartObj As ChartObject, aChart As Chart

    Set aChartObj = ActiveSheet.Shapes(1).OLEFormat.Object

    Set aChart = aChartObj.Chart

    If aChart.ChartType = xlArea Then

        aChart.ChartType = xlLine

    Else

        aChart.ChartType = xlArea

        End If

    End Sub

Code Sample 4

 

 

Working with an ActiveX control

 

So far we have looked at objects that were ‘native’ to Excel, so to say.  With the bootstrap technique demonstrated above, we are ready to tackle objects that are external to Excel.

The simplest objects are the ActiveX controls that are part of the Office Control Toolbox.  The controls can be used both in the context of a userform or directly in a worksheet.  To insert a control such as a checkbox in a worksheet, open the toolbar named Control Toolbox (see Figure 4).

Figure 4

Insert a checkbox on the worksheet surface by selecting it in the toolbar and then clicking on the worksheet.

 

 

Working with an embedded MSGraph object

Next, we look at something that is a bit more complex.  However, with the bootstrap method discussed earlier the task is just as easy as in the previous sections.  We shift our attention to a MSGraph chart.  While not many would insert an MSGraph chart into Excel, it is very popular in the context of other programs such as PowerPoint.  The technique for analyzing and manipulating an object in PowerPoint is the same as in Excel including the bootstrap method.  Consequently, we will stick with Excel.  To follow along with the example below, create a new workbook and, in an otherwise empty worksheet, insert an MSGraph chart with Insert | Chart…

The analysis starts, as before, with ?typename(activesheet.shapes(1).oleformat.object).  This time the response is OLEObject.  Using the bootstrap method we check ?typename(activesheet.shapes(1).oleformat.object.object) to learn that the type of the object is Chart.  How do we know this is a Microsoft Graph chart and not an Excel chart?  We check the application that the object belongs to with ?activesheet.shapes(1).oleformat.object.object.application.name and the result is Microsoft Graph.  So, now we know it is a MS Graph chart and not an Excel chart.  Next, we set a reference to the Microsoft Chart object library with Tools | References…  Scroll down to the entry for the Graph object library and select it.

Figure 5

As soon as we set the reference, the VBE Intellisense capability will help us access the various objects, properties, and methods that are part of the MS Graph application.  For example, to access the y-axis of the embedded chart, we create a variable of type Graph.Chart, another of type Graph.Axis, and then use the Intellisense drop-down selection capability as shown in Figure 5.  The complete code (see Code Sample 5) toggles the visible major gridlines of the value axis.

Figure 6

Sub toggleEmbeddedGraphYGridlines()

    Dim aGraphChart As Graph.Chart, anAxis As Graph.Axis

    Set aGraphChart = ActiveSheet.Shapes(1).OLEFormat.Object.Object

    Set anAxis = aGraphChart.Axes(xlValue)

    anAxis.HasMajorGridlines = Not anAxis.HasMajorGridlines

    End Sub

Code Sample 5

An embedded Word document

 

Dealing with a linked object in contrast to an embedded object