Mar 1 2009

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:



Below is our example Macro code for the macro, CreateFullName:

Public Sub CreateFullName()
‘ CreateFullName will take the first name in Column
‘ A and the last name in column B and combine with
‘ a  separating space in Column C.

‘ Assumptions: No empty rows of data between the first
‘ and last rows of the dataset

Dim numRows As Integer

‘ numRows equals the number of names in the spreadsheet
numRows = Application.WorksheetFunction.CountA(Range(“A:A”))

‘ Loop through every name in the spreadsheet (Start after header)
For x = 2 To numRows
          ‘ Full Name = First Name + ” ” + Last Name
           Cells(x, 3) = Cells(x, 1) + ” ” + Cells(x, 2)
Next x

MsgBox (“CreateFullName Macro Complete”)

End Sub

Finally, here is what the spreadsheet will look like after the macro finished:
Walking Through the Example
Alright, now that we have the macro and have shown what it will do when run, let’s step through this example line-by-line.
  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

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

TAGS: , ,

3 Comments on this post


  1. Working with Userforms Part 1: Hello World! Button | Excel Hints wrote:

    [...] the macro with this code (more info on getting started with macros): Sub TestMacro() UserForm1.Show End [...]

    September 8th, 2009 at 3:21 pm
  1. Vijay said:


    The example which you shown is very good .Can u upload some examples which have good logic.
    Thanks ,

    June 18th, 2009 at 2:19 am
  2. Ulhas said:

    Hi John

    The example which you shown is Excellent. Please upload few more examples which have good logic. Also can you please resolve my query regarding macros? I have few doubts which I need to clear.

    Thanks ,

    July 10th, 2010 at 7:40 pm

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks