Writing a Macro

You may have heard a macro as being referred to also as a sub or subroutine, but any way you phrase it, it is a set of instructions in VBA used to perform an action.  Using macros in Excel will greatly enhance your Excel experience and open up many possibilities through flexibility and speed.  In this post we are going to take a look at how to write a simple macro.


Getting to the VBA Editor
There are several methods to open the VBA editor, as we discussed in Use the Record Macro Feature in Excel and Familiarizing With the Visual Basic Editor.  To quickly open the VBA Editor, hit Alt-F11 on your keyboard from Excel.  Then from the main menu at the top, select Insert–>Module and a blank Module will appear.

Example Macro
For this example, we are going to have the macro loop through the rows on the spreadsheet below and combine the first name and last name into a column called Full Name.  This can easily be done with a formula as well, but I just want to show you the basic working of a macro.  We will get into more involved macros later. 

Below is our spreadsheet before we run the macro:

1. Line 1 – keyword Public  - the keyword public must be used if you want to use the subroutine outside of the current module code.  If you have a larger program with multiple modules, you will want to do this
2. Line 1 – keyword Sub - the keyword sub tells the program that this is a subroutine and not a function.
3. Line 1 – Sub name- the name of the subroutine must not contain any spaces (Must be written like CreateFullName, not Create Full Name.
4. Lines 2-8 – Comments – Anything place after a single quote will be ignored during the execution of the program.  It’s always a good idea to comment your code to better document it for future use.
5. Line 9 – Dim Variables – as a best practice, it is important to declare any variables you will use in your code at the beginning of the subroutine, using the DIM keyword, then by telling the subroutine what type these variables are.
6. Lines 10-17 – Code for Subroutine- this is where you find all the work for our subroutine, CreateFullName.  All we are doing here is finding the bottom of the data we want to run the macro on.  Next we loop (read the post on loops) through all data from rows 2 through the number of rows found.  During each loop, the macro is concatenating First Name (Column A), space, Last Name(Column B) and placing the string in Column C.  Finally a message box is shown telling the user the macro has completed.
7. Line 11 – End Sub - this tells the program that this is the end of the Subroutine and there is no more code to evaluate.

This is a very simple example that could have easily been performed with a formula.  But the goal of this example is to show you the basics of creating a macro that you can build off of with new projects.  In future posts, I will be showing more involved macros for special needs.

More Excel Tips

• Macro to Disable Cell Calculations on a Sheet
• Is VBA in Excel 2007 Slower Than in Excel 2003?
• Using the Status Bar to Display Messages
• Count Rows with Data in a Macro
• Using a For Loop in a Macro