Now let us look at an example that allows you to run your VBA procedure directly from the Quick Access Toolbar.
Note that the sequence of steps to link a VBA procedure to a chosen button is similar to the one we followed when assigning a macro to a button.
Suppose we want to add four buttons to the Quick Access Toolbar, which will perform the following actions respectively:
- determine whether a given number of points with the center at the origin falls inside a circle,
- calculate employees’ commissions according to a certain rule,
- replace the values of cells in a selected range according to a specified rule,
- format numbers according to a specified rule.
Let us now consider in detail the conditions and the VBA procedures that implement the required actions.
The VBA procedure that determines whether a given number of points falls inside a circle centered at the origin is presented in the standard module Module1.
Next, here is an example of calculating employee commissions according to the following rule:
- if products are sold for 1,000,000 currency units or more, the commission is 4% of the sales value;
- if products are sold for less than 1,000,000 currency units, the commission is 2% of the sales value;
- if the employee has worked in the company for 5 years or more, an additional bonus of 1.5% of the sales value is paid.
The corresponding VBA procedure is written in the standard module Module2.
The standard module Module3 contains the procedure for replacing cell values in a selected range according to the following rule:
- positive numbers are replaced with « + »,
- negative numbers with « – »,
- zeros with « zero ».
In the standard module Module4, the procedure demonstrates formatting numbers in a selected worksheet range according to this rule:
- numbers from 0 to 1000 are formatted in red,
- numbers from 1000 to 10,000 are formatted in green,
- numbers above 10,000 are formatted in black,
- positive numbers use the data format #000,000,
- zeros are displayed in purple with the text « Zero!!! »,
- negative numbers are formatted in blue with the format #000,000.
So, to add buttons to the Quick Access Toolbar that perform the actions described above, follow these steps:
- Open the VBA editor by going to the Developer tab on the Ribbon, selecting the Code group, and clicking Visual Basic.
- In the VBA editor window, successively add 4 modules using the commands Insert | Module, and paste into each the corresponding VBA procedure code.
- Switch back to the Excel workbook window.
- Right-click the Quick Access Toolbar and select Customize Quick Access Toolbar.
- In the Excel Options window, in the Quick Access Toolbar category, choose Macros in the Choose commands from list.
- One by one, select your created procedures in the left column and transfer them to the right column using the Add button. Use the Modify button to change the icon for each button.
- After adding all your created procedures to the right column of the Excel Options – Quick Access Toolbar window, click OK.
- Make sure that the required buttons have appeared on the Quick Access Toolbar and that pressing them executes the actions you programmed.