Lean Mean VBA Machine

This is Recruit a New VBA Programmer Week, according to Dick Kusleika, so we’d better get moving. I haven’t recruited anyone yet, have you?

  • If you aren’t a VBA programmer already, keep reading, and you can be my victim, errr, willing recruit. Don’t be afraid – it won’t hurt a bit. Or byte.
  • If you are a VBA programmer, just forward this post to a friend or co-worker, and you’ll have completed your recruiting responsibilities.

Getting Started With VBA

If you don’t know anything about Excel VBA, where should you start?

  1. Understand what Excel can do without macros
  2. Think of a simple, but boring, Excel task that you have to repeat every day
  3. Use the Macro Recorder to automate that simple routine

What Can Excel Do?

Excel can do all kinds of amazing things, without macros. Get to know Excel’s built-in features, such as:

If you use those features, you might not need a macro. For example, instead of checking each cell in a column, and colouring it red if it’s over $100, use conditional formatting to highlight the cells automatically.

Identify a Task to Automate

If you use Excel every day, you probably have a few tasks that you repeat regularly. To get started with Excel VBA, pick one of those tasks, to try and automate it.
Maybe you have a list of orders, and every day you filter that list to find orders for a specific product. Here are the steps that you follow every morning:

  1. Open the Orders file
  2. Filter the list for Product ABC
  3. Copy the Product ABC orders
  4. Create a new workbook
  5. Paste the Product ABC orders into the new workbook.

Instead of doing that task manually every day, you could automate it.

Get Ready to Record

Once you’ve decide which task to automate, you’ll use Excel’s Macro Recorder tool to create the VBA code. Before you start recording, get everything into position. For example:

  • Do you want the macro to open a specific workbook, or will that workbook already be open?
  • Should you select a cell or worksheet before the macro runs, or will that be part of the macro?

In our example, we want the macro to open a workbook for us, then filter and copy, so nothing needs to be open when we start recording.

Start Recording

Once everything is in position, you can start recording.

  1. At the bottom left of the Excel window, click the Record Macro button.
    • RecordMacro01
  2. In the Record Macro dialog box, type a one word name for the macro, e.g. CopyOrdersABC
  3. Select the workbook where you’d like to store the VBA code. In this example we’ll store the code in a new workbook. Later, we can open that workbook every morning, to run the macro.
  4. (optional) Type a brief description of what the macro will do.
  5. Click OK, to start recording.

RecordMacro02

Perform the Macro Steps

While the Macro Recorder is on, perform the steps that you want to automate. In this example, these are the steps:

  1. Open the Orders file
  2. Filter the list for Product ABC
  3. Copy the Product ABC orders
  4. Create a new workbook
  5. Paste the Product ABC orders into the new workbook.

If you mess something up, don’t worry about it. You can stop the recording, close files without saving, and start over again.

Stop the Recording

Once you finished all the steps, turn off the Macro Recorder.

  1. Click the Stop Recording button at the bottom left of the Excel window.
    • RecordMacro03
  2. If you stored the macro in a new workbook, save that file (as Excel Macro-Enabled Workbook *.xlsm) , so you can run the macro later.

Get Ready to Test the Macro

To test the macro, get everything into position again. For example:

  • If you saved the macro in a different file, make sure that workbook is open.
  • If the macro will open a specific workbook, make sure that workbook is closed.

To run the macro, you can add the Developer tab to the Excel Ribbon, if it’s not there already:

  1. Click the Microsoft Office Button, then click Excel Options.
  2. Click the Popular category
  3. Add a check mark to Show Developer tab in the Ribbon
  4. Click OK

DevTab01
If you haven’t run macros before, you might need to change your macro security level. (You might have to clear this with your IT department.)

  1. On the Ribbon, click the Developer tab
  2. In the Code group, click Macro Security.
  3. In the Macro Settings category, under Macro Settings, click Disable all macros with notification
  4. Click OK.
  5. If you changed the setting, close the workbook, then reopen it.
  6. If a security warning appears at the top of the worksheet, click the Options button.
    • SecurityWarn01
  7. Then click Enable This Content, to allow the workbook’s macros to run.

Test the Macro

Now, you can run the macro, to see if it works the way you want.

  1. On the Ribbon, click the Developer tab
  2. in the Code group, click Macros.
  3. In the Macro dialog box, click the macro that you want to run.
  4. Click Run.

If you get an error message, click the End button, or click Debug, if you’re feeling adventurous. A line of code should be highlighted in yellow, and that might give you a clue as to what went wrong.
If everything went as expected, great!

Recruitment Completed

Whether things went right or wrong, congratulations! You created an Excel macro. Consider yourself recruited, and welcome to the wonderful world of Excel programming.
You’re on your way to becoming a lean, mean VBA machine.

Watch the Video

[updated 2009-Nov-26: Added video tutorial]
To see the steps demonstrated, you can watch this video.

______________