Case Study – Embedded and Linked Objects
There are many instances when one has to programmatically manipulate an object inside an Excel worksheet or a Word document or a PowerPoint presentation. As some readers may have already discovered, it is not as easy to work with an object such as a MSGraph chart as it is to work with an object native to the application (e.g., in Excel, a range of cells or a chart or in Word a paragraph or a sentence or a word). Native objects have their own specific property for quick access (in Excel: Range or Cells or Charts; in Word: Paragraphs or Sentences or Words). For non-native objects the Visual Basic Editor (VBE), by default, provides little or no Intellisense support. Indeed, it is often a chore to just identify the kind of object one is dealing with. For reasons best known to Microsoft, it embeds every object in any Office document in a generic container – the Shape object. Yes, there are some advantages as we will see shortly to this concept but it also represents an additional object layer that must be traversed before one gets to the actual object.
The Shapes collection contains every embedded object in an Excel worksheet, Word document, or PowerPoint presentation. How broad is the scope of the shapes collection? It’s broad. It encompasses every object – a rectangle or some other object from the Drawing toolbar, an embedded MSGraph chart, a picture, an audio clip, a video clip, an Excel chart in an Excel worksheet – each is a member of the Shapes collection. The collection even includes a cell comment in an Excel worksheet as well as a file from another Office application. Further, the Shapes collection is not restricted to embedded objects but also includes linked objects. So, for example, both a picture wholly contained within an Office document and a picture linked to an external file are members of the Shapes collection. As is an Excel workbook embedded in a Word document or inserted as a linked object in a PowerPoint presentation.
Example 1: The reader can explore the Shapes collection quite easily. Open an Office application such as Excel. Enter some numbers in a column. Create a chart in the worksheet. Switch to the Visual Basic Editor (VBE) with ALT+F11 (or Tools | Macro > Visual Basic Editor). Open the Immediate Window with CTRL+G (or View | Immediate Window). Use CTRL+END to go to the bottom of the window. On a new line, type ?Activesheet.Shapes.Count then press the ENTER key. This gives the number of shapes on the activesheet and should be 1. Move back to the Excel worksheet and insert a comment in some cell. Switch back to the VBE and check the number of shapes. The result should be 2. Go back to the worksheet and insert, say, a rectangle from the Drawing toolbar. Again, check the number of shapes. Do the same with an ActiveX object – insert a MSGraph chart with Insert | Chart… Or, insert a document from an external file with Insert | Object… In the resulting Object dialog box select the Create from file tab. Use the Browse… button to find, say, a Word document. Back in the Object dialog box, experiment with the Link to File option. In each case, switch to the VBE and check the number of shapes on the activesheet.
Gary C on Dec 23, 2011:
I have been struggling with copy/paste of multiple object types from Excel to PowerPoint. This page really helped me write better code. Thanks so much.
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:
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.
Dim i As Integer
For i = 2 To .Count
.Item(i).Left = .Item(1).Left
.Item(i).Top = .Item(i - 1).Top + .Item(i - 1).Height
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.
The code below toggles the color of the interior of the rectangle between red and gray.
Dim aWS As Worksheet, aShape As Shape, aRect As Rectangle
Set aWS = ActiveSheet
Set aShape = aWS.Shapes(3)
Set aRect = aShape.OLEFormat.Object
If .Color <> &HFF Then
.Color = RGB(255, 0, 0)
.Color = RGB(127, 127, 127)
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.
.Color = IIf(.Color <> &HFF, RGB(255, 0, 0), RGB(127, 127, 127))
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.
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).
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.
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
aChart.ChartType = xlArea
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).
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.
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.
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
An embedded Word document
Dealing with a linked object in contrast to an embedded object