Join Curt Frye for an in-depth discussion in this video Running a VBA routine, part of Up and Running with VBA in Excel.
After you work with Excel for a while, you will develop preferred ways of implementing your VBA solutions. Because every programmer is different and because not every technique works well in every circumstance, Excel provides several different ways of running your macros. The first way to run a subroutine is to display the macro's code module in the Visual Basic Editor, pressing Alt+F11 and opening the window you want, and pressing F5. When you do, Excel runs the macro. In this case, it calculates 9% sales tax on the value in the active cell. The second way to run a macro is to open the Macros dialog box, click the macro, and then click Run.
To do that, you go back into Excel, then on the View tab, click Macros > View macros. Click the macro you want to run, and then click the Run button. And we get the same answer. The third way to run a macro is to link it to a shape in the body of the workbook and then when you click the shape, Excel runs the macro. In this case, I will just insert a rounded rectangle and give it the label, Calculate Sales Tax.
Now when I right-click the button, on the Shortcut menu appears the Assign macro option. When you click it, you can pick the macro you want to run when the shape is clicked. In this case, WriteTaxTotal is the one we want. Click OK, and there we are. If you click away from the shape to deselect it, in this case I'll go back and click the cell that contains the value that I want to calculate the sales tax from, and then when you move back over the shape, you'll see that the mouse pointer changes to a pointing hand. That means that some sort of action will take place.
You see the same thing when you hover the mouse pointer over a hyperlink. That will take you to another Excel document, or to a web page. So when I click it, Excel runs the macro, and it calculates 9% sales tax on the value in the cell we selected. In Excel 2007, you can also add a button to the Quick Access toolbar and run a macro from there. To do that, you go up to the Quick Access toolbar, which is here to the right of the Office button, right-click it, and then click Customize Quick Access toolbar. To add a macro command to the Quick Access toolbar, in the Choose commands from list, you click macros.
And you see a list of all the macros that are available to you. In this case, it's WriteTaxTotal. That's the one we want, and I will click Add. And the macro appears in the Quick Access toolbar list here on the right side. If I wanted, I could change the button's position by clicking the move up or move down buttons, but in this case I won't bother. I can, however, change the appearance of the button. Right now, when it appears on the Quick Access toolbar, it will just have this generic macro-looking button. If I want to change that, I can click it, so it's highlighted in blue, and then click Modify.
And from here, I can select a new button image. We are calculating taxes, so I will click the dollar sign in quotation marks and click OK. When I do, the button updates here, and when I click OK, the button appears on the Quick Access toolbar. And as a tooltip, the name of the macro, WriteTaxTotal, appears when I hover the mouse pointer over that button. And just make sure that I have the proper cell selected. I do. Click the button, and Excel runs the macro. The example I just covered works in Excel 2007, and will also work in Excel 2010; however, Excel 2007's User Interface is different from Excel 2003's, so you can't use the same techniques.
For more information about running macros using custom menu items in Excel 2003, see the course named Excel 2003 Essential Training. The movie you want is Macro Menus. Excel gives you many ways to run a macro you've created, whether that's from within the Visual Basic Editor, the macros dialog box, by clicking a shape, or by clicking a toolbar or Ribbon command. You can use any of those methods when you design your solution. Of course, if you discover that your first choice of method isn't intuitive or doesn't meet your user's needs, you can always change it.
- Working in the Visual Basic Editor
- Adding code to a macro
- Creating, exporting, and deleting code modules
- Declaring and using variables
- Managing variable scope
- Defining arrays
- Managing workbooks and worksheets with VBA
- Repeating tasks with loops
- Debugging VBA code
- Cutting, copying, and pasting cell data
- Running and triggering event procedures