Installing Data Analysis & Solver Add-ins for Excel 2019(Office 365) Resolved: Excel 2016 for Mac - Solver Add-in not allowing cell references to be picked Crestron AirMedia for Faculty, Staff, Students and Guests. When you're ready to bring the data into Excel, click Return Data. In the Import Data dialog box, choose where you want the data to be: either on the existing sheet, on a new sheet, or in a PivotTable.

• Data Callout Excel 2019 Mac Download
• Excel Data Callout Option
• Data Callout Excel 2019 Mac Version

With the release of Office 2007, Microsoft rewrote the drawing tools from the ground up. On the plus side, this meant new shapes, new styles, and the addition of SmartArt. On the downside, they didn't have time to incorporate AutoShape operations in their macro recorder prior to release. Thankfully, Excel 2010 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. For the most part, the online help is good, but there is some information that doesn't get explained in detail. 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.

Excel Flowchart Wizard

Definitions

Two properties of the Shape object will be used in the code samples below - Shape.Type and Shape.AutoShapeType. 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. For AutoShapes, the AutoShapeType property lets you get/set the type of shape as shown in the gallery image below.

Knowing when to check the Shape.Type property versus the Shape.AutoShapeType is very useful. For example, if the AutoShapeType value is -2, then for all practical purposes the shape is not an AutoShape. If the value is greater than 1, then the shape is one of the types display in the Shapes gallery. The tricky part comes when the AutoShapeType is 1, which equals the AutoShape constant msoShapeRectangle. 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. So if the AutoShapeType evaluates to 1, then you also need to check the Type property.

Callouts are another special type of shape that can cause confusion. They are discussed more in the Miscellaneous Issues section below.

Accessing a Shape Object

Each worksheet contains a Shapes collection consisting of Shape objects. Like other collections in VBA, the Shape object is accessed either via its name or index number, as in:

Or, using the For...Each syntax:

Adding an AutoShape

The syntax for adding a shape is:

The AutoShapeType is a constant that ranges from 1 to 137 for Excel 2003 and earlier versions. Excel 2007 added shapes 139 through 183. AutoShapeTypes 125-136 are special AutoShapes. The online help file states that they support mouse over and click events, but that only applies when they are used in PowerPoint presentations. You can use them in Excel but they don't have any special properties.

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). Not all the AutoShapes are available in the Shapes gallery, so this will also give you a look at some of the hidden ones.

The Left, Top, Width, and Height parameters of AddShape() are specified in points. The origin is the top left corner of the worksheet, with the Left and Top values increasing to the right and down, respectively. 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:

Adding Text to an AutoShape

The Shape object has both a TextFrame and TextFrame2 members. The TextFrame2 member was added in Excel 2007 and gives better control over the formatting of the text. Because it is not backward compatible, I would recommend using the TextFrame object, as shown in the following code.

Setting Border and Fill Styles

If you take advantage of the built-in styles for Excel 2007 - 2019, setting the AutoShape formatting is ridiculously easy compared to Excel 2003 and previous versions. Excel 2007 introduced the ShapeStyle property with the 42 preset styles shown below.

The style numbers can be set using a simple line of code:

Where Shape is the shape object and XX is the style number. The style numbers are shown in the image gallery in order from left to right, top to bottom. For example, the red button in the second row msoShapeStylePreset10).

Adding Connectors and Lines

Connectors and lines are different objects in Excel. Connectors are special lines that 'connect' to shapes, and if the shape is moved the connector stays connected and reroutes accordingly. Connectors cannot connect to other connectors, but they can connect to the end point of a line.

The syntax for adding a line is straightforward:

..with all coordinates as Singles. Adding a connector is a bit more complex, since you typically want it to connect two shapes. The code below calculates the begin and end points, creates the connector, attaches the connector to the two shapes, then finally does a reroute to ensure the shortest path.

Several points worth mentioning are:

• The ConnectorType can be one of three constants - msoConnectorCurve, msoConnectorElbow, or msoConnectorStraight.
• The calculations for the beginning and ending points are not normally needed. You could put any values in for the AddConnector() function because once you call BeginConnect and EndConnect, the connector is attached to the shapes and the begin and end points get set automatically.
• How the end coordinates are specified is not consistent between Excel versions. Prior to Excel 2007, the end coordinates were relative to the begin coordinates. Starting in Excel 2007, the function now uses absolute coordinates.
• When you route a Connector to an AutoShape, you need to specify the side using a connection site constant. The constants are different for each AutoShape type, but generally they start with the top side = 1 and go counter-clockwise. For example, most rectangular shapes have connection site constants where top = 1, left = 2, bottom = 3, and right = 4.
• When you call the RerouteConnections() function, it sets the connection sides automatically in order to create the shortest path between the two shapes. So, unless you want a specific routing, you can get usually just guess at the connection site values then call RerouteConnections().

Formatting Connectors and Lines

Like AutoShapes, formatting Connectors and Lines is fairly straightforward in Excel 2007 - 2019. Here is a comparison of two formatting routines for older versions of Excel versus the newer versions:

The Connector property, used above, returns a Boolean indicating whether the shape is a connector. The Type = msoLine statement checks if the shape is a line. In this case the code will format both connectors and lines the same way, but at times you may want handle them separately. (NB: The Insert Shapes gallery of Excel 2007 only lets you add Connectors, not Lines. So unless you are dealing with legacy files or add Lines via code, testing Type = msoLine may never be an issue for you.)

Like the shape styles, you can format the line style by setting the ShapeStyle to one of the msoLineStylePresetXX values, where XX matches the order they appear in the style gallery (below) from left to right, top to bottom. Note: Excel 2016 added additional styles not shown in the image below. If you use the msoLineStylePreset values for those in your code, be aware that your code will not be compatible with previous versions of Excel.

Data Callout Excel 2019 Mac Download

The Line object has several other members worth mentioning. In addition to the EndArrowheadStyle shown above, there is a corresponding BeginArrowheadStyle property, a DashStyle property, and also a Style property that lets you create double lines.

Miscellaneous Issues

Here are a few Excel 2007 issues with AutoShapes that are good to be aware of :

Excel Data Callout Option

• If you copy a set of shapes, older versions of Office gave the option to Paste Special as editable AutoShapes in other Office applications. This option no longer exists in Office 2007 and later.
• In Excel 2007 - 2013, changing the AutoShape type will disconnect any incoming or outgoing Connectors to a shape.
• Some print drivers (including PDF export handlers) do not handle printing thick Connectors well, e.g., elbow connectors may straighten. If this happens, either change the line thickness or try grouping all the shapes together prior to printing.
• The Arc is an AutoShape but needs to be treated as a line when setting the ShapeStyle property.
• Most of the styles are backward compatible except for styles 37-42 (the glossy button look in the bottom row of the style gallery). Styles 31-36 (the second row from bottom) do not render very well in Excel 2000.
• You can add Callouts using the AddShape() or AddCallout() methods, but the AddCallout() method will only let you add four types, two of which are actually the same. Callouts have AutoShapeType values in the range of 105-124. Even though they have AutoShapeType values, the Shape.Type property will return msoCallout - not msoAutoShape. Confused? Wait, there's more. The callouts with AutoShapeTypes from 105-108 actually return a Shape.Type = msoAutoShape - not msoCallout.

Sample File

The sample file includes three demo sheets. The ShapeDemo sheet contains a macro to add two shapes, format them, then add a connector and format it. The Animation sheet has a simple macro showing how to move a shape around the sheet. The CreateAutoShapes sheet has a macro to create all AutoShapes available in your version of Excel.

The ShapeDemo routine has two function calls that are commented out - IlluminateShapeText2003() and IlluminateShapeText2007(). These subs add some gaudy formatting to the first letter of each text block, but they serve to highlight some of the differences between Excel 2007 and previous versions. Two parts of the code worth looking at are the HasText property and the formatting properties of TextFrame2. With the old TextFrame object, you would have to try accessing the TextFrame.Characters.Count property, which throws an error if no text exists. As for the formatting, the font colors in Excel 2003 and previous were limited to the colors in the pallet. In Excel 2007 and later, you can add reflections, drop shadows, and glow as well as set the color to whatever RGB value your heart desires.

Lastly, there is a module MCommon containing a sub that deletes all the AutoShapes on a sheet. In order not to delete the command buttons on the sheet (which are shapes too), it creates a ShapeRange of the AutoShapes and deletes that. The online help file shows the syntax for creating a ShapeRange when you know the names of the shapes at design time, but the syntax is a bit tricky when creating one dynamically. The DeleteAllAutoShapes() sub in the sample file shows you how to do this.

About the Author

Nicholas Hebb

Nicholas Hebb is the owner and developer of BreezeTree Software, makers of FlowBreeze Flowchart Software, a text-to-flowchart maker, and Spreadspeed, an auditing and productivity toolset for Microsoft Excel^®.

Excel macros allow you to automate commands to simplify your tasks. Excel 2019 enables you to add an optional Developer tab to the Ribbon that contains its own Record Macro command button (among other command buttons that are very useful when doing more advanced work with macros). To add the Developer tab to the Excel 2019 Ribbon, follow these two steps:

1. Click File → Options or press Alt+FT to open the Excel Options dialog box.
2. Click Customize Ribbon, then select the Developer check box under Main Tabs in the Customize the Ribbon list box on the right side of the dialog box, and then click OK.

Even if you don’t add the Developer tab to the Ribbon, the Excel 2019 Status bar contains a Record Macro (to the immediate right of the Ready status indicator), and the View tab of the Ribbon contains a Macros command button with a drop-down menu containing a Record Macro option.

When you turn on the Excel macro recorder in the Record Macro dialog box — opened by clicking the Record Macro button on the Status bar (automatically added once you record your first macro), the Record Macro option on the Macros button’s drop-down menu (Alt+WMR), or even the Record Macro button on the Developer tab (Alt+LR) — the macro recorder records all your actions in the active worksheet or chart sheet when you make them.

Data Callout Excel 2019 Mac Version

The Excel macro recorder doesn’t record the keystrokes or mouse actions that you take to accomplish an action — only the VBA code required to perform the action itself. This means that mistakes that you make while taking an action that you rectify won’t be recorded as part of the macro; for example, if you make a typing error and then edit it while the macro recorder is on, only the corrected entry shows up in the macro without the original mistakes and steps taken to remedy them.

The Excel macros that you create with the macro recorder can be stored as part of the current workbook, in a new workbook, or in a special, globally available Personal Macro Workbook named PERSONAL.XLSB that’s stored in a folder called XLSTART on your hard drive. When you record an Excel macro as part of your Personal Macro Workbook, you can run that macro from any workbook that you have open. (This is because the PERSONAL.XLSB workbook is secretly opened whenever you launch Excel, and although it remains hidden, its macros are always available.) When you record macros as part of the current workbook or a new workbook, you can run those macros only when the workbook in which they were recorded is open in Excel.

When you create a macro with the macro recorder, you decide not only the workbook in which to store the macro but also what name and shortcut keystrokes to assign to the macro that you are creating. When assigning a name for your macro, use the same guidelines that you use when you assign a standard range name to a cell range in your worksheet. When assigning a shortcut keystroke to run the macro, you can assign

• The Ctrl key plus a letter from A to Z, as in Ctrl+Q
• Ctrl+Shift and a letter from A to Z, as in Ctrl+Shift+Q

You can’t, however, assign the Ctrl key plus a punctuation or number key (such as Ctrl+1 or Ctrl+/) to your macro.

To see how easy it is to create a macro with the macro recorder, follow these steps for creating a macro that enters the company name in 12-point, bold type and centers the company name across rows A through E with the Merge and Center feature:

1. Open the Excel workbook that contains the worksheet data or chart you want your macro to work with.
   If you’re building a macro that adds new data to a worksheet (as in this example), open a worksheet with plenty of blank cells in which to add the data. If you’re building a macro that needs to be in a particular cell when its steps are played back, put the cell pointer in that cell.
2. Click Record Macro button on the Status bar or Alt+WMR or Alt+LR if you have added the Developer tab to the Ribbon.
   The Record Macro dialog box opens where you enter the macro name, define any keystroke shortcut, select the workbook in which to store the macro, and enter a description of the macro’s function.
3. Replace the Macro1 temporary macro name by entering your name for the macro in the Macro Name text box.

   Remember that when naming a macro, you must not use spaces in the macro name and it must begin with a letter and not some number or punctuation symbol. For this example macro, you replace Macro1 in the Macro Name text box with the name Company_Name.

   Next, you can enter a letter between A and Z that acts like a shortcut key for running the macro when you press Ctrl followed by that letter key. Just remember that Excel has already assigned a number of Ctrl+letter keystroke shortcuts for doing common tasks, such as Ctrl+C for copying an item to the Clipboard and Ctrl+V for pasting an item from the Clipboard into the worksheet (see the Cheat Sheet for a complete list). If you assign the same keystrokes to the macro you’re building, your macro’s shortcut keys override and, therefore, disable Excel’s ready-made shortcut keystrokes.

4. (Optional) Click the Shortcut key text box and then enter the letter of the alphabet that you want to assign to the macro.

   For this example macro, press Shift+C to assign Ctrl+Shift+C as the shortcut keystroke (so as not to disable the ready-made Ctrl+C shortcut).

   Next, you need to decide where to save the new macro that you’re building. Select Personal Macro Workbook on the Store Macro In drop-down list box to be able to run the macro anytime you like. Select This Workbook (the default) when you need to run the macro only when the current workbook is open. Select New Workbook if you want to open a new workbook in which to record and save the new macro.

5. Click the Personal Macro Workbook, New Workbook, or This Workbook option on the Store Macro In drop-down list to indicate where to store the new macro.

   For this example macro, select the Personal Macro Workbook so that you can use it to enter the company name in any Excel workbook that you create or edit.

   Next, you should document the purpose and function of your macro in the Description list box. Although this step is purely optional, it is a good idea to get in the habit of recording this information every time you build a new macro so that you and your coworkers can always know what to expect from the macro when it’s run.

6. (Optional) Click the Description list box and then insert a brief description of the macro’s purpose in front of the information indicating the date and who recorded the macro.

   Now you’re ready to close the Record Macro dialog box and start recording your macro.

7. Click OK to close the Record Macro dialog box.

   The Record Macro dialog box closes, the square Stop Recording button appears on the Status bar, and the Record Macro option becomes Stop Recording on the Macros button’s drop-down menu and in the Code group on the Developer tab.

   On the Macros button’s drop-down menu on the Ribbon’s View tab and Code group on the Developer tab, you find a Use Relative References option. You click this drop-down menu item or command button when you want the macro recorder to record the Excel macro relative to the position of the current cell. For this example macro, which enters the company name and formats it in the worksheet, you definitely need to click the Use Relative References button before you start recording commands. Otherwise, you can use the macro only to enter the company name starting in cell A1 of a worksheet.

8. (Optional) Click the Use Relative References option on the Macros button’s drop-down menu on the View tab or click the Use Relative References button on the Developer tab if you want to be able to play back the macro anywhere in the Excel sheet.
9. Select the cells, enter the data, and choose the Excel commands required to perform the tasks that you want recorded just as you normally would in creating or editing the current worksheet, using the keyboard, the mouse, or a combination of the two.

   For the example macro, type the company name and click the Enter button on the Formula bar to complete the entry in the current cell. Next, click the Bold button and then click 12 on the Font Size drop-down list in the Font group on the Home tab. Finally, drag through cells A1:E1 to select this range and then click the Merge and Center command button, again on the Home tab.

   After you finish taking all the actions in Excel that you want recorded, you’re ready to shut off the macro recorder.

10. Click the Stop Recording button on the Status bar or select Stop Recording option on the View or Developer tab on the Ribbon.

    The square Stop Recording button on the Status bar turns into a Record Macro button (with an icon showing a tiny worksheet with a circle in the left corner). This lets you know that the macro recorder is now turned off and no further actions will be recorded.

After you finish recording your first macro in Excel 2019, the Record Macro button continues to appear on the Status bar whenever you use the program. This means that you can click or tap this button to open the Record Macro dialog box whenever you need to create new macros rather than having to select the Record Macro option on the View or Developer tab of the Ribbon, as described in the previous steps.

Assigning Excel macros to the Ribbon and the Quick Access toolbar

If you prefer, instead of running a macro by selecting it in the Macro dialog box or by pressing shortcut keys you assign to it, you can assign it to a custom tab on the Ribbon or a custom button on the Quick Access toolbar and then run it by clicking that custom button.

To assign an Excel macro to a custom group on a custom Ribbon tab, you follow these steps:

1. Click File → Options and then click the Customize Ribbon tab in the Excel Options dialog box (or press Alt+FTC).

   Excel displays the Customize Ribbon pane in the Excel Options dialog box.

2. Click Macros in the Choose Commands From drop-down list box on the left.
   Excel lists the names of all the macros created, both those in the current workbook and those that are saved in the PERSONAL.XLSB workbook, in the Choose Commands From list box.
3. Click the name of the custom group on the custom tab to which you want to add the macro in the Main Tabs list box on the right.

   If you haven’t already created a custom tab and group for the macro or need to create a new one, follow these steps:

   1. Click the New Tab button at the bottom of the Main Tabs list.
      Excel adds both a New Tab (Custom) and New Group (Custom) item to the Main Tabs list while at the same time selecting the New Group (Custom) item.
   2. Click the New Tab (Custom) item you just added to the Main Tabs.
   3. Click the Rename button at the bottom of the Main Tabs list box and then type a display name for the new custom tab before you click OK.
   4. Click the New Group (Custom) item right below the custom tab you just renamed.
   5. Click the Rename button and then type a display name for the new custom group before you click OK.
4. In the Choose Commands From list box on the left, click the name of the macro you want to add to the custom group now selected in the Main Tabs list box on the right.
5. Click the Add button to add the selected Excel macro to the selected custom group on your custom tab. If you want to rename the macro and/or assign it a new icon, click the Rename button and make these changes in the Rename dialog box before you click the OK button to close the Excel Options dialog box.

   After you add a macro to the custom group of a custom tab, the name of the macro appears on a button on the custom tab of the Ribbon. Then, all you have to do to run the macro is click this command button.

To assign an Excel macro to a custom button on the Quick Access toolbar, follow these steps:

1. Click the Customize Quick Access Toolbar button at the end of the Quick Access toolbar and then click More Commands on its drop-down menu.
   Excel opens the Excel Options dialog box with the Quick Access Toolbar tab selected.
2. Click Macros in the Choose Commands From drop-down list box.

   Excel lists the names of all the macros you created, both those in the current Excel workbook and those that are saved in the PERSONAL.XLSB workbook, in the Choose Commands From list box.

3. Click the name of the macro to add to a custom button on the Quick Access toolbar in the Choose Commands From list box and then click the Add button.
4. Click the Modify button to open the Modify Button dialog box if you want to change the display name and assign a different icon to the macro button.
5. Click OK to close the Excel Options dialog box.

After you close the Excel Options dialog box, a custom button sporting its associated macro icon (the default with a standard command flowchart unless you changed it) appears on the Quick Access toolbar. To see the name of the Excel macro assigned to this custom macro button as a ScreenTip, position the mouse pointer over the button. To run the macro, click the button.