Activate the VBE and select the VBA module that contains the procedure.
Move the cursor anywhere in the procedure’s code.
3. Press F5 (or choose Run➪Run Sub/UserForm).
Respond to the input box and click OK.
The procedure displays the cube root of the number you entered.
Literature
1. Джон Уокенбах, Excel VBA Programming For Dummies 5 августа 1999 г.
2. Джон Мюллер, VBA for dummies 2003 г.
3. Ричард Мансфилд, Mastering VBA for Microsoft Office 2007 2008 г.
4. Билл Джелен, Трейси Сирстад, Excel 2013 VBA and Macros 2013 г
5. Ричард Мансфилд, Mastering VBA for Office 2010 2010 г
6. Ричард Шеперд, Excel 2007 VBA Macro Programming 2009 г
7. Майк Мак-Грат, EXCEL VBA IN EASY STEPS. 2017 г.
Lecture №5. Using the Excel Macro Recorder
In This Chapter
1. Recording your actions by using the Excel built-in macro recorder
2. Understanding the types of macros you can record
You can use two methods to create an Excel macro:
✓ Record it by using the Excel macro recorder.
✓ Write it manually.
This chapter deals specifically with the ins and outs of using the Excel macro recorder. Recording a macro isn’t always the best approach, and some macros simply can’t be recorded, no matter how hard you try. You’ll see, however, that the Excel macro recorder is very useful. Even if your recorded macro isn’t quite what you want, the macro recorder can almost always lead you in the right direction.
Recording Basics
You take the following basic steps when recording a macro. I describe these steps in more detail later in this chapter.
Determine what you want the macro to do.
Get things set up properly.
This step determines how well your macro works.
Determine whether you want cell references in your macro to be rela- tive or absolute.
4. Click the Record Macro button in the left side of the status bar (or choose Developer➪Code➪Record Macro).
Excel displays its Record Macro dialog box.
Enter a name, shortcut key, macro location, and description.
Each of these items — with the exception of the name — is optional.
Click OK in the Record Macro dialog box.
Excel automatically inserts a VBA module. From this point, Excel converts your actions into VBA code. It also displays a square Stop Recording button on your status bar.
Perform the actions you want recorded by using the mouse or the keyboard.
|
|
|
8. After you’re finished, click the Stop Recording button on the status bar (or choose Developer➪Code➪Stop Recording).
Excel stops recording your actions.
Test the macro to make sure it works correctly.
As an option, you might want to clean up the code by removing extra- neous statements.
Relative or Absolute?
When recording your actions, Excel normally records absolute references to cells. (This is the default recording mode.) But quite often, this is the wrong recording mode. If you use absolute recording mode, Excel records actual cell references. If you use relative recording, Excel records relative references to cells. Keep reading to see the difference.
Recording in absolute mode
Follow these steps to record a simple macro in absolute mode. This macro simply enters three month names into a worksheet:
1. Make sure that the Developer➪Code➪ Use Relative References button is not highlighted and then choose Developer➪Code➪Record Macro.
2. Type Absolute as the name for this macro.
Click OK to begin recording.
4. Activate cell B1 and type Jan in that cell.
5. Move to cell C1 and type Feb.
6. Move to cell D1 and type Mar.
Click cell B1 to activate it again.
Stop the macro recorder.
9. Press Alt+F11 to activate the VBE.
Examine the Module1 module.
 |
Excel generates the following code:
When executed, this macro selects cell B1 and inserts the three month names in the range B1:D1. Then the macro reactivates cell B1.
These same actions occur regardless of which cell is active when you execute the macro. A macro recorded by using absolute references always produces the same results when it is executed. In this case, the macro always enters the names of the first three months into the range B1:D1.
Recording in relative mode
In some cases, you want your recorded macro to work with cell locations in a relative manner. You may want the macro to start entering the month names in the active cell. In such a case, you need to use relative recording.
|
|
|
You can change the manner in which Excel records your actions by clicking the Use Relative References button in the Code group in the Developer tab. This button is a toggle button. When the button appears highlighted in a dif- ferent color, the recording mode is relative. When the button appears nor- mally, you are recording in absolute mode.
You can change the recording method at any time, even in the middle of recording.
Literature
1. Джон Уокенбах, Excel VBA Programming For Dummies 5 августа 1999 г.
2. Джон Мюллер, VBA for dummies 2003 г.
3. Ричард Мансфилд, Mastering VBA for Microsoft Office 2007 2008 г.
4. Билл Джелен, Трейси Сирстад, Excel 2013 VBA and Macros 2013 г
5. Ричард Мансфилд, Mastering VBA for Office 2010 2010 г
6. Ричард Шеперд, Excel 2007 VBA Macro Programming 2009 г
7. Майк Мак-Грат, EXCEL VBA IN EASY STEPS. 2017 г.
Дата добавления: 2019-03-09; просмотров: 229; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!