 |
|
|
|
|
In our last two lessons in our Excel Level 3 course we will be taking a look at recording, editing and running macros using Excels Macro Recorder. A macro is simply an action or a set of actions you can use to automate a particular task or tasks. You use the Macro recorder a bit like using a video camera. You switch it on, record what you want to record, then switch it off. In effect a macro is like a mini-program that performs the actions that you have recorded. Macros are a fantastic time-saving feature and are ideal for automating repetitive tasks. They are also useful if you are setting up an application for others to use as you can use macros to create buttons and dialog boxes to guide a user through your application as well as automating the processes involved.
As a macro is just a recorded set of key strokes, it is important to note that everything is recorded, even if you make an error which you then correct and then move on to finish recording your macro. These errors can be removed once you understand the basics of editing macros which we will discuss later. Macros are recorded in the Excel Visual Basic for Applications programming language, which is an application within Excel itself that allows you to use Visual Basic code to run the many features of the Excel package, thereby allowing you to customize your Excel applications to suit a particular need.
Recording a Simple Macro
When you create a macro, you must firstly give it a unique name. This is so that the macro can be identified and run at a later time. The first character of the macro name must be a letter. Other characters can be letters, numbers, or underscore characters. Spaces are not allowed in a macro name; an underscore character works well as a word separator. Note: Do not use a macro name that is also a cell reference or you can get an error message that the macro name is not valid.
You can also, if you want to, specify a shortcut key or a button that can be used to activate the macro once it is recorded. The shortcut key must be a letter of the alphabet and can be activated by holding down the Control key (if lower case) and pressing the letter that you have chosen or Control + Shift (if upper case) and pressing the letter that you have chosen.
When you record your macro you will need to nominate where you wish it to be stored. There are three locations to choose from:
The current workbook - this is the default option
In a new workbook
In Personal Macro Workbook - This means that you can store macros here that you want to use in more than one workbook. The Personal Macro Workbook is hidden from normal view and is automatically opened when Excel is started.
Lets create a macro to calculate a 10 cent price increase on goods available for sale. The first thing that we need to do is to get a blank Excel workbook in front of us, then follow these steps:
Select Tools>Macro>Record New Macro
The Record Macro dialog box will pop up in front of you, and you will notice that Macro1 is highlighted as a default name in the Macro Name: box.
Type in the name GoodsforSale
Moving through the box you will notice the next area is where you would nominate a shortcut key. We are not going to nominate a shortcut key in this exercise, so we will leave this blank.
Under Store macro in: you will see that the default is This Workbook. If you click on the drop down arrow you will see two other options in this list; New workbook and Personal Macro Workbook. We actually want the default This Workbook, so we need not make any changes here.
Now click or TAB to the Description: box and in here we are going to type Goods for sale at corner shop
Now click OK and we will begin recording our macro.
Note two things at this point; you will have the Stop Recording toolbar appear on your screen and your status bar is indicating that recording is in progress.
Click in cell A3.
Now type the following in cells A3:A10; apples, oranges, pears, bananas, kiwi fruit, mango, strawberries, water melon.
Now click the Stop Recording button (the blue square) on the Stop Recording toolbar. (Notice the toolbar disappear). You will notice that there is another tool on the Stop Recording toolbar this is the Relative Reference tool and we will talk about it later.
Save your file.
Now that we have created our macro, we need to be able to run it. There are several ways to run a macro, but the most common way is to run the macro via the Tools>Macros command, which is the way that we are going to use now.
Highlight the range A3:A10
Click Delete to remove all data from our worksheet
Now select Tools>Macro>Macros
You will see the Macro dialog box pop up in front of you and GoodsforSale should be highlighted under Macro Name:
Click Run
Absolute/Relative Cell Addressing
We have available to us two variations when we are recording macros. These are absolute recording and relative recording. Absolute recording is the default state that you have when recording macros.
The macro that we have just created was an absolute recorded macro. When a macro is created with absolute recording this means that any cell or range references are actually hard-coded into Excel, so no matter where your cell pointer is within your workbook, once you activate your macro your cell pointer will always return to the specific cells used when your macro was recorded and run your macro in this space. Lets demonstrate that now.
Highlight the range A3:A10
Click Delete to remove all data from our worksheet
Hold down Control + downward arrow, then Control + right pointing arrow This will take you to cell IV65536.
Now select Tools>Macro>Macros
You will see the Macro dialog box pop up in front of you and GoodsforSale should be highlighted under Macro Name:
Click Run
So all this goes to show you is that wherever you are in your worksheet, when you run a macro that has been recorded as an absolute macro, the macro will be run in exactly the same place every time.
The opposite of an absolute recorded macro is a relatively recorded macro. If you moved your cell pointer one cell to the right while you were recording your macro, when you ran your macro at a later stage, wherever your cell pointer is located at the time, your cell pointer would move one cell to the right. You would use a Relatively recorded macro if you wanted to perform the same operation, but on different areas within your worksheet.
Lets use a relatively recorded macro to add a price increase of 10 cents to our goods for sale.
Widen column A
Place the heading Current Price in cell B2 and Increased Price in C2 and widen these columns accordingly.
Add the following values in cells B3:B10; 0.50, 0.75, 0.40, 1.20, 0.70, 0.35, 0.45, 1.00
Make sure columns B and C are formatted for two decimal places.
Click in Cell C3, where we will begin our macro.
Select Tools>Macro>New Macro
Name the macro PriceIncrease
This time we will assign our macro to a shortcut key, so click in the Shortcut key: area and type in p
We wish to save our macro in This Workbook, so no need to make any changes under Store macro in:
Under Description: type in Price increase of 10 cents added
Click on OK to start recording your macro
On your Stop Recording Toolbar, you will see the right handed tool is called your Relative Reference tool (you will see this if you wave your mouse over it). Click on this tool so that it appears pressed in.
Type =
Now click in cell B3 and type +0.1
Now click on Enter to complete your formula
Now click back on cell C3 and select Copy
Now select Edit>Paste Special
When you see your Paste Special dialog box pop up in front of you, make sure that you check the option Values under Paste
Click OK
now hit your Esc key (top left hand side of your keyboard). This will clear your marquee (marching ants) around cell C3.
Click on the Stop Recording tool on your Stop Recording Toolbar.
Now we have recorded our relatively recorded macro, we need to run it to check that it works. One thing that you MUST bear in mind with relatively recorded macros is that care must be taken BEFORE you run your macro that your cell pointer is not in a position where any existing data could be altered, deleted or damaged in any way.
Click in cell C4
Select Tools>Macro>Macros
Now make sure that PriceIncrease is highlighted in your Macro name: box
Click Run
Now click in cell C5
This time lets run our macro using our shortcut key (Control + p)
As you will see in each case, our macro has gone one cell to the left and added 10 cents to the value in that cell, we have then removed our formula from our cell by going to Edit>Paste Special>Values. Just to prove what a relative macro does, follow these steps:
Click in cell D3
Select Control + p to run your macro
You will notice that in cell D3 you have the result of .70. This is because when you have run your macro this time, your macro has still gone one cell to the left (this time to cell C3) and added 10 cents to that value.
Viewing and Editing with the VBE
When you record a macro, Excel re-writes this macro in a programming language called Visual Basic for Applications which is contained within Excel (VBA is a high-level, visual-programming version of Basic. Visual Basic was developed by Microsoft for building Windows-based applications.) It then creates a special container in which the macro instructions and the workbook or workbooks that it relates to are stored. This container is called a VBA Project The macro instructions that are stored in the VBA Project are placed into what is known as a Module. In appearance, a module looks a bit like a word processing document because there are lines of text (even though you won't understand most of them!!) that represent the various instructions that you have given Excel to record your macro. These word processors are also referred to as text editors. The text editor that we are using in VBA is known as the Visual Basic Editor, or VBA. It is in the VBE that we can view and edit our macro instructions.
There are a number of ways that you can open the VBE (Visual Basic Editor) to see the instructions that were recorded in your macro, one of which is to follow the following steps:
Select Tools>Macro>Macros
When your Macro dialog box appears, click on PriceIncrease
Now select Edit
What you are looking at now is the Visual Basic Editor. The VBE is displaying the programming instructions for the PriceIncrease macro in a Module window.
Using your scroll bars, scroll up and down the Module window and have a look at the programming instructions, you may feel that some of it looks totally foreign, but some of it you may get the gist of.
Of course if you wanted to make complicated changes to your macro you would need to have a reasonable grasp of the VBA programming language, but it is relatively simple to make small changes. Lets say that we want to change our macro to add an extra 20 cents to each item in our list of goods, not 10 cents as originally planned.
Locate the line of code ActiveCell.FormulaR1C1 = "=RC[-1]+0.1"
Change it to ActiveCell.FormulaR1C1 = "=RC[-1]+0.2"
Select File>Close and Return to Microsoft Excel
Now Save your worksheet.
Now lets clear the range C10:D10
Click in cell C3
Run your macro and notice that now you are adding an extra 20 cents to the Current Price.
OK, now have a go at the following two exercises on your own, just to be sure that you really get the hang of what a macro does.
Macro 1 - Absolute Recording
Get a new blank worksheet in front of you.
In cell A2 type the heading Division Names
Create an absolute recorded macro to place the Divisions; Marketing, Finance, Administration, Human Resources, Information Technology, Engineering & Maintenance in cells A3:A8. Bold and italic the font, and widen column A for best-fit.
Now test your macro
Macro 2 - Relative Recording
In cell B2 type the heading Gross Profit and type the figures 150000, 155000, 136000, 147000, 128000, 126000, in cells B3:B8.
Type the heading Bonus Paid in C2
Now create a relative recorded macro to calculate the bonus paid to each division. The bonus paid is 15% of the Gross Profit. We also want to comma style our values, and add dollar symbols and two decimal places. You may also need to widen your column. Assign this macro a shortcut key.
Now remove the formula from the cells leaving only the value, by ensuring that Copy>Paste Special>Values is included in your macro.
Test your macro.
Now in the Visual Basic Editor, have a go at making the following changes to your macros:
Macro 1 - Remove the bold and italic from the font.
Macro 2 - Alter the bonus paid to read 17.5%
Summary
In this lesson we have had a look at recording a basic macro, running a macro and editing a macro from within the Visual Basic Editor. We have also had a look at the different modes that you can use with a macro - absolute recording and relative recording. In the next and final lesson in our Level 3 course, we will delve further into macros and create some more complicated macros to make up a mini application. We will also take a look at creating objects that start macros and assigning macros to objects