Unformatted text preview: Decision Structures – If Statements Decision structures also known as conditional statements are a piece of code that allows a programmer to make a choice (decision) based on a logical expression. An example would be to check whether a number entered by the user is positive, negative or zero. This statement can be written down in a layman’s term as: If number is greater than ‘zero’, it is positive; else if the number is less than ‘zero’, it is negative; if neither of the above statements is ‘true’, then the number has to be equal to ‘zero’. There are many types of conditional structure syntaxes that are made available to the programmer to help make a decision in the program. These include: 1. If – Else Statements (or Structures) 2. Select – Case Statements 3. Try – Catch Statements Of these three, the If‐Else statements are the simplest and can be classified as the most widely used decision statement type. The structure of an If‐Else statement is very similar to how the above example is written in spoken language. There are a lot of different types of If‐Else statements and functions that are made available on Excel and VBA to assist the programmer. 1.
2.
3.
4.
5. Excel If() function VBA IIf() function If‐Then(‐Else)‐ Structures If‐ElseIf‐ Structures Nested If Structures The function types of If‐statements (in both VBA and Excel) have been made available predominantly to perform a single calculation decision. These can only perform one set of calculation for each of the two possible cases (TRUE or FALSE); while the other three types of If‐Statements are used for more complex set of calculations. 1. Excel If() Function: Syntax: Cell = If(Expression, Calculations if expression is TRUE, Calculations if expression is FALSE) Example: Write an If‐statement to determine whether A – B is positive or not. If “A‐B” is positive, print its value back to the excel spreadsheet; if not, print 0. Example solution on next page Values for “A” are in Cells R2C2 to R6C2 and the value for “B”are in cells R2C3 to R6C3. Cell R2C4 = If(RC[‐2]‐RC[‐1] > 0, RC[‐2]‐RC[‐1],0) Let’s assume that: R2C2 = 5 and R2C3 = 2 R2C3 = If(RC[‐2]‐RC[‐1] > 0, RC[‐2]‐RC[‐1], 0) TRUE Hence, R2C4 =5 – 2 = 3 Working: The excel “If()” function when called upon, first evaluates the value for the expression (whether the expression is true or false). Based on the result of the expression, it evaluates the appropriate section and displays the result in the cell. 2. VBA IIf() Function: Syntax: Variable = IIf(Expression, Calculations if expression is TRUE, Calculations if expression is FALSE) Example: Write an If‐function to determine the absolute difference between two numbers. A = IIf(B > C, B ‐ C, C ‐ B) Let’s assume that: B = 2 and C = 1. A = IIf(B > C, B ‐ C, C ‐ B) TRUE 1 ‐1 Hence, A = 1 Working: The VBA “IIf()” function when called upon, first evaluates the value for the value for in all three sections of the statement (expression, if TRUE, and if FALSE). Based on the result of the expression, it picks the corresponding value and prints (returns or stores) it. One should be careful when using the IIf() function especially when checking for cases that might lead the program to crash (ex. Divide by zero). 3. If‐Then(‐Else)‐ Structures: Syntax: If Expression Then List of calculation steps – when expression is TRUE Else List of calculations steps – when expressions is FALSE OPTIONAL End If Example: Write an If‐statement to determine the maximum of two numbers. If A > B Then MsgBox(“A is the maximum”) Else MsgBox(“B is the maximum or equal to A”) End If Let’s assume that: A = 1 and B = 5. A > B = FALSE ∴ VBA jumps to the calculation steps for the “Else” part and we get: MsgBox(“B is the maximum or equal to A”) Working: The VBA “IIf()” function when called upon, first evaluates the value for the value for in all three sections of the statement (expression, if TRUE, and if FALSE). Based on the result of the expression, it picks the corresponding value and prints (returns or stores) it. 4. If‐ElseIf‐ Structures: Syntax: If Expression1 Then List of calculation steps – when expression1 is TRUE ElseIf Expression2 Then List of calculations steps – when expression2 is TRUE ElseIf Expression3 Then • • Can have any number of ElseIf statements Else List of calculations steps – when expressions is FALSE End If OPTIONAL Example: Write an If‐statement to determine the maximum of two numbers. If A > B Then MsgBox(“A is the maximum”) ElseIf B > A Then MsgBox(“B is the maximum”) Else MsgBox(“B is equal to A”) End If Let’s assume that: A = 1 and B = 1. A > B = FALSE; B > A = FALSE ∴ VBA jumps to the calculation steps for the “Else” part and we get: MsgBox(“B is equal to A”) 5. Nested If‐ Structures: Nested If statements are decision structures that are used to satisfy multiple conditions simultaneously. They use one If‐statement placed (nested) inside a bigger If‐statement. For example, if we need to find a number that lies in the range 1 to 100, we will check if the number is say greater than or equal to 1 and then we go in and check if the number is less than or equal to 100. Example: If A >= 1 Then If A <= 100 Then Else MsgBox(“A lies in the range 1 to 100”) Msgbox(“A is greater than 100”) Else MsgBox(“A is less than 1”) End If ...
View
Full Document
 Spring '08
 staff
 Expression, calculation steps

Click to edit the document details