How to Create and Edit Excel Macros

This instructable will show you how to record excel macros and to open and edit the code. The record a macro function allows you to repeat functions, in this case averaging and graphing a data set, by doing it once and simply having to click a button after that. This is accomplished by excel actually creating and recording the code in VBA that is associated with every function you perform in excel. This instructable is intended for people who have a basic knowledge of excel but need help saving time on everyday tasks that they repeat several times. Note that these pictures are from Excel 2013. Older version of excel may be different.

This is a preview of the workbook we will be working with today. There are 65 years of data listed for 4 counties in Iowa. We will average the 4 counties into a new column and graph that column on the y axis and the years on the x axis. Note that these pictures are from Excel 2013.

The first thing we must do is to get the Developer tab to appear so we can access VBA, the code language behind excel. To get started with this click on the File tab as in the picture above.

Once the file tab has been selected, the window will appear as above, now click on the options as shown in Picture 2.

Once in the options window click on customize ribbon.

In the customize ribbon window select the check box by the Developer tab so that it appears as above in picture 4. After this click ok.

Now the DEVELOPER tab is on the ribbon as shown above. This tab will allow access to VBA through excel. Click on this tab.

Once in the developer tab, notice the Record Macro button next to the horizontal arrow. This is the main function we will be using today. Also the vertical arrow is pointing to the Visual Basic and Macros buttons. These will be used later to access the code for the macro once it has been recorded.

As I said before, today we will be averaging the four counties data and placing this value in column F, then graphing the year vs. the average. This is a very basic operation that many people may perform similar functions to, but macros can be recorded for nearly any operation you may perform in excel.

Click on the record macro button.

This box will pop up on the screen. The macro name will default to Macro1 (or whatever number of macro you are making). Macros have restrictions on their names, but mostly be sure to use an underscore (_) between words. The shortcut key can be specified as a keyboard shortcut not already used. Note that it will default to Ctrl + (your input), but if you use a capitol letter then it will look as above. Pressing all of the shortcut keys at once will automatically cause the macro to run, assuming that workbook is open. Next choose where to store the macro in one of the 3:

1. Personal Macro Workbook – you will be able to see this macro in all workbooks you open

2. New Workbook – This will save it to only a new workbook that will be generated

3. This Workbook – This will save it to only this workbook

Since this macro will likely be used on multiple different files, save it to Personal Macro Workbook. Lastly there is a description that can be added, but doesn’t have to be. This will be entered as comments in the code, which will be covered later. Once everything is entered, click Ok

Notice the change from record macro to stop recording as shown above. Until this button is clicked, excel will create VBA code for any action made. Now any function can be performed as it normally would be in excel.

Start by averaging the first row. Then click the bottom right corner of the new cell and drag it down until the last row of the data.

The averaged column should look as it does now until the last row. Then click on the insert tab (far left arrow) and choose a chart from one of the drop down menus (far right arrow). For this example a scatter plot with straight lines was used. Hover over the different types for a description.

Once the graph appears blank on the page, right click in the graph area and choose select data as shown in the figure above.

This window will then appear, click Add so you can select which columns to graph.

This box will then appear. The series name can be set as anything you choose. The x values in this case are the values in column A. The y values are the values in column F. An entire column can be chosen very quickly by clicking in the top column and selecting ctrl+shift+down arrow. Click Ok when finished.

After you select data and click Ok, the same window from step 12 will appear but with a data series added. At this point more data series could also be added, if needed, by clicking add again. Click Ok when finished.

Now the graph will appear as shown above. Next click on the developer tab to get back to stop recording.

Once in the developer tab, click Stop Recording. Now the code can be edited for future use. If there is no need to edit the code for future use simply save and follow one more step.

The second arrow highlights the Visual Basic and Macros buttons mentioned earlier. The visual basic button will simply open the VBA window and display code (also can use shortcut Alt + F11) but to display the code of the macro that was just recorded or to access all macros click on the Macros button.

This window will pop up. To run the macro in the future select it in the display list and either click Run or double click on the name. If the macro is not listed notice the drop down menu under the main window. This will display several choices:

1. All Open Workbooks – Macros in any workbooks you currently have open on your computer

2. This Workbook – Only macros in this current workbook

3. Personal Macro Workbook – All macros you choose to save to your personal workbook

Now to edit the code, in this example to make it able to handle workbooks of different lengths, click on the Step Into button (top arrow).

You will see an entirely new window that looks like the top figure, the bottom is zoomed in on for an example. This is the VBA window where you can edit code by deleting or writing new code. Notice the comments listed at the top of the lines of code in green, they are the name, shortcut and description of the macro that were defined earlier. The code seen on your screen may be different if you clicked in various cells to scroll or if you selected a wrong graph, deleted it and added another. All those lines of code can be deleted here. It is good practice though to “comment” a line of code by adding an apostrophe before the line and running the macro to make sure it still works as it should. The macro can be started and stopped by the green arrow and blue box on the top of the screen highlighted by the red arrows.

This shows how to make the macro work for a set of data of any length. These, and similar pieces of code, can be found online by googling what you want to accomplish and searching through online forums. The top arrow does what’s called defining a variable. It defines a variable called “LastRow”. The next line searches from the very bottom of excel (about 65,000 row I believe) and finds the last cell that has a value in it.

The next 4 arrows are changing the values of the original code, now in green. When a line has an apostrophe in front of it, it becomes a comment and will turn green. This means that that line will not execute when the macro is ran. Many programmers use these to leave notes in their macros for themselves or other people about what a particular part of the program does.

These 4 changes next to the arrows are taking advantage of the fact that a range of cells in excel can be specified in the form “Range(Cells(Row#,Column#),Cells(Row#,Column#)).” This is just a way that VBA will search for a range of cells and is the easiest way to use when a variable is assigned a number, as this example does with LastRow.

To find more ways to edit code similar to these google whatever you need to accomplish and look for online forums. Mr. Excel is one website that has a lot of useful information for beginning programmers.

The macro is now complete and you can save and exit out of the VBA window. Then try deleting the graph from the excel file, click macros in the developer tab and run your macro. If any errors occur search for differences in the code that is provided in the example.