Macros
Definition
Macro’s work very nice when you want to make your spreadsheets more interactive. It makes spreadsheets more attractive and more user friendly. The concept is to insert something (a button or something else) and put a macro on it, to do something when clicked or ticked.
Macro Toolbar and security settings
Before working with macro’s you must insert the developer’s toolbar. Click on the excel button and then on Excel options.
Tick the “Show developer tab in ribbon” box.
In excel 2010, you have to go the customize option and tick it from there.
Examples
Example 1
For this example we are going to insert a “menu” with one button. When this button is clicked a specific sheet will be shown.
Step 1: Open a new excel file
Step 2: Rename the one sheet “menu”
Step 3: Rename the other sheet “test”
Step 4: We want our macro to unhide the test sheet when clicking it. So for setting up the function you will need to hide the sheet. Right click on the test sheet and select hide. The selected sheet will be hidden. Go to the menu sheet.
Step 5: Go to the developer tab as inserted above. Click on insert and then on the button in he left corner on top
Step 6: Your mouse cursor changes to a star
Step 7: Drag the cursor (with the left button) over the screen to form a button, and then leave the button. The following screens will pop-up.
Step 8: Excel will automatically give a name to the macro. Click on “record” and on the next screen click “ok”
Step 9: After clicking “ok” you will see at the bottom of screen the word “recording”. This means the macro is busy recording. Everything you do from this point will be repeated when clicking the button in the future. What do we want to do? Unhide the test sheet. Remember, Excel is busy recording, so what you do will be repeated. Go to HOME > FORMAT > HIDE AND UNHIDE > UNHIDE SHEET. Select the test sheet to unhide. For better working go to the menu sheet again and click anywhere, and then go to the “test” sheet again and click on there.
Step 10: We must stop the macro to record. Click on the blue button in the bottom left corner and click it to stop.
Step 11: To have the best effect we must put the same button in the test sheet to hide it. So when standing in the test sheet, insert a button again and record a new macro. But this time hide the “test” sheet and click anywhere on the menu sheet. Stop the recording of the macro again.
Now you will have an interactive file with a menu, to hide and unhide sheets. You can make the menu very nice now, with more than one button
Example 2
The other way of using macros is by making amounts interactive. For example, instead of making the user insert 20%, you can put a scrolling bar next to the cell with arrows, to change the percentage.
Step 1: Open a new file
Step 2: Drag a “Spinner” down to excel. The same as explained in example 14.1
Step 3: Right Click on the “spinner” and go to format control. The following screen will pop up
Step 4: The button must be linked to a cell. So put in any cell in the “cell link”.
Step 5: Click ok
Step 6: If we link the cell to A1, then every time we click on the spinner the number in A1 will go down or up.
Step 7: For a percentage to show, you must put the percentage in cell A2. Put a formula in there like follows for a percentage. Take A1 divide by 100. Now, every time you click the spinner, A1 will change by a whole numbers. Then A2 will change by decimals of 0.01, Thus 0.1%
This function makes your spreadsheet look very professional.
Articles:
- Using Macros to Automate Functions in Microsoft Excel
- The Use of Microsoft Excel Macros Made Easy
- Microsoft Excel Short Introduction to Macros
- Macros Made Easy How to Maximize Excel's Capabilities
- Learning Macros in Microsoft Excel Is it Important
- Learn Microsoft Excel Online Introduction to Macros
<-----Back to index
