10/15 Day #3 VBA and Macros

[ajlee12]

Morning Exercise Review

TimBits

Using IFS to check multiple conditions

Good reference for IFS: https://exceljet.net/excel-functions/excel-ifs-function

Go through a series of conditions. If a condition is true, the value that follows the condition is returned.

Note that for all these examples, we use A1 cell as the reference.

IFS(MOD(A1, 15)=0,"TimBits", MOD(A1, 3)=0, "Tim", MOD(A1, 5)=0, "Bits")

If no conditions are met, it returns an NA error.

To avoid that, wrap the whole thing in an IFERROR and return a default value in case of errors.

IFERROR(IFS(MOD(A1, 15)=0,"TimBits", MOD(A1, 3)=0, "Tim", MOD(A1, 5)=0, "Bits"), A1)

• In this case, the default value is the original value in the cell.

Another way of supplying default value: Add another set of condition & return value, where that condition evaluates to TRUE all the time.

IFS(MOD(A1, 15)=0,"TimBits", MOD(A1, 3)=0, "Tim", MOD(A1, 5)=0, "Bits", TRUE, A1)

Using nested IF statements

IF(MOD(A1, 15)=0, "TimBits", IF(MOD(A1, 3)=0, "Tim", IF(MOD(A1, 5)=0, "Bits")))

• Note that this code may be harder to read than the one using IFS...

VBA (Visual Basic for Applications)

Can be used to automate tasks across the Microsoft suite.

• Some quick videos to get up to speed (6-8 min. each):
  • Part 1/4: buttons and Macros
  • Part 2/4: changing cell values
  • Part 3/4: positioning and offset
  • Part 4/4: loops

DEVELOPER Tab --> Visual Basic --> You'll see a Module (Module 1 by default)

• Check out the first video above if you cannot find the DEVELOPER tab.

• Option Explicit

• Option Base 1

  • Arrays start at position 1

Sub (sub procedure)

Reference: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/sub-statement

Example:

Sub TimBits()
  Range("A1").Value = "HELLO WORLD"
End Sub

• Note that TimBits is the name we gave to this Sub.
• We also need to mark the end of the Sub with End Sub.

Execute the Macro

• Place your cursor anywhere inside the sub procedure, and click Play.
• Or you can go into Macros and run the Macro of your choice.
• Or you can add a button, assign a Macro to it, and then you'll be able to execute the Macro by clicking the button.

Declare a variable

For example, we can declare a variable RowNum by writing:

Dim RowNum As Integer

• As Integer is needed because of our Option Explicit.

Iterate with a for-loop

Example:

For RowNum = 1 to 100
  '(task to be done for each iteration)
Next

RowNum's value starts from 1, and changes to 2, and to 3...and finally to 100.

Print

Debug.Print ([thing_you_want_to_print])

Turn the code off (i.e. comment it out)

Add ' before the lines of code you want to comment out. The line of code that's commented out will not run.

' Sub TimBits() <== This is a comment>
Sub TimBit() '<== not comment; will run.

Keywords in VBA

These are purple, and they are case-sensitive. e.g. For, Sub, Next, End Sub, As, Range, etc.

Reference: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/keywords-by-task

Conditional Logic (If, Else)

Example:

If RowNum < 10 Then (execute some task)

ElseIf RowNum < 20 Then (execute some task)

Else (execute some task)

End If

• Note that the first ones take priority.

• The Else is the catch-all (i.e. the default things to do).

Styling & Formatting with VBA

See image below for reference.

Google is your best friend... Even experienced practitioners can't memorize all the properties and values.

• e.g. I want to know how to set a color for a cell. So I searched "excel vba set cell color". And the first Google result gave me an answer.

StackOverflow.com is also an awesome place to search for answers, and it's great for all programming languages.

Data Types in VBA

The Set keyword: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/set-statement

• The Set keyword is used to create new objects, creating a new Range, for example.

This is a good, short video on how to use Dim and Set.

Exmple: Reference an object in a worksheet

Dim StudentsWorksheet As Worksheet
Dim StudentsTable As Object

Set StudentsWorksheet = Sheets("Students")
Set StudentsTable = StudentsWorksheet.ListObjects("StudentsTable")

*Hint: It's a common convention to group all Dims before Sets.

Confirm that we retrieved the correct object:

Debug.Print(StudentsTable.Range.Rows.Count)

• ^^ See if you get the correct number of rows.

Range covers the entire range including headers.

• But what if we don't want the headers?
• Good resource obtained by Google searching "vba table body".

Dim StudentsBodyRange As Range
Set StudentsBodyRange = StudentsTable.DataBodyRange

*Hint: If you're targeting specific cells/columns/etc., make sure you have the referenced workbook in focus.

Make a random student selector

1. First, get the number of students by counting the rows.

   Dim StudentsCount As Integer
   StudentsCount = StudentsBodyRange.Rows.Count

2. Generate a random number between 1 and StudentsCount.

   • Rnd generates a random decimal number between 0 and 1.
   • Multiply Rnd by StudentsCount, and we get a random decimal number between 1 and 64.999*.
   • So we + 1 to allow it to go up to 65.
   • Use Int to round it to an integer.
   • Save this random number to a variable, RandomIndex.

   RandomIndex = Int(Rnd * StudentsCount) + 1

3. Select a random student row from the table.

   StudentsBodyRange.Rows(RandomIndex).Select