Examples Introduction to Macros
Step 1 – Record macros
What is a macro?
Imagine programming a robot that does a repetitive task for you, such as washing dishes. You can demonstrate all the necessary actions so that the robot can “learn” how to fill a bowl with soapy water, how to clean a plate, etc. After completing all the associated tasks, the dishes must wash them off each time You need the robot just instruct him to run his program ‘WashDishes’. How a macro works in Excel: Show all the tasks associated with a repeating worksheet job. Then just run the macro every time the job has to be repeated.
Macros can contain all the built-in features and functions of Excel in any series or combination. For example, you can create a macro that uses a complex number format every time it is needed. A more complex macro may open the daily sales table, create a chart from today’s totals, add a header and footer, and print the chart. Macros are a powerful labor-saving feature that lets you perform all tasks in Excel.
Before taking a macro
As with the washing demonstration, the tasks associated with a macro must be demonstrated in Excel so that they can be registered in the correct order. This is called recording your macro, and Excel must be set up properly before you begin recording.
You would not record a TV program without checking that the video was switched to the right channel and you did not want to record on your favorite movie. Also in Excel some things have to be configured at the beginning.
1) Choice of start position for recording
You need to make sure that you are in the right place for the very first task of your macro. it’s all too easy to forget. Let’s go back to the robot who learns to wash the dishes. The first task is “go to the kitchen”, the second task is “pressing out the detergent bottle”. If the robot is already in the kitchen when you start the demonstration, it can not be demonstrated that you are going to the kitchen. The first task the robot will remember is to squeeze out the detergent bottle. This will result in a soap carpet if you later start the program ‘WashDishes’ when the robot is in the living room. For demonstration purposes, you must start outside the kitchen and include going to the sink as part of the robot’s programming.
To record a macro that inserts a logo-type heading into the first cell of a worksheet, it is important that you are not in that cell at the beginning of the recording. This way, when you are demonstrating or recording, you must first switch to the correct cell before entering the text.
To record a macro typing this logo in A1, make sure that you start elsewhere in the table so that Excel records the action of moving to A1 as part of the macro.
If you are not sure where to start recording, remember that computers have no intelligence. that they only execute the instructions that have been communicated to them. Ask yourself: What must happen first in your macro?
Here are some examples to illustrate this point:
If the macro needs to open a specific workbook and then perform some tasks, make sure that the workbook is not already open.
If the macro needs to run on a particular sheet, make sure another sheet is selected when you start recording. In this way, you can record the activation of the worksheet as part of the macro.
If you want the macro to format the selected cell or cells, make sure the correct cells are selected before you start recording. Otherwise, your macro will select different cells each time it is used.
2) Is the movement on the sheet relative or absolute?
There are two ways to record movements. First – “go to the kitchen” is an absolute statement, no matter where it is performed, the result is the same – you end up in the kitchen. The second – six feet forward and three to the left – is a relative instruction. The result depends entirely on your starting position.
Programming the robot for dishwashing would begin with an absolute instruction, while programming to dance the foxtrot would be a series of relative instructions.
Just like the robot, some Excel macros have to be recorded with relative motion and some with absolute motion. Occasionally it is necessary to change from one to another during the recording. To record the BBC Worldwide-logo macro, you must first switch to A1, regardless of where it starts. This would therefore be recorded as an absolute macro.
Recording a Macro – The Developer Tab
To record macros in Excel 2007/10, you must have a shortcut on the View tab or … must display the Developer tab on the Ribbon. This is not visible in the standard installation, but can be viewed by clicking “Excel Options” at the bottom of the “Office” menu (the button in the upper left corner of the screen). Then check the “Show Developer Tab in Ribbon” box.
(In 2010, right-click and adjust the ribbon.)
The “Developer” tab contains all the macros.
As described in the previous section, the first step in recording a macro is to ensure that you are well positioned for the recording you want to perform. Capture the macro that includes the BBC Worldwide logo. Make sure you are in a cell other than A1. That way, the first process has to go “to the right place” for the logo. You also need to make sure that the motion is recorded as absolute motion so that it always moves into the same cell regardless of the starting position.
On the Developer tab, click Record Macro. Excel displays the Record Macro dialog box.
Enter a name for the macro. This is a unique title that identifies this macro if you want to execute or change it. Macro names can not contain spaces or punctuation except the underscore “_”. Therefore, multiple words like InsertWorldwideLogo must be put together or separated by the underscore (Insert_Worldwide_Logo). Macro names can contain both letters and numbers, but must begin with a letter. In addition, there are certain macro keywords reserved exclusively for Excel, including IF, SELECT, NEXT, TRUE, and a number of others. Excel simply does not record a macro with any of these words as names. If you violate any of these naming conventions, you will be notified that the name is invalid.
Macro names must:
1. Start with a letter
2. Contains no spaces or punctuation (except underscore _)
3. Do not be one of the reserved words of Excel
There are also certain words that can cause problems in Excel. These include words such as sum, format, lookup, etc. The list of these words is long and in Excel you can use them as names, but they can lead to misunderstandings or ambiguities later when you run the macro. For this reason, it is advisable to avoid single (non-compound) words, unless they are obviously not connected to computers like “Walnut” or “Arsenal”. However, the best names clearly indicate the function of the macro, as this helps the user remember the name or select it from a list. If you prefix a single descriptive word with “mine” or add a series of initials such as “MyFormat” or “LookupMJH”, it is common practice to avoid using reserved Excel words.
Location of your macro – the Personal Macro Workbook
Macros are saved as part of an Excel file. In the Record Macro dialog box, you have the option to save your macro in a default workbook (the default is This Workbook) or in the Personal Macro Workbook. The latter is an automatic workbook that is stored in one of Excel’s technical folders, so any macro stored in it becomes the default part of the application. Macros stored in a specific workbook can only be executed if the file that contains them is open. However, macros stored in the personal macro workbook are always available.
Macros are stored as program code in a part of the file called a module. Modules are not visible in Excel, but can be viewed using the Visual Basic Editor (click Visual Basic on the Developer tab).
The personal macro workbook is created as soon as the first macro is saved in it. It’s called Personal.xlsm and is stored in one of the Xlstart folders. Each file in Xlstart automatically opens when Excel starts, so macros are immediately available in Personal.xlsm.
Personal.xlsm is not displayed because it is created as a hidden workbook. However, if there are macros in the personal macro workbook, you can view them by selecting the Show … command on the View tab. It probably appears as a workbook that contains only a blank sheet.
The usual location for the Personal Macro Workbook is:
C: \ Users \ [username] \ AppData \ Roaming \ Microsoft \ Excel \ XLSTART
If the macros are no longer needed in Personal.xlsm, it is perfectly acceptable to search for and delete the file using Windows Explorer. Excel creates a new file if necessary.
For macros that are occasionally run or intended for a small number of specific files, the macro is usually stored in a regular Excel workbook. However, if the macro is intended for use in a book, e.g. For example, to apply a complex number format or to add a default header and footer, it may be a good idea to save the macro in the personal macro workbook. Our InsertWorldwideLogo macro could be a candidate for the Personal Macro Workbook.
The Record dialog box has an area for assigning a keyboard shortcut. Shortcuts are a fast and efficient way to run a macro. When you’re done recording, you’ll want to test your new macro in many different circumstances. The key combination facilitates this.
Keyboard shortcuts can be uppercase or lowercase letters and are selected by typing the letter in the box. When recording is complete, you can run the macro by holding down the Ctrl key and entering the letter. If a capital letter was used, you must also hold down the Shift key as you type the letter. In the dialog box, a shortcut to X appears in uppercase as Ctrl + Shift + X.
Macro keyboard shortcuts take precedence over Excel keyboard shortcuts. When the keyboard shortcut Ctrl + b is assigned to a macro, the bold command is no longer activated. This remains until the file is closed with the macro or the macro is deleted. At this point, Ctrl + b returns to normal.
The description is nice; It will appear at the bottom of the Macros dialog box when the name of the macro is selected. You may want to enter a brief description of the macro function.
When the Record dialog box is complete, click OK. It is now appropriate to perform all actions that are to be performed by the macro. As with the demonstration of a series of actions for a robot, perform all the tasks you want to include in the macro so that Excel can save and then repeat these actions.
Excel indicates that a recording is in progress by displaying Record Stop instead of Record Macro on the Developer tab. The Status Bar contains the Stop Recording button.
All actions performed now are recorded as part of the macro. If mistakes are made during recording, simply correct them and continue. When the macro runs, it makes the same mistakes, but corrects them.
Selection of relative or absolute recording
The Developer tab contains the Use Relative References button. If you click this button, it will remain selected (a kind of “orange” color). All subsequent actions that select a different cell or range are recorded using relative references. If you click it again, it will appear flush with the toolbar, and any movement on the sheet will be recorded as an absolute motion. This must be clicked on the appropriate setting before a move is made on the sheet.
All actions that can be performed in Excel, except for the creation and editing of macros, can be recorded as part of the macro.
To record the InsertWorldwideLogo macro:
1. Make sure that a cell other than A1 is selected. On the Developer tab, select Record Macro.
2. Give the macro a name and a keyboard shortcut and save it in the personal macro workbook. If necessary, add a description as well. OK click. They are recording now.
3. Make sure the Relative References button on the Developer tab is not selected. It should be flush with the toolbar to indicate that no relative movement is being recorded.
4. Click in A1 and enter “BBC Worldwide”. Format it to print in bold in the Arial font, size 18pt.
5. Select all three cells it covers now and apply white text and a black background. Finally, click in A2.
6. Click the Stop button on the status bar or Stop Recording on the Developer tab.
7. Test the macro with the key combination.