Lean Mean VBA Machine

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:

Conditional Formatting

Data Validation

Pivot Tables

AutoFilters and Advanced Filters

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.
Excel Record Macro dialog box
Excel Record Macro dialog box

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
Show Developer tab in the Ribbon
Show Developer tab in the Ribbon

Macro Security Settings

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 me do all the steps for recording and running an Excel macro, you can watch this video.

______________

15 thoughts on “Lean Mean VBA Machine”

  1. OK You got me – oh joy – I really want to get into it. But one thing if in 2007 Excel using macros to clear a worksheet and then take another column which is linked to an Access table which keeps it up to date and then transfers that column to another worksheet, then breaks it down into columns of 30 and then prints it, does VBA change the process? That column from Access gets shorter almost every day, have problems with that because I get blanks were the data is now deleted by way of Access.

  2. I am on board. most of the VBA tutorials start and end with recording Macro and editing it. Hope this one does more than that.

    Good Luck

    Sri

  3. Thank Fred and Sri, good to have you on board with this VBA thing.

    Fred, you could record the steps as you do that process manually, then run it every day. Since the list is always getting shorter, the worst that will happen is that blank cells will be copied and pasted at the end of the macro.
    Or are the blanks within the list?

  4. I would like to send it to you if I may. I have found that you can not send an Access dB through as an attachment but I have everthing outlined. I laugh as rough as it is, it is cool running thing, the results appear in a matter of a 30 sec start to finish. The Excel just takes those numbers from one worksheet puts them on to another in a straight column then breaks them down and then prints the sheet out at the end ( it does stop in a print preview mode) I’m not that confident that the result is going to be the right thing.

  5. […] To see the Excel VBA code that changes the setting, we can use the Excel Macro Recorder. […]

  6. […] To add Option Buttons in Excel 2007, you’ll use commands on the Ribbon’s Developer tab. If you don’t see that tab on your Excel Ribbon, follow the instructions here: Show the Developer Tab on Excel Ribbon […]

  7. Hi Debra
    Do you have the data you used in this blog somewhere that I can download?
    Best regards
    Gerald

  8. A newbie to VBA. Would like to take rows from multiple worksheets (different layouts) when a certain column is populated (based on worksheet), have portions of that row appear on another worksheet. Any ideas?

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.