From the course: Excel: Learning VBA

Run a VBA procedure - Microsoft Excel Tutorial

From the course: Excel: Learning VBA

Start my 1-month free trial Buy for my team

Run a VBA procedure

“

- [Instructor] 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. I'll demonstrate a few of them in this movie. My sample file is 010_9_RunAMacro and you can find it in the chapter one folder of the exercise files collection. I have some data on the sales tax worksheet and you can see that I have a sales value in cell C7. Now I'll move to the Visual Basic Editor and show you the macro that I want to run against this data. So I'll press Alt F11 and I have a module with a subroutine and the subroutine is called Display Tax. What this does is to take the value in the active cell, multiply it by 0.09 and round the result to two digits. It then displays the result in a message box and there are a number of ways that I can run this particular sub routine. First to make sure that the active cell is the one I want. I'll press Alt F11 and C7 is selected. Good. So Alt F11 again, and with the cursor flashing in the subroutine, I'll press F5 to run it and sales tax due is $562.52. Alright, I'll click Ok. Another way to run the subroutine is to do so from the macros dialogue box. So I'll press Alt F11 to move back to the workbook, make sure C7 is again the active cell and then on the view tab of the ribbon, I'll go over to the right and I'll click the body of the macros dialogue box. That displays the available macros. It can be in this workbook or in others. So I have Display Tax, click Run, 562.52 and okay, and the macros dialogue box closes. You can also assign a macro to a shape. So for that, I'll go to the insert tab on the ribbon. Then I will click the illustrations control, click Shapes and I will get a rectangle with rounded corners. So I'll click that and I'll draw it here and cell E4 and I'll make it a little larger, but I won't change this formatting. Now I'll double click it and I'll say Show Tax and I'll click away. So now that the shape is in place, I can right click it and from the shortcut menu that appears, I can go about three quarters of the way down and click assign macro. So I have a couple of options. The first one is an event that would run when the shape is clicked. In this case, I want to run Display Tax. So I'll click that, click okay, and then I'll click away. Actually, I'll click away so that C7 is selected and is my active cell and then I'll click the control and you can see that the mouse pointer changes to a pointing hand and I get my message box again. If you want to edit the shape, rather than running the macro, hover your mouse pointer over it, then hold down the control key and right click the shape and the menu will come up and you can edit it, format it, and so on and I'll go ahead and click away and click escape. You can also assign a macro to a button on the quick access toolbar. To do that, I will go up to the customized quick access toolbar button that's on the title bar of the program window and I'll click More commands and this opens the quick access toolbar page of the Excel Options dialogue box. From here, I'll click the Choose commands from Controls down arrow, and I'll click Macros, then I'll click Display Tax and click Add and you see that it has been added to this list of macros that are available on the quick access toolbar. If you want to change the appearance of that macro by changing its button, you can do that. So go ahead and click it on the quick access toolbar, then click modify, and you can select any of the symbols that are available here and so for this one, I'll just click a green check to make it different and click Ok and Ok again and you can see that it has been added to the quick access toolbar. Now cell C7 is selected, I'll click that button, sales tax due is 562,52, click Ok and we have several different ways to run a macro within an Excel workbook.

Contents