Chapter 31: 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
If you like to cruise the Internet, you're undoubtedly familiar with FAQs — lists of frequently asked questions (and their answers) about a particular topic. FAQs are prevalent in the discussion groups and are posted in an attempt to reduce the number of messages that ask the same questions over and over again. They rarely serve their intended purpose, however, because the same questions keep appearing despite the FAQs.
I've found that people tend to ask the same questions about Excel programming, so I put together a list of FAQs that cover the following programming topics for Excel:
• Excel quirks that you can and can't work around
• Frequently asked questions about Excel programming
• Some help getting around in the Visual Basic Editor
Although this FAQ list certainly 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
• The Visual Basic Editor (VBE)
• Sub procedures
• Function procedures
• Objects, properties, methods, and events
• UserForms
• Add-ins
• Excel user interface modification
In some cases, my classifications are rather 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 its shortcut key, Alt+F8). Or, choose Developer⇒Code⇒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 macros were gone! Where did they go? By default, Excel proposes that you destroy your macros when you first save a new workbook. When you save the file, read Excel's warning very 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? Excel 2010 has a new Minimize the Ribbon icon, next to the Help icon in the title bar. You can click that icon to toggle the Ribbon display. Or, use the Ctrl+F1 shortcut key to toggle the display of the Ribbon. If you'd like to toggle the Ribbon display using VBA, you must resort to using the Sendkeys method:
Sub ToggleRibbon()
Application.SendKeys “^{F1}”
End Sub
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 get the Ribbon to display again is to re-run the XLM code, with the last argument set to True.
Where are my old custom toolbars? 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 in Excel 2010? 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? That's probably because the cell pointer is 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 time selects the data cells, the second time selects the data cells and header row, and the third time selects all cells in the worksheet.
Why is the Custom Views command disabled? That's probably because your workbook 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 wish. 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.
Can I use this drop-down list method if my list is stored on a different worksheet in the workbook? Yes. In previous versions of Excel you needed to create a name for the list (for example, ListEntries). Excel 2010 allows you to use a range in any worksheet, and the worksheet can even be in a different workbook.
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: 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.
Why doesn't the F4 function key repeat all my operations? I don't know. Unfortunately, the very useful F4 key became much less useful beginning with Excel 2007. For example, if you click the Insert Worksheet icon (in the row of sheet tabs) and then press F4, Excel does not repeat the Insert Worksheet command. However, if you insert the worksheet by using Shift+F11, then F4 does repeat the command.
Another example: If you apply a style to a chart (using Chart Tools⇒Design⇒Chart Styles), pressing F4 doesn't repeat the style. Rather it duplicates all of the series in the chart!
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? Excel 2010 worksheets contain 1,048,576 rows and 16,384 columns. If you're not seeing this many rows and columns, then 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 2010 workbook. You need to do it manually: Save the workbook in an Excel 2010 file format, close it, and then re-open 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, and 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 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? In Excel 2010, 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 template, my worksheet no longer fits on a single page. That's probably because the new theme 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 very 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? That's probably because the table cells were formatted manually. Select the cells and set the fill color to No Fill and the font color to Automatic. Then, applying a table style will work.
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 (see Chapter 11). 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 File⇒Info⇒Edit Links to Files). 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.”
Why does Excel crash every time I start it? When Excel starts, it opens several files, including an *.xlb file, which contains menu and toolbar customizations. If this file is damaged, it might cause Excel to crash when it's started. Also, this file might (for some reason) be very large. In such a case, this could also cause Excel to crash. Typically, your *.xlb file should be 100K or smaller.
If Excel crashes when it's started, try deleting your *.xlb file. To do so, close Excel and search your hard drive for *.xlb. (The filename and location will vary.) Create a backup copy of this file, delete the original file, and then try restarting Excel. It's likely that Excel will now start up normally, and create a new *.xlb file.
Deleting your *.xlb file will also delete any toolbar or menu customizations that appear in the Add-Ins tab.
Where can I find examples of VBA code? The Internet has thousands of VBA examples. A good starting point is my Web site:
Or, do a search at
The Visual Basic Editor
Can I use the VBA macro recorder to record all my macros? No. Recording is useful for very 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 the relevant properties or methods.
I have some macros that are general in nature. I would like to have these available all the time. What's the best way to do this? Consider storing those general-purpose macros in your Personal Macro Workbook. This is 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.
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.
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 the 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 the 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 2002 introduced a new setting: 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 pretty much 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 re-opened, you'll need to re-execute the statement in order to reapply the UserInterfaceOnly protection.
How can I tell whether a workbook has a macro virus? In the VB Editor, activate the project that corresponds to the workbook. Examine all the code modules (including the ThisWorkbook code module) and look for VBA code that isn't familiar to you. 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.
I'm having trouble with the concatenation operator (&) in VBA. When I try to concatenate two strings, I get an error message. 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 character (underscore) to work. The line continuation sequence is actually two characters: a space followed by an underscore.
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 the VB Editor (choose Tools⇒Options). You can't change this setting with VBA.
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 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 a bit slower and isn't the most efficient 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 I'd like to perform other types of operations when data is entered into a cell. In that case, 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 19.
I'm very familiar with creating formulas in Excel. Does VBA use the same mathematical and logical operators? Yes. And it includes some additional operators that aren't valid in worksheet formulas. These additional VBA operators are listed in the following table:
|
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 the 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 the 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 issue the File⇒Open command. 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.
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 set things up so that my macro runs 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 a macro from showing 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? What if the procedure is in a different module? Or in a different workbook? 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, prior to 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 it 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 into 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 10 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
The following table lists the valid function category numbers:
|
Number |
Category |
|
0 |
No category (appears only in All) |
|
1 |
Financial |
|
2 |
Date & Time |
|
3 |
Math & Trig |
|
4 |
Statistical |
|
5 |
Lookup & Reference |
|
6 |
Database |
|
7 |
Text |
|
8 |
Logical |
|
9 |
Information |
|
10 |
Commands (normally hidden) |
|
11 |
Customizing (normally hidden) |
|
12 |
Macro Control (normally hidden) |
|
13 |
DDE/External (normally hidden) |
|
14 |
User Defined (default) |
|
15 |
Engineering |
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 arguments that are not appropriate, 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 the 64-bit version of Excel 2010. API declarations must be designated as “PtrSafe” in order 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:
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 the word “PtrSafe” after the Declare keyword. Adding the PtrSafe keyword works for most commonly used API functions, but some function might require that you change the data types for the arguments.
The following declaration is compatible with both 32-bit Excel 2010 and 64-bit Excel 2010:
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 16-Bit Excel 2010. 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.
Is there any way to 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? There are several ways. You can use the Object Browser available in the VBE. Press F2 to access the 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 the 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).
And, of course, the Help system for VBA is very 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 the VBE and move the cursor anywhere within 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? What are collections? 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: 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 quite 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
Is there a quick way to 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
When I try to record the File⇒Exit command, Excel closes down before I can see what code it generates. Is there a VBA command to quit Excel? Use the following instruction to end Excel:
Application.Quit
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 = False
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
Excel 2010 doesn't seem to have a print preview window. I can see a preview in the Backstage View (when I choose File⇒Print), but I'd like the old-style preview window. The only way to display the old print preview window is to use a VBA statement.
ActiveSheet.PrintPreview
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 actually slow down your macro. Recording a very 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”)
Notice 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 quite 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 (like a chart) is selected. How can I make sure that a range is selected? You can use VBA's 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 SelectionAreasCount 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 one, 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 non-empty cell in a particular column? The following instruction displays the address of the last non-empty 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 very 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.
Can I let the user undo my macro? In some cases, yes — but undoing a macro can't be done automatically. 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”
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, but there's also 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 VBA's 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 VBA's 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 heard that programming some aspects of Excel can be done only if you use the old XLM macro language. Is that true? It was true. One of the design goals of Excel 2010 was to remove this limitation and make the features available in VBA.
For example, previous versions required an XLM macro to specify descriptions for arguments in a custom worksheet function. Excel 2010 added the ArgumentDescriptions argument for the MacroOptions method. Another example is the new AddIns2 collection, which is comprised of all open add-ins (not just those that are installed). In earlier versions, accessing open (but uninstalled) add-ins required an XLM macro.
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.
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 VBA's MsgBox function and its InputBox function. Alternatively, you might want to use Excel's InputBox method.
I have 12 CommandButtons on a UserForm. How can I assign a single macro to be executed when any of the buttons is clicked? There is no easy way to do this 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 15.
How can I display a chart in a UserForm? There is no direct way to do this. 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 15.
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? 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 while a lengthy process is being executed. How can I do this? You can do this with a UserForm. Chapter 15 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 choose Home⇒Clipboard⇒Copy. 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 into my UserForm so the user can select a file from the list? There's no need to do that. Use VBA's 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 14 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 22 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. Is there a way to 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.
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 — probably a 1024 × 768 display.
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 14 for an example.
Can I change the startup position of a UserForm? Yes, you can set the UserForm's Left and Top properties. But for these to be effective, you 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. Is there a way to 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 15 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 Web site.
• Third-party developers distribute and sell add-ins for special purposes.
• Many developers create free add-ins and distribute them via 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, there is no way to 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.
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 the VB Editor and then save your changes in the VBE. If you need to modify information on a worksheet, activate the 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. This is true whether it's in an XLSM file or an XLAM file. However, XLAM files still contain the actual VBA code — not some special compiled code. Another difference is that the workbook is never visible in an XLAM file.
How do I protect the code in my add-in from being viewed by others? Activate the 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 the 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.
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 do it by unzipping the document and making 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, you have these choices:
• Modify the Ribbon by adding 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 (also a manual task that's not possible to perform using VBA).
• Assign a shortcut key to the macro.
• Add a new menu item to a shortcut menu.
• 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? 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? 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 the VBE.
How can I disable all the right-click shortcut menus? The following procedure will do the job:
Sub DisableAllShortcutMenus()
Dim cb As CommandBar
For Each cb In CommandBars
If cb.Type = msoBarTypePopup Then _
cb.Enabled = False
Next cb
End Sub