Macros 101 - Task Automation

Macros are your gateway to time savings.  Simply put, a macro is a piece of VBA (Visual Basic for Applications) code that will accomplish a specified set of repetitive or time-consuming tasks.  Just think of a macro as a list of detailed instructions used to accomplish a task.  These instructions are the method of communication needed to interact with our friend Excel, the medium willing to interpret and perform these routine tasks.  Before we dive into how macros work and how they can help save you time in your day to day tasks we need to enable the ‘Developer Ribbon’.  This will allow us to access the Visual Basic Editor (VBE), which is Excel’s Integrated Development Environment (IDE).  This is the place where you will write and interact with VBA code.

To enable the Developer Ribbon:

File > Options > Customize Ribbon > Check the option labeled ‘Developer’

After doing this you will notice a new ribbon at the top of your version of Excel.  Your new, handy, dandy way to access the VBE.  So, now that you’ve uncovered this hidden gem let’s get to work.

First, we’ll need to access the VBE.  You can do this by using key combination Alt + F11 or going to the ‘Developer’ ribbon and clicking on the Visual Basic icon (far left).  After performing this action you’ll see what appears to be a blank screen, with the navigation ribbon on the left.

In the navigation ribbon, you’ll see a few icons that relate to different parts of the excel workbook you have open.  Since, this is your first trip to the code editor, let’s customize it, in order to make it more useful.  There are a number of different tools you can use in the VBE.  To get started let’s enable a few of the one’s I use on a regular basis and explain their function (You’ll thank me later!):

Properties Window:  To access this window hit the F4 key. This window lists a number of settings or properties for the selected object.

Immediate Window:  To access this window use key combination Ctrl + G.  This window is used for debugging code.  It can be especially helpful in diagnosing issues with visual basic code.  I often use it to get the values of variables.  It acts like a Q & A of sorts.  You ask it a question it returns an answer.

Watch Window:  To access this window go to View > Watch Window.  This window is used to view the properties and values of relevant objects and variables.

Edit Toolbar: To access this menu go to View > Toolbars > Click ‘Edit’. This is helpful when writing and debugging code.  I use it frequently to indent, comment and uncomment code.

Now that we’ve set up the VBE let’s create our first macro.  In order to do this, we’ll need to insert a code module.  This can be accomplished by going to Insert > Module in the VBE.  In the blank white screen input this text:

To Execute this VBA code you’ll need to click on the ‘play’ button on the ribbon or hit the F5 key.  When you do, you’ll see a message box that looks like this:

Congratulations!  Now you’re a developer.