Minimizing and maximizing VBA code windows
If you have several projects open, the VBE may have lots of Code windows at any given time.
Code window overload isn’t a pretty sight.Code windows are much like workbook windows in Excel. You can minimize them, maximize them, resize them, hide them, rearrange them, and so on. Most people find it much easier to maximize the Code window that they’re working on. Doing so lets you see more code and keeps you from getting distracted.
To maximize a Code window, click the Maximize button on its title bar (right next to the X). Or just double-click its title bar to maximize it. To restore a Code window to its original size, click the Restore button. When a window is maximized, its title bar isn't visible, so you'll find the Restore button below the VBE title bar.
Sometimes, you may want to have two or more Code windows visible. For example, you may want to compare the code in two modules or copy code from one module to another. You can arrange the windows manually or choose Window → Tile Horizontally or Window → Tile Vertically to arrange them automatically.
You can quickly switch among Code windows by pressing Ctrl+F6. If you repeat that key combination, you keep cycling through all the open Code windows. Pressing Ctrl+Shift+F6 cycles through the windows in reverse order. (For more information, check out these Excel VBA keyboard shortcuts.)
Minimizing a Code window gets it out of the way. You can also click the window's Close button (which displays X) on a Code window’s title bar to close the window. (Closing a window just hides it; you won't lose anything.) To open it again, just double-click the appropriate object in the Project window. By the way, working with these Code windows sounds more difficult than it really is.
Creating a VBA module
In general, an Excel VBA module can hold three types of code:- Declarations: One or more information statements that you provide to VBA. For example, you can declare the data type for variables you plan to use or set some other module-wide options. Declarations are basically housekeeping statements. They aren't actually executed.
- Sub procedures: A set of programming instructions that, when executed, performs some action.
- Function procedures: A set of programming instructions that returns a single value (similar in concept to a worksheet function, such as SUM).
How you organize a VBA module is completely up to you. Some people prefer to keep all their VBA code for an application in a single VBA module; others like to split the code into several modules. It’s a personal choice, just like arranging furniture.
Getting VBA code into a module
An empty VBA module is like the fake food you see in the windows of some Chinese restaurants; it looks good but it doesn’t really do much for you. Before you can do anything meaningful, you must have some VBA code in the VBA module. You can get VBA code into a VBA module in three ways:- Enter the code directly.
- Use the Excel macro recorder to record your actions and convert those actions to VBA code.
- Copy the code from one module and paste it into another.
Entering Excel VBA code directly
Sometimes, the best route is the most direct one. Entering code directly involves … well, entering the code directly. In other words, you type the code by using your keyboard. Entering and editing text in a VBA module works as you might expect. You can select, copy, cut, paste, and do other things to the text.Use the Tab key to indent some of the lines to make your code easier to read. Indenting isn’t necessary, but it’s a good habit to acquire. As you study Excel VBA coding, you’ll understand why indenting code lines is helpful.
A single line of VBA code can be as long as you need it to be. However, you may want to use the line-continuation characters to break up lengthy lines of code. To continue a single line of code (also known as a statement) from one line to the next, end the first line with a space followed by an underscore (_). Then continue the statement on the next line. And don't forget the space. An underscore character that's not preceded by a space won't do the job.
Here’s an example of a single statement split into three lines:Selection.Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
Orientation:=xlTopToBottom
This statement would perform exactly the same way if it were entered in a single line (with no line-continuation characters). Notice that the second and third lines of this statement are indented. Indenting is optional, but it helps clarify the fact that these lines are not separate statements.
The white-coated engineers who designed the VBE anticipated that people would be making mistakes. Therefore, the VBE has multiple levels of undo and redo. If you deleted a statement that you shouldn’t have, click the Undo button on the toolbar (or press Ctrl+Z) until the statement shows up again. After undoing, you can click the Redo button to perform the changes you’ve undone.
Are you ready to enter some real-live code? Try the following steps:- Create a new workbook in Excel.
- Press Alt+F11 to activate the VBE.
- Click the new workbook’s name in the Project window.
- Choose Insert → Module to insert a VBA module into the project.
- Type the following code in the module:
Sub GuessName() Msg = "Is your name " & Application.UserName & "?" Ans = MsgBox(Msg, vbYesNo) If Ans = vbNo Then MsgBox "Oh, never mind." If Ans = vbYes Then MsgBox "I must be psychic!" End Sub
- Position the cursor anywhere within the text you typed and press F5 to execute the procedure.
The GuessName procedure displays this dialog box.
When you enter the code listed in Step 5, you might notice that the VBE makes some adjustments to the text you enter. For example, after you type the Sub statement, the VBE automatically inserts the End Sub statement. And if you omit the space before or after an equal sign, the VBE inserts the space for you. Also, the VBE changes the color and capitalization of some text. This is all perfectly normal. It’s just the VBE’s way of keeping things neat and readable.
If you followed the previous steps, you just wrote a VBA Sub procedure, also known as a macro. When you press F5, Excel executes the code and follows the instructions. In other words, Excel evaluates each statement and does what you told it to do. (Don’t let this newfound power go to your head.) You can execute this macro any number of times — although it tends to lose its appeal after a few dozen times.
For the record, this simple Excel macro uses the following concepts:
- Defining a Sub procedure (the first line)
- Assigning values to variables (Msg and Ans)
- Concatenating (joining) a string (using the & operator)
- Using a built-in VBA function (MsgBox)
- Using built-in VBA constants (vbYesNo, vbNo, and vbYes)
- Using an If-Then construct (twice)
- Ending a Sub procedure (the last line)
Using the Excel VBA macro recorder
Another way you can get code into a VBA module is by recording your actions, using the Excel macro recorder.By the way, there is absolutely no way you can record the GuessName procedure shown above. You can record only things that you can do directly in Excel. Displaying a message box is not in Excel’s normal repertoire. (It’s a VBA thing.) The macro recorder is useful, but in many cases, you’ll probably need to enter at least some code manually.
Here’s a step-by-step example that shows how to record a macro that inserts a new worksheet and hides all but the first ten rows and all but the first ten columns. If you want to try this example, start with a new, blank Excel workbook and follow these steps:- Activate a worksheet in the workbook.
Any Excel worksheet will do.
- Click the Developer tab, and make sure that Use Relative References is not highlighted.
This macro is recorded using Absolute References.
- Choose Developer → Code → Record Macro, or click the icon next to the Ready indicator on the left end of the status bar.
Excel displays its Record Macro dialog box.
- In the Record Macro dialog box, name the macro TenByTen, specify that you want the macro stored in This Workbook, and press Shift+T for the shortcut key.
The macro can be executed when you press Ctrl+Shift+T.
- Click OK to start recording. Excel automatically inserts a new VBA module into the project that corresponds to the active workbook.
From this point on, Excel converts your actions to VBA code. While you’re recording, the icon in the status bar turns into a small square. This is a reminder that the macro recorder is running. You can also click that icon to stop the macro recorder.
- Click the New Sheet icon to the right of the last sheet tab.
Excel inserts a new worksheet.
- Select the entire Column K (the 11th column) and press Ctrl+Shift+right arrow; then right-click any selected column and choose Hide from the shortcut menu.
Excel hides all of the selected columns.
- Select the entire Row 11 and press Ctrl+Shift+down arrow; then right-click any selected row and choose Hide from the shortcut menu.
Excel hides all of the selected columns.
- Select cell A1.
- Choose Developer → Code → Stop Recording, or click the Stop Recording button on the status bar (the small square). Excel stops recording your actions.
Here’s the code generated by your actions:
Sub TenByTen()
'
' TenByTen Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Sheets.Add After:=ActiveSheet
Columns("K:K").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.EntireColumn.Hidden = True
Rows("11:11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Hidden = True
Range("A1").Select
End Sub
To try this macro, activate any worksheet and press the shortcut key that you assigned in Step 4: Ctrl+Shift+T.If you didn't assign a shortcut key to the macro, don't worry. Here's how to display a list of all macros available and run the one you want:
- Choose Developer → Code → Macros. Keyboard fans can press Alt+F8.
Either of these methods displays a dialog box that lists all the available macros.
- Select the macro in the list (in this case, TenByTen).
- Click the Run button.
Excel executes the macro, and you get a new worksheet with ten visible rows and ten visible columns.
And, of course, you can also edit the macro after you record it. To test your new skills, try editing the macro so that it inserts a worksheet with nine visible rows and columns — perfect for a Sudoku puzzle.
Copying VBA code
The final method for getting code into a VBA module is to copy it from another module or from some other place (such as a website). For example, a Sub or Function procedure that you write for one project might also be useful in another project. Instead of wasting time reentering the code, you can activate the module and use the normal Clipboard copy-and-paste procedures. (You’re probably rather fond of the keyboard shortcuts Ctrl+C to copy and Ctrl+V to paste.) After pasting the code into a VBA module, you can modify the code if necessary.By the way, you'll find lots of VBA code examples on the web. If you'd like to try them, select the code in your browser and press Ctrl+C to copy it. Then activate a module and press Ctrl+V to paste it.
When you copy code from a website, it sometimes requires some fixing. For example, quote characters may be “smart quotes” and they must be converted to simple quote characters. And sometimes, long lines wrap around. Erroneous statements are easy to spot in the VBE because they appear in red.