Writing Excel Macros with VBA, 2nd Edition

Appendix A. The Shape Object

Now we want to take a brief look at the issue of drawing pictures using VBA code. Since this subject is not fundamental to Excel VBA programming, we will be very brief, but hopefully this introduction will give you the necessary background for further study using the VBA help files.

What Is the Shape Object?

Each Excel sheet (chartsheet or worksheet) and each Excel chart has a drawing layer upon which we can place drawing objects. A drawing object is represented by a Shape object.

As usual, the Shape objects for a sheet are stored in a Shapes collection. The Chart object and the Worksheet object both have a Shapes property that returns the collection of all Shape objects drawn on the chart or worksheet.

There is also a ShapeRange object that holds a collection of selected Shape objects, much as a Range object can contain a collection of selected cells. The ShapeRange object allows us to set the properties of a subcollection of all Shape objects.

The Shape-related objects are shown in Figure A-1.

Figure A-1. The Shape-related objects

Z-Order

Every Shape object has an order, called its z-order , that indicates the object’s relative position with respect to an imaginary z-axis that comes directly out of the monitor at right angles, towards the user, as pictured in Figure A-2.

Figure A-2. Illustrating z-order

The read-only ZOrderPosition property of a Shape ...