1 / 50

Spreadsheets in Finance and Forecasting

Spreadsheets in Finance and Forecasting. Presentation 10 Visual Basic. 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

Download Presentation

Spreadsheets in Finance and Forecasting

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related