How to Get VBA Code into a Module with Code Windows - dummies

How to Get VBA Code into a Module with Code Windows

By Michael Alexander

One of the way in which you can get VBA code into a VBA module is to use the Excel macro recorder to record your actions and convert them to VBA code. However, not all tasks can be translated to VBA by recording a macro. You also can enter the code directly or copy the code from one module and paste it into another.

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, _

This statement would perform the same way if it were entered in a single line (with no line-continuation characters).

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.

Ready to enter some real, live code? Try the following steps:

  1. Create a new workbook in Excel.

  2. Press Alt+F11 to activate 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 the cursor is located anywhere within the text you typed and then press F5 to execute the procedure.

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

When you enter the code listed in step 5, you might notice that VBE makes some adjustments to the text you enter. For example, after you type the Sub statement, VBE automatically inserts the End Sub statement. And if you omit the space before or after an equal sign, VBE inserts the space for you. Also, VBE changes the color and capitalization of some text. These changes are 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) 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)

As mentioned, 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 the code into a VBA module, you can modify the code as necessary.