Are you using Excel in first gear?

Picture this scene on an interstate. You are standing in a rest area and hear a loud engine roar coming towards you. A powerful red sports car drives into view, revving really hard. It moves past you pretty slowly and continues to rev hard in first gear, so loud that you need to cover your ears to stop going deaf!

At this point you’re probably thinking to yourself “The driver doesn’t know how to drive that car!” as he continues to rev the engine hard in first gear, instead of changing up to second and third. Now keep that picture in mind as I ask the question “Are you using Excel in first gear?”

Let’s assume you know most of Excel’s menus and shortcuts, and are able to use many of Excel’s powerful features such as Pivot Tables and Charting. But if you don’t know anything about macros and VBA, your ability to use Excel is still pretty limited. You to do everything by hand, step by step. It’s like you can’t take Excel’s powerful engine into second or third gear.

Let me show you a quick video to introduce the Macro recorder in Excel 2010, and how we can use a simple recorded macro to change number formats quickly.

Image Credit: Racing car model courtesy of Buckey

Watch this 9 minute video about Macros and VBA in Excel 2010

YouTube responded to TubePress with an HTTP 410 - No longer available

Sound Credit: Ferrari355 courtesy of “enginemusic”

Introduction to Macros and VBA in Excel

You can think of a macro as a tape recording. It remembers word for word what you “say” to Excel. VBA = Visual Basic for Applications. This is the language used by Excel to record macros. I wrote another article on this subject here.

Changing number formats in Excel with VBA

The number format we used in the video gives us 2 decimal places, with a comma thousand separator, puts negative numbers in red font between brackets like this (1,015), and has a dash for zero values.

In the macro this is represented by “#,##0.00_);[Red] (#,##0.00);-_)”

If you want to learn more about custom number formats in Excel, I recommend this article by Jon Peltier.

Check out the Google Maps VBA demo

For an example of advanced usage of VBA, check out our Google maps Google Maps in Excel article here. You’ll see that once you master the VBA programming language there is a lot you can accomplish in Excel.

Tell us how you feel about Macros and VBA – Leave a comment below

We’d love to hear how you feel about learning Macros and VBA. It’s a tricky subject to get into, especially if you are not an experienced programmer, but with the right guidance and instruction you will be able to grasp the basics of Macros and VBA. What kind of things would you like to automate in Excel? Looking forward to your comments.

About-Launch-ExcelWelcome to Launch Excel
If this is your first time to - Welcome to Launch Excel. My name is Victor and I'm the Chief Teaching Officer of Launch Excel, a website I started to help you learn how to use Microsoft Excel more effectively. Check out my welcome page created specially for you: Click here to view Welcome Page

7 Responses to Are you using Excel in first gear?
  1. Don
    October 14, 2012 | 4:02 pm

    I would love to learn VBA. I have just macro’s, but not gone back & edit them like you did with the number format. That was cool.


    • Victor
      October 15, 2012 | 3:35 am

      Hi Don – thanks for your comment. Yes there’s a whole new world waiting, once you start learning VBA for Excel. I can remember the first time I opened the VBA editor and my colleague showed me how to read the VBA code. If you watched the film ‘The Matrix’, my learning journey with VBA was a bit like going from looking at the green falling code (total gibberish) to becoming immersed in the full virtual world of the matrix (total immersion).

      What’s really cool about MS Excel is that you can record macros and then edit them in the VBA editor. I highly recommend you have a go at recording your own actions in Excel, then checking out what the code looks like. It’s a really quick way to learn about Excel’s objects (the things that make up the Excel world) – just remember that the code generated by Excel can almost always be improved for efficiency and readability. You can even position the Excel window and VBA editor window side-by-side, and watch ‘live’ as Excel records the VBA commands for the actions you do in Excel.

      Over the next couple of months I will try to create more material on VBA and Macros, so look out for new videos and articles coming soon.

      Cheers – Victor

  2. tamoghna
    October 21, 2012 | 12:03 am

    Hi Victor, Great introductory video on VBA!! Would like to see more of them in the future.

    • Victor
      November 7, 2012 | 6:26 am

      Hi Tamoghna

      Thanks for your comment. Yes I’d love to create more introductory videos on VBA like this, and hopefully will get round to it soon. I’ll keep you posted!


  3. Chris
    December 18, 2012 | 12:27 am

    I would like to monitor google print directly with excel VBA.
    my macro creates reports every day and I would like this report to be printed on a remote printer.

    • Victor
      December 18, 2012 | 8:54 pm

      Hi Chris. Have you tried recording a macro as you send your report to the remote printer via Google Print? I don’t have any experience with Google Print so can’t comment further, but you might want to post your question on StackOverflow as someone there might have an idea on how to proceed:

      About Stack Overflow:
      “This is a collaboratively edited question and answer site for professional and enthusiast programmers. It’s 100% free, no registration required.”

      • Chris
        December 22, 2012 | 1:41 am

        thanks for your quick response I check on stack overflow