#Running visual basic for excel 2010 code#
#Running visual basic for excel 2010 plus#
#Running visual basic for excel 2010 download#

The style numbers can be set using a simple line of code: Shape.ShapeStyle = msoShapeStylePreset XX Excel 2007 introduced the ShapeStyle property with the 42 preset styles shown below. If you take advantage of the built-in styles for Excel 2007 and Excel 2010, setting the AutoShape formatting is ridiculously easy compared to Excel 2003 and previous versions. Sub AddFormattedTextToShape(oShape As Shape, sText As String) Because it is not backward compatible, I would recommend using the TextFrame object, as shown in the following code. The TextFrame2 member was added in Excel 2007 and gives better control over the formatting of the text. The Shape object has both a TextFrame and TextFrame2 members.

#Running visual basic for excel 2010 code#

Dealing with points on a worksheet isn’t intuitive, so if you prefer you can add a shape to a given range address by using code like this: Function AddShapeToRange(ShapeType As MsoAutoShapeType, sAddress As String) As Shape The origin is the top left corner of the worksheet, with the Left and Top values increasing to the right and down, respectively. The Left, Top, Width, and Height parameters of AddShape() are specified in points. Not all the AutoShapes are available in the Shapes gallery, so this will also give you a look at some of the hidden ones.

#Running visual basic for excel 2010 download#

To see what the AutoShapeType constant is for each AutoShape, you can copy and paste the following code into the Excel Visual Basic Editor and run it (or download the sample file and run the macro). You can use them in Excel but they don’t have any special properties. The online help file states that they support mouse over and click events, but that only applies when they are used in PowerPoint presentations. AutoShapeTypes 125-136 are special AutoShapes. The AutoShapeType is a constant that ranges from 1 to 137 for Excel 2003 and earlier versions. The syntax for adding a shape is: (AutoShapeType, Left, Top, Width, Height) Or, using the For…Each syntax: Dim shp as Shape Like other collections in VBA, the Shape object is accessed either via its name or index number, as in: ActiveSheet.Shapes('SHAPE_NAME') Accessing a Shape ObjectĮach worksheet contains a Shapes collection consisting of Shape objects. They are discussed more in the Miscellaneous Issues section below. So if the AutoShapeType evaluates to 1, then you also need to check the Type property.Ĭallouts are another special type of shape that can cause confusion. It could be a Rectangle AutoShape, but it could also be anything shaped like a rectangle, such as a text box, a comment, or even a picture. The tricky part comes when the AutoShapeType is 1, which equals the AutoShape constant msoShapeRectangle. If the value is greater than 1, then the shape is one of the types display in the Shapes gallery. For example, if the AutoShapeType value is -2, then for all practical purposes the shape is not an AutoShape. Knowing when to check the Shape.Type property versus the Shape.AutoShapeType is very useful. For AutoShapes, the AutoShapeType property lets you get/set the type of shape as shown in the gallery image below. Excel has a broad range of shape Types consisting not only of AutoShapes, but also connectors, lines, pictures, charts, comments, and many other graphical items. Two properties of the Shape object will be used in the code samples below – Shape. This article attempts to provide a basic overview of working with AutoShapes using VBA and touch on some areas that are not covered extensively in the help documentation. For the most part, the online help is good, but there is some information that doesn’t get explained in detail. Thankfully, Excel 2010 has added macro support back, but like all macros the code generated is often bloated and relies heavily on the Selection object, which tends to hide the core objects in use.

On the downside, they didn’t have time to incorporate AutoShape operations in their macro recorder prior to release.

#Running visual basic for excel 2010 plus#

On the plus side, this meant new shapes, new styles, and the addition of SmartArt. With the release of Office 2007, Microsoft rewrote the drawing tools from the ground up. Programming Excel 2007 and Excel 2010 AutoShapes with VBA Nick appears online frequently, commenting on Excel-related blogs and corresponding in the various forums. Today I am pleased to present a guest post written by my colleague Nicholas Hebb.