By Sections

 

 

 

 

 

By Levels

 

 

 

 

Other Links

 

 

VBA Lesson 1-8: The Excel Macro Recorder

Note: For a better comprehension of the lessons in section 1 on the Visual Basic Editor and the Macro Recorder www.excel-vba.com has created a workbook (vba-tutorial-editor.xls) one of the 25 that are included with the VBA for Excel Desktop Tutorial.

One of the tool that makes the programming environment in Excel the most  user friendly is the Macro Recorder.

In this section  you will work with the Macro Recorder and you will run the macro that you have recorded.

With the Excel macro recorder you cannot develop a macro that will damage Excel or your computer. The bolder you are in your trials the more you will learn.

Even after 10 years of programming you will still use the macro recorder daily. Not to learn anymore but to write code (VBA words and sentences) for you.

Would you rather write the following code or copy/paste it from a recorded macro?
Selection.Replace What:=":" , Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

The Excel macro recorder is the best teacher that you can have and will remain the best assistant for the rest of your VBA developer's life.

The Excel Macro Recorder has tendencies to overdo it sometimes. We will see in lesson 9  how to modify a recorded macro.

Print this page and follow the instructions step by step.

IMPORTANT NOTE: To be able to run macros or develop them in Excel the level of security of you version of Excel must be set to "Medium" . From then on every time you open a workbook you will be asked if you want to "Enable Macros" . To modify the security setting of Excel go to "Tools/Macros/Security" and follow the instructions.

1- Open Excel.

2- On the menu bar go to "Tools/Macro/Record New macro" and the following dialog window appears:

VBA for Excel record macros window

In the window you see that a macro called "Macro1" will be created. You can change the name if you want. In "Store macro in:" is a list of all the spreadsheets that are open, select "This Workbook" which is the workbook in which you are actually workings. You can also write a comment in "Description" .

At this point you can choose to attach the macro to a key from your keyboard. If you do so it means that to get Excel to run the macro you will just have to click on the set of keys that you have chosen. For the purpose of this exercise enter  capital A in the "Shortcut Key:" text box. Later you will be able to run the macro by just holding the CTRL and the SHIFT keys and clicking on A. (NOTE: Always select a capital letter so that you don't deactivate important functions that are already attached to some lower case letters by Excel). If you don't link your macro to a combination of keys now you can do it later.

When you click on "OK" Excel will record a macro named as you specified and attach it to the workbook that you are working in.  When you save the spreadsheet that you are working in the macro will be saved at the same time. It also means that Macro1 will disappear from you computer when you delete the spreadsheet in which it resides. In section  4 you will see how easy it is to transfer a macro to any other workbook. So you can create and try your macros in one workbook and when it works you can transfer it to "Your macros" 0 workbook or any other workbook.

  3- Click on "OK" . The following toolbar might appear in the middle of the screen. You can click on the "X" for now we will talk about it later.

4- Let's now record a macro with the Excel macro recorder:

Select cell A1 and enter 34
Select cell A2 and enter 55
Select cell A3 and enter the formula =A1 + A2
Select cell A2 and change the color of the font to red
Select cell A1 and change the background color to blue
Select cell A3 and change the size of the font to 24

5- Go to the menu bar "Tools/Macro/Stop Recording"

The macro has been recorded by the Excel macro recorder now let's run it

6- There are two ways to run the macro. If you have entered a letter in the "Shortcut Key:" text box at the beginning you can just hold the CTRL and the SHIFT keys and click on the letter that you have chosen (A). You can also go to the menu bar "Tools/Macro/Macros..." select "Macro1" in the list and click on "Run" . The macro is executed.

7- Go to Sheet2 and run the macro again. You see that you can run a macro on any sheet of the workbook.

8- Keep this workbook open and open a new workbook. Make sure cell "A1" is selected and run the macro again (SHIFT/CTRL/A). You can see that the macro that you have created will run in any spreadsheet as long as the workbook in which it has been created is open.

9- Close the workbook (save it or not) in which you have created the macro. Open a new workbook and try to run the macro again SHIFT/CTRL/A. It doesn't work because the macro only runs when the workbook in which it resides is open. Go to the menu Tools/Macro/Macros. Macro1 is not there anymore so you can't run it and the shortcut key is free.

Remember that a macro can be run in any workbook as long as the workbook in which it has been created is open.

You have done it, you have recorded your first macro with the Excel macro recorder and used it, congratulations.

Second Exercise

For this second exercise use  the  spreadsheet "excel-visual-basic-editor.xls".

Macros will not run on protected sheets, hidden sheets and very hidden sheets.

1-Close all workbooks and open the workbook "excel-visual-basic-editor.xls". While you are on the sheet "Introduction" try clicking "SHIFT/CTRL/A"   or run " Macro1" from the menu bar " Tools/Macro/Macros" and you will get the following window telling you about a bug:

VBA for Excel run-time errors

Click on "End" for now. You have just learned that a macro cannot run if the sheet is protected. In lesson 2-5 on "Worksheets" you will learn how to programmatically protect and unprotect the sheets. For now go to "Tools Protection/Unprotect Sheet" . Run the macro again and it works

11-You can run the macro on sheet "Test1" but there are 2 other test sheets in this workbook one named " Hidden" and the other one named "veryHidden" .

12- To run the macro on the sheet "Hidden" you must first unhide it .   In Excel go to the menu bar " Format/Sheet/Unhide" , select cell "A1"   and run the macro again. In lesson 2-5 on "Worksheets" you will learn how to programmatically hide and unhide the hidden sheets and the " very hidden " sheets" .

13- You cannot unhide the " veryHidden" sheet from Excel so you cannot run the macro on it. This type of very hidden sheets is used a lot to hide price lists, salary lists and other confidential information. Even if you go to " Format/Sheet/Unhide" you will not see its name. You can unhide that kind of sheet manually in the Visual Basic Editor only if the project is not password protected. Learn about this precious sheet status in lesson 2-5 on "Worksheets".

14- Delete column A on the sheet " Test1" . Select cell B6 and run " Macro1" . You see that cell B6, A2 and A3 are modified.

In the next lesson you will discover how to modify your recorded macros

                 
       

Developed and Presented by PLI Consultant Inc