Chapter 29: Frequently Asked Questions about Excel Programming

In This Chapter

• Understanding Excel quirks

• Exploring FAQs about Excel programming

• Getting VBE help

Getting the Scoop on FAQs

People tend to ask the same questions about Excel programming (and related topics), so I put together a list of frequently asked questions (FAQs).

Although this FAQ list won't answer all your questions, it covers many common questions and might set you straight about a thing or two.

I organized this list of questions by assigning each question to one of these categories:

• General Excel questions

• Visual Basic Editor (VBE)

• Sub procedures

• Function procedures

• Objects, properties, methods, and events

• Security-related issues

• UserForms

• Add-ins

• Excel user interface modification

In some cases, my classifications are arbitrary; a question could justifiably be assigned to other categories. Moreover, questions within each category are listed in no particular order.

By the way, most of the information in this chapter is discussed in greater detail in other chapters in this book.

General Excel Questions

How do I record a macro?

Click the little square icon in the left side of the status bar, at the bottom of Excel's window.

How do I run a macro?

Choose View⇒Macros⇒Macros (or press Alt+F8). Or choose Developer⇒Code⇒Macros. The Macros dialog box will display a list of all available macros.

What do I do if I don't have a Developer tab?

Right-click anywhere in the Ribbon and choose Customize the Ribbon. In the Customize Ribbon tab of the Excel Options dialog box, place a check mark next to Developer (which is in the list labeled Main tabs).

I recorded a macro and saved my workbook. When I reopened it, the macro was gone! Where did it go?

Excel proposes that you destroy your macros when you first save a new workbook. When you save the file, read Excel's warning carefully and don't accept the default Yes button. If your workbook contains macros, you must save it as an XLSM file, not an XLSX file.

How do I hide the Ribbon so that it doesn't take up so much space?

One way is to use the Ribbon Display Options control, in the Excel title bar. This control gives you three Ribbon display options.

Or just double-click a tab to hide the Ribbon. When you click a tab, the Ribbon redisplays. Double-click a tab to revert to normal.

Yet another option is to press Ctrl+F1. This key combination toggles the visibility of the Ribbon.

By using an XLM macro, you can remove the Ribbon completely:

ExecuteExcel4Macro “SHOW.TOOLBAR(“”Ribbon””,False)”

When this statement is executed, the user can't make the Ribbon visible. The only way to display the Ribbon again is to rerun the XLM code, with the last argument set to True.

Where are my old custom toolbars?

If the toolbars are set to display automatically, click the Add-Ins tab, and you'll see them in the Custom Toolbars group.

Can I make my old custom toolbars float?

No, you can't. The old custom toolbars are fixed in place in the Add-Ins⇒Custom Toolbars group.

How can I hide the status bar?

You must use VBA to hide the status bar. The following statement will do the job:

Application.DisplayStatusBar = False

Is there a utility that will convert my Excel application into a stand-alone .exe file?

No.

Why doesn't Ctrl+A select all the cells in my worksheet?

The cell pointer is probably inside a table. When the active cell is in a table, you must press Ctrl+A three times to select all worksheet cells. The first keypress selects the data cells, the second keypress selects the data cells and header row, and the third keypress selects all cells in the worksheet.

If the active cell is in a block of cells, Ctrl+A selects the entire range. Pressing Ctrl+A again selects all cells in the worksheet.

Why is the Custom Views command disabled?

Your workbook probably contains a table. Convert the table to a range, and then you can use Views⇒Workbook Views⇒Custom Views. Nobody (except Microsoft) knows why that command is disabled when the workbook contains a table.

How can I add a drop-down list to a cell so the user can choose a value from the list?

This technique doesn't require any macros. Type the list of valid entries in a single column. You can hide this column from the user if you want. Select the cell or cells that will display the list of entries, choose Data⇒Data Tools⇒Data Validation, and then click the Settings tab in the Data Validation dialog box. From the Allow drop-down list, select List. In the Source box, enter a range address or a reference to the single-column list on your sheet. Make sure the In-Cell Dropdown check box is selected. If the list is short, you can simply type the items, each separated by a comma.

I use Application.Calculation to set the calculation mode to manual. However, this seems to affect all workbooks and not just the active workbook.

The Calculation property is a member of the Application object. Therefore, the calculation mode affects all workbooks. You can't set the calculation mode for only one workbook. Excel 2000 and later versions provide a new Worksheet object property called EnableCalculation. When this property is False, the worksheet will not be calculated, even if the user requests a calculation. Setting the property to True will cause the sheet to be calculated.

What happened to the ability to “speak” the cell contents?

To use those commands, you must customize your Quick Access toolbar or customize the Ribbon. Perform these tasks in the Excel Options dialog box. The speech commands are listed in the Commands Not in the Ribbon category (they all begin with the word Speak).

I opened a workbook, and it has only 65,546 rows. What happened?

By default, worksheet in Excel 2007 and later contain 1,048,576 rows and 16,384 columns. If you're not seeing this many rows and columns, the workbook is in compatibility mode. When Excel opens a workbook that was saved in a previous version's file format, it doesn't automatically convert it to an Excel 2007 workbook. You need to do the conversion manually: Save the workbook in the newer format (*.xlsx or *.xlsm), close it, and then reopen it. You'll then see the additional rows and columns.

How do I get my old workbook to use the new fonts?

Beginning with Excel 2007, the default font is much easier to read because it is not as cramped-looking as in previous versions. To force an old workbook to use these new fonts, press Ctrl+N to create a blank workbook. Activate your old workbook and choose the Home tab. Click the very bottom of the vertical scroll bar in the Styles gallery and choose Merge Styles. In the Merge Styles dialog box, double-click the new workbook you created with Ctrl+N, and the old styles will be replaced with the new styles. But this technique works only with cells that haven't been formatted with other font attributes. For example, bold cells retain their old fonts. For these cells, you must update the styles manually.

How do I get a print preview?

Print preview occurs automatically when you choose File⇒Print. Another option is to use the Page Layout view (the icon on the right side of the status bar).

To get the old-style print preview, you need to use VBA. The following statement displays a print preview for the active sheet:

ActiveSheet.PrintPreview

When I switch to a new document theme, my worksheet no longer fits on a single page. Why?

The new theme probably uses different fonts. After applying the theme, use the Page Layout⇒Themes⇒Fonts control to select your original fonts to use with the new theme. Or modify the font size for the Normal style. If page fitting is critical, you should choose the theme before you do much work on the document.

How do I get rid of the annoying dotted-line page break display in Normal view mode?

Open the Excel Options dialog box, click the Advanced tab, scroll down to the Display Options for This Worksheet section, and remove the check mark from Show Page Breaks.

Can I add that Show Page Breaks option to my Quick Access toolbar or to the Ribbon?

No. For some reason, this useful command can't be added to the Quick Access toolbar or Ribbon. You can turn off the page break display by using this VBA statement:

ActiveSheet.DisplayPageBreaks = False

I'm trying to apply a table style to a table, but it has no visible effect. What can I do?

The table cells were probably formatted manually. Select the cells and set the fill color to No Fill and the font color to Automatic. You can then apply a table style.

Can I change the color of the sheet tabs?

Right-click the sheet tab and select Tab Color. Tab colors will change if you apply a different document theme.

Can I write VBA macros that play sounds?

Yes, you can play WAV and MIDI files, but it requires Windows Application Programming Interface (API) functions. You might prefer to take advantage of the Speech object. The following statement, when executed, greets the user by name:

Application.Speech.Speak (“Hello” & Application.UserName)

When I open a workbook, Excel asks whether I want to update the links. I've searched all my formulas and can't find any links in this workbook. Is this a bug?

Probably not. Try using the Edit Links dialog box (choose Data⇒Connections⇒Edit Links). In the Edit Links dialog box, click Break Link. Keep in mind that links can occur in places other than formulas. If you have a chart in your workbook, click each data series in the chart and examine the SERIES formula in the formula bar. If the formula refers to another workbook, you've identified the link. To eliminate it, move the chart's data into the current workbook and re-create your chart.

If your workbook contains any Excel 5/95 dialog sheets, select each object in each dialog box and examine the formula bar. If any object contains a reference to another workbook, edit or delete that reference.

Choose Formulas⇒Defined Names⇒Name Manager. Scroll down the list in the Name Manager dialog box and examine the Refers To column. Delete names that refer to another workbook or that contain an erroneous reference (such as #REF!). This is the most common cause of phantom links.

Where can I find examples of VBA code?

The Internet has thousands of VBA examples. A good starting point is my website at http://spreadsheetpage.com. Or do a search at http://google.com.

Visual Basic Editor

Can I use the VBA macro recorder to record all my macros?

No. Recording is useful for simple macros only. Macros that use variables, looping, or any other type of program-flow changes can't be recorded. In addition, you can't record Function procedures. you can, however, often take advantage of the macro recorder to write some parts of your code or to discover relevant properties or methods.

I have some general macros that I would like to have available all the time. What's the best way to do this?

Consider storing those general-purpose macros in your Personal Macro Workbook, a (normally) hidden workbook that is loaded automatically by Excel. When you record a macro, you have the option of recording it to your Personal Macro Workbook. The file, Personal.xlsb, is stored in your XLStart directory.

I can't find my Personal Macro Workbook. Where is it?

The Personal.xlsb file doesn't exist until you record a macro to it and then close Excel.

When I insert a new module, it always starts with an Option Explicit line. What does this mean?

If Option Explicit is included at the top of a module, it means that you must declare every variable before you use it in a procedure (which is a good idea). If you don't want this line to appear in new modules, activate VB Editor, choose Tools⇒Options, click the Editor tab, and clear the Require Variable Declaration check box. Then you can either declare your variables or let VBA handle the data typing automatically.

Why does my VBA code appear in different colors? Can I change these colors?

VBA uses color to differentiate various types of text: comments, keywords, identifiers, statements with a syntax error, and so on. You can adjust these colors and the font used by choosing the Tools⇒Options command (Editor Format tab) in VBE.

Can I delete a VBA module by using VBA code?

Yes. The following code deletes Module1 from the active workbook:

With ActiveWorkbook.VBProject

    .VBComponents.Remove .VBComponents(“Module1”)

End With

This might not work, though. See the next question.

I wrote a macro that adds VBA code to the VB project. When my colleague tries to run it, he gets an error message. What's wrong?

Excel has a setting that determines whether VBA code can modify a VB Project: Trust Access to Visual Basic Project. By default, this setting is turned off. To change it, choose File⇒Options⇒Trust Center. Click the Trust Center Settings button to display the Trust Center dialog box. Click the Macro Settings tab and place a check mark next to Trust Access to the VBA Project Object Model.

How can I write a macro to change the user's macro security setting? I want to avoid the security message when my application is opened.

The ability to change the security level using VBA would render the entire macro security system worthless. Think about it.

How does the UserInterfaceOnly option work when protecting a worksheet?

When protecting a worksheet using VBA code, you can use a statement such as

ActiveSheet.Protect UserInterfaceOnly:=True

This causes the sheet to be protected, but your macros can still make changes to the sheet. It's important to understand that this setting isn't saved with the workbook. When the workbook is reopened, you'll need to re-execute the statement to reapply the UserInterfaceOnly protection.

How can I tell whether a workbook has a macro virus?

In VB Editor, activate the project that corresponds to the workbook. Examine all the code modules (including the ThisWorkbook code module) and look for unfamiliar VBA code. Usually, virus code won't be formatted well and will contain many unusual variable names. Another option is to use a commercial virus-scanning program.

Why do I get an error message when I try to concatenate two strings with the concatenation operator (&) in VBA?

VBA is probably interpreting the ampersand as a type-declaration character. Make sure that you insert a space before and after the concatenation operator.

I can't seem to get the VBA line continuation sequence (underscore) to work.

The line continuation sequence is actually two characters: a space followed by an underscore. If you omit the space, it won't work.

I distributed an Excel application to many users. On some machines, my VBA error-handling procedures don't work. Why not?

The error-handling procedures won't work if the user has the Break on All Errors option set. This option is available in the General tab of the Options dialog box in VB Editor (choose Tools⇒Options). You can't change this setting with VBA.

Another possibility is that the user has installed Microsoft's Euro Currency Tools add-in. Older versions of that add-in are known to cause problems with other add-ins.

Procedures

What's the difference between a VBA procedure and a macro?

Nothing, really. The term macro is a carry-over from the old days of spreadsheets. These terms are now used interchangeably.

What's a procedure?

A procedure is a grouping of VBA instructions that can be called by name. If these instructions are to give an explicit result (such as a value) back to the instruction that called them, they most likely belong to a Function procedure. Otherwise, they probably belong to a Sub procedure.

What is a variant data type?

Variables that aren't specifically declared are assigned the Variant type by default, and VBA automatically converts the data to the proper type when it's used. This behavior is particularly useful for retrieving values from a worksheet cell when you don't know in advance what the cell contains. Generally, it's a good idea to specifically declare your variables with the Dim, Public, or Private statement because using variants is slower and is an inefficient use of memory.

What's the difference between a variant array and an array of variants?

A variant is a unit of memory with a special data type that can contain any kind of data: a single value or an array of values (that is, a variant array). The following code creates a variant that contains a three-element array:

Dim X As Variant

X = Array(30, 40, 50)

A normal array can contain items of a specified data type, including nontyped variants. The following statement creates an array that consists of three variants:

Dim X (0 To 2) As Variant

Although a variant containing an array is conceptually different from an array whose elements are of type Variant, the array elements are accessed in the same way.

What's a type-definition character?

VBA lets you append a character to a variable's name to indicate the data type. For example, you can declare the MyVar variable as an integer by tacking % onto the name, as follows:

Dim MyVar%

VBA supports these type-declaration characters:

• Integer: %

• Long: &

• Single: !

• Double: #

• Currency: @

• String: $

Type-definition characters are included primarily for compatibility. Declaring variables by using words is the standard approach.

I would like to create a procedure that automatically changes the formatting of a cell based on the data that I enter. For example, if I enter a value greater than 0, the cell's background color should be red. Is this possible?

It's certainly possible, and you don't need any programming. Use Excel's Conditional Formatting feature, accessed with the Home⇒Styles⇒Conditional Formatting command.

The Conditional Formatting feature is useful, but can I perform other types of operations when data is entered into a cell?

You can take advantage of the Change event for a worksheet object. Whenever a cell is changed, the Change event is triggered. If the code module for the Sheet object contains a procedure named Worksheet_Change, this procedure will be executed automatically.

What other types of events can be monitored?

Lots! Search the Help system for events to get a complete listing.

I tried entering an event procedure (Sub Workbook_Open), but the procedure isn't executed when the workbook is opened. What's wrong?

You probably put the procedure in the wrong place. Workbook event procedures must be in the code module for the ThisWorkbook object. Worksheet event procedures must be in the code module for the appropriate Sheet object, as shown in the VB Editor Project window.

Another possibility is that macros are disabled. Check your settings in the Trust Center dialog box (accessible from the Excel Options dialog box).

I can write an event procedure for a particular workbook, but can I write an event procedure that will work for any workbook that's open?

Yes, but you need to use a class module. Details are in Chapter 17.

I'm familiar with creating formulas in Excel. Does VBA use the same mathematical and logical operators?

Yes. And VBA includes the following additional operators that aren't valid in worksheet formulas:

Operator	Function
	Division with an integer result
Eqv	Returns True if both expressions are true or both are false
Imp	A bitwise logical implication on two expressions (rarely used)
Is	Compares two object variables
Like	Compares two strings by using wildcard characters
Xor	Returns True if only one expression is true

How can I execute a procedure that's in a different workbook?

Use the Run method of the Application object. The following instruction executes a procedure named Macro1 located in the Personal.xlsb workbook:

Run “Personal.xlsb!Macro1”

Another option is to add a reference to the workbook. Do this by choosing the Tools⇒References command in VBE. After you've added a reference, you can then run the procedures in the referenced workbook without including the name of the workbook.

I've used VBA to create several custom functions. I like to use these functions in my worksheet formulas, but I find it inconvenient to precede the function name with the workbook name. Is there any way around this?

Yes. Convert the workbook that holds the function definitions to an XLAM add-in. When the add-in is open, you can use the functions in any other worksheet without referencing the function's filename.

In addition, if you set up a reference to the workbook that contains the custom functions, you can use the function without preceding it with the workbook name. To create a reference, choose the Tools⇒References command in VB Editor.

I would like a particular workbook to be loaded every time I start Excel. I would also like a macro in this workbook to execute automatically. Am I asking too much?

Not at all. To open the workbook automatically, just store it in your XLStart directory. To have the macro execute automatically, create a Workbook_Open macro in the code module for the workbook's ThisWorkbook object.

I have a workbook that uses a Workbook_Open procedure. Is there a way to prevent this from executing when I open the workbook?

Yes. Hold down Shift when you open the file. To prevent a Workbook_BeforeClose procedure from executing, press Shift when you close the workbook. Using the Shift key won't prevent these procedures from executing when you're opening an add-in.

Can a VBA procedure access a cell's value in a workbook that isn't open?

VBA can't do it, but Excel's old XLM language can. Fortunately, you can execute XLM from VBA. Here's a simple example that retrieves the value from cell A1 on Sheet1 in a workbook named myfile.xlsx in the c:files directory:

MsgBox ExecuteExcel4Macro(“'c:files[myfile.xlsx]Sheet1'!R1C1”)

Note that the cell address must be in R1C1 notation.

Unless the file is very large, it may be simpler to just open the workbook, retrieve the values, and then close the workbook. If you turn off screen updating, the user won't even notice that a workbook was opened and closed.

How can I prevent the “save file” prompt from being displayed when I close a workbook from VBA?

You can use this statement:

ActiveWorkbook.Close SaveChanges:=False

Or you can set the workbook's Saved property to True by using a statement like this:

ActiveWorkbook.Saved = True

This statement, when executed, doesn't actually save the file, so any unsaved changes will be lost when the workbook is closed.

A more general solution to avoid Excel prompts is to insert the following instruction:

Application.DisplayAlerts = False

Normally, you'll want to set the DisplayAlerts property back to True after the file is closed.

How can I run a particular macro once every hour?

You need to use the OnTime method of the Application object. This enables you to specify a procedure to execute at a particular time of day. When the procedure ends, use the OnTime method again to schedule another event in one hour.

How do I prevent the display of a macro in the macro list?

To prevent the macro from being listed in the Macro dialog box (displayed by using View⇒Macros⇒Macro), declare the procedure by using the Private keyword:

Private Sub MyMacro()

Or you can add a dummy optional argument, declared as a specific data type:

Sub MyMacro (Optional FakeArg as Long)

Can I save a chart as a .gif file?

Yes. The following code saves the first embedded chart on Sheet1 as a .gif file named Mychart.gif:

Set CurrentChart = Sheets(“Sheet1”).ChartObjects(1).Chart

Fname = ThisWorkbook.Path & “Mychart.gif”

CurrentChart.Export Filename:=Fname, FilterName:=”GIF”

Are variables in a VBA procedure available to other VBA procedures?

You're referring to a variable's scope. The scope of a variable can be any of three levels: local, module, and public. Local variables have the narrowest scope and are declared within a procedure. A local variable is visible only to the procedure in which it was declared. Module-level variables are declared at the top of a module, before the first procedure. Module-level variables are visible to all procedures in the module. Public variables have the broadest scope, and they're declared by using the Public keyword.

Functions

I created a VBA function for use in worksheet formulas. However, it always returns #NAME?. What went wrong?

You probably put the function in the code module for a Sheet (for example, Sheet1) or in the ThisWorkbook module. Custom worksheet functions must reside in standard VBA modules.

I wrote a VBA function that works perfectly when I call it from another procedure but doesn't work when I use it in a worksheet formula. What's wrong?

VBA functions called from a worksheet formula have some limitations. In general, they must be strictly passive. That is, they can't change the active cell, apply formatting, open workbooks, or change the active sheet. If the function attempts to do any of these things, the formula will return an error.

When I access a custom worksheet function with the Insert Function dialog box, it reads “No help available.” How can I get the Insert Function dialog box to display a description of my function?

To add a description for your custom function, activate the workbook that contains the Function procedure. Then choose View⇒Macros⇒Macros to display the Macro dialog box. Your function won't be listed, so you must type it in the Macro Name box. After typing the function's name, click Options to display the Macro Options dialog box. Enter the descriptive text in the Description box.

Can I also display help for the arguments for my custom function in the Insert Function dialog box?

Yes. Excel 2010 added a new argument to the MacroOptions method. You can write a macro to assign descriptions to your function arguments. See Chapter 8 for details.

My custom worksheet function appears in the User Defined category in the Insert Function dialog box. How can I make my function appear in a different function category?

You need to use VBA to do this. The following instruction assigns the function named MyFunc to Category 1 (Financial):

Application.MacroOptions Macro:=”MyFunc”, Category:=1

See Chapter 8 for a list of category numbers and names.

How can I create a new function category?

Specify a text string for the Category argument in the MacroOptions method. Here's an example:

Application.MacroOptions Macro:=”MyFunc”, Category:=”XYZ Corp Functions”

I have a custom function that will be used in a worksheet formula. If the user enters inappropriate arguments, how can I make the function return a true error value (#VALUE!)?

If your function is named MyFunction, you can use the following instruction to return an error value to the cell that contains the function:

MyFunction = CVErr(xlErrValue)

In this example, xlErrValue is a predefined constant. Constants for the other error values are listed in the Help system.

I use a Windows API function in my code, and it works perfectly. I gave the workbook to a colleague, and he gets a compile error. What's the problem?

Most likely, your colleague uses a 64-bit version of Excel. API declarations must be designated as “PtrSafe” to work with 64-bit Excel. For example, the following declaration works with 32-bit Excel versions but causes a compile error with 64-bit Excel 2010 or 64-bit Excel 2013:

Declare Function GetWindowsDirectoryA Lib “kernel32” _

  (ByVal lpBuffer As String, ByVal nSize As Long) As Long

In many cases, making the declaration compatible with 64-bit Excel is as simple as adding PtrSafe after the Declare keyword. Adding the PtrSafe keyword works for most commonly used API functions, but some functions might require that you change the data types for the arguments.

The following declaration is compatible with both 32-bit and 64-bit versions of Excel 2010 and Excel 2013:

Declare PtrSafe Function GetWindowsDirectoryA Lib “kernel32” _

  (ByVal lpBuffer As String, ByVal nSize As Long) As Long

However, the code will fail in Excel 2007 (and earlier versions) because the PtrSafe keyword isn't recognized. Here's an example of how to use compiler directives to declare an API function that's compatible with 32-bit Excel (including versions prior to Excel 2010) and 64-bit Excel:

#If VBA7 And Win64 Then

  Declare PtrSafe Function GetWindowsDirectoryA Lib “kernel32” _

  (ByVal lpBuffer As String, ByVal nSize As Long) As Long

#Else

  Declare Function GetWindowsDirectoryA Lib “kernel32” _

  (ByVal lpBuffer As String, ByVal nSize As Long) As Long

#End If

The first Declare statement is used when VBA7 and Wind64 are both True — which is the case only for 64-Bit Excel. In all other versions, the second Declare statement is used.

How can I force a recalculation of formulas that use my custom worksheet function?

To force a single formula to be recalculated, select the cell, press F2, and then press Enter. To force all formulas and functions to be recalculated, press Ctrl+Alt+F9.

Can I use Excel's built-in worksheet functions in my VBA code?

In most cases, yes. You access Excel's worksheet functions via the WorksheetFunction method of the Application object. For example, you could access the SUM worksheet function with a statement such as the following:

Ans = Application.WorksheetFunction.Sum(Range(“A1:A3”))

This example assigns the sum of the values in A1:A3 (on the active sheet) to the Ans variable.

Generally, if VBA includes an equivalent function, you can't use Excel's worksheet version. For example, because VBA has a function to compute square roots (Sqr), you can't use the SQRT worksheet function in your VBA code.

Can I force a line break in the text of a message box?

Use a carriage return or a linefeed character to force a new line. The following statement displays the message box text on two lines (vbNewLine is a built-in constant that represents a carriage return):

MsgBox “Hello” & vbNewLine & Application.UserName

Objects, Properties, Methods, and Events

Is there a listing of the Excel objects I can use?

Yes. The Help system has that information.

I'm overwhelmed with all the properties and methods available. How can I find out which methods and properties are available for a particular object?

You can use the Object Browser available in VBE. Press F2 to access Object Browser and then choose Excel from the Libraries/Workbooks drop-down list. The Classes list (on the left) shows all the Excel objects. When you select an object, its corresponding properties and methods appear in the Member Of list on the right.

You can also get a list of properties and methods as you type. For example, enter the following:

Range(“A1”).

When you type the dot, you'll see a list of all properties and methods for a Range object. If the list doesn't appear, choose Tools⇒Options (in VBE), click the Editor tab, and place a check mark next to Auto List Members. Unfortunately, Auto List Members doesn't work for all objects. For example, you won't see a list of properties and methods when you type this statement:

ActiveSheet.Shapes(1).

But if you declare an object variable, Auto List Members will work. Here's an example of declaring an object variable:

Dim S as Shapes

And, of course, the Help system for VBA is extensive; it lists the properties and methods available for most objects of importance. The easiest way to access these lists is to type the object name into the Immediate window at the bottom of VBE and move the cursor anywhere in the object name. Then press F1, and you'll get the help topic appropriate for the object.

What's the story with collections? Is a collection an object?

A collection, which is an object that contains a group of related objects, is designated by a plural noun. For example, the Worksheets collection is an object that contains all the Worksheet objects in a workbook. You can think of this as an array (although a collection is not an array): Worksheets(1) refers to the first Worksheet object in the Workbook. Rather than use index numbers, you can also use the actual worksheet name, such as Worksheets(“Sheet1”). The concept of a collection makes it easy to work with all related objects at once and to loop through all objects in a collection by using the For Each-Next construct.

When I refer to a worksheet in my VBA code, I get a “subscript out of range” error. I'm not using any subscripts. What gives?

This error occurs when you attempt to access an element in a collection that doesn't exist. For example, the following instruction generates the error if the active workbook doesn't contain a worksheet named MySheet:

Set X = ActiveWorkbook.Worksheets(“MySheet”)

How can I prevent the user from scrolling around the worksheet?

You can either hide the unused rows and columns or use a VBA instruction to set the scroll area for the worksheet. The following instruction, for example, sets the scroll area on Sheet1 so that the user can't activate any cells outside of B2:D50:

Worksheets(“Sheet1”).ScrollArea = “B2:D50”

To set scrolling back to normal, use a statement like this:

Worksheets(“Sheet1”).ScrollArea = “”

Keep in mind that the ScrollArea setting is not saved with the workbook. Therefore, you need to execute the ScrollArea assignment instruction whenever the workbook is opened. This instruction can go in the Workbook_Open event-handler procedure.

What's the difference between using Select and Application.Goto?

The Select method of the Range object selects a range on the active worksheet only. Use Application.Goto to select a range on any worksheet in a workbook. Application.Goto might or might not make another sheet the active sheet. The Goto method also lets you scroll the sheet so that the range is in the upper-left corner.

What's the difference between activating a range and selecting a range?

In some cases, the Activate method and the Select method have exactly the same effect. But in other cases, they produce different results. Assume that range A1:C3 is selected. The following statement activates cell C3. The original range remains selected, but C3 becomes the active cell — that is, the cell that contains the cell pointer.

Range(“C3”).Activate

Again, assuming that range A1:C3 is selected, the following statement selects a single cell, which also becomes the active cell:

Range(“C3”).Select

Can I quickly delete all values from a worksheet yet keep the formulas intact?

Yes. The following code works on the active sheet and deletes all nonformula cells. (The cell formatting isn't affected.)

On Error Resume Next

Cells.SpecialCells(xlCellTypeConstants, 23).ClearContents

The second argument, 23, is the sum of the values of the following built-in constants: xlErrors (16), xlLogical (4), xlNumbers (1), and xlTextValues (2).

Using On Error Resume Next prevents the error message that occurs if no cells qualify.

I know how to write a VBA instruction to select a range by using a cell address, but how can I write one to select a range if I know only its row and column numbers?

Use the Cells method. The following instruction, for example, selects the cell in the 5th row and the 12th column (that is, cell L5):

Cells(5, 12).Select

How can I turn off screen updating while a macro is running?

The following instruction turns off screen updating and speeds up macros that modify the display:

Application.ScreenUpdating = False

When your procedure ends, the ScreenUpdating property is set back to True. However, you can resume screen updating at any time by executing this statement:

Application.ScreenUpdating = True

I wrote a macro that uses a loop to animate a chart, but I don't see any animation.

Try inserting the following statement inside your loop:

DoEvents

What's the easiest way to create a range name in VBA?

If you turn on the macro recorder while you name a range, you get code something like this:

Range(“D14:G20”).Select

ActiveWorkbook.Names.Add Name:=”InputArea”, _

    RefersToR1C1:=”=Sheet1!R14C4:R20C7”

A much simpler method is to use a statement like this:

Sheets(“Sheet1”).Range(“D14:G20”).Name = “InputArea”

How can I determine whether a particular cell or range has a name?

You need to check the Name property of the Name object contained in the Range object. The following function accepts a range as an argument and returns the name of the range (if it has one). If the range has no name, the function returns False.

Function RangeName(rng) As Variant

    On Error Resume Next

    RangeName = rng.Name.Name

    If Err <> 0 Then RangeName = False

End Function

I have a lengthy macro, and it would be nice to display its progress in the status bar. Can I display messages in the status bar while a macro is running?

Yes. Assign the text to the StatusBar property of the Application object. Here's an example:

Application.StatusBar = “Now processing File “ & FileNum

Before your routine finishes, return the status bar back to normal with either of the following instructions:

Application.StatusBar = False

Application.StatusBar = “”

I recorded a VBA macro that copies a range and pastes it to another area. The macro uses the Select method. Is there a more efficient way to copy and paste?

Yes. Although the macro recorder generally selects cells before doing anything with them, selecting is not necessary and can slow down your macro. Recording a simple copy-and-paste operation generates four lines of VBA code, two of which use the Select method. Here's an example:

Range(“A1”).Select

Selection.Copy

Range(“B1”).Select

ActiveSheet.Paste

These four lines can be replaced with a single instruction, such as the following:

Range(“A1”).Copy Range(“B1”)

Note that this instruction doesn't use the Select method.

I have not been able to find a method to sort a VBA array. Does this mean that I have to copy the values to a worksheet and then use the Range.Sort method?

There is no built-in way to sort an array in VBA. Copying the array to a worksheet is one method, but you can also write your own sorting procedure. Many sorting algorithms are available, and some are easy to code in VBA. This book contains VBA code for several sorting techniques.

My macro works with the selected cells, but it fails if something else (such as a chart) is selected. How can I make sure that a range is selected?

You can use the VBA TypeName function to check the Selection object. Here's an example:

If TypeName(Selection) <> “Range” Then

    MsgBox “Select a range!”

    Exit Sub

End If

Another approach is to use the RangeSelection property, which returns a Range object that represents the selected cells on the worksheet in the specified window, even if a graphic object is active or selected. This property applies to a Window object — not a Workbook object. The following instruction, for example, displays the address of the selected range:

MsgBox ActiveWindow.RangeSelection.Address

How can I determine if a chart is activated?

Use a block of code like this:

If ActiveChart Is Nothing Then

  MsgBox “Select a chart”

  Exit Sub

End If

The message box will be displayed only if a chart isn't activated. (This includes embedded charts and charts on a chart sheet.)

My VBA macro needs to count the number of rows selected by the user. Using Selection.Rows.Count doesn't work when nonadjacent rows are selected. Is this a bug?

Actually, this is the way it's supposed to work. The Count method returns the number of elements in only the first area of the selection (a noncontiguous selection has multiple areas). To get an accurate row count, your VBA code must first determine the number of areas in the selection and then count the number of rows in each area. Use Selection.Areas.Count to count the number of areas. Here's an example that stores the total number of selected rows in the NumRows variable:

NumRows = 0

For Each area In Selection.Areas

    NumRows = NumRows + area.Rows.Count

Next area

By the way, this process is also relevant to counting selected columns and cells.

I use Excel to create invoices. Can I generate a unique invoice number?

One way to do this is to use the Windows Registry. The following code demonstrates:

Counter = GetSetting(“XYZ Corp”, “InvoiceNum”, “Count”, 0)

Counter = Counter + 1

SaveSetting “XYZ Corp”, “InvoiceNum”, “Count”, Counter

When these statements are executed, the current value is retrieved from the Registry, incremented by 1, and assigned to the Counter variable. Then this updated value is stored back to the Registry. You can use the value of Counter as your unique invoice number.

You can adapt this technique for other purposes. For example, you can keep track of the number of times a workbook has been opened by including similar code in a Workbook_Open procedure.

Is there a workbook property that forces an Excel workbook to always remain visible so it won't be hidden by another application's window?

No.

Is there a VBA instruction to select the last entry in a column or row? Normally, I can use Ctrl+Shift+ or Ctrl+Shift+→ to do this, but how can I do it with a macro?

The VBA equivalent for Ctrl+Shift+ is the following:

Selection.End(xlDown).Select

The constants used for the other directions are xlToLeft, xlToRight, and xlUp.

How can I determine the last nonempty cell in a particular column?

The following instruction displays the address of the last nonempty cell in column A:

MsgBox ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Address

But that instruction won't work if the last cell in the column is not empty. To handle that unlikely occurrence, use this code:

With ActiveSheet.Cells(Rows.Count, 1)

    If IsEmpty(.Value) Then

        MsgBox .End(xlUp).Address

    Else

        MsgBox .Address

    End If

End With

VBA references can be lengthy, especially when I need to fully qualify an object by referencing its sheet and workbook. Can I reduce the length of these references?

Yes. use the Set statement to create an object variable. Here's an example:

Dim MyRange as Range

Set MyRange = ThisWorkbook.Worksheets(“Sheet1”).Range(“A1”)

After the Set statement is executed, you can refer to this single-cell Range object simply as MyRange. For example, you can assign a value to the cell with the following:

MyRange.Value = 10

Besides making it easier to refer to objects, using object variables can also help your code execute more quickly.

Can I declare an array if I don't know how many elements it will have?

Yes. You can declare a dynamic array with the Dim statement by using empty parentheses; then allocate storage for that array later with the ReDim statement when you know how many elements the array should have. Use ReDim Preserve if you don't want to lose the current array's contents when reallocating it.

How can I undo previous actions when I execute a macro?

You can't. Unfortunately, running a macro in Excel destroys the Undo stack.

Can I let the user undo my macro?

In some cases, yes — but undoing a macro can't be done automatically. And it does not restore the Undo stack.

To enable the user to undo the effects of your macro, your VBA code module must keep track of what was changed by the macro and then be capable of restoring the original state if the user chooses Undo.

To enable the Undo command, use the OnUndo method as the last action in your macro. This method enables you to specify text that will appear on the Undo menu item and also to specify a procedure to run if the user chooses Undo. Here's an example:

Application.OnUndo “The Last Macro”, “MyUndoMacro”

See Chapter 14 for more information about undoing a macro.

Can I pause a macro so the user can enter data into a certain cell?

You can use Excel's InputBox statement to get a value from a user and place it in a particular cell. The first instruction that follows, for example, displays an input box. When the user enters a value, that value is placed in cell A1.

UserVal = Application.InputBox(prompt:=”Value?”, Type:=1)

If TypeName(UserVal)<>”Boolean” Then Range(“A1”) = UserVal

VBA has an InputBox function as well as an InputBox method for the Application object. Are these the same?

No. Excel's InputBox method is more versatile because it allows a user to select a range. In addition, Excel's InputBox method allows validation of the user's entry. The preceding example uses 1 (which represents a numeric value) for the Type argument of the InputBox method. This ensures that the user enters a value into the input box.

I'm trying to write a VBA instruction that creates a formula. To do so, I need to insert a quote character (“) within quoted text. How can I do that?

Assume that you want to enter the following formula into cell B1 with VBA:

=IF(A1=”Yes”,TRUE,FALSE)

The following instruction generates a syntax error because of the embedded quote characters:

Range(“B1”).Formula = “=IF(A1=”Yes”,TRUE,FALSE)”   ‘erroneous

The solution is to use two double quotes side by side. When two quotes are embedded within another set of quotes, Excel interprets the double quote characters as a single quote. The following instruction produces the desired result:

Range(“B1”).Formula = “=IF(A1=””Yes””,TRUE,FALSE)”

Another approach is to use the VBA Chr function with an argument of 34, which returns a quotation mark. The following example demonstrates:

Range(“B1”).Formula = _

  “=IF(A1=” & Chr(34) & “Yes” & Chr(34) & “,TRUE,FALSE)”

Yet another technique is to compose your formula using apostrophes in place of the quote marks. Then use the VBA Replace function to replace the apostrophes with quote characters:

MyFormula = “=IF(A1='Yes',TRUE,FALSE)”

Range(“B1”).Formula = Replace(MyFormula, “'”, Chr(34))

I created an array, but the first element in that array is being treated as the second element. What's wrong?

Unless you tell it otherwise, VBA uses 0 as the first index number for an array. If you want all your arrays to always start with 1, insert the following statement at the top of your VBA module:

Option Base 1

Or you can specify the upper and lower bounds of an array when you declare it. Here's an example:

Dim Months(1 To 12) As String

I would like my VBA code to run as quickly as possible. Any suggestions?

Here are a few general tips:

• Make sure that you declare all your variables. Use Option Explicit at the top of your modules to force yourself to do this.

• If you reference an Excel object more than once, create an object variable for it.

• Use the With-End With construct whenever possible.

• If your macro writes information to a worksheet, turn off screen updating by using Application.ScreenUpdating = False.

• If your application enters data into cells that are referenced by one or more formulas, set the calculation mode to manual to avoid unnecessary calculations.

Security-Related Issues

When I open a file, why does Excel indicate that macros have been disabled when the workbook has no macros?

You will see this warning even if a workbook contains an empty VBA module. Remove the empty module and you won't see the message.

How can I ensure that everyone who opens my workbook enables macros?

There's no way to ensure that, but one approach is to make the workbook useless if macros are not enabled. For example, you can hide critical worksheets, and unhide them via a macro. But this method isn't foolproof.

How do I protect the code in my add-in from being viewed by others?

Activate VBE and choose Tools⇒xxxx Properties (where xxxx is the name of your project). Click the Protection tab, select Lock Project for Viewing, and enter a password. Then save the file.

Are my add-ins safe? In other words, if I distribute an XLAM file, can I be assured that no one else will be able to view my code?

Protect your add-in by locking it with a password. This prevents most users from being able to access your code. Recent versions of Excel have improved security features, but the password still might be broken by using any of a number of utilities. Bottom line? Don't think of an XLAM as being a secure file.

I locked my VBA project with a password, and I forget what it was. Is there any way to unlock it?

Several third-party password-cracking products exist. Use a web search engine to search for Excel password. The existence of these products should tell you that Excel passwords aren't very secure.

How can I write a macro to change the password of my project?

You can't. The protection elements of a VBA project aren't exposed in the object model. Most likely, this was done to make it more difficult for password-cracking software.

I wrote a macro that creates other macros. It works great on my system, but it doesn't work for others.

Most likely, the other users haven't enabled the setting called Trust Access to the VBA Project Object Model. This setting is available in the Macro Settings tab of the Trust Center dialog box. See Chapter 26 for more information.

UserForms

My macro needs to get just a few pieces of information from the user, and a UserForm seems like overkill. Are there any alternatives?

Yes, check out the VBA MsgBox function and its InputBox function. Alternatively, you might want to use the Excel InputBox method. See Chapter 10 for examples that use these functions.

I have 12 CommandButtons on a UserForm. How can I assign a single macro to be executed when any of the buttons are clicked?

You can't do this easily because each CommandButton has its own Click event procedure. One solution is to call another procedure from each of the CommandButton_Click procedures. Another solution is to use a class module to create a new class. This technique is described in Chapter 13.

How can I display a chart in a UserForm?

You can't display a chart in a UserForm directly. One solution is to write a macro that saves the chart to a GIF file and then loads the GIF file into an Image control on the UserForm. You'll find an example in Chapter 13.

How can I remove the X from the title bar of my UserForm? I don't want the user to click that button to close the form.

Removing the close button on a UserForm's title bar requires some complex API functions. A simpler approach is to intercept all attempts to close the UserForm by using a UserForm_QueryClose event procedure in the code module for the UserForm. The following example doesn't allow the user to close the form by clicking the close button:

Private Sub UserForm_QueryClose _

  (Cancel As Integer, CloseMode As Integer)

    If CloseMode = vbFormControlMenu Then

        MsgBox “You can't close the form like that.”

        Cancel = True

    End If

End Sub

I created a UserForm with controls that are linked to cells on the worksheet with the ControlSource property. Is this the best way to do this?

Probably not. In some cases, using links to worksheet cells can slow your application because the worksheet is recalculated every time a control changes the cell. In addition, if your UserForm has a Cancel button, the cells might have already been changed when the user clicks Cancel.

Can I create a control array for a UserForm? It's possible with Visual Basic, but I can't figure out how to do it with Excel VBA.

You can't create a control array, but you can create an array of Control objects. The following code creates an array consisting of all CommandButton controls:

Private Sub UserForm_Initialize()

    Dim Buttons() As CommandButton

    Cnt = 0

    For Each Ctl In UserForm1.Controls

        If TypeName(Ctl) = “CommandButton” Then

            Cnt = Cnt + 1

            ReDim Preserve Buttons(1 To Cnt)

            Set Buttons(Cnt) = Ctl

        End If

    Next Ctl

End Sub

Is there any difference between hiding a UserForm and unloading a UserForm?

Yes. The Hide method keeps the UserForm in memory but makes it invisible. The Unload statement unloads the UserForm, beginning the termination process (invoking the Terminate event for the UserForm) and removing the UserForm from memory.

How can I make my UserForm stay open while I do other things?

By default, each UserForm is modal, which means that it must be dismissed before you can do anything else. However, you can make a UserForm modeless by using vbModeless as the argument for the Show method. Here's an example:

UserForm1.Show vbModeless

I need to display a progress indicator like those you see when you're installing software and a lengthy process is being executed. How can I do this?

You can display a progress indicator with a UserForm. Chapter 13 describes several different techniques, including one in which the code gradually stretches a shape inside a frame while the lengthy macro is running.

How can I use Excel's shapes on my UserForm?

You can't use the shapes directly with a UserForm, but you can do so indirectly. Start by adding a shape to a worksheet. Then select the shape and press Ctrl+C to copy it. Activate your UserForm and insert an Image object. Press F4 to display the Properties window. Select the Picture property and press Ctrl+V to paste the Clipboard contents to the Image control. You might also need to set the AutoSize property to True.

How can I generate a list of files and directories in my UserForm so the user can select a file from the list?

There's no need to do that. Use the VBA GetOpenFilename method. This method displays an Open dialog box in which the user can select a drive, directory, and file. This method doesn't open the selected file, so you need to write additional code.

I need to concatenate strings and display them in a ListBox control. But when I do so, they aren't aligned properly. How can I get them to display equal spacing between strings?

You can use a monospaced font such as Courier New for the ListBox. A better approach, however, is to set up your ListBox to use two or more columns. (See Chapter 12 for details.)

Is there an easy way to fill a ListBox or ComboBox control with items?

Yes. You can use an array. The statement that follows adds three items to ListBox1:

ListBox1.List = Array(“Jan”, “Feb”, “Mar”)

Can I display a built-in Excel dialog box from VBA?

Many of Excel's dialog boxes can be displayed by using the Application.Dialogs method. For example, the following instruction displays the dialog box that enables you to format numbers in cells:

Application.Dialogs(xlDialogFormatNumber).Show

However, this method isn't reliable, and not all of Excel's dialog boxes are available.

A better option is to execute Ribbon commands (including those that display a dialog box) by using the ExecuteMso method along with the control name. The statement that follows, for example, displays the dialog box that enables you to format numbers in a cell:

Application.CommandBars.ExecuteMso(“NumberFormatsDialog”)

See Chapter 20 for more information.

I tried the technique described in the preceding question and received an error message. Why is that?

The ExecuteMso method will fail if the context isn't appropriate. For example, the following statement displays the Insert Cells dialog box. But if you execute this statement when a chart is selected or the worksheet is protected, you'll get an error message.

Application.CommandBars.ExecuteMso “CellsInsertDialog”

Every time I create a UserForm, I go through the steps of adding an OK button and a Cancel button. Can I get these controls to appear automatically?

Yes. Set up a UserForm with the controls that you use most often. Then choose File⇒Export File to save the UserForm. When you want to add a new form to another project, choose File⇒Import File.

Can I create a UserForm without a title bar?

Yes, but it requires some complex API functions. See Chapter 13 for an example.

When I click a button on my UserForm, nothing happens. What am I doing wrong?

Controls added to a UserForm do nothing unless you write event-handler procedures for them. These procedures must be located in the code module for the UserForm, and they must have the correct name.

Can I create a UserForm whose size is always the same, regardless of the video display resolution?

You can, but it's probably not worth the effort. You can write code to determine the video resolution and then use the Zoom property of a UserForm to change its size. The normal way to deal with this matter is simply to design your UserForm for the lowest resolution that will be used.

Can I create a UserForm box that lets the user select a range in a worksheet by pointing?

Yes. Use the RefEdit control for this. See Chapter 12 for an example.

Can I change the startup position of a UserForm?

Yes. You can set the UserForm's Left and Top properties, but you also need to set the UserForm's StartUpPosition property to 0.

I use a system with two monitors, and UserForms don't display in the center of Excel's window. Can I force the UserForm to be centered?

Yes. Use the following code to display your UserForm:

With UserForm1

  .StartUpPosition = 0

  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)

  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)

  .Show 0

End With

Can I make a UserForm that's resizable by the user?

Yes. See Chapter 13 for an example.

Add-Ins

Where can I get Excel add-ins?

You can get Excel add-ins from a number of places:

• Excel includes several add-ins that you can use whenever you need them. Use the Add-Ins dialog box to install them.

• You can download more add-ins from the Microsoft Office Update website.

• Third-party developers distribute and sell add-ins for special purposes.

• Many developers create free add-ins and distribute them through their Internet sites.

• You can create your own add-ins.

How do I install an add-in?

The most common way to install an add-in is by using the Add-Ins dialog box. Choose File⇒Options. In the Excel Options dialog box, select the Add-Ins tab. Then select Excel Add-ins from the Manage drop-down control and click Go. A quicker method to display the Add-Ins dialog box is to press Alt+TI. Or, if the Developer tab is displayed, choose Developer⇒Add-Ins⇒Add-Ins.

You can also open an add-in by using the File⇒Open command, but using the Add-Ins dialog box is the preferred method. An add-in opened with File⇒Open can't be closed without using VBA.

When I install my add-in from Excel's Add-Ins dialog box, it shows up without a name or description. How can I give my add-in a description?

Before creating the add-in, use the File⇒Info⇒Properties⇒Advanced Properties command to display the Properties dialog box. Click the Summary tab. In the Title field, enter the text that you want to appear in the Add-Ins dialog box. In the Comments field, enter the description for the add-in. Then create the add-in as usual.

I have several add-ins that I no longer use, but I can't figure out how to remove them from the Add-Ins Available list in the Add-Ins dialog box. What's the story?

Oddly, you cannot remove unwanted add-ins from the list directly from Excel. One way to remove an add-in from the list is to move or delete the add-in file. Then, when you attempt to open the add-in from the Add-Ins dialog box, Excel will ask whether you want to remove the add-in from the list. Answer yes.

How do I create an add-in?

Activate any worksheet and then choose File⇒Save As. Then select Excel Add-in (*.xlam) from the Save as Type drop-down list. The add-in is created, and the original workbook remains open.

I try to create an add-in, but the Save as Type drop-down box doesn't provide Add-in as an option.

The most likely reason is that the active sheet isn't a worksheet. An add-in must have at least one worksheet, and a worksheet must be the active sheet when you save the file as an add-in.

If I create an add-in, will it work with the Excel Web App (the online version of Excel)?

No, the Excel Web App doesn't support add-ins or even macros.

Will my add-ins work with the version of Excel that runs on Windows RT ARM-based devices?

No, these devices don't support add-ins or macros.

Should I convert all my essential workbooks to add-ins?

No! Although you can create an add-in from any workbook, not all workbooks are suitable. When a workbook is converted to an add-in, it's essentially invisible. For most workbooks, being invisible isn't a good thing.

Do I need to keep two copies of my workbook: the XLSM version and the XLAM version?

No, you can edit an add-in and even convert an add-in back to a normal workbook.

How do I modify an add-in after it has been created?

If you need to modify only the VBA code, no special action is required; you can access the code from VB Editor and then save your changes in VBE. If you need to modify information on a worksheet, activate VB Editor (press Alt+F11) and then set the IsAddIn property of the ThisWorkbook object to False. Make your changes to the worksheet, set the IsAddIn property to True, and resave the file.

What's the difference between an XLSM file and an XLAM file created from an XLSM file? Is the XLAM version compiled? Does it run faster?

There isn't a great deal of difference between the files, and you generally won't notice any speed differences. VBA code is always compiled before it's executed, whether it's in an XLSM file or an XLAM file. However, XLAM files still contain the actual VBA code — not some special optimized code that runs faster. Another difference is that the workbook is never visible in an XLAM file.

User Interface

How do I use VBA to add a button to the Ribbon?

You can't. You must write special XML code (known as RibbonX code) and insert the XML document into a workbook file by using third-party tools. Or, if you're a glutton for punishment (and know what you're doing), you can unzip the document and make the edits manually.

What are my options for modifying the user interface to make it easy for a user to run my macros?

In Excel 2010 and later, you have these choices:

• Modify the Ribbon by adding RibbonX code (not an easy task).

• Add a new item to a right-click shortcut menu by using RibbonX code (not an easy task).

• Add your macro to the Quick Access toolbar (a manual task that's not possible to perform using VBA).

• Add your macro to the Ribbon (a manual task that's not possible to perform using VBA).

• Assign a shortcut key to the macro.

• Use VBA to add a new menu item to a right-click shortcut menu. In Excel 2013, this method has some limitations.

• Use VBA to create an old-style toolbar or menu, which will display in the Add-Ins tab.

How do I add a macro to the Quick Access toolbar?

It must be done manually. Right-click the Quick Access toolbar and choose Customize Quick Access Toolbar from the shortcut menu. In the Quick Access Toolbar tab of the Excel Options dialog box, choose Macros from the drop-down list on the left. Select your macro and click Add. To change the icon or text displayed, click the Modify button.

How do I add a macro to the Ribbon?

It must be done manually. Right-click the Ribbon and choose Customize the Ribbon from the shortcut menu. In the Customize Ribbon tab of the Excel Options dialog box, choose Macros from the drop-down list on the left. Select your macro and click Add. Note that you can't add a macro to an existing group. You must first add a new group to a tab by using the New Group button.

How do I use VBA to activate a particular tab on the Ribbon?

SendKeys is your only choice. Press the Alt key to find out the keystroke(s) required. For example, to switch to the Page Layout tab, use this:

Application.SendKeys “%p{F6}”

This statement works only when Excel is the active window. For example, you can't execute this statement directly from VBE.

My custom shortcut menus aren't working correctly in Excel 2013. What's wrong?

Excel 2013 uses a single document interface — each workbook has its own window. Because of this, custom shortcut menus work very differently. When your code customizes a shortcut menu, the customization occurs only in the active workbook. So if you want your customized shortcut menus to work in all workbooks, you need to write additional code that loops through all workbooks. And even then, you can't be certain that the shortcut menu modification is available to all workbooks.

Does this mean that using customized shortcut menus to execute macros in an add-in is no longer a viable option in Excel 2013?

Shortcut menus are still viable, but they must be modified by using RibbonX code, not VBA.