Many people use Excel, but few of those people know how to add programs within Excel either to be part of the spreadsheet or just as a tool to help build the spreadsheet. Over a series of blog posts, I will examine how to work with macros and create your own UDFs (User Defined Functions). My goal is not to teach you everything about how to use these two features because there are books out there devoted to that, but just to show you how to get started.
The first thing you have to do is to activate the Developer ribbon. While Microsoft provides a developer ribbon with options to develop macros and UDFs, it does not by default display this ribbon. I guess that is another example of my opening statement showing that even Microsoft does not believe that the ‘average’ Excel users will need to use these features. But then you are not average either, right?
To turn on the Developer ribbon, open the File menu and click on Options toward the bottom of the menu. This opens the Excel Options dialog that consists of the menu on the left and the options for the selected menu item on the right. Find in this menu the Customize Ribbon menu item and click it. There are many controls on the right side of the dialog to help you customize the ribbon. For now, the only control you need to focus on is the large list box on the right side that shows the main ribbon tabs. Unless you have turned it on previously, the only ribbon not selected (with a check in the checkbox to the left of the name) is Developer. Click this check box and then click OK. You should now see the Developer tab at the top of Excel and if you click this tab, you should see the Developer ribbon shown below.
Now that you are ready to access the developer tools such as the ability to record macros or create your own UDFs, let’s suppose you have the spreadsheet shown on the right from a product inventory. It shows the product code, product name, and current count. However, the data appears as a single column of information. You want to create rows from the data. You could manually copy and paste data into multiple columns but that would be too time consuming. Because the structure of the data is consistent (3 rows of data sequentially displaying the code, name and count), you realize that a macro could perform those repetitive steps for you much easier.
Position your cursor in the first cell of the data and open the Developer ribbon. Because we are going to be moving data around, first you want to make sure that you select the option Use Relative References in the Code group of this ribbon. When building a macro that must move between cells, there are two things that you must remember:
1) Never use the mouse to move between cells. When you click on cells with the mouse to move to a new cell, it adds the absolute cell reference to the macro. This is rarely what you want. In fact, it only really makes sense if you want to access a specific cell every time you run the macro no matter where in your spreadsheet you started the macro. Instead, you want to use the keyboard arrows to move between cells.
2) Even if you move between cells with the keyboard, you will not get a relative reference (to allow you to apply the macro on different rows or columns in the spreadsheet) unless you first click the Use Relative References option in the Code group of the Developer ribbon. You will know when you have selected this option because Excel highlights it.
Failure to consider these two points can lead to a very frustrating experience for the first-time macro builder and may even convince them that macros are not worth learning. Unfortunately, I do not know of a way to make relative references the default setting for all new worksheets even though that would seem to be a logical need and which could appear in the Excel Options we looked at earlier.
Check again that you have the first cell and only the first cell selected and click the Record Macro option in the Code group. A dialog box appears as shown below. You must provide a name for the macro. Excel suggests a simple name that begins with the word Macro followed by a sequential number. You do not have to use this name. You can provide your own name, but it cannot use spaces. If you want a name that has multiple words in it, use an underscore rather than blanks between the words. You can then provide a shortcut key to quickly execute the macro. While not required, a shortcut key makes it easier to execute the macro without going through the Developer ribbon, opening the Macros dialog, selecting the macro you want to execute and then clicking Run. You also have some options about where you can store your macro. For now, just store the macro in: This Workbook. Finally, the Description field is also optional but can be useful if you create many macros or if you use the Personal Macro Workbook to collect all the macros that you typically use. I’ll discuss macro storage options in a future blog entry.
From this point forward until you stop recording, Excel translates all of your actions into the macro, which is essentially a small Visual Basic program. If you are following along in your own version of this data, follow these steps:
1) Press the down arrow key once.
2) Press CTRL-C to copy the product name to the clipboard.
3) Press the up arrow key once, then the right arrow key once.
4) Press CTRL-V to paste the clipboard contents in the second column of the row having the product number.
5) Press the left arrow key once and the down arrow key twice.
6) Press Ctrl-C to copy the product quantity to the clipboard.
7) Press the up arrow key twice, then the right arrow key twice.
8) Press CTRL-V to past the quantity in the third column.
9) Press the left arrow key twice and the down arrow key once.
10) Hold the Shift key while pressing the down arrow key once more to select the two rows we now need to remove.
11) Right click the mouse button to display and dropdown menu and select Delete.
12) From the popup Delete dialog, select Shift Cells up and click OK.
13) Now two cells are still selected, the product code and product name of the next item. You only want to the product code cell selected. To correct this, press the up arrow once followed by pressing the down arrow key once.
14) The product ID should now be the only selected cell so we can stop recording by clicking either the Stop Recording option in the Code group of the Developer ribbon or by clicking the Stop Recording button just below the sheet tabs in the bottom left corner of the worksheet.
You can now repeat these steps for each product item by making sure the product ID cell is selected and then pressing the shortcut key combination you created in the Record Macro dialog until you have the entire table converted as shown to the right.
Well, that’s a quick start to creating macros to replace a series of repetitive keystrokes with a single executable program. In future blog entries I’ll talk about different ways you can access your macros, different ways you can save macros depending on how you use them, how to edit your macros to customize them further.