EIN 4905/ESI 6912 Decision Support Systems Excel

1
Spreadsheet-Based Decision Support Systems
Chapter 16 Programming Structures
Prof. Name
name_at_email.com Position
[phone] 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
  nested If 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 lt 1000 Then
• MsgBox Your number is smaller than 1000.
• ElseIf x lt 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 lt 1000 And x gt 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 gt 1000 Or x lt 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 True Then
• 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.
• Select Case 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.
• Select Case 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 Case Else 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.
• Select Case 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.
• Select Case number
• Case number lt 10
• MsgBox Your number is less than 10.
• Case number gt 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.
• Select Case 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).
• Case Else
• 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 Do Loops
• 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 lt 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 lt 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 lt 10
• x 2x
• 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 lt 10)
  and the next 10 times.
• The action (x 2x, again assuming x is
  initially 1) will therefore be repeated 10 times
  yielding the final values of
• x 210
• count 11

33
While vs Until (cont)

• Do Until count 10
• x 2x
• 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 29
• 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)

• Do While found False
• actions
• If x gt 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) gt 50 Then
• MsgBox The calculation values exceed the
  limit.
• Exit For
• End If
• Next i
• End Sub

42
Exit Do Example

• Do
• x x2
• If x mod 5 0 Then
• MsgBox The number x is divisible by
  5.
• Exit Do
• End If
• x x 1
• Loop Until x gt100

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 gt 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 (contd)

• 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 users 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

51
Figure 16.1
52
Account Sheet

• The Account sheet is where the program actions
  will occur.
• The record of account transactions is shown with
  the date and description of each transaction.
• Each button is associated with the action stated
  in the button caption.
• The Exit button, as in all of our applications,
  will bring the user back to the welcome sheet.

53
Figure 16.2
54
Procedures

• We will have a NewDeposit and NewWithdrawal
  procedure associated with the first button
  options.
• In each of these procedures we will be adding a
  new user-supplied value to the Amount column.
• We will also need to update the balance in both
  procedures by calling an UpdateBalance function
  procedure.
• We will also need two summing procedures which we
  will call SumDeposits and SumWithdrawals.

55
Procedures (cont)

• The first, and most important organizational sub
  procedure will be the Main sub procedure.
• This macro will be associated with the Start
  button on the welcome sheet.
• We will clear previous sum values from their
  respective ranges, set any range variables,
  initialize any other variables if needed, and
  hide the welcome sheet and bring the user to the
  Account sheet.
• There will also be a short ExitAccount procedure
  to be assigned to the Exit button.

56
Variables

• We will have a deposit and withdrawal variable to
  insert the new values and update the balance.
• We will use a value variable (declared as a
  variant data type) to receive user input before
  assigning values to the deposit and withdrawal
  variables this is in order to perform error
  checking.
• We will have sumDep and sumWith variables to
  calculate the respective sums.

57
Variables (cont)

• We will have two range variables to identify the
  Amount and Balance columns.
• We call them AccountStart and BalanceStart.
• We will later use an integer counter i for our
  loops and a response variable (declared as a
  variant data type) in a MsgBox function.

58
Workbook_Open

• This time we improve our code by using a
  worksheet variable, ws, and a For Each, Next
  loop.
• Private Sub Workbook_Open()
• For Each ws In ActiveWorkbook.Worksheets
• If ws.Name "Account Welcome" Then
• ws.Visible True
• Else
• ws.Visible False
• End If
• Next
• End Sub

59
Variables (contd)

• We define all previously discussed variables,
  including the worksheet variable, publicly as
  follows.
• Public deposit As Double, withdrawal As Double,
  sumDep As Double, _
• sumWith As Double, AccountStart As Range,
  BalanceStart As Range, _
• ws As Worksheet, Value As Variant, i As Integer,
  response As Variant

60
Main

• In preparation for coding we have named the two
  ranges in which the sum values will be
  calculated.
• DepositSum and WithdrawalSum respectively
• Sub Main()
• Range("DepositSum").ClearContents
• Range("WithdrawalSum").ClearContents
• Set AccountStart Worksheets("Account").Range
  ("D5")
• Set BalanceStart Worksheets("Account").Range
  ("F5")
• sumDep 0
• sumWith 0
• Worksheets("Account").Visible True
• Worksheets("Account Welcome").Visible False
• Worksheets("Account").Activate
• End Sub

61
ExitAccount

• The navigational procedure for the Exit button is
• Sub ExitAccount()
• Worksheets("Account Welcome").Visible True
• Worksheets("Account").Visible False
• End Sub

62
Summing Procedures

• For each Sum button we will look through every
  entry of the Amount column until the end of the
  column is reached.
• We do this using a Do, Until loop, AccountStart
  range variable, and the Offset property.
• For each entry we check if the value is a
  withdrawal or deposit and update our respective
  sum variables.
• We do this using If, Then statements.
• We will use the counter variable i for the loop.

63
SumDeposits

• Sub SumDeposits()
• i 1
• Do Until AccountStart.Offset(i, 0).Value ""
• If AccountStart.Offset(i, 0).Value gt 0
  Then
• sumDep sumDep AccountStart.Offset(
  i, 0).Value
• End If
• i i 1
• Loop
• Range("DepositSum").Value sumDep
• End Sub

64
SumWithdrawals

• Sub SumWithdrawals()
• i 1
• Do Until AccountStart.Offset(i, 0).Value ""
• If AccountStart.Offset(i, 0).Value lt 0
  Then
• sumWith sumWith
  AccountStart.Offset(i, 0).Value
• End If
• i i 1
• Loop
• Range("WithdrawalSum").Value sumWith
• End Sub

65
NewDeposit

• The first line of code will be to ask the user
  for the amount of the new deposit.
• We will temporarily set the value variable equal
  to our InputBox function so that we can perform
  error checking on the input.
• Sub NewDeposit()
• value InputBox("Please enter amount to
  deposit.", _"New Deposit", 150)

66
NewDeposit (cont)

• To perform the error checking, we will use the
  IsNumeric function with the value variable to see
  if the user entered a numerical data type.
• If so, then we can continue running the sub
  procedure, however if the value is not numeric
  then we cannot continue with the procedure
  actions.
• We therefore inform the user of their error using
  a simple Message Box and then use Exit Sub.
• If IsNumeric(value) False Then
• MsgBox "You have not entered a numerical
  value. Please try again."
• Exit Sub
• End If
• If the user has provided a numeric value then we
  assign this value to the deposit variable.
• deposit value

67
NewDeposit (cont)

• We now insert this value into the Amount column
  using the AccountStart range variable and the End
  property.
• We also format this new row of the table by
  changing the background color of the appropriate
  cells.
• With AccountStart.End(xlDown).Offset(1, 0)
• .Value deposit
• .Interior.ColorIndex 0
• End With
• Range(AccountStart.End(xlDown).Offset(0,-3),
  _
• AccountStart.End(xlDown).Offset(0,
  -2)).Interior.ColorIndex 0
• BalanceStart.End(xlDown).Offset(1,
  0).Interior.ColorIndex 0

68
NewDeposit (cont)

• The only action left to perform is to update the
  balance by calling the UpdateBalance function.
• After those actions are complete we can simply
  inform the user that they may enter a date and
  description for the new entry with a Message Box.
• Call UpdateBalance(deposit, "D")
• MsgBox "You may now enter the date and
  description of your _
• deposit into the table."

69
NewDeposit (cont)

• We can add an additional option to continue
  adding more new deposits by using the MsgBox
  function and response variable.
• We prompt the user if they want to enter another
  deposit and set the Message Box buttons to be
  vbYesNo.
• The MsgBox function is assigned to the response
  variable so we check the value of the response
  variable and perform the related actions.

70
NewDeposit (cont)

• We will use another If, Then statement to see if
  the response was Yes, which is equal to the VB
  Constant vbYes, and call the NewDeposit sub
  procedure again if it is.
• Otherwise we will just end the sub.
• .
• response MsgBox("Would you like to enter
  another deposit?", _ vbYesNo, "Another Deposit?")
• If response vbYes Then
• Call NewDeposit
• End If
• End Sub

71
NewWithdrawal

• The NewWithdrawal procedure will be very similar.
• We do not give the option to create another new
  withdrawal here, but you can add this later.
• The only main changes are using the withdrawal
  variable instead of deposit and entering the
  value in the table as a negative number.

72
NewWithdrawal (cont)

• Sub NewWithdrawal()
• value InputBox("Please enter amount to
  withdraw.", "New withdrawal", 150)
• 'error checking
• If IsNumeric(value) False Then
• MsgBox "You have not entered a numerical
  value. Please try again."
• Exit Sub
• End If
• withdrawal value
• 'insert into table and format new row
• With AccountStart.End(xlDown).Offset(1, 0)
• .Value -withdrawal
• .Interior.ColorIndex 0
• End With
• Range(AccountStart.End(xlDown).Offset(0,-3),
  _
• AccountStart.End(xlDown).Offset(0,
  -2)).Interior.ColorIndex 0
• BalanceStart.End(xlDown).Offset(1,
  0).Interior.ColorIndex 0
• 'update balance

73
UpdateBalance

• There will be two parameters for this function.
• The deposit or withdrawal value.
• And a simple letter signifying if this is a
  deposit D or withdrawal W.
• Notice that these variables are passed when the
  function is called from the above two procedures.
• The function declaration refers to these two
  parameters simply as x and y.
• We can then use a Select, Case statement to check
  the value of the y parameter.
• In the case that it is D we will add the
  deposit value to the last entry of the Balance
  column.
• In the case that it is W we will subtract the
  withdrawal value from the last entry of the
  Balance column.

74
UpdateBalance (cont)

• We must also perform one more check in the case
  that a withdrawal is made we must ensure that
  the 100 balance requirement will still be met
  after the withdrawal is made.
• We use an If, Then statement to check the result
  of this calculation.
• If the withdrawal will result in a balance less
  than 100, we do not perform the transaction.
• The user is informed that their balance is too
  low, the initial entry to the Amount column is
  cleared, and the function is exited using the
  Exit Function statement.
• However, if the requirement will still be met,
  then we update the balance and tell the user they
  can enter the date and description for the
  withdrawal .

75
UpdateBalance (cont)

• Function UpdateBalance(x, y)
• Select Case y
• Case "D"
• BalanceStart.End(xlDown).Offset(1,0).v
  alue _
• BalanceStart.End(xlDown).value x
• Case "W"
• If BalanceStart.End(xlDown).value - x
  lt 100 Then
• MsgBox "This withdrawal cannot be
  made due to the 100 _
• balance requirement."
• AccountStart.End(xlDown).ClearCont
  ents
• Exit Function
• End If
• BalanceStart.End(xlDown).Offset(1,0).v
  alue _
• BalanceStart.End(xlDown).value - x
• MsgBox "You may now enter the date
  and description of your _
• withdrawal into the table."
• End Select
• End Function

76
Application Conclusion

• The procedures are now complete.
• We assign them to their respective buttons and
  test the macro.
• The application is finished.

77
Summary

• If, Then statements allow you to perform
  conditional actions. If a condition is met, a
  certain set of actions is performed if it is
  not, another set of actions may be performed
  instead.
• Use the Select, Case statement to list possible
  situations in which certain actions should be
  performed.
• Loops are programming structures that allow you
  to repeat a set of actions a certain number of
  times.
• VBA provides several Exit Statements that allow
  current actions to stop and moves the program to
  ensuing code.

78
Additional Links

• (place links here)