Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 98

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.