|
Creating a macro shortcut button is faster way to execute macros from within Excel. Excel 2003 and earlier allow you to place shortcut buttons
directly on your built-in toolbars, or to create a custom toolbar. Excel
2007 has a Quick Access Toolbar across the top of the program window where you can
create custom macro buttons.
In Excel 2007, the shortcut bar is either across the top border of the
program window or under the Ribbon, depending on which option you have selected
in Excel Options. Follow these steps to add a shortcut button which will
execute your VBA Monte Carlo function.
First you will need to create a subroutine which calls the function, since the
set-up dialogue
recognizes only subroutines as macros. In the example below, CoinToss() is
the Monte Carlo function. FritzMacroButton() is the subroutine which calls
the function.
Now that the subroutine has been created, we'll assign it as a macro to a button
on the shortcut bar. Click the button at the right end of the shortcut bar
to reveal the drop-down menu. Select "More Commands...".
This opens the Customize section of the Excel Options dialogue. Select
Macros in the "Choose commans from:" list, and your current workbook in the
"Customize Quick Access Toolbar:" list (unless you want to be able to run the
macro from within any workbook). Then, find the desired macro in the list
box on the left, and add it to the Quick Access Toolbar by clicking the "Add"
button. Click "OK" and the shortcut button should appear on your Quick
Access toolbar.
Unlike earlier versions of Excel which allow customization of menus, Excel 2007
does not allow customization of the Ribbon interface. As this page is
being written shortly before the release of Excel 2010, it is reported that the
new version of Excel will allow customization of the Ribbon menus. It may
be possible therefore in Excel 2010 and later to add custom macro menu items to the
Ribbon.
In Excel 2003 and earlier, add a macro button by first selecting "Tools -
Customize" from the drop-down main menu bar. This opens the "Customize"
dialogue box shown below. Select the Commands tab, then scroll down in the
Categories list until you see Macros. Click on Macros. You can then
drag the Custom Button item from the Commands list onto any open toolbar.
With the Customize dialogue box still open, right click on the custom button
you've placed on the toolbar. This opens a drop-down menu where you can
assign a macro and perform other functions to customize the button.
Similarly in Excel 2003 and earlier, you can drag the Custom Menu Item from the
Commands list in the Customize dialogue box and drop it anywhere within your
menu structure. Right-clicking on the menu item while the Customize
dialogue box is still open gives you options for assigning a macro to the item
and customizing it in other ways.
| 
