1 / 78

Spreadsheet-Based Decision Support Systems: Programming Structures

Learn how to use If, Then statements, Select, Case structure, loops, and exit statements to create powerful spreadsheet-based decision support systems.

mollyfernandez
Download Presentation

Spreadsheet-Based Decision Support Systems: Programming Structures

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. Spreadsheet-Based Decision Support Systems Chapter 16: Programming Structures Prof. Name name@email.com Position (123) 456-7890 University Name

  2. Overview • 16.1 Introduction • 16.2 If, Then Statements • 16.3 Select, Case • 16.4 Loops • 16.5 Exit Statements and End • 16.6 Applications • 16.7 Summary

  3. Introduction • Using If, Then statements for conditional programming, logical checks, and error checking • VBA Select, Case structure • For Loops and Do Loops • Various exit statements and the End statement • Creating a banking account management application using the programming structures described in the chapter

  4. If, Then Statements • Logical Checks and Booleans

  5. If, Then Statements • If, Then statements allow you to perform the same conditional actions that we learned with the IF function in Excel. • If a condition is met, a certain set of actions is performed, if it is not, another set of actions may be performed instead. • The general format for the If, Then statement is If condition Then action End If

  6. If, Then (cont) • The If, Then statement defines the action to performed if the condition is false by using the Else and ElseIf statements. • The Else statement allows you to specify the action to be performed if the condition is not met. • The ElseIf statement allows you to construct nestedIf statements. • That is, instead of performing a direct action if the original condition is not met, another If condition is considered.

  7. If, Then Example If x < 1000 Then MsgBox “Your number is smaller than 1000.” ElseIf x < 2000 Then MsgBox “Your number is greater than 1000 but less than 2000.” Else MsgBox “Your number is larger than 2000” End If

  8. Logical Checks and Booleans • If, Then statements are also used with logical checks and Boolean variables. • Logical checks include And and Or statements similar to the AND and OR functions we learned in Excel.

  9. And Logical Check • The And logical statement requires every condition in the If, Then statement to be true in order for the following action to be performed. • If only one of the conditions is false, the action will not be performed and the following Else or ElseIf statement will be read or the If, Then statement will end. • This statement structure is as follows If condition1 And condition2 And condition3 And … Then action 1 Else (or ElseIf) action 2 End If

  10. Or Logical Check • The Or logical statement requires only one condition in the If, Then statement to be true in order for the following action to be performed. • Every condition would have to be false to skip the following action and read the Else, ElseIf, or End If statements. • This statement structure is as follows If condition1 Or condition2 Or condition3 Or … Then action 1 Else (or ElseIf) action 2 End If

  11. And, Or Example If x < 1000 And x > 500 Then MsgBox “Your number is between 500 and 1000.” Else MsgBox “Your number is smaller than 500 or greater than 1000.” End If • Here, the And check requires that both conditions be met. • If this is true, then the value of x should be between 500 and 1000, thus the first Message Box is displayed. • However, if either condition is false, then the statement in the second Message Box must be true.

  12. And, Or Example (cont) If x > 1000 Or x < 500 Then MsgBox “Your number is smaller than 500 or greater than 1000.” Else MsgBox “Your number is between1000 and 500.” End If • Here we can see the difference in using the Or logical check. • With Or, either of the conditions can be true to display the first Message Box. • That is either x can be greater than 1000 or less than 500. • If neither condition is met, then the second Message Box must be true.

  13. Using Boolean Variables • If, Then statements are used with Boolean variables to check if their values are True or False. • You can simply use the following two statements If variable = TrueThen action 1 End If -------------------------------- If variable = False Then action 2 End If

  14. Using Boolean Variables(cont) • To check if a Boolean variable is True, you can also just state the name of the variable. • that is, the default value of any Boolean variable is True. • Instead of the first statement in the previous slide, you can just type If variable Then action End If

  15. Boolean Example If found Then MsgBox “The solution has been found.” ElseIf found = False Then MsgBox “The solution has not been found.” End If

  16. Select, Case • The Select, Case statement is used to list possible situations in which certain actions should be performed. • The general structure of the Select, Case statement gives a particular expression which is to be evaluated and a list of cases of possible values of that expression. SelectCase number Case 1 MsgBox “Your number is 1.” Case 2 MsgBox “Your number is 2.” End Select

  17. Select, Case (cont) • We can also give a range of values as a case instance. SelectCase number Case 1 To 5 MsgBox “Your number is in the interval (1, 5).” Case 6 To 10 MsgBox “Your number is in the interval (6, 10).” End Select

  18. Select, Case (cont) • There is also an optional CaseElse statement which can be used to specify all other cases which are not listed. • The ability to give a range of values as a case instance using the To statement can be extended to string values. SelectCase name Case “Adams” To “Henderson” MsgBox “Please look in files A to H.” Case “Ignatius” To “Nichols” MsgBox “Please look in files I to N.” Case Else MsgBox “Please look in files N to Z.” End Select

  19. Select, Case (cont) • You may also include some conditions as cases instead of simple instances. • This is useful in replacing several ElseIf statements in an If, Then structure. SelectCase number Case number < 10 MsgBox “Your number is less than 10.” Case number > 10 MsgBox “Your number is greater than 10.” End Select

  20. Select, Case (cont) • You can place a condition on a range of values using the Is statement. SelectCase number Case 1 To 5 Is = number MsgBox “Your number is in the interval (1, 5).” Case 6 To 10 Is = number MsgBox “Your number is in the interval (6, 10).” CaseElse MsgBox “Your number is greater than 10.” End Select

  21. Loops • For Loops • Do Loops

  22. Loops • Loops are programming structures which allow you to repeat a set of actions a certain number of times. • The number of loops can be specified by counting up to (or down to) a certain value. • For, Next • For Each, Next • Or the loops can run continuously while or until a certain condition is met. • Do, While • Do, Until

  23. For Loops • The For, Next and For Each, Next loops are used to repeat a loop while counting up toward to down to a certain number. • We refer to them both generally as For Loops. • We perform the counting using some simple index variable such as i or a counting variable such as count or iteration. • These variables are integer data types.

  24. For, Next • The more common of these two structures is the For, Next loop. • The structure of this loop is as follows For counter = start To end actions Next counter

  25. For, Next (cont) • There is also a Step parameter used with this loop. • The Step value specifies how much the counter variable should increase or decrease during each loop.

  26. For, Next (cont) • If you want to count up towards a number, the Step value should be positive and the start value should be less than the end value. For i = 1 to 10 Step 2 • actions Next i • If you wish to count down to a number, the Step value should be negative and the start value should be greater than the end value. For i = 10 to 1 Step -1 • actions Next i

  27. For Each, Next • The For Each, Next loop works almost identically to the For, Next loop. • The only difference is that For Each, Next is counting a certain number of objects in a group of objects. • That is objects are counted rather than using a counter variable.

  28. For Each, Next (cont) • For example, if we want to count the number of worksheets in the current workbook, we could declare a worksheet variable ws and use the following For Each, Next loop with some count variable. For Each ws In ActiveWorkbook.Worksheets count = count + 1 Next

  29. Do Loops • There are two main DoLoops • Do, While • Do, Until • These Do Loops perform a set of actions repeatedly while or until a condition is met. • There are two main structures for these loops • Specify While or Until condition before actions • Specify While or Until condition after actions

  30. Do Loop Structures • In the structure below, a while condition is considered before a set of actions will be performed. Do While count < 10 actions count = count + 1 Loop • In the second structure, the set of actions will be performed and then the while condition will be checked before the actions are repeated. Do actions count = count + 1 Loop While count < 10

  31. While vs Until • The difference between looping while the condition is met and until the condition is met is important to note. • For the Do, While loop, if the condition is true this signals the loop to repeat the actions. • However, for the Do, Until loop, if the condition is true, this signals the loop to stop repeating the actions.

  32. While vs Until (cont) • Compare the values generated by the following two loops. Do While count <= 10 x = 2*x count = count + 1 Loop • For this first loop, assuming the value of the count variable is initialized to be 1, the condition will be met the first time (1 <= 10) and the next 10 times. • The action (x = 2*x, again assuming x is initially 1) will therefore be repeated 10 times yielding the final values of • x = 2^10 • count = 11

  33. While vs Until (cont) Do Until count = 10 x = 2*x count = count + 1 Loop • This second loop, however, will stop repeating when count reaches 10 (but not including 10). • Thus yielding final values of • x = 2^9 • count = 10

  34. Boolean Variables in Do Loops • Do Loops can also be used with Boolean variables. • That is, the Boolean variable can be used as the condition for the Do, While or Do, Until loops. • These conditions usually imply some nested If, Then statements which would change the value of the Boolean variable once a certain result is found.

  35. Boolean Variables (cont) DoWhile found = False actions If x > 100 Then found = True End If Loop • Here we are performing some actions while a certain result is still not found. • Once the found variable is set to True, the While condition is no longer met and the loop ends.

  36. Exit Statements and End • Exiting Procedures • Exiting Loops • Ending the Program

  37. Exit Statements • As we develop and run longer programming structures such as nested If, Then statements and Do Loops, we may want a way to exit the current set of actions at any time. • We may also want to use this option while running any sub procedure or function procedure. • VBA provides several Exit Statements which allow current actions to stop and moves the program to following code. • We will usually use these statements with the If, Then structure.

  38. Exiting Procedures • To exit a procedure we use either Exit Sub or Exit Function depending on if we are currently running a sub procedure or function procedure respectively. • When Exit Function is stated, the function procedure will stop being executed and the program will return to the point in the code from which the function was called. • When the Exit Sub statement is used, the sub procedure will stop being executed, and if the sub was called from another sub, the program will return to this sub and begin executing the next line of code.

  39. Exit Sub and Exit Function Example • Here, we perform simple error checking with a Divide function. Sub Calculations() x = InputBox(“Enter x value.”) y = InputBox(“Enter y value.”) value = Divide(x, y) If value = “none” Then Exit Sub End If MsgBox “x divided by y is “ & Divide(x, y) End Sub ------------------------------------------------ Function Divide(a, b) If b = 0 Then Divide = “none” Exit Function End If Divide = a / b End Function

  40. Exiting Loops • To exit a loop we use Exit For and Exit Do depending on if we are currently in a For, Next or For Each, Next loop or in a Do, While or Do, Until loop. • Similar to the above exit statements, Exit For will stop executing a For, Next or For Each, Next loop and move to the next line of code after the Next statement. • The Exit Do code will stop executing a Do Loop and move to the next line of code after the Loop statement.

  41. Exit For Example Sub Organize() Set StartCell = Range(“A1”) For i = 1 to 100 StartCell.Offset(i, 0).Value = i * (i – 1) If i * (i – 1) > 50 Then MsgBox “The calculation values exceed the limit.” Exit For End If Next i End Sub

  42. Exit Do Example Do x = x^2 If x mod 5 = 0 Then MsgBox “The number “ & x & “ is divisible by 5.” Exit Do End If x = x + 1 Loop Until x >100

  43. Exiting Loops (cont) • These statements can also be used with nested loops. • The exit statement will apply to the inner most loop in which it is used. For i = 1 to 100 For j = 1 to 50 StartCell.Offset(i, j).Value = i + j If i + j > 60 Then Exit Do End If Next j Next i

  44. Ending the Program • We can stop executing the entire program by using the End statement. • Just as the End Sub, End Function, End If, and End With statements end the execution of the enclosed lines of code, the End statement will stop executing all code and exit all loops and procedures. • This can be a useful function if there is some significant condition or requirement that must be met before the program can function correctly.

  45. End Example • In this code, the Username sub procedure is called to prompt the user for a username. • Then the FindFile function procedure is called to find the filename listed for the given username in a particular database. Sub Main() Call Username Call FindFile(username) If filename = “” Then MsgBox “No file was found for this user.” End End If Call EditRecord End Sub • If the file is found, then the record for this user can be edited, however if no file is found which matches the username given then there is no need to continue running this program as this is a necessary requirement for all further actions.

  46. Basic Error Checking • Possible Error: User Input • User input is usually assigned to a variable. • This variable must have been declared previously with a specific data type. • An error can occur if the data that the user provides as input does not match the data type already assigned to the variable. • The Variant data type • A temporary Variant data type variable can be used to assign to any input that is provided by the user • An Error Checking Solution: IsNumeric and CInt functions • The IsNumeric function returns the value True or False depending on whether or not the variable or expression is a numeric data type • The CInt conversion function will ensure that this variable has the data type needed for subsequent calculations

  47. Basic Error Checking (cont’d) Dim TempInput As Variant, x As Integer TempInput = InputBox(“Please enter a number.”) If IsNumeric(TempInput) = True Then x = CInt(TempInput) End If • Similarly, the IsDate and CDate functions can be used for date input. • (See Appendix B for a detailed discussion of other error checking solutions.)

  48. Applications • Banking Account Management

  49. Description • This program stores deposits, withdrawals, and the current balance of a user’s banking account. • The user can record a new deposit or new withdrawal or they can sum current deposits or withdrawals. • Each time a new deposit or withdrawal is made, the balance is updated. • There is also a minimum balance requirement of $100 for this account.

  50. Preparation • There are only two worksheets for this program. • “Account Welcome” • “Account” • Like any welcome sheet, the “Account Welcome” sheet has a program title and description. • We have also added a Start button; this button will be associated with a Main sub procedure

More Related