General Excel QuestionsHow do I record a macro?How do I run a macro?What do I do if I don’t have a Developer tab?I recorded a macro and saved my workbook. When I reopened it, the macros were gone! Where did they go?Before saving my workbook as an XLSM file, I converted all my VBA statements to comments so I could debug the code later. When I re-opened the workbook, all my VBA code was gone.How do I hide the Ribbon so it doesn’t take up so much space?Where are my old custom toolbars?Can I make my old custom toolbars float?Where can I find examples of VBA code?How can I hide the status bar in Excel 2007?Is there a utility that will convert my Excel application into a standalone EXE file?Why doesn’t Ctrl+A select all the cells in my worksheet?Why is the Custom Views command is grayed out?How can I add a drop-down list to a cell so the user can choose a value from the list?Can I use this drop-down list method if my list is stored on a different worksheet in the 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.Why doesn’t the F4 function key repeat all my operations?What happened to the ability to “speak” the cell contents?How can I increase the number of columns in a worksheet?How can I increase the number of rows in a worksheet?I opened a workbook, and it has only 65,546 rows. What happened?How do I get my old workbook to use the new fonts?How do I get a print preview?When I switch to a new document template, my worksheet no longer fits on a single page.How do I get rid of the annoying dotted-line page break display in Normal view mode?Can I add that Show Page Breaks option to my QAT?I changed the text in a cell to use Angle Clockwise orientation (in the Home Alignment group). How do I get the orientation back to normal? There’s no Horizontal Alignment option.I’m trying to apply a table style to a table, but it has no visible effect. What can I do?How do I get Office 2007 to support PDF output?Can I change the color of the sheet tabs?Can I change the font of the sheet tabs?Can I change the default font and color of cell comments?Can I write VBA macros that play sounds?When I open a workbook, Excel asks whether I want to update the links. I’ve searched all my formulas and cannot find any links in this workbook. Is this a bug?Why does Excel crash every time I start it?The Visual Basic EditorCan I use the VBA macro recorder to record all my macros?I turned on the macro recorder when I edited a chart, but many of the commands weren’t recorded.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?I can’t find my Personal Macro Workbook. Where is it?I locked my VBA project with a password, and I forget what it was. Is there any way to unlock it?How can I write a macro to change the password of my project?When I insert a new module, it always starts with an Option Explicit line. What does this mean?Why does my VBA code appear in different colors? Can I change these colors?Can I delete a VBA module by using VBA code?I wrote a macro in Excel 2000 that adds VBA code to the VB project. When I run it in Excel 2007, I get an error message. What’s wrong?How can I write a macro to change the user’s macro security setting? I want to avoid the “this workbook contains macros” message when my application is opened.How does the UserInterfaceOnly option work when protecting a worksheet?How can I tell whether a workbook has a macro virus?I’m having trouble with the concatenation operator (&) in VBA. When I try to concatenate two strings, I get an error message.I can’t seem to get the VBA line continuation character (underscore) to work.I distributed an Excel application to many users. On some machines, my VBA error-handling procedures don’t work. Why not?ProceduresWhat’s the difference between a VBA procedure and a macro?What’s a procedure?What is a variant data type?What’s the difference between a variant array and an array of variants?What’s a type-definition character?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?The Conditional Formatting feature is useful, but I’d like to perform other types of operations when data is entered into a cell.What other types of events can be monitored?I tried entering an event procedure (Sub Workbook_Open), but the procedure isn’t executed when the workbook is opened. What’s wrong?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?I’m very familiar with creating formulas in Excel. Does VBA use the same mathematical and logical operators?How can I execute a procedure that’s in a different 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?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?I have a workbook that uses a Workbook_Open procedure. Is there a way to prevent this from executing when I open the workbook?Can a VBA procedure access a cell’s value in a workbook that is not open?How can I prevent the “save file” prompt from being displayed when I close a workbook from VBA?How can I set things up so that my macro runs once every hour?How do I prevent a macro from showing in the macro list?Can I save a chart as a GIF file?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?FunctionsI created a VBA function for use in worksheet formulas. However, it always returns #NAME?. What went wrong?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?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?Can I also display help for the arguments for my custom function in the Insert Function dialog box?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?How can I create a new function category?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!)?How can I force a recalculation of formulas that use my custom worksheet function?Can I use Excel’s built-in worksheet functions in my VBA code?Is there any way to force a line break in the text of a message box?Objects, Properties, Methods, and EventsIs there a listing of the Excel objects I can use?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?What’s the story with collections? Is a collection an object? What are collections?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?How can I prevent the user from scrolling around the worksheet?What’s the difference between using Select and Application.Goto?What’s the difference between activating a range and selecting a range?Is there a quick way to delete all values from a worksheet yet keep the formulas intact?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 number?When I try to record the Office Exit Excel command, Excel closes down before I can see what code it generates. Is there a VBA command to quit Excel?How can I turn off screen updating while a macro is running?What’s the easiest way to create a range name in VBA?How can I determine whether a particular cell or range has a name?Can I disable the Setup and Margins buttons that are displayed in Excel’s Print Preview window?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?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?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?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?How can I determine if a chart is activated?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?I use Excel to create invoices. Can I generate a unique invoice number?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?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?How can I determine the last non-empty cell in a particular column?VBA references can become 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?Can I declare an array if I don’t know how many elements it will have?Can I let the user undo my macro?Can I pause a macro so the user can enter data into a certain cell?VBA has an InputBox function, but there’s also an InputBox method for the Application object. Are these the same?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?I created an array, but the first element in that array is being treated as the second element. What’s wrong?I would like my VBA code to run as quickly as possible. Any suggestions?UserFormsMy macro needs to get just a few pieces of information from the user, and a UserForm seems like overkill. Are there any alternatives?I have 12 CommandButtons on a UserForm. How can I assign a single macro to be executed when any of the buttons is clicked?How can I display a chart in a UserForm?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.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?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.Is there any difference between hiding a UserForm and unloading a UserForm?How can I make my UserForm stay open while I do other things?Excel 97 gives me a compile error when I write UserForm1.Show vbModeless. How can I make the form modeless in Excel 2000 and later while allowing it to remain modal in Excel 97?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?How can I use Excel’s shapes on my UserForm?How can I generate a list of files and directories into my UserForm so the user can select a file from the list?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?Is there an easy way to fill a ListBox or ComboBox control with items?Can I display a built-in Excel dialog box from VBA?I tried the technique described in the preceding question and received an error message. Why is that?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?Can I create a UserForm without a title bar?When I click a button on my UserForm, nothing happens. What am I doing wrong?Can I create a UserForm whose size is always the same, regardless of the video display resolution?Can I create a UserForm box that lets the user select a range in a worksheet by pointing?Can I change the startup position of a UserForm?Can I make a UserForm that’s resizable by the user?Add-insWhere can I get Excel add-ins?How do I install an add-in?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?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?How do I create an add-in?I try to create an add-in, but the Save as Type dropdown box doesn’t provide Add-in as an option.Should I convert all my essential workbooks to add-ins?Do I need to keep two copies of my workbook: the XLSM version and the XLAM version?How do I modify an add-in after it has been created?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?How do I protect the code in my add-in from being viewed by others?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?User InterfaceHow do I use VBA to add a simple button to the Ribbon?What are my options for modifying the user interface to make it easy for a user to run my macros?How do I add a macro to the Quick Access Toolbar?I added my macro to the QAT, but clicking the icon generates an error.How do I use VBA to activate a particular tab on the Ribbon?How can I disable all the right-click shortcut menus?
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.