Spreadsheets in Finance and Forecasting

1. Spreadsheets in Finance and Forecasting Presentation 10 Visual Basic

2. Objectives • After studying this week’s work you will be able to: • Use the Visual Basic Editor to examine and interpret VB Code • Modify Macros in order to extend their role • Communicate with the user via input and message boxes • Allow the user to choose courses of actions

3. Introduction

4. VB Examples • We will use a spreadsheet to examine the process of creating Visual Basic code via a macro. • The example is a simple situation where we have new data to be used to update a total.

5. VB Examples • One way of doing this is to incorporate the calculation into the spreadsheet formulae. • Another way is to write a macro which does this (and undoes it) at the press of a button.

6. What you should know • Before you start this work, you should have configured Excel with the VB toolbar. • You also need to remember the methods introduced last week.

7. Section 1 Understanding VB Code

8. Action Point VB Examples • Open the VB Examples Spreadsheet, and carry out the task described there

9. Excel Macros • Excel Macros are recorded as routines in Visual Basic (VB) Code • When we “run” macros, what we are doing is running short Visual Basic programs. • During the rest of this presentation we will be working with this VB Code.

10. Action Point The Visual Basic Editor Click on: Tools – Macro- Visual Basic Editor The Folders The Visual Basic Code

11. Visual Basic Code -Features Title of the macro Comments are prefaced with a single quote mark, and appear in green Visual Basic Commands End of macro

12. Understanding the Code • VB code is highly complex and would take you a long time to learn to write. • However if you know what the code is doing, you can work out what each part does. 2. Write =J4+E4 in cell L4 3. Autofill L4:L8 4. Copy L4:L8 5. Paste L4:L8 over to J4:J8 6. Delete L4:L8

13. Understanding the Code • We will now examine the code apart bit-by bit. • Click the buttons to see what each section does J4 + L4 Autofill Copy Paste Delete Next Slide

14. This code comprises two lines: Range("L4").Select ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-7]" Write “=J4+E4” in cell L4 • The first line is simply a command to select cell L4, which then becomes the active cell.

15. Range("L4").Select ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-7]" Write “=J4+L4” in cell L4 • The second line puts a formula in this active cell. • This cell is now regarded (for the moment) as Row 1 Column 1 • In the formula, RC[-2] means “same Row”, but 2 Columns to the left Return

16. This takes two lines: Range("L4").Select Selection.AutoFill Destination:=Range("L4:L8"), Type:=xlFillDefault Autofill L4:L8 • The first line simply selects cell L4 • The second line (all three parts of it!) auto fills it down to L8 Return

17. This takes two lines: Range("L4:L8").Select Selection.Copy Copy cells L4:L8 • The first line selects the range of cells from L4 to L8 • The second line is an instruction to copy this selection. Return

18. This takes two lines: Range("J4:J8").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Paste J4:J8 over to L4:L8 • The first line selects J4:J8, the range of cells to be copied.

19. Range("J4:J8").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Paste J4:J8 over to L4:L8 • The second line is the Paste Special option, which pastes values only. • Note that lots of other things are included in this that you might not have been aware of. Return

20. This takes three lines of code Range("L4:L8").Select Application.CutCopyMode = False Selection.ClearContents Delete L4:L8 • The first line selects cells L4 to L8 • The second line establishes that we are not going to cut or copy the cells. • The third line clears the contents. Return

21. Working in Visual Basic • As you can see, VB is not a simple language, and not something that you can immediately use. • However, the usual way that people work is to record macros and use the code as a basis for what they want to do.

22. Section 2 Modifying VB Code

23. Your first Original Macro! • Return to Sheet 1 of the VB Examples spreadsheet. • You are going to write a macro which will subtract “Today’s Sales” from the “Total so far this month”.

24. Step 1: Copy and Rename • Switch to VB Editor mode, and copy the entire “increaseTotal” macro Action Point • Underneath the original, paste a second version, but change the title to “decreaseTotal”

25. Your first Original Macro! • Now examine the macro • You only need to change one symbol on one line • That is the line which puts the formula into cell L4 ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-7]"

26. Your first Original Macro! • Make the change to the macro and assign it to the second button on the sheet. Check that it works. • The line should read: Instead of writing “=J4+L4”, we need to have this say “=J4-E4” ActiveCell.FormulaR1C1 = "=RC[-2]- RC[-7]"

27. Section 3 Communicating with the User

28. Amending Macros • It is often useful to send a message to the user to say that a macro has been completed. • There is a simple piece of coding which allows you to do this

29. Adding a Message Box • In the increaseTotal macro, just before the End Sub line, add the following: MsgBox “Task completed” Action Point

30. Adding a Message Box The line: MsgBox “Task completed” • Generates this box • The user needs to press the button to continue. • The message box is a useful way to communicate with a user

31. Disabling the Message Box • Add a single quotation mark, and change the line to: ‘ MsgBox “Task completed” • Now click off the line; note that this turns the text green. • If you now run the macro, the message box will not appear. • This is because VB treats the line as a comment, not as a piece of code. Action Point

32. Section 4 Giving the user Choice

33. Giving the User Choice • We will now write a completely new macro which will allow the user to choose whether to increase or decrease the “total so far” • In doing this you will see how macros can be developed.

34. Action Point Recording a New Macro • Record a macro called makeSelection • This macro will consist of two button presses: • Increase Total • Decrease Total

35. The New Macro Sub makeSelection() ' ' makeSelection Macro ' Macro recorded 20/03/2002 by User Application.Run "'VBExamples.xls'!increaseTotal" Application.Run "'VBExamples.xls'!decreaseTotal" End Sub • The macro simply consists of a title, an end, some comments in green, and two lines of code. Increase Total button Decrease Total button

36. Action Point Adding an Input Box Choice = InputBox(“increase or decrease?”) Application.Run "'VBExamples.xls'!increaseTotal" Application.Run "'VB Examples.xls'!decreaseTotal" • After the green comments, but before the lines of VB code, insert the following: This will put an input box on the screen which will ask the user for a reply.

37. Action Point Adding choice Choice = InputBox(“increase or decrease?”) If choice = “inc” Then Application.Run "'VBExamples.xls'!increaseTotal" End If If choice = “dec” Then Application.Run "'VBExamples.xls'!decreaseTotal“ End If • We will assume that the user will type in either “inc” or “dec” • We now add lines which will allow the user to choose which action is carried out. 1 2

38. Assigning the Macro • Assign the Macro to the third button on the VB Examples Sheet. • Run the macro. • When the input box appears, type in either inc or dec Action Point

39. Action Point Finishingthe Macro • The macro carries out the task, but does not tell you that it has done it. • Add code to the macro so that the correct one of these message boxes appears

40. makeSelection Macro Sub makeSelection() ' ' makeSelection Macro ' Macro recorded 20/03/2002 by User ' choice = InputBox("Do you want to Increase or Decrease?") If choice = "inc" Then Application.Run "'VBExamples.xls'!increaseTotal" MsgBox "Total Increased" End If If choice = "dec" Then Application.Run "'VBExamples.xls'!decreaseTotal" MsgBox "Total Decreased" End If End Sub The best place to put the message boxes is inside the “IF” commands

41. What have we learned? • The macro we have just written is not all that useful, since we could just have pressed the buttons. • However, suppose that we wanted to update one item selectively. • With the commands, we could do this.

42. Section 5 Using Selections

43. Selective Updating of Items • Firstly, add numbers 1-5 to each of the type of item • This will allow us to use a number rather than the name Action Point

44. Action Point Recording the initial macro • Record a new macro called selectItem. • This should: • Put the formula =L4+E4 in cell L4 • Copy L4 and Paste Values into K4 • Delete L4

45. Action Point Examine the initial macro • Record a new macro called selectItem. • This should: • Put the formula =L4+E4 in cell L4 • Copy L4 and Paste Values into K4 • Delete L4 Sub selectItem() ' ' selectItem Macro ' Macro recorded 28/03/2004 by User ' Range("L4").Select ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-7]" Range("L4").Select Selection.Copy Range("J4").Select Selection.PasteSpecial Paste:=xlValues Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("L4").Select Application.CutCopyMode = False Selection.ClearContents End Sub

46. At the beginning of the macro (after the comments) add these five lines of code: The first (all one line) asks the user which line is to be updated. These four lines identify which cells will be modified. Item = InputBox("Which item do you wish to update: 1,2,3,4,or 5") If Item = 1 Then cell1 = "L4" cell2 = "J4" End If Adding New Lines (1) Action Point

47. Now add these eight lines: These identify the cells which will be updated if the user selects item 2 or 3 Now add the other eight lines needed if the user wants to update items 4 or 5 If Item = 2 Then cell1 = "L5" cell2 = "J5" End If If Item = 3 Then cell1 = "L6" cell2 = "J6" End If Action Point Adding New Lines (2)

48. Modify the code underneath as follows: Wherever “L4” occurs, replace it with cell1 Wherever “J4” occurs, replace it with cell2 Range(cell1).Select ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-7]" Range(cell1).Select Selection.Copy Range(cell2).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range(cell1).Select Application.CutCopyMode = False Selection.ClearContents Modifying the code Action Point

49. Action Point Finishing off • Finally, add a line just before the end: MsgBox “Item " & Item & " updated“ • This will add a message box to say which item has been updated. Now, reassign the “Make Selection” button to the selectLine macro and test it out.

50. What Next? • There are two separate Activity Sheets which follow up this work: • Formative Activity 8 creates macros which draw charts for selected lines of data • Summative Activity C explores how to update and interrogate a database