In today’s class, we will spend time working in groups on a small-scale project that reemphasizes what we have learnt so far.

Coding is like math. Practice is definitely needed to be better at it. There are a lot of algorithms and formulas are available online which will lower the difficulty level, and the pro tip is you should keep a record of what seems important to you in terms of code snippets and examples.

Proficiency with code is fundamental with data analysis because coding is akin to a chef’s cooking skill, and data is akin to raw ingredients. Creativity and intuition with data will grow exponentially when you reach a certain level of coding skill.

Also, we will take this time to explore how you can learn to collaborate with each other in our upcoming projects.

Students Do: Star Counter

VBA Formatting

We have been dealing with numbers and data through our VBA scripts, but VBA scripts can do more than that. It can also format the spreadsheet for design.

	Sub formatter()
		' Set the Font color to Red
		Range("A1").Font.ColorIndex = 3 ' Go to for the grid

		' Set the Cell Colors to Red
		Range("A2:A5").Interior.ColorIndex = 3


Go to for the color index in Excel. You don’t have to memorize this.

Students Do: VBA Grade Book

Partners Do: Checkerboard


Remember we discussed that we can use Mod to identify even or odd numbers? This is the same logic to identify even or odd rows.

And this is another nested loop example. Refer to your previous activity files for references.

Looking to the Next Cell

When looping through rows and/or columns, it can be necessary to check for changes and then run some alternative code based on those changes.

  • For example, you might want to count how many cells in a column contain a specific value. One way to do this is to loop through the cells, comparing each cell to the one below it, and add 1 to a counter if both cells match.
  • This is especially relevant to spreadsheets, as the values in different cells can be part of a formula which you want to use to solve problems.

Students Do: Card Checker – Cell Comparison



	Dim row_num As Integer
	row_num = 2

	Range("A" & row_num).Value = 5 ' It will show up as A2

Everyone Do: U.S. Census – Part 1

You’ll see census data being repeated throughout the course.

Students should place one person from their group in charge of writing code. This is to promote collaboration and ensures that everyone is working at the same general pace.

  • This is a prelude to your upcoming project work where everybody has to contribute to the final deliverable and presentation for each projects.
  • Not everybody is your team is going to be an expert in everything. Work out the strengths of your team mates and organize yourselves so that you can deliver the project.
    • When you work in a corporate setting, this is what happens on a daily basis anyway. In life, it is okay to fail fast, but the idea is to quickly recover and move on towards the goal and outcome you want for your project.
    • Do be patient with each other as everybody is on a learning journey.
  • Take the opportunity to learn from each other.

This activity will be divided into parts, and we will review each part as the class progresses.

Open up census_data_2016-2019_pt1.xlsm and read the for instructions.


  • In this activity, you will iterate through worksheets to succeed.
    • You can use the expression to iterate through worksheets: For Each ws In Worksheets
    • For Each is a special for loop that doesn’t require you know how many items you’re looping.
    • You can reference the entities of a worksheet, such as ws.Range("A2")

Everyone Do: U.S. Census – Part 2


  • In this activity, you will create a sheet within the file using using: Sheets.Add.Name = "Combined_Data"
    • You can move the worksheet programatically using this expression: Sheets("Combined_Data").Move Before:=Sheets(1)
    • You can reference a sheet as a variable (more accurately, an object), and make changes to it as though you’re editing the sheet directly.

Next Lesson Prep

You’ll need to install Git Bash for PC users, and Mac users will use the that is innate within Macs.