Using macros

Introduction

A macro command is a series of instructions that are always executed one after the other in the same order. They're very practical to automate repetitive tasks. The exercise that follows will demonstrate how to create a macro command. The next macro basically changes the background color of the selected cells. It contains only that single command. But after you've finished, you will be able create your own "macros" and insert as many instructions as you need.

Creating of a macro command

Write the following numbers in the appropriate cells.
Place the cursor in the A1 cell.

You must place the cursor in that cell before you can begin your "macro". You'll see why later on. From the Tools menu, select the Macro option. Select the Record New macro option. A new window will open asking you for some information about the new macro. The first box asks you for the name that you give to this macro. You can also put a letter or a number in the shortcut box. You'll be able to execute the macro by pressing the Ctrl and a keys. You can place in any letter or number that you want. The shortcut key is compulsory. The window asks you if you want to store the macro in this file or in another worksheet file. It's possible "to reuse" the macro commands in a personal macro file. The same macro can then be used for several files. But this is only for those that are really serious about using macros. For the exercise: Give a name to your macro. It should represent the actions that will be done such as " Printing_the_budget" . The name of the macro cannot have any spaces. An underline can be used to link words. It's also possible to have a shortcut key to activate a macro command. This avoids you having to go into the Tools menu, followed by the Macro, Macro commands, select the macro of your choice and pressing on the Execute button. Enter the data as shown in the picture above. Press the OK button. All the actions that you go to make will be added to the macro command until you stop the recording. As soon as you press the OK button, the window disappears and a small toolbar appears in its place. This small toolbar has only two buttons. The first one is to stop the macro from recording. The second is to activate or deactivate the relative reference option. This can be important according to the type of macro that you want to carry out. You do not activate this option if you want the macro to do whatever you want it to do at always the same location. You activate this option if you want the macro to start where the active cursor is located. There will be more details of this option a little later on this page. Press the second button to be sure that the relative option is activated. (It's very important for this demonstration). Make a block out of the A1 to C1 cells. Press the Fill button to change the cell's background color to the color of your choice. Press the first button of the macro toolbar to stop the macro from recording. OR From the Tools menu, select the Macro and Stop recording options. The new macro command is now complete. It could have had a lot more instructions than this example. But this is only to demonstrate what a macro can do. It's time to see if you may repeat it. Place the cursor in the A3 cell. Press the Ctrl and A keys to activate the shortcut to the macro. Here is the result of the macro. The cells of the third row have now the same background color as the one you chose for the cells of the first row. There is an explanation if it didn't work. You forgot to activate the relative position option when asked. Excel will repeat the macro at the same location instead of beginning it where the cursor is located. It's as for that reason that it was asked to you to move the cursor in the A3 cell and to activate the relative button. Because the relative reference option was activated, you may execute the macro to another place rather than where it was created. You just need to put the cursor where you need to activate the macro. If the relative option is not activated, the macro will always repeat itself at the same location. It can be practical for your needs. It depends on the box. You decide when you should activate the relative reference option or not. It's practical when you know that you'll want to apply the macro to another place in your file. For the last exercise, it was necessary to be able to apply it to the A3 to C3 cells. A 1004 error message can also appear. Generally, it's because you forgot to stop the macro command from recording. The macro is then caught in an endless loop. The macro is recalled before it's even finished! It will be necessary to change the macro. Change a macro command From the Tools menu, select the Macro option Select the macro of your choice and press the Edit button. The Visual BASIC editor will appear with the code of the macro command that you want to change. The macro indicates that three cells were selected (A1 to C1). It then indicates the color as well as the pattern of the background of the selected cells. Print a macro command It's possible to print the code while changeing the macro command. From the File menu, select the Print option. To return to Excel... From the File menu, select the Close and return to Microsoft Excel option. Attach a macro command to a button It's sometimes very interesting to be able to execute macro commands by just pressing a button; even more if you leave your file to another person. They probably don't know all the macro commands that you created to work faster. The next exercise consists in attaching a macro command to a button. IMPORTANT: You must already have created a macro that you will need before attaching it to a button. From the Edit menu, select the Toolbar option. From the list of the available toolbars, select the Forms toolbar. Move the toolbar if it's necessary. Place the cursor on the titles bar of the toolbar (the blue bar on top). Press the left mouse button and move the toolbar towards the place of your choice. Once the toolbar is at its new location, release the left mouse button. This toolbar is used for creating forms in Excel files. It facilitates the use of the model for the users. This toolbar offers you several objects, or controls, that you can place on forms. For the moment, you only have to worry about the button to attach a macro command. Press the button. Press the left mouse button and determine the place and size of the button. When satisfied with the size, release the left mouse button. Excel will automatically ask you for the name of the macro that you want to attach to the newly created button. Select the macro of your choice. Press the OK button. While the button is still selected, you can change the text on the button to whatever you need it to be. Once finished, click outside of the button. To execute the macro that's now connected to the button. Place the cursor in the A5 cell. Place the cursor over the button. Press the left mouse button. The button now executes the macro. You can apply any macro that you make to a button. It's that easy! If you wish to change the options of the button. Place the cursor over the button. Press the right mouse button. A context menu will appear next to the button. If you want to affect the presentation of this macro button, select the Format Control option. The properties window of the button will allow you to change all the options of your choice. All the possible options you will be found under these seven tabs: Font, Alignment, Size, Protection, Properties, Margins and Web. Under the Font tab you will find all the options for the presentation of the text on the button. You can change the font, font style, size, color and effects. The Alignment tab allows you to change the placement of the text inside the button including its' orientation. The Size tab allows you to determine exactly the size of the button on the worksheet. Like for the protection for cells, it's also possible to protect buttons under the Protection tab. By default, all the buttons are protected when the protection is activated. You should leave it protected unless you want the user to be able change the button properties for some reason. The Properties tab allows you to decide if the button should change size and placement when you change you change you size and placement of the cells of the worksheet below it. You can also decide to move or not the button if you insert or delete rows and columns. By default, the button will not be printed unless you activate the Print the object option. The Margins tab allows you to control the margin, or the space between the text on the button and its border. You can use the predetermined margins or change them to your choice. You can always save this worksheet as a Web page. So Excel offers you some Web properties under the Web tab. For buttons, it only allows you to put some alternate Assign a macro to a drawing It's interesting, even practical, to place a macro on a command button. It's easier for the users to use the options that you prepared them for them. But these buttons lack originality. They're grey! That's why Excel also offers you the possibility of placing a macro on a drawing. With a bit of work, these drawings can have very interesting forms. Here are some examples. Before being able to attach a macro to a drawing, you need two things: a drawing and a macro. Let's presume that you already have both. The next part consists only in attaching a macro to a drawing. Place the cursor over the drawing. Press the right mouse button. From the context menu, select the Assign macro option. From the list of macro commands that you have already prepared, select the one of your choice. Press the OK button. If you can't select the Assign macro option, click on the border of the drawing instead of the text inside it. You should then be able to assign the macro of your choice. You can repeat this operation on as many drawings as you want. It certainly puts a little fun in your file! Assign a macro to a toolbar Assigning a button to a toolbar can be done in two Create a new toolbar From the View menu, select the options Toolbar and Customize. Press the New button. Enter the name you wish to give to your new toolbar and press the OK button. An empty toolbar will appear on the screen. You can now drag any button that you wish to that toolbar; including macros. Creating a button for the toolbar Now is the time to add buttons that you will be able to place on any toolbar. You just create a new toolbar that has no buttons in it. This next step will show you how to add buttons to it, or to any toolbar. Select the Commands tab. From the Categories group, select Macros. Drag the Custom Button to your new Toolbar. The result is that you now have a button on your toolbar. You can drag other button onto it. Close the Customize toolbar window. The only thing left to do is to assign a macro to that new button. Place the cursor over the button. Press the right mouse button. From the content menu, select the Assign Macro option. From the list of available macros, select the one you wish to assign to the button. Press the OK button. Let's see if it works. Place the cursor in the A7 cell. Press on the button from the new toolbar. Change button picture You can now make macros, apply them where you want them and even create toolbars! But it's not over. A default button picture was placed on the toolbar when you added a macro command to it. Excel offers you a list of pictures you can use to better represent what your macro command will do. Place the pointer over the new toolbar button. Press the right mouse button. From the context menu, select the Change Button Image option. A list of pictures will appear. Select the picture that best represents the action from your macro. The picture will be replaced. You can change again if you wish. Edit button picture The number of pictures you can select from is limited and may not answer your needs. So you can change the pictures or create your own. Place the pointer over the new toolbar button. Press the right mouse button. From the context menu, select the Edit Button Image option. The size of the picture and the number of colors is limited but not your imagination. You can try any form, put a shade on it, anything you can think! Once you created the picture you wish, press the OK button. You can now create you own macros and toolbars. This will help you personalize Excel to better answer you needs make you more efficient. That's what this site is all about!