Making a Macro
Creighton University
April 2, 2002
Peter Kolar
Brandon Ebert
John Reuter
A macro is a program in Microsoft Excel that allows you to customize a common
task to make the process easier and quicker.[1] Fortunately, Excel records your actions so
you don’t have to worry about complex program writing. We will take you through
the steps to create a very simple macro that will lay the foundation for more
complex macro work in the future. In
this example, we use Excel 97. The basic fundamentals are the same in other
versions of Excel even though some steps might have small differences.
1. The first step in making a Macro is to walk through the process on paper of what you want to get accomplished. After you have very detailed notes of what you want to do you should follow the steps you outlined on paper to make sure it work through Excel.
2. The next step necessary in preparing a Macro is to open a new Excel worksheet. In this process we are going to make a macro that will multiply any numbers you place in cells A1 and A2 and place the answer in cell B1. Yes, by using the function wizard we can do this task quicker but, remember, this is just the groundwork we are working on now to build a foundation to make bigger and better macros in the future.
3. Type any numbers in cells A1 and A2.
4. Now we are ready to start. Go to Tools > Macro > Record New Macro and you should see something that looks similar to this.
In the Macro Name Box, enter the name of your macro. For our example, we name our Macro “Multiplication” or something that will remind us what we are doing.
The shortcut key area is where you type in what shortcut letter you want to use to run your macro. This is the letter you will push along with the “Ctrl” key to run the macro when you are ready. For our macro, we will use the letter “m”. This will overwrite any of the default Excel shortcuts so make sure you don’t overwrite one of the ones you use more often. Some of the most common are “Ctrl + c” for copy and “Ctrl + v” for paste. If you don’t use shortcuts, it won’t make a difference which letter you use. Lastly in the “Store Macro Pull Down” you can select “This Workbook,” “New Workbook” or “Personal Macro Workbook.” We will select “This Workbook” because we want our Macro to run on the workbook we are currently working on. However, if we wanted it to work in any Excel worksheet, we would choose “Personal Macro Workbook.” If we want to open another worksheet to start from scratch, we could select “New Workbook.”
So, we are going to name our Macro “Multiplication”, mark the shortcut key as “m” and select “This Workbook.” The window should now look like this.
Click OK and we will move on to the next step.
5. Now Excel will record every move we make, including mistakes, so be cautious with every step. Because of this, we recommend that you make detailed notes on what you want to do and follow them step by step.
6. Click your cursor in Cell B1. This is where we will do the multiplication and where we want the answer to show up. Go to the formula bar and click on the “=” sign to start our formula. Click on cell A1, then the multiplication key “*” and then A2. Next, push OK. The result of this step will be the answer from the multiplication process which will appear in cell B1. Since this is all we wanted to do in our Macro we can now stop recording our Macro. By using the tools and macro menus and then clicking on the stop recording choice Excel will stop recording our Macro.
Your macro is now recorded and it is time to check to see if it works. Clear the contents in all the cells and place any numbers in Cells A1 and A2. Place your cursor in B1. Now, use the shortcut “ctrl + m” by holding the control key and typing the letter “m”, the macro will multiply the numbers and show you how a macro works. In the example we used it is necessary for the cursor to be in cell B1 because we used absolute references whereas the multiplication took the numbers directly to the left and left and down one. Therefore, if you clicked on cell B4 and tried to run the Macro the formula in cell B4 would read A3*A4. Since we did not put any values in those cells the result would be zero.
Yes, this is a very simple macro, but this is just the foundation for bigger and more complex macros. A macro will record anything you do in Excel and will duplicate it when you ask it to. You can make graphs from data that the macro found, create a macro that will save the document as whatever appears in cell A1 or even set a macro that will allow you to email the report to a colleague.
Now you are able to make as many macros as you like. You can make many Macros; Excel will save them for you until you need them again. After making a few Macros, you can go again to Tools>Macro> Macros and look at the ones you have saved. This way, if you forget the shortcut key, you can use it through this screen.
Since we have only saved one Macro, it is the only one that shows up. From here, you can run the macro or you can go to “edit” and look at the programming of the macro. If you are proficient in program writing, you can inquire farther. However, if you don’t understand, we would suggest staying clear of this area. You must look at what is the most proficient way of doing the work for the task you need to perform. If it is something as simple as what we did with the multiplication, it would be better to just use the function wizard. Part of the process of learning any new skill is the steps that you take to get there, therefore you should take notes on everything you do and try a few different ways to see what works best for your final project. A Macro is a great tool to help you get work done, but it can also be very time consuming in preparation and productions. However, if it is a task that must be performed often, the benefits are numerous and the amount of time saved can be great.
Bibliography
Rowell, Marge. “Creating Macros in Excel.” http://www.wellesley.edu/Computing/Excel97/macro.html. February 10, 2002