Excel 2007 Upgrade FAQ: Macros

Category: General VBA | [Item URL]

Note: I originally posted this information at Daily Dose of Excel. I've updated it slightly, and augmented it with information from commenters.

Q. How do I record a macro?

A. Click the little square icon in the bottom left of the status bar.

Q. How do I run a macro?

A. Choose Macros in the Code group of the Developer tab.

Q. I don't have a Developer tab.

A. Display the Excel Options dialog box, click Popular, and then enable 'Show Developer tab in the Ribbon.'

Q. I recorded a macro and saved my workbook. When I reopened it, the macros were gone!

A. By default, Excel proposes that you destroy your macros when you save the workbook. When you save the file, read the warning very carefully, and don't accept the default "Yes" button.

Q: I recorded a macro while formatting a chart, and the macro was empty.

A: The VBA macro recorder ignores formatting applied to individual chart elements. Maybe this will be fixed in a future service pack.

Q: Using VBA to modify Shapes is very tricky, so I tried to record a macro while working with a Shape. The macro was empty.

A: Maybe this will be fixed in a future service pack.

Q: I'm trying to automate creating a simple SmartArt diagram. Recording a macro produces an empty macro.

A: Maybe this will be fixed in a future service pack.

Q: How do I use VBA to add a simple button to the ribbon?

A: You can't. You must write XML code and insert the document into a workbook file using 3rd party tools. Or, if you're a glutton for punishment, you can do it by unzipping the document and making the edits manually.

Q: How do I use VBA to activate a particular tab.

A: Sendkeys is your only choice. Press the Alt key to find out the keysroke(s) required. For example, to switch to the Page Layout tab, use this: Application.SendKeys "%w{F6}"

Q: I'm trying to display a topic from a *.chm help file from a Messge Box or an Input Box. Using Application.Help simply displays the main Excel help window.

A: Maybe this will be fixed in a future service pack.

Q: Can I use the VBA Application.Help method to display a particular Excel 2007 help topic?

A: No, but you can use Application.Assistance.ShowHelp method. First, navigate the local Help system and identify the topic ID. Right-click, and select the "Copy xxxxxxxx" option (this copies the topic ID to the clipboard. Then use a VBA statement like this: Application.Assistance.ShowHelp "HP10062493". The text in quotes is the topic ID pasted from the clipboard.