IE 212 Quiz 4 Cheatsheet

Cheatsheet Content

### Variable Fundamentals Review Variables are named storage locations for data. Choosing the correct data type optimizes memory usage and prevents errors. | Type | VBA Example | Python Example | Description | Key Use | |---|---|---|---|---| | **Integer**| `Dim x As Integer` | `x = 10` | Whole numbers (VBA: +/- 32,767). | Loop counters, simple counts. | | **Long** | `Dim y As Long` | (Python `int` handles this) | Larger whole numbers (VBA: up to +/- 2 billion). | Large counts, Excel row numbers. | | **Double** | `Dim z As Double` | `z = 3.14159` | Floating-point numbers (decimals). High precision. | Financial calculations, measurements. | | **String** | `Dim s As String` | `s = "Hello World"` | Text, alphanumeric data. | Names, messages, user input. | | **Boolean**| `Dim b As Boolean` | `b = True` | `True` or `False`. | Flags, conditional checks. | | **Range** | `Dim r As Range` | (N/A directly) | VBA-specific Excel cells/collections. | Interacting with worksheets. | | **Variant**| `Dim v As Variant` | (Python's dynamic typing) | VBA: Any data type. Flexible but less efficient. | Unknown or varying data types. | ### Conditional Logic and Operators Programs "think" via conditional statements (`True`/`False`). Operators build these conditions. #### Key Operators - **Equality:** `=` (VBA), `<>` (VBA); `==` (Python), `!=` (Python) - **Comparison:** ` `, `>=` (VBA/Python) - **Logical:** - **AND:** `And` (VBA), `and` (Python) - **OR:** `Or` (VBA), `or` (Python) - **NOT:** `Not` (VBA), `not` (Python) #### Precedence Rules (Order of Operations) 1. **Parentheses `()`** 2. **Arithmetic Operators** 3. **Concatenation Operator** (`&` in VBA) 4. **Comparison and Equality Operators** 5. **`Not` / `not`** 6. **`And` / `and`** 7. **`Or` / `or`** **Quiz Q/A (VBA & Python):** **Q1 (Python):** What is the result of `10 > 5 and not (4 == 4 or 2 0 And x ### Selection Structures: `If/ElseIf/Else` and `Select Case` Allow code to execute different blocks based on conditions. #### 1. VBA `If...Then – ElseIf...Then - Else` ```vba If condition1 Then ' Code block 1 ElseIf condition2 Then ' Code block 2 Else ' Code block 3 End If ``` - Only one block executes. `Then` is mandatory for `If`/`ElseIf`. #### 2. Python `if/elif/else` ```python if condition1: # Code block 1 elif condition2: # Code block 2 else: # Code block 3 ``` - Indentation defines blocks. Colons `:` are mandatory. #### 3. VBA `Select Case` Alternative to `If...ElseIf` for single variable/expression with multiple values/ranges. ```vba Select Case test_expression Case value1 ' Code for value1 Case value2, value3 ' Code for value2 or value3 Case Is > limit ' Code if expression > limit Case range_start To range_end ' Code if expression is within range Case Else ' Code if no match End Select ``` **Common Pitfalls:** - **VBA:** Forgetting `Then` keyword in `If`/`ElseIf`. - **Python:** Missing colon (`:`) after `if`, `elif`, `else`. Incorrect indentation for code blocks. **Quiz Q/A (VBA & Python):** **Q1 (VBA):** Identify syntax errors: `Dim x As Integer = 10` and `If x > 5` **A1:** `Dim x As Integer = 10` (VBA doesn't allow inline assignment). `If x > 5` (Missing `Then`). **Q2 (Python):** Identify syntax errors: `x = 10` and `if x > 5` (missing colon) and `print("x is greater than 5")` (missing indent). **A2:** Missing colon after `if x > 5`, and `print` statement is not indented. ### Repetition Structures (Loops) Automate repetitive tasks. #### 1. "For" Loops (Counter Controlled) Known number of iterations. **VBA `For...Next` Loop** ```vba For counter = start To end [Step increment] ' Code to repeat Next [counter] ``` - `Step increment` (optional, default 1). Use negative for countdown. - **Example (Factorial):** ```vba Dim n As Integer, factorial As Long, i As Integer n = 5: factorial = 1 For i = 1 To n: factorial = factorial * i: Next i ' Output: Factorial of 5 is: 120 ``` - **Example (Iterating Cells):** ```vba For i = 1 To 5 ' Loop rows 1 to 5 ws.Cells(i, 1).Value = "Item " & i Next i ``` **Python `for` Loop with `range()`** ```python for item in iterable: # Code to repeat ``` - `range(stop)`: `0` to `stop-1`. - `range(start, stop)`: `start` to `stop-1`. - `range(start, stop, step)`: `start` to `stop-1` with `step`. - **Example (List Iteration):** ```python products = ["Laptop", "Mouse"] for product in products: print(f"- {product}") # Output: - Laptop, - Mouse ``` - **Example (Sum Even Numbers):** ```python total_even = 0 for i in range(2, 11, 2): # Generates 2, 4, 6, 8, 10 total_even += i # Output: 30 ``` **Quiz Q/A (VBA & Python):** **Q1 (Python):** Output of `for i in range(5, 0, -1): print(i)`? **A1:** `5, 4, 3, 2, 1`. **Q2 (VBA):** Loop from 10 down to 1. Header? **A2:** `For i = 10 To 1 Step -1`. #### 2. "While" Loops (Condition Controlled) Unknown number of iterations; loop as long as condition is `True`. **VBA `Do While...Loop`** ```vba Do While condition ' Code to repeat (must change condition to False eventually) Loop ``` - Condition evaluated *before* each iteration. - **Example (Guessing Game):** ```vba targetNum = Int((10 * Rnd) + 1) Do While guess <> targetNum guess = InputBox("Guess 1-10:") ' ... validation and feedback ... Loop ' Output: Congratulations! You guessed X in Y attempts. ``` **Python `while` Loop** ```python while condition: # Code to repeat (must change condition to False eventually) ``` - Condition evaluated *before* each iteration. - **Example (Countdown):** ```python import time countdown = 3 while countdown > 0: print(countdown) time.sleep(1) countdown -= 1 # Output: 3, 2, 1, Launch! ``` **Quiz Q/A (While Loop Logic):** **Q3:** You need to repeatedly ask a user for a password until they enter the correct one. Which type of loop is most appropriate and why? **A3:** A `While` loop (or `Do While` in VBA). The number of attempts is unknown, so a condition-controlled loop that continues as long as the password is incorrect is ideal. #### 3. Infinite Loops Loop's termination condition is never met. - **Causes:** Forgetting to update counter, always `True` condition. - **Stop:** `Esc` (VBA), `Ctrl+C` (Python). **Quiz Q/A (Infinite Loop):** **Q4:** What would cause the following Python loop to be infinite? `x = 0; while x ### Controlling Loops and Nested Loops #### 1. Controlling Loop Execution Modify loop flow. - **VBA:** - `Exit For`: Exits current `For...Next` loop. - `Exit Do`: Exits current `Do...Loop`. - `Exit Sub`/`Exit Function`: Exits entire procedure. - `End`: Terminates all code (use with extreme caution). - **Python:** - `break`: Exits innermost `for`/`while` loop. - `continue`: Skips rest of current iteration, proceeds to next. - `sys.exit()`: Terminates entire script (requires `import sys`). **Quiz Q/A (VBA & Python):** **Q1 (Python):** Difference between `break` and `continue`? **A1:** `break` terminates the loop; `continue` skips current iteration. **Q2 (VBA):** What happens if `Exit Sub` is used inside a `For` loop? **A2:** The entire Sub procedure terminates immediately. #### 2. Nested Loops A loop inside another loop. Inner loop completes all iterations for each outer loop iteration. - **Performance:** Can be expensive for large iterations. - **Naming:** Use distinct counters (`i`, `j`, `row`, `col`). **Quiz Q/A (VBA & Python):** **Q1 (Python):** How many times does `print` run? `for a in range(2): for b in range(3): print(f"({a}, {b})")` **A1:** 6 times (2 * 3). **Q2 (VBA):** Structure nested `For` loops to fill a 5x3 Excel cell grid. **A2:** ```vba For rowNum = 1 To 5 For colNum = 1 To 3 ' Fill cell (rowNum, colNum) Next colNum Next rowNum ``` ### Lab and Demo Insights Practical application and common pitfalls from class. #### VBA Specifics - **`Option Explicit`**: **MUST** be at module top. Forces variable declaration, prevents typos. - **Variable Declaration**: Choose appropriate types (`Long` for Excel indices). - **`InputBox`**: Returns `String`. *Always* validate `IsNumeric()` and convert (`CInt()`, `CDbl()`). - **`Range` Object**: Use `Set` for `Range("A1")`, `Cells(row, col)`. - **Debugging**: `Debug.Print` to Immediate Window (Ctrl+G). - **Error Handling**: `On Error GoTo` for robust input. #### Python Specifics - **Indentation**: **CRITICAL**. Defines code blocks. Incorrect leads to `IndentationError`. - **Colons (`:`):** Required after `if`, `for`, `while`, etc. Missing causes `SyntaxError`. - **`input()`**: Returns `string`. *Always* convert (`int()`, `float()`) and use `try-except` for `ValueError`. - **`range()`**: Predict loop outputs (e.g., `range(stop)` is `0` to `stop-1`). - **String Formatting**: `f-strings` (`f"Value: {var}"`). #### Common Errors & Debugging - **Infinite Loops**: Ensure termination condition is met. - **Off-by-One Errors**: Check loop bounds (`range(1, 5)` vs `range(1, 6)`). - **Logical Errors**: Use print/Debug.Print to trace variable values. - **Precedence Issues**: Use parentheses `()` for clarity. - **Type Mismatch (VBA)**: Assigning incorrect data type. - **`Set` Keyword (VBA)**: Forgetting `Set` for objects. **Quiz Q/A (VBA):** **Q1:** Why is `Option Explicit` a best practice? **A1:** Forces variable declaration, catches typos, prevents bugs. **Q2:** How to skip even numbers in a `For` loop but continue? **A2:** Use `If i Mod 2 = 0 Then GoTo SkipPrint` or `If/Else` structure. **Quiz Q/A (Python):** **Q1:** Which line causes `SyntaxError` due to missing colon? ```python x = 10 if x > 5 print("Hello") ``` **A1:** `if x > 5` (missing `:`) **Q2:** How to safely convert `input()` to a number? **A2:** Use `try-except` block with `int()` or `float()` to handle `ValueError`.