Follow these steps to assign a button to the Quick Access toolbar.
1. Click the Microsoft Office button and click "Excel Options" if you run Excel 2007. If you run a newer version, click the "File" tab and then click "Options."
2. Click "Customize" in the left panel. Several menu customization options are shown in the center window.
3. Click "Macros" in the first dropdown labeled "Choose commands from." This selection should show all of the macros you've created.
4. Click the macro you want to add to the Quick Access toolbar.
5. Click the "Add" button to move it to the right text box, which indicates the macro has been added.
6. Click "OK" to save the changes.
Also note that you can turn the Quick Access toolbar off or on. If it's not checked in the customization window, then check it to add the toolbar to your main Excel interface. If you add a macro and the toolbar is hidden, you won't see it after you make these changes.
Create a Custom Button for Your Macro
You can also create a custom button to run your macro. A custom button is useful when you want to distribute your workbook to other users. This button makes it easier for users to run the macro if they don't have the Quick Access toolbar enabled. You don't need to explain to them how to add the toolbar with a button created, so this option makes it much easier for distributed Excel workbooks.
Follow these steps to create a custom form control button to your workbook to run a macro.
1. Click the Developer tab or ribbon, depending on your Excel version. If you don't have the Developer tab enabled, refer to chapter 1 to enable the Developer tab.
2. Click the "Insert" button in the Developer tab. This button is found in the "Controls" group. A dropdown menu displays with several form controls you can add to your workbook.
3. Click the "Button" item in the dropdown menu.
4. Click the location on the spreadsheet where you want the button to display. Note that the location you choose will be the upper left corner of the button. If you misplace the location, you can always choose to move the button's location later. When you place the button, the "Assign Macro" window automatically opens.
5. Click the macro that you want to assign to the button. If you have no macros created yet, you won't be able to perform this action. You can always assign a macro to a button in the future after you've created the macro you want to use.
6. (Optional) Right-click the button and select "Format Control." In this dialog window, you can assign different properties for your button. Again, this should only be done if you've already created your macro and know the properties that you want to use.
Running a Macro Using Clipart or SmartArt
You can use pre-made clipart to create a "button" for your macro. You can also use a new type of clipart called SmartArt. If you've ever created PowerPoint presentations, then you'll recognize SmartArt. SmartArt looks like organization shapes that you normally see in presentations. Microsoft has recently added these organizational shapes to Excel, and you can use them to start a macro.
Follow these steps to set up a clipart or SmartArt button for your macro.
1. Click the "Insert" dropdown or open the Insert ribbon, depending on the Excel version you're using.
2. Select a clipart or SmartArt image for your worksheet. You can arrange the image in any cell or location you want after it's been added to the worksheet. You can also size the image by hovering the mouse over it and using the sizing tool to enlarge or reduce the size.
3. Right-click the newly created clipart or SmartArt and select "Assign Macro." A dialog window opens with a list of macros you've created in the workbook.
4. Select the macro you want to assign to the image and click "OK."
You can now test your image by clicking it. If it's a larger image, the place where you right-click will be the hotspot to run the macro. Make sure you click this hotspot on larger images to verify that the macro runs when clicked.
Running a Macro from a Shape
You don't need to use clipart or SmartArt for images. You can also create your own shapes. You can use circles, squares, or polygons that you draw on the spreadsheet. Remember that you want to create a shape that indicates it's clickable. Most macro developers add some kind of text that instructs the user to click the shape to run the macro.
Before you assign a macro to a shape, you need to draw the shape on the spreadsheet. Shapes are found in the "Insert" tab or ribbon. You can later edit the shape sizes and properties after you assign it a macro. The steps to assign a macro to a shape are similar to assigning one to clipart or SmartArt.
Follow these steps to assign a macro to a shape.
1. Right-click the shape and select "Assign Macro." A dialog window opens and displays all of the macros you've created.
2. Click the macro that you want to use and click "OK."
You can format your shape before or after you assign it a macro. Just remember that if you delete the shape, image or clipart, you must reassign the macro to any new image that you create.
Running a Macro When the Workbook is Opened
Excel gives you the ability to execute a macro when you or your users open the workbook. This type of macro should be used sparingly, because you don't want to edit data that shouldn't be edited prior to the user seeing it.
Another issue with a macro running when the workbook opens is that it's a security issue for the user. If macros are disabled except when enabled after notification (which they should be), the user must take an extra step to allow the macro to run every time the workbook is opened. This could be inconvenient for your users.
Even though it's a security issue and sometimes inconvenient, there are still times when you need the macro to run. For instance, you might want to auto-fill cells before the user begins filling out any data.
If you are automatically running a macro on your own spreadsheet, you must first enable all macros in the Trust Center. You can also access macro security from the Developer tab in the "Code" group. We covered how to enable all macros in lesson 2.
Follow these steps to start a macro when the user opens a workbook. These steps assume that you've already created the macro. You must create the macro first when you set an auto-run macro.
1. Click "Visual Basic" in the "Code" group in the Developer tab or ribbon. This opens the main project explorer window.
2. Right-click "This Workbook" in the project explorer window and select "View Code." The main code window opens where you'll see several Visual Basic functions.
3. Click "Workbook" in the dropdown menu labeled as "Object." This will automatically create an empty function named "Workbook_Open()".
4. Type the following code in the new function.
MsgBox "Test"
Worksheets("Sheet1").Range("A1").Value = "Test"
End Sub
5. Click "Save" to save the code. You can close the project explorer window and reopen the Excel file if you want to test it.
In the function listed above, the string value "Test" is assigned to the first cell in Sheet1. This is a good test to ensure that your macros are running when you open a workbook.
Each time a workbook opens, the "Workbook_Open" function runs. This is your first introduction to actual Visual Basic code for macros. We'll discuss Visual Basic code in future lessons, but this is good practice to get used to the Visual Basic IDE and project explorer window.
This lesson showed you some simple ways to create a button for your macro. We also showed you how to automatically run a macro when you open the workbook. When you create complex workbooks, these buttons make it much more convenient for developers and users to run macros rather than work with the Developer tab. You don't want your users opening the Developer tab for simple workbooks, so this makes it much more convenient without users editing macro code and Excel settings.
The Visual Basic Editor (VBE)
How to Access the VBE
When you open the VBE, it opens a new programming window similar to the old Visual Basic IDE that programmers used to create desktop applications.
Method 2
The VBE is also available from the Developer tab. Click the Developer tab in Excel and then click the Visual Basic button in the Code section. This opens the main project explorer window.
Method 3
If you've already typed code, you probably want to edit and view it as you develop your macros. You can click on a button or other control in your spreadsheet and then open the Developer tab. With the control clicked, click the "View Code" button in the Developer tab.
Getting Familiar with the VBE Interface
After you use one of the three methods in the previous section, the VBE editor opens and you're presented with an interface that has its own toolbar and layout from the Excel IDE.
The first section to take note of is the left panel. This panel contains all of your objects. You'll notice that the top of the tree object is the name of your file, which is "Book1" if you haven't saved it.
The next section in the tree is a list of your objects. Objects are containers that define several properties and methods. A sheet is an object, because it contains several rows and columns that can contain data. You'll notice that the VBE shows all sheets as a list in the "Microsoft Excel Objects" section. For each sheet you create, it shows in this list of objects.
Also notice that an object named "ThisWorkbook" is shown. This is the entire Excel file including all worksheets. With an Excel file, there are three layers: the workbook, the worksheet (sheet) and columns and rows.
The workbook is the entire file, so it's the top-level object in your code. A sheet or worksheet is contained within a workbook, so it's the next level in your list of objects. You can have several sheets within one workbook.
A worksheet contains rows and columns, so they are the third level in your list of objects. Rows and columns can be further broken down to a fourth level. Cells are contained within rows and columns, and this is the level that contains the actual data.
All of these objects display in the left panel. As you code your macros with modules, functions, properties and other objects, they will show in your VBE for quick access.
Double-click the first worksheet named Sheet1. This will open a code file in the center IDE. This window is where you will code your macros and add-ins. Notice that there are two dropdown menus in the code window. The left one says "General." You won't need to work with this one very often. The right one says "Declaration." This is the one you will use often.
Click the "Declaration" dropdown. Right now, you have no functions or subroutines coded in your files, so you see nothing in the dropdown. However, when you begin coding your macros and create several functions and subroutines, this dropdown will be useful. If you have 1000 lines of code, it's tedious to scroll down the page and find the function you need to edit. With this dropdown, you just click the function or subroutine you want to edit and the editor scrolls directly to its position. This makes editing your code much more convenient.
You can test this dropdown by creating a subroutine. Copy and paste the following code into the sheet editor.
Sub Test()
MsgBox "Test"
Save the sheet and now click the dropdown menu. Notice now that "Test" is displayed in the dropdown. When you click the "Test" option, the VBE will automatically scroll to this subroutine's location.
Notice at the top of the window that the VBE has several options in the toolbar. These options are similar to Excel, but you also have a Run, Pause, and Stop button. These buttons are used to execute your code, pause the code's execution, and stop the code from running. They are used often when you are debugging your code. We'll get into debugging in the next section.
Debugging VBA Code
Debugging VBA code is similar to other Microsoft languages. We added a "Test" subroutine, and we can use it to show how to debug and run your new macro. The "MsgBox" function is an internal VBA object that displays a popup on the screen. It's a common method used to test VBA code and verify that subroutines and procedures are executing.
First, let's test the new function we created. With the sheet code window open, click the Run button in the main toolbar. You should see a popup that displays "Test." After you click "OK," the VBA code stops executing.
In many cases, just running code does not help you identify issues or properly test. It also doesn't help you when you need to debug the application. You need to debug the application and pinpoint what section of the code is causing errors. When you have a 1000 lines of code and you run it, you don't know where the code is causing a problem unless you can step through it and review variable values. You do this with breakpoints.
Open the Sheet module we created in the previous section. Right-click the left panel in front of the MsgBox code line. A context window opens. Click "Toggle" and then click "Breakpoint." When you set a breakpoint, the entire line of code is highlighted in red.
Now, click the "Run" button again in the main toolbar. The same code runs, but now the code stops executing at the breakpoint. Note that it does not execute the line of code. It stops just before execution. With a breakpoint, you can hover over a variable and see its value. We don't have a variable in our program yet, but we will show you in future chapters.
Click the "Run" button again. The MsgBox line of code executes and you see a message box with "Test" as the display. Click "OK" and the program ends. When you code larger applications, you might need to set several breakpoints to identify a bug. A breakpoint helps you step through the code line-by-line, so you can read the variable changes.
You'll sometimes need to step through code with only one breakpoint set. Click the "Run" button again. Again, the code stops executing at the MsgBox line of code. When you click the Run button, the code executes until it's finished. Many times, you want to run 2 or 3 lines of code after the breakpoint and see the way variables change values. You could set another breakpoint, but you must remove breakpoints when you are finished with them. With too many breakpoints, you must take the time to go back and remove them.
Visual Basic lets you step through your code line by line with only one breakpoint. Press the F8 key this time. Notice that instead of running through the entire code and closing, the cursor now points to the next line of code. In this example, the next line is the end of the subroutine. If you had another message box or other type of code, execution would stop at the next line of code. It does not execute until you press the F8 key again or you click the Run button to finish the program.
You can also set the next line of code manually. Suppose you want to skip the message box at the breakpoint. You can manually set the cursor to move to a different line of code. Run the code once again until the breakpoint stops the code. Right-click the "End Sub" line of code and choose "Set Next Statement." This action will skip the MsgBox line of code and move the next line of execution to the end of the subroutine. This means that no message box appears when you set the statement.
Now that you understand the Visual Basic Editor IDE, you can get started with coding. We'll cover actual code in most of the next chapters, but it's important that you first understand how to work with the IDE. Write a few small lines of code, set a breakpoint, and step through the code to understand the way it functions in VBA. Most of your development for VBA is in this IDE, so get familiar with these small functions before coding.
