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

Let’s open up activity 1’s README.md in your student’s Gitlab repo to get started.

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 https://learn.microsoft.com/en-us/office/vba/api/excel.colorindex for the grid

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

Hint

Go to https://learn.microsoft.com/en-us/office/vba/api/excel.colorindex for the color index in Excel. You don’t have to memorize this.

Students Do: VBA Grade Book

Look at the README.md to start your activity in the 03-Stu_Gradebook-Conditionals folder.

Partners Do: Checkerboard

Look at the README.md to start your activity in the 04-Stu_Checkerboard-CodingLogic folder.

Hint

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

Look at the README.md to start your activity in the 06-Stu_CreditCardChecker-CellComparison folder.

Hint

• Column B has this formula here: =CONCATENATE("*****", RANDBETWEEN(100000, 999999))
  • It is just a random number generator, and should be ignored in your code.
  • This is simulating data obfuscation of credit card numbers.
• You can use a text concatenation operator so that you can dynamically loop through ranges: https://support.microsoft.com/en-us/office/overview-of-formulas-34519a4e-1e8d-4f4b-84d4-d642c4f63263#:~:text=Use%20the%20ampersand%20(%26)%20to,Meaning

Example

	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

For the rest of the class, we will work in small groups to create a VBA script that takes an Excel workbook with multiple sheets of US Census data, formatting each sheet to improve readability, and then combining the data into a single table.

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 README.md for instructions.

Hints

• 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

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

Hints

• 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 Terminal.app that is innate within Macs.