class: center, middle, inverse, title-slide .title[ # Loops and conditional statements ] .author[ ### Lars Relund Nielsen ] --- layout: true <div class="my-footer"> <span> <a href="https://bss-osca.github.io/tfa/mod-vba-loops-cond" target="_blank">Notes</a> | <a href="03_vba-loops-cond-slides.html" target="_blank">Slides</a> | <a href="https://github.com/bss-osca/tfa/blob/master/slides/03_vba-loops-cond-slides.Rmd" target="_blank">Source</a> | <a href="https://github.com/bss-osca/tfa/blob/master/vba/vba-template.xlsm" target="_blank">Excel file</a> </span> </div> --- ## Relational (comparison) operators <table class="table table-striped table-hover table-condensed table-responsive" style="margin-left: auto; margin-right: auto;border-bottom: 0;"> <caption>Comparison/relational operators.</caption> <thead> <tr> <th style="text-align:left;"> Operator </th> <th style="text-align:left;"> Description </th> <th style="text-align:left;"> Example </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> = </td> <td style="text-align:left;"> Equal to. </td> <td style="text-align:left;"> A = B ' False </td> </tr> <tr> <td style="text-align:left;"> <> </td> <td style="text-align:left;"> Not equal to </td> <td style="text-align:left;"> A <> B ' True </td> </tr> <tr> <td style="text-align:left;"> > </td> <td style="text-align:left;"> Greater than. </td> <td style="text-align:left;"> A > B ' False </td> </tr> <tr> <td style="text-align:left;"> < </td> <td style="text-align:left;"> Less than. </td> <td style="text-align:left;"> A < B ' True </td> </tr> <tr> <td style="text-align:left;"> >= </td> <td style="text-align:left;"> Greater than or equal to. </td> <td style="text-align:left;"> A >= B ' False </td> </tr> <tr> <td style="text-align:left;"> <= </td> <td style="text-align:left;"> Less than or equal to. </td> <td style="text-align:left;"> A <= B ' True </td> </tr> </tbody> <tfoot><tr><td style="padding: 0; " colspan="100%"> <sup></sup> Assume that A = 2 and B = 4.</td></tr></tfoot> </table> --- ## Logical operators <table class="table table-striped table-hover table-condensed table-responsive" style="margin-left: auto; margin-right: auto;border-bottom: 0;"> <caption>Logical operators.</caption> <thead> <tr> <th style="text-align:left;"> Operator </th> <th style="text-align:left;"> Description </th> <th style="text-align:left;"> Example </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> AND </td> <td style="text-align:left;"> If both the conditions are True, then the expression is true. </td> <td style="text-align:left;"> A<>0 AND B<>0 ' False </td> </tr> <tr> <td style="text-align:left;"> OR </td> <td style="text-align:left;"> If any of the two conditions are True, then the expression is true. </td> <td style="text-align:left;"> A<>0 OR B<>0 ' True </td> </tr> <tr> <td style="text-align:left;"> NOT </td> <td style="text-align:left;"> Reverse logical: if the expression is true, then the NOT operator returns false. </td> <td style="text-align:left;"> NOT(A<>0 OR B<>0) ' False </td> </tr> <tr> <td style="text-align:left;"> XOR </td> <td style="text-align:left;"> Logical Exclusion. If exactly one condition is True, the result is True. </td> <td style="text-align:left;"> A<>0 XOR B<>0 ' True </td> </tr> </tbody> <tfoot><tr><td style="padding: 0; " colspan="100%"> <sup></sup> Assume that A = 0 and B = 4.</td></tr></tfoot> </table> --- ## Conditional statements Used to redirect code execution based on the conditions. If the condition is met then the code is executed. The general layout of an if-then-else conditional statement is: An if then statement: ``` If <condition> Then <code> ElseIf <condition> Then <code> ElseIf <condition> Then <code> Else <code> End If If <condition> Then <code> Else <code> ' single line form ``` You can drop the `ElseIf` and `Else` code chunks. Let us try to look at procedure `TM3_Comparison` and `TM3_Logical` in the Excel file --- ## Loops Loops are used to repeat pieces of code. We will consider - For loops (repeat a number of times): ``` For i = 1 To 10 <code> Next ``` - While loops (repeat until a condition is met): ``` Do While <condition true> <code> Loop ``` - Other loops exists but in general you can always use for or while loops instead. - Use `Exit for` and `Exit Do` to break a `For` and a `Do While` loop before it ends (jump to the code after the for loop). --- ## Nested loops Loops may be nested inside each other. For instance if some action needs to be performed for each day and each employee or for each project and each work package of that project. ``` Sub NestedLoops() Dim i As Integer, j As Integer For i = 1 To 2 ' days For j = 1 To 3 ' employees MsgBox ("(" & i & "," & j & ")") Next Next End Sub ``` --- ## Beware of endless loops .pull-left[ If the stopping criteria is NOT reached when using a while loop, the computer will keep going ... - Always “save” before “run” - Make sure the stopping criterion will be reached. - On Windows you may try to stop the program using Ctrl + Break or Ctrl + Alt + Delete. - On a mac you may try ⌘ + ., ⌃ + Esc or ⌘⌥ + Esc. ] .pull-right[ <img src="./img/loop.png" width="80%" style="display: block; margin: auto;" /> ] --- ## The `For Each` loop The loop is used for running trough a set of objects (we will have a closer look at objects next week) ``` Sub TM3_ForEach() Dim rngC As Range Dim i As Integer Worksheets("TM3").Activate i = 1 For Each rngC In Range("D6:E9") rngC = i i = i + 1 Next End Sub ``` Here `rngC` is used to run through all the cells in the range and set values. --- ## Examples In module `TM3_ex` there are examples using conditional statements and loops: - Separate persons into groups. - Distance matrix calculations. - Find Jen.