1.            Macros

Creating Macros Using Macro Code

Learning Outcomes

On completion of this chapter you will know:

·         How to record and play back macros

·         How to modify the code of a macro

·         How to combine a number of macros into one macro.

Download pdf version

Introduction

Macros are a way of automating repetitive tasks within a spreadsheet or a word processing application.  The idea is that instead of repeating the same set of operations time after time,  we set the macro recorder on, perform the tasks once with the macro recorder recording them, and finally save the macro.  From now on all we need to do is to play back the macro and our tasks are performed automatically for us.

Macros are, in fact, Visual Basic code that the system writes automatically while it is recording our actions in the workbook.  As Visual Basic programmers we can modify this code in any way we want as long as we stay within the correct syntax of the Visual Basic language.  In this course the most frequent use we will be making of it is to copy sections of it into more general purpose programmes.

Creating an Excel Macro

Go to top

To create a macro in Excel we simply turn on the macro recorder.  To do this we go to the View tab and in the Macros group click on Macros.  From the drop down list we select Record Macro.  We then perform the tasks we want recorded. Once finished we stop the recorder.  We do this in the same way that we started the recorder in the first place except that instead of clicking on Record Macro we click on Stop Recording.

 

Figure 13‑1

In Figure 13‑2 below we have an example that we looked at in another course for calculating the interest and repayments on a bank loan. If entering this manually the steps would be:

1.      Select cell B1 and enter the text “Jan” into it

2.      Autofill this across to G1

3.      Enter the text items “Opening Balance”, “Interest”, “Repayment” and “Closing Balance” into the range A2:A5

4.      Enter “Interest Rate” into cell A8

5.      Adjust the column width of A to accommodate the text items it contains.

6.      Enter the values 0.12, 10000,  and 700 into the cells B8, B2 and B4 respectively

7.      Enter the formula =B2*$B$8/12 into cell B3 in order to calculate the monthly interest

8.      Enter the formula =B2+B3-B4 into cell B5 in order to calculate the closing balance for the first month

9.      Enter the formula =B5 into cell C2 in order to calculate the opening balance for the second month

10.  Autofill this formula across to column G

11.  Select cell B3 and copy its formula across to column G using Autofill

12.  Select cell C4 and enter the formula =B4 into it.

13.  Copy this formula across to G4

14.  Select cell B5 and copy the formula in it across to G5

15.  Select the range B2:G5 and format it for PNG currency

16.  Select cell B8 and format it for percentage.

Now let us examine the code in Listing 13‑1 and see how it performs the above steps for us.

Figure 13‑2

 

Listing 13‑1

1

Sub BankSetup()

2

    Range("B1").Select

3

    ActiveCell.FormulaR1C1 = "Jan"

4

    Selection.AutoFill Destination:=Range("B1:G1"), Type:=xlFillDefault

5

    Range("A2").Select

6

    ActiveCell.FormulaR1C1 = "Opening Balance"

7

    Range("A3").Select

8

    ActiveCell.FormulaR1C1 = "Interest"

9

    Range("A4").Select

10

    ActiveCell.FormulaR1C1 = "Repayment"

11

    Range("A5").Select

12

    ActiveCell.FormulaR1C1 = "Closing Balance"

13

    Range("A8").Select

14

    ActiveCell.FormulaR1C1 = "Interest Rate"

15

    Columns("A:A").EntireColumn.AutoFit

16

    Range("B8").Select

17

    ActiveCell.FormulaR1C1 = "0.12"

18

    Range("B2").Select

19

    ActiveCell.FormulaR1C1 = "10000"

20

    Range("B3").Select

21

    ActiveCell.FormulaR1C1 = "=R[-1]C*R8C2/12"

22

    Range("B4").Select

23

    ActiveCell.FormulaR1C1 = "700"

24

    Range("B5").Select

25

    ActiveCell.FormulaR1C1 = "=R[-3]C+R[-2]C-R[-1]C"

26

    Range("C2").Select

27

    ActiveCell.FormulaR1C1 = "=R[3]C[-1]"

28

    Selection.AutoFill Destination:=Range("C2:G2"), Type:=xlFillDefault

29

    Range("B3").Select

30

    Selection.AutoFill Destination:=Range("B3:G3"), Type:=xlFillDefault

31

    Range("C4").Select

32

    ActiveCell.FormulaR1C1 = "=RC[-1]"

33

    Range("C4").Select

34

    Selection.AutoFill Destination:=Range("C4:G4"), Type:=xlFillDefault

35

    Range("B5").Select

36

    Selection.AutoFill Destination:=Range("B5:G5"), Type:=xlFillDefault

37

    Range("B2:G5").Select

38

    Selection.NumberFormat = "[$PGK] #,##0.00"

39

    Range("B8").Select

40

    Selection.NumberFormat = "0.00%"

41

End Sub

 

Step 1 is performed by the code lines 2 and 3.  The command Range("B1").Select is equivalent to clicking on the cell B1 or moving into it using the arrow keys.  The command ActiveCell.FormulaR1C1 = "Jan" is equivalent to typing the text “Jan” into the active cell.

Step 2 is performed by code line 4.  The value supplied to the range must include the cell which contains the data we want autofilled as well as the end cell of the range we want it copied into.

Steps 3 and 4 are performed by code lines 5 – 14.  As the logic is the same as for step 1 we don’t need to elaborate on it here.

Step 5 is performed by code line 15

Step 6 is performed by code lines 16, 17, 18, 19, 22 and 23.  Notice that the code enters numeric values as if they were text.

Step 7 is performed by lines 20 and 21.  Formulae are entered using code in quite a different way to how they are entered manually.  Manually the formula would be entered in cell B3. B2*$B$8/12.  Now let us look at the formula in relation to the cell B3.  Cell B2 is one row above B3 and the reference to cell B8 is absolute addressing.  Thus the coder version of the formula R[-1]C*R8C2/12 can be read as “multiply the cell one row above the current cell by the cell in row 8 column 2 and divide the result by 12”

Step 8 is similar in logic to the step above.  Manually the formula entered into B5 is B2+B3-B4.  B2 is 3 rows above B5, B3 is 2 rows above it and B4 is 1 row above it, thus R[-3]C+R[-2]C-R[-1]C can be read as “add the cell 3 rows above the current one to the cell 2 rows above it and subtract the cell one row above it from the result.

Step 9, which is performed in lines 26 and 27, is similar in logic to the above step

Step 10 is performed in line 28 and uses the AutoFill we have discussed before.

Steps 11 – 14 are performed in lines 29 – 36 contain code already familiar to us.

Step 15 is performed in lines 37 and 38

Step 16 is performed in lines 39 and 40.

Using Macro Code as part of a general Visual Basic application

Go to top

In Listing 13‑1 we have seen Visual Basic code for:

1.      Selecting cells and ranges

2.      Entering data into cells

3.      Entering formulae into cells

4.      Using the AutoFill feature

5.      Widening columns

6.      Formatting ranges

This code is not confined to macros only.  Parts of it can be copied and used as part of a general Visual Basic application running within Excel. Even within a macro the code can be modified to perform actions differently to how the macro was originally recorded.

As an example of editing existing macro code we shall look at altering Listing 13‑1 so that instead of copying formulae across to column G it copies them across to column M. Here is one example: Selection.AutoFill Destination:=Range("B5:G5”)

This copies whatever is in cell B5 into the range B5:G5. We can easily alter it to copy the data as far as column M by altering to code to Selection.AutoFill Destination:=Range("B5:M5”)

If all of the other destination ranges are altered accordingly then the altered macro will create a 12 month application instead of a 6 month one.