Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
How do I record a macro?
Click the little square icon in the left side of the status bar.
How do I run a macro?
Choose View
What do I do if I don't have a Developer tab?
Display the Excel Options dialog box (Office
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.
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.
Unfortunately, that's how Excel 2007 works. If a module contains no executable procedures, it is deleted. I'd call that a dumb design decision on the part of Microsoft.
How do I hide the Ribbon so it doesn't take up so much space?
Press Ctrl+F1 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
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
Where can I find examples of VBA code?
The Internet has thousands of VBA examples. A good starting point is my Web site:
http://www.j-walk.com/ss
Or, do a search at
http://www.www.google.com
How can I hide the status bar in Excel 2007?
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 standalone 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.
Why is the Custom Views command is grayed out?
That's probably because your workbook contains a table. Convert the table to a range, and then you can use Views
How can I add a drop-down list to a cell so the user can choose a value from the list?
This technique does not 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
Can I use this drop-down list method if my list is stored on a different worksheet in the workbook?
Yes. You need to create a name for the list (for example, ListEntries ). Then, in the Data Validation dialog box, enter =ListEntries in the Source box. Make sure that you include the initial equal sign; otherwise , it won't work.
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 cannot 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 is much less useful in Excel 2007. For example, if you click the Insert Worksheet icon 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
Hopefully, this problem will be fixed in a subsequent update.
What happened to the ability to "speak" the cell contents?
To use those commands, you must customize your Quick Access Toolbar (QAT). Right-click the QAT and choose Customize Quick Access Toolbar. The speech commands are listed in the Commands Not in the Ribbon category (they all begin with the word "Speak").
How can I increase the number of columns in a worksheet?
You can't. This number is fixed and cannot be changed. Excel 2007 worksheets contain 16,384 columns.
How can I increase the number of rows in a worksheet?
See the answer to the previous question. If you need more than 1,048,576 rows, Excel is probably not the solution to your problem.
I opened a workbook, and it has only 65,546 rows. What happened?
When Excel opens a workbook that was saved in a previous version's file format, it does not automatically convert it to an Excel 2007 workbook. You need to do it manually: Save the workbook in an Excel 2007 file format, close it, and then re- open it. You'll then see the additional rows.
How do I get my old workbook to use the 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 scrollbar 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 have not been formatted with other font attributes. For example, bold cells retain their old fonts.
How do I get a print preview?
Try using the Page Layout view (the icon on the right side of the status bar). Or, add the Print Preview button to your QAT. To add this button to your QAT, click the down arrow control to the right of the QAT and choose Print Preview from the list.
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
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 QAT?
No. For some reason, this very useful command isn't available as a QAT icon.
I changed the text in a cell to use Angle Clockwise orientation (in the Home
To change the cell back to normal, click the option that corresponds to the current orientation (that option is highlighted). Or, choose the Format Cell Alignment option and make the change in the Format Cells dialog box.
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. Removing the old cell background colors and applying a style should work.
How do I get Office 2007 to support PDF output?
You need to download a free add-in from Microsoft (try http://www.office.microsoft.com). After you download and install the add-in, click the Office Menu button and then choose Save As
Can I change the color of the sheet tabs?
Right-click the sheet tab and select Tab Color. Versions prior to Excel 2002 do not allow you to change the tab color.
Can I change the font of the sheet tabs?
Yes, but you must go outside Excel to do so. If you use Windows XP, access the Windows Control Panel and select Display. In the Display Properties dialog box, click the Appearance tab and then click the Advanced button. In the Advanced Appearance dialog box, access the Item list and select Scrollbar. Enter a different font size or use the spinner to increase or decrease the size. This setting will affect other programs. The procedure varies slightly with other versions of Windows.
Can I change the default font and color of cell comments?
Yes. See the answer to the previous question - but select ToolTip in the Item list. Use the controls to change the settings. Changing the setting will not affect existing comments.
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). If you're using Excel 2002 or later, you can 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 cannot find any links in this workbook. Is this a bug?
Probably not. Try using the Office
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
Why does Excel crash every time I start it?
When Excel starts, it opens 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 is 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. Hopefully, 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.