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.

[ad#in-post-ad]
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:

macro_example1 

 

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:
 
macro_example2
  
[ad#in-post-ad]
 
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: , ,

12 Comments on this post

Trackbacks

  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. ganesh said:

    hi………..JOHN
    this is good macro eg..
    i try this it work very fine … i really wnt some more good eg.. in excel macro with some for programming code.

    can u upload all those macro..

    THANKS

    March 6th, 2009 at 2:38 am
  2. john said:

    I’ve been thinking about adding a tool that can easily upload code and distribute through Excel Hints. I’m going to look more into that.

    Thanks for the suggestion and visiting.

    John

    March 6th, 2009 at 10:01 am
  3. Billy G said:

    Nice clean example of a Macro John – your website is very accessible

    March 11th, 2009 at 2:07 pm
  4. john said:

    Thanks Billy… looking forward to adding much more content.

    March 11th, 2009 at 4:07 pm
  5. Vijay said:

    Hi,

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

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

    Hi Vijay… I’ve written some examples throughout my posts of some ways to use macros. What kindof examples were you looking for specifically?

    John

    June 18th, 2009 at 7:51 am
  7. 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
  8. Praveen said:

    Hi John,

    I have seen many examples for macros.But this is best example to understand very easily

    Thanks ,
    Regards,

    Praveen

    May 25th, 2012 at 6:36 am
  9. Florence Lorenzo said:

    This macro example is great and I used it to my excel spreadsheet. Can you provide more macro examples like macros for adding columns and entering vlookup formulas.

    August 31st, 2012 at 4:45 pm
  10. Joe James said:

    I am trying to write a macro that: column A has data, B,C or D may have data but if B,C and D are empty increment my counter to count+1. Can’t get this to run. Count, counta, countblank will not work because of conditional variables.
    E.G.Cell A1 not empty, cell B1, empty, C1empty, D1 empty
    count = count+1 for next to look at all the rows

    June 26th, 2014 at 9:32 pm
  11. engine submissions said:

    Hi terrific website! Does running a blog like this
    take a lot of work? I’ve absolutely no knowledge of programming but I had been hoping to start my own blog
    soon. Anyways, if you have any ideas or tips for new blog owners please share.
    I understand this is off topic however I simply wanted to
    ask. Thanks!

    August 8th, 2014 at 6:43 pm

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives