How To: Use Someone Else’s Macro

This is the first in a series of How To posts that are targeted at Excel users who are unfamiliar with VBA programming. Topics will include

  • Using someone else’s macro
  • Recording your own macro
  • Fixing a recorded macro
  • Assigning a macro to a button or shape, ActiveX button, or menu or toolbar
  • VBE settings
  • VBE features
  • Debugging and error proofing
  • Suppose you need Excel to do something beyond its normal capabilities, or just do something automatically to save you from hours of tedium. The answer is often a VBA procedure, or “macro”. You’re not a programmer, you don’t know a macro from a mackerel, so what do you do? You could ask a colleague, you can Google for help, you can ask on a forum or newsgroup. And the answer is, “Use this macro.” Oh boy. Now what do you do?

    Let’s put the macro into your workbook and try it out. We’ll use the following simple macro for this exercise.

    Sub TestMacro()
      ' center alignment
      Selection.HorizontalAlignment = xlCenter
    
      ' font: arial, 10 point, bold, blue color
      With Selection.Font 
       .Name = "Arial"
        .Size = 12
     .Bold = True
        .ColorIndex = 5
      End With
    End Sub
     

    The first step to using a VBA procedure is to open the VB Editor (VBE). There are three ways to get there:

    • Tools menu > Macro > Visual Basic Editor
    • Visual Basic Editor button on the Visual Basic toolbar
    • Shortcut key combo Alt+F11

    VB Toolbar - VB Editor Button

    Find your workbook in the Project Explorer window. By default, the Project Explorer is docked along the left edge of the VBE window. If you don’t see it, activate it from View menu > Project Explorer.

    Project Explorer Window

    Note the treeview of the workbook. The top level is the VBAProject, with the workbook name in parentheses. Under that is a folder entitled Microsoft Excel Objects. Within this is an element for the workbook (ThisWorkbook) and for the worksheet, with the sheet name in parentheses. There would be an element in this list for every worksheet and chart sheet in the workbook.

    We need to insert a code module. This is easy: Insert menu > Module.

    Project Explorer with New Module

    A new folder, Modules, has opened under the VBAProject entry in the treeview, and an item called Module1 appears in the folder. A blank code module window has opened in the VB Editor.

    Code Module Window

    Copy the macro code from wherever you found it, and paste it into this window. Red text indicates a syntax error, so you will need to figure out what’s wrong. Often it’s just a problem with line wrapping in email or on a web page.

    Code Module with macro

    This macro requires a range to be selected, so go back to Excel and select a range, then run the macro. This can be done in a number of ways:

    • Tools menu > Macro > Macros, select the Macro, and click Run
    • Shortcut Key combo Alt+F8, select the Macro, and click Run
    • Run Macro button on the Visual Basic toolbar, select the Macro, and click Run
    • Click in the macro with the mouse and press F5

    Run Macro Button

    Macro Dialog

    This is the first exposure most people have to VBA and the VB Editor. In a series of upcoming posts, I’ll cover the following VB topics:

     

    Peltier Tech Charts for Excel

    Comments

    1. You know Jon, I’m going to take a tangent on this topic. I am an experienced programmer (in other languages) and it seems like every VBA lesson out there assumes that you have no knowledge whatsoever. Are you aware of some better advanced material (i.e., I know how to program and now I want to know how to connect to a SQL server and a few excel sheets on networked drives and then perform data validation and calculations on the combined data with VBA). Sometimes I think it would be faster to figure out the COM model and just use IronPython :P

      Excel’s object model is a bit quirky and it’s implementation of OOP seems to be lacking, where is the material on that stuff?

      Thanks

    2. I look forward to this series. VBA is the frontier I just cannot seem to cross. I can do a lot with “charts and things” without VBA, but usually it means I have to maintain my sreadsheets myself. If I could turn my knowledge into robust simple programs, I could hand more work off to my colleagues :-)

    3. Derek – Charting was the last area of VBA I got into. There was something scary about it. Or that I was losing control. But I got over it, got into the branch of the object model that dealt with charts, and despite some funny business there, realized it’s really no different that the rest of it.

      Dan – The advanced work is not readily available as tutorials. The examples in advanced Excel VBA is there, but hard to find. You might find it useful to expand your search to VB6: those resources are still available despite dot-net having taken over. There are a small number of advanced books. Walkenbach’s books range from beginner to more-than-intermediate, while advanced topics and details are found in the Excel VBA Handbook series and the seminal Professional Excel Development (look for authors Bullen, Bovey, and Green together, and occasional others).

      Here is a link to a number of books about programming in Excel and VBA:
      http://peltiertech.com/Excel/xlbooks.html#ExcelVBA

      And here are the most advanced Excel VBA books you will find:

    4. Just to be clear, I wasn’t saying I’m new to VBA as it applies to charts. I was saying I’m new to VBA, period.

    5. Derek – VBA isn’t too tough. The macro recorder (see the next post) gives you much of what you need, then it’s a matter of fiddling around, or using Google, to make it do wyat you need.

    6. Jon, I believe Derek is the user Charley Kyd talks about…

      Derek, believe me, I am not a programmer, and I don’t go much beyond the examples above, but once you start automating some tasks with recorded macros you will not turn back. There are many things that you simply can’t do without VBA (like stupid and repetitive tasks and user interfaces).

    7. Note to self: blog about how I got started with Excel programming.

      Jorge – I’ve heard it said that laziness is the mother of all inventions, which may be true. I know for me, laziness is why I got started programming. Back in the old days of Excel 4, I had this technique I used to model material deformation behavior, and it required iteration between two Solver loops, one for loading and the other for unloading. You had to run alternative Solver simulations on each part until the difference between them was less than some small acceptable error, which meant probably a total of a dozen or so Solver loops, and you had th change all of the constraints and target cells and changing cells each time. Then repeat for a total of a dozen cases. So let’s say 144 Solver loops, each set up manually. This took more than half a day to do by hand, and it was tedious as hell, almost as tedious as this description. Anyway, I spent a couple weeks in Excel with the XLM manual in my lap, and I automated the sucker, and finally it ran in about two minutes. When I got Excel 97, this was my first VBA project, and it was hard, but I got the time down to ten or fifteen seconds. There was no turning back.

    8. Thanks for the pointers (no pun intended) Jon.

    9. Hello,
      I m in need of urgent help!
      I m putting together a large presentation requiring the use of the same layout (including graph)
      I m trying to build a blank template with graphs that I can copy & paste several times in order to save time but I m encountering some issues with the source data of the graph
      It keeps pulling the information from the original sheet (sheet 1) in order to correct the graph I have to go in
      -source date and reselect all the cells for each individual cell.
      Is there away to have the graph read only the cells vs sheet + cells?

      Your urgent help is appreciated
      Thank you
      Mathieu Imbert

    10. Following guidelines in Getting Answers For Your Excel Questions, please submit your question to a dedicated Excel forum.

    Trackbacks

    1. […] This procedure should be placed into a regular code module, as described in How To Use Someone Else’s Macro. […]

    2. […] a short procedure to make it happen. Copy this code and paste it into a regular code module (see How To: Use Someone Else’s Macro if you are not familiar with this). Select the shape and run the following […]

    Speak Your Mind

    *

    Peltier Tech Charts for Excel 3.0