Today is a reinforcement of what we have learned in the previous lesson, and how you can be better at coding in order to be more effective in data analysis.

With what you learned in the previous lesson and today, you’ll be able to automate and provide deeper analysis using VBA scripts within Excel. It is a prelude to using pandas for Python.

Throughout the activities, the school notes show you can use MsgBox function to test your logic by outputting some values. I recommend using Debug.Print function instead to speed things up.

Students Do: Warm-up Activity

You’ll write a VBA script to run a budget checker in Excel.

There are 3 parts to the problem:

  1. Calculate the total amount after adding in the fee, and enter the value in the “Total” cell.
  2. Create a message box to alert the user if the total amount, including the fee, is within or over budget.
  3. (Challenge): If the total is over budget, correct the price so it fits within the user’s budget. Be sure to round down!
    • For example: If the user’s budget is 100 and the fees are 15%, the max price should be 86.
    • For example: If the user’s budget is 100 and the fees are 15%, the max price should be 86.

Hints:

  • Break up the problem into smaller steps.
  • Look at old code! You got this!

For Loops

For loops are repeated actions in which you design and control. Lets open up the basic_for_loop_solution.xslm to take a look.

Notes:

  • The cells coordinates start from the top left of the screen, where Cell(row,column).
  • If you’re unsure of how your looping logic behaves, start small.
  • Looping is essential even for Python, as you may need to apply some form of process or math over values as part of your data analysis.

Students Do: Chicken Nuggets Loops

Look at the README.md to start your activity in the 03-Stu_ChickenNuggets-ForLoop folder.

Loop conditionals

Let’s see how we can introduce conditionals in loops. Open up modulus_soultion.xslm for reference.

Students Do: Fizz Buzz

Look at the README.md to start your activity in the 05-Stu_FizzBuzz folder. In this activity, you’ll work on a popular logic problem that countless coders have encountered in technical interviews.

Hint

Use the Mod operator.

The Mod operator is taking the remainder after dividing. For example: 3 % 2 = 1, because the remainder is 1.

You can find out by using this expression on the Google search bar: 3 % 2. In VBA, modulus is represented by Mod instead of %.

Why is Mod great? One of the strengths is it can help you create even and odd numbers easily. With this, you can programmatically design a chess board.

Students Do: Lotto Search

Look at the README.md to start your activity in the 06-Stu_Lotto-AdvancedForLoops folder. Open up lotto_numbers.xlsm in your folder.

Hints

  • Loop through the column to scan for the winning numbers.
  • Using conditionals, extract the first and last name of the winners if there is a match.
  • For the bonus question, assume that the numbers are in sequential order from the top of the column.

Nested For Loops

Nested For Loops are conceptually simple, but can be complex to debug and execute. Many of the sorting algorithims do use nested for loops to iterate and sort.

Let’s open up nested_for_loop_solution.xlsm for our discussion.

Students Do: Crypto Kennel

Look at the README.md to start your activity in the 08-Stu_CryptoKennel-NestedForLoops folder.

Hints

You may want to create a backup of your spreadsheet as your macro will write over the contents.