Excel 2019 Power Programming with VBA. Michael Alexander

(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 really visible, so you'll find the Restore button to the right of the Type a Question for Help box.

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 use the Window ➪ Tile Horizontally or Window ➪ Tile Vertically command to arrange them automatically.

      You can quickly switch among Code windows by pressing Ctrl+Tab. If you repeat that key combination, you keep cycling through all the open Code windows. Pressing Ctrl+Shift+Tab cycles through the windows in reverse order.

      Minimizing a Code window gets it out of the way. You can also click the window's Close button in a Code window's title bar to close the window completely. (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. Working with these Code windows sounds more difficult than it really is.

      Getting VBA code into a module

      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.

       Use the Excel macro recorder to record your actions and convert them to VBA code.

       Enter the code directly.

       Copy the code from one module and paste it into another.

      You have discovered the excellent method for creating code by using the Excel Macro recorder. However, not all tasks can be translated to VBA by recording a macro. You often have to enter your code directly into the module. Entering code directly basically means either typing the code yourself or copying and pasting code you have found from somewhere else.

      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.

      A single line of VBA code can be as long as you like. However, you may want to use the line-continuation character 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. 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 VBE has multiple levels of undo and redo. If you deleted a statement that you shouldn't have, use the Undo button on the toolbar (or press Ctrl+Z) until the statement appears again. After undoing, you can use the Redo button to perform the changes you've undone.

      Are you ready to enter some real, live code? Try the following steps:

      1 Create a new workbook in Excel.

      2 Press Alt+F11 to activate the VBE.

      3 Click the new workbook's name in the Project window.

      4 Choose Insert ➪ Module to insert a VBA module into the project.

      5 Type the following code into the module: Sub GuessName() Dim Msg as String Dim Ans As Long 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 clairvoyant!" End Sub

      6 Make sure that the cursor is located anywhere within the text you typed, and press F5 to execute the procedure.

      TIP

      F5 is a shortcut for the Run ➪ Run Sub/UserForm command.

      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 and press Enter, 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 created 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. You can execute this macro any number of times—although it tends to lose its appeal after a few dozen executions.

      This simple macro uses the following concepts:

       Defining a Sub procedure (the first line)

       Declaring variables (the Dim statements)

       Assigning values to variables (Msg and Ans)

       Concatenating (joining) strings of text (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)

      As mentioned previously, you can copy and paste code into a VBA module. 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 copy and paste procedures (Ctrl+C to copy and Ctrl+V to paste). After pasting it into a VBA module, you can modify the code as necessary.

      Alternatively, you can right-click your module and select the Export File option. This allows you to save your module as a .bas file. Once you have your .bas file, you can open another workbook, open the VBE, and then choose File ➪ Import File to import the saved .bas file.

      Customizing the VBA environment

      If you're serious about becoming an Excel programmer, you'll spend a lot of time with VBA modules on your screen. To help make things as comfortable as possible, the VBE provides quite a few customization options.

      When the VBE is active, choose Tools ➪ Options. You'll see a dialog box with four tabs: Editor, Editor Format, General, and Docking. Take a moment to explore some of the options found on each tab.

      The Editor tab

Figure 2.13 shows the options accessed by clicking the Editor tab of the Options dialog box. Use the options in the Editor tab to control how certain things work in the VBE.

FIGURE 2.13 The Editor tab in the Options dialog box

       The Auto Syntax Check option The Auto Syntax Check setting determines whether the VBE pops up a dialog box if it discovers a syntax error while you're entering your VBA code. The dialog box tells roughly what the problem is. If you don't choose this setting, the VBE flags syntax errors by displaying them in a different color from the rest of the code, and you don't have to deal with any dialog boxes popping up on your screen.

       The Require Variable