MAKING EXCEL WORK FOR YOU
The Excel Macro Recorder - a good way to create your first macros.
A macro is a series of commands executed from just one command from the user. You can use Excel's macros to automate tasks that you find yourself doing repeatedly. There are several ways to create macros, also called "subs," using Visual Basic for Applications (VBA), the programming language for Excel. You can copy some code from a friend or a Web site, you can write the VBA code yourself. or you can record a simple macro by using the Macro Recorder from Excel's Tools Menu → Macros → Record New Macro. Since the introduction of Excel 2007 you can open the macro Recorder from the Developer Tab
Record a new macro
From the Tools Menu, choose Macro; Record New Macro and you will get the macro Recorder dialog box. Note in later versions there's a small icon in the bottom left of the screen on the StatusBar which starts the macro recorder or you need to add the Developer tab to the ribbon - File → Excel options →Peronalize, then you will see the Record macro button.
The first step is to give your macro a descriptive name. You cannot use spaces, dashes (hyphens) or other special characters in your macro name, if you try then you will get an error message - "That name is invalid". You can use underscores (_ ) in place of spaces/dashes. (i.e. Page_Setup or Delete_Spaces will work).
Next decide where to store the macro. You can save it in the current workbook (ThisWorkbook). This choice will attach this macro to the current workbook, and it will only be accessible when this workbook is open. If you want to be able to use the macro from any workbook on that computer, choose the "Personal Macro Workbook". Next decide if you want a shortcut key assigned to this Macro. If you do not want a shortcut key, leave the option blank, and click OK to begin recording your macro. If desired, you can enter a description to better explain what this macro will do.
Creating a Macro in your Personal.xls file
If this is a macro that you want to have available at all times, it is a good idea to store it in a special file called Personal.xls. This is a hidden file that opens automatically every time you open Excel. (It's hidden in the sense that you don't really see it, but it's there.) You might or might not have a Personal.xls file on your PC, but it's easy to create one, just in case. Open a new workbook, select from the Tools Menu, Macro > Record New Macro, and fill it in as shown below. The important part is that you should choose Personal Macro Workbook as the place to store your macro. This will place your new macro in your Personal.xls file, if it already exists, or it will create this file if it doesn't yet exist.
Recording a Macro:
After you've named your macro, Excel takes you back into your spreadsheet. You will now have a new Toolbar floating on your screen.
If you want you can move the Toolbar, as with any other Toolbar, without affecting the macro, by clicking and dragging the blue title bar of the Toolbar. This new Toolbar only has two buttons, Stop and Use Relative Reference. If you click the X to close this menu, Excel will stop recording the macro. You can also stop recording by clicking Stop.
The Use Relative Reference button determines whether the Macro recorder uses relative or absolute references to the cells on a worksheet. The button is set to Absolute when it's first shown. This means that Excel will record the exact address. Absolute references do not automatically adjust when you insert rows and columns. When the button is pushed, Excel will keep track of a Relative Reference. This means Excel will record the macro, relative to where you start. It is possible to switch between modes while recording your macro.
Once you have completed all the steps that you wanted to automate, stop the macro. You should test the macro to make sure that it works, if you assigned a keyboard shortcut, use that, otherwise go through the menu, Tools>Macros>Macros or Alt-F8, choose the macro and Run.
If you Edit the macro you will actually see the Visual Basic code, in fact it is often advisable to do so.This is also the window where you can delete a macro.
By saving your macro in the PERSONAL.XLS as the macro shown above was, you will not be able to delete the macro yet, you will have to unhide the personal workbook.
Once you are in this Worksheet, you can delete the macros saved here, just be sure to hide this worksheet again when you are done. (Window->Hide)
To run a macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key box, either when you create the macro, or later from the Options button on the Macro Window. You can use CTRL+ letter or CTRL+SHIFT+ letter, where letter is any letter key on the keyboard. The shortcut key will override any default Microsoft Excel shortcut keys while the workbook that contains the macro is open.
The Visual Basic Editor
To view a recorded macro, you can access the Visual Basic Editor by pressing the Alt-F11 key combination. On the left you will see the Project window, which lists the contents of Personal.xls. (Click on the plus signs to expand the contents, if necessary.) Yours will differ from mine, depending on what you already have in your Personal.xls file, but you should have a Modules folder with a Modules Folder (probably Module1) item. By double-clicking on this item, you see the code you just recorded in the code window to the right. You can actually edit the macro directly here and as you become more proficient with VBA write complete macros. Another way is to simple copy and paste code samples from various sources.
Click OK and you will create a blank Toolbar like this:
To add buttons to the toolbar switch to the Commands tab, on the Customize Window If the button you want already exists you can click and drag the button to the toolbar. As you drag the button to the toolbar, you will see an I marker on the toolbar to indicate where it's placing the button.
If you would like to add the buttons for your macros, scroll to the bottom of the categories list. Choose Macros and drag over the Custom Button that looks like a happy face. To assign the button to your macro, make sure the button is highlighted (it has a box around it), from the Customize Window choose Modify Selection. Choose Assign Macro..., Select the macro you want for this button, and click OK.
To change the way the button looks, so you don't have several happy face buttons, click on the Modify Selection button again. You can Change the Button Image, or give the button an appropriate Name and tell Excel to use Text Only or Image and Text. You can also copy and paste an existing button image and even Edit the Button Image to create your own graphical button.
However, the best way to add your a menu or toolbar it is best to do this within your code. See the attached examples, but remember that if you use the newer versions of Excel since 2007 then these will not show in the Ribbon, but you can find them in the Addins Tab, It is possible to add your own Tabs to the Ribbon, but this is not so simple but if you want to give it a try then Ron de Bruin has some excellent articles and example files.
Download the Excel Menu Example Workbook
Download the Excel ToolBar Example Workbook
© Roy Cox 2005 to 2013
Adding Toolbars and Buttons
You can run your macros by adding a button to an existing Toolbar, or even create your own Toolbar. Before going any further, it's important to realize that any changes you make to menus or toolbars are stored in a secret file on your hard drive. This means that they are remembered when you close Excel. So if you create a new menu item, say, and then close Excel and open it the next day, that new menu item will still be there. The name of the secret file is Excelxx.xlb. (For example, the file is Excel11.xlb on my PC that's running Excel 2003. It's stored somewhere in the Documents and Settings folder. You can find it most easily by doing a search in Windows Explorer.) If you modify toolbars and/or menus on one PC and want these same changes to occur on another PC, you can copy this .xlb file to the other PC. Of course, if the modified menu items or toolbar buttons run macros from your Personal.xls file, you should copy this file to your other PC as well. Again, you can do a search in Windows Explorer to find where Microsoft automatically stores your Personal.xls file. It's also a good idea to locate and create a copy of the .xlb file, that way you can restore you settings easily if anything goes wrong. To add a toolbar, choose Tools > Customize, click on the Toolbar tab. Click New; Change the Toolbar name to your choice e.g. MyToolbar.
|The Personal WorkBook|
|Excel Sheets in VBA|
|Excel UserInterfaceOnly <New>|
|Excel Macro Recorder|
|Excel Camera Tool|
|Excel Summarise Data|
|Excel CHOOSE Function|