Ultimately, the success of a recorded macro depends on five factors: You record a macro so that Excel can automatically repeat the actions you record. Preparing to Recordīefore you take the big step and turn on the macro recorder, spend a minute or two thinking about what you’re going to do.
#Macro recorder excel code
Although the result may not be exactly what you want, viewing the recorded code might steer you in the right direction. I make this point throughout the topic: Recording your actions is perhaps the best way to learn VBA. The macro recorder’s limited capability certainly doesn’t diminish its importance. Performing any type of conditional actions (using an If-Then statement) Assigning values to variables Specifying data types Displaying pop-up messages Displaying custom dialog boxes The macro recorder cannot generate code for any of the following tasks, which I describe later in the topic: Performing any type of repetitive looping In most cases, you need to edit the recorded code and add some new VBA statements. Often, I record some actions and then copy the recorded code into another, more complex macro. You may also find the macro recorder helpful for developing more-complex macros. You can’t use the macro recorder to create Function procedures. The macro recorder is for Sub procedures only. For example, you might want a macro that applies formatting to a selected range of cells or that sets up row and column headings for a new worksheet. The macro recorder is best suited for simple, straightforward macros. Test the macro to make sure it works correctly. After you’re finished, click the Stop Recording button on the miniature toolbar (or choose ToolsOMacroOStop Recording).ĩ. Perform the actions you want recorded using the mouse or the keyboard.Ĩ. It also displays a miniature floating toolbar, which contains two toolbar buttons: Stop Recording and Relative Reference.ħ. From this point, Excel converts your actions into VBA code. Click OK in the Record Macro dialog box.Įxcel automatically inserts a VBA module. Each of these items - with the exception of the name - is optional.Ħ. Enter a name, shortcut key, macro location, and description. Excel displays its Record Macro dialog box.ĥ. Determine whether you want cell references in your macro to be relative or absolute.Ĥ. This step determines how well your macro works.ģ. I describe these steps in more detail later in this chapter.ġ. You take the following basic steps when recording a macro. Yes, but you need to do a lot of editing first. Yes, if it’s good (editing usually required). No, it’s just a bunch of magnetic impulses. Rerecord the macro (or edit it if possible). Rerecord the tape (or edit it if possible). Table 6-1 Tape Recording versus Macro RecordingĬhoose ToolsO MacroOMacros (or other methods).ĭepends on the situation and the equipment quality. Table 6-1 compares tape recording with macro recording. Turn it on, do your thing, and then turn it off. Recording a macro is sort of like using a tape recorder. Even if your recorded macro isn’t quite what you want, the recorder is an excellent learning tool. You see, however, that the Excel macro recorder is very useful. Recording a macro isn’t always the best approach, and some macros simply can’t be recorded, no matter how hard you try. This chapter deals specifically with the ins and outs of using the Excel macro recorder. Record it using the Excel macro recorder Write it with VBA Recording your actions using the Excel built-in macro recorder Understanding the types of macros you can record Setting the appropriate options for macro recording You can use two methods to create a macro: