The Aggie Advisors Scenario

You work in the advising office at Mays Business School. Each semester a new group of students is accepted into the Professional Program in Accountancy (PPA). Your job is to build a macro that processes the incoming applicant list, adds accepted students to the master student roster, and updates the summary reports.

This scenario mirrors the Project Demo from class intentionally. The difference is that you build it one module at a time — each module's Exam Challenge is one piece of the complete macro. By the time you finish all nine modules, you have built the whole thing yourself.

No downloadable file is provided. Copy the data tables below and paste them into your own Excel workbook following the worksheet setup instructions.

Workbook Setup Instructions

Create a new Excel file and save it as Aggie_Advisors_Practice.xlsm (macro-enabled workbook). Create the following sheets in this order:

Sheet 1 — Instructions

Leave this sheet for your own notes about the project.

Sheet 2 — Applicant Information

• Table name: ApplicantData
• Column headers (row 1): StudentID | LastName | FirstName | TAMU_GPR | Grade229 | Grade230 | Grade327 | FinalDecision
• Copy the 30 applicant records from the data table below into rows 2–31

Sheet 3 — Student Information

• Table name: PPAData
• Column headers (row 1): StudentID | Group | TrackCode | LastName | FirstName | UndergradGPR | GradGPR | Advisor | Employer1 | Employer2 | RecruitingStatus
• This sheet will have 85 existing student records when fully set up. For exercises, you can start with just the header row and add records as needed.

Sheet 4 — Advisor Data

• Table name: AdvisorData
• Column headers: TrackCode | TrackName | Advisor
• Enter these 4 rows:

Sheet 5 — Group Summary

• Type 30 into cell B2 as the starting group number, then select B2 and create a Named Range called CurrentGroup pointing to that cell.

Sheet 6 — Valid Values

• Column A header: Group Number
• Enter groups 30 through 34 in rows 2–6
• In cell D2, type the word Accept (without quotes). Then select cell D2 and create a Named Range called AcceptValue pointing to that cell. This is the value the macro compares against to identify accepted applicants.

Applicant Information Data

Copy this data and paste it into cell A1 of your Applicant Information sheet. Press Ctrl+V — Excel will split the columns automatically. Then format the data as a table named ApplicantData.

Module-by-Module Guide

Each module's Exam Challenge adds one piece to the complete macro. Click any module below to jump to its section, or go directly to the module page to review the concept.

The Complete Macro

Once you have completed all nine modules, here is the complete AddNewStudents macro you will have built. Each section is labeled with which module introduced it.

Option Explicit
' ACCT 628 - Sanders
' Aggie Advisors: New Student Onboarding
' Module: Onboarding

Sub AddNewStudents()

    ' ── MODULE 2 ────────────────────────────────────────────────
    ' Speed settings
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    ' ── MODULE 3 ────────────────────────────────────────────────
    ' DEFINE Variables
    Dim NewGroup As Integer
    Dim NumberAccepted As Integer
    Dim StudentID As Long
    Dim GPR As Double
    Dim LastName As String
    Dim FirstName As String

    ' ── MODULE 2 ────────────────────────────────────────────────
    ' Check for records
    Sheets("Applicant Information").Select
    Range("A2").Select

    If ActiveCell = "" Then
        MsgBox "No applicants found. Please enter applicant data first."
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        Exit Sub
    End If

    ' Prompt for group number
    NewGroup = InputBox("Enter the new group number:")

    ' ── MODULE 1 ────────────────────────────────────────────────
    ' Add NewGroup to Valid Values
    Sheets("Valid Values").Select
    Range("A2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell = NewGroup
    ActiveCell.Offset(1, 0).Select

    ' Copy Applicant Information as backup
    Sheets("Applicant Information").Select
    Sheets("Applicant Information").Copy Before:=Sheets(6)
    ActiveSheet.Name = "Applicant Information Group " & NewGroup

    ' ── MODULE 6 ────────────────────────────────────────────────
    ' Navigate to first empty row on Student Information
    Sheets("Student Information").Select
    Range("A2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("PPAData[[#Headers],[StudentID]]").Select

    ' Navigate to first applicant record
    Sheets("Applicant Information").Select
    Range("A2").Select

    ' ── MODULE 4 ────────────────────────────────────────────────
    ' Loop through all applicants
    Do Until ActiveCell = ""

        ' ── MODULE 7 ──────────────────────────────────────────
        ' Only process accepted students
        If ActiveCell.Offset(0, 7) = Range("AcceptValue") Then

            ' ── MODULE 3 ──────────────────────────────────────
            ' POPULATE variables
            StudentID = ActiveCell
            LastName  = ActiveCell.Offset(0, 1)
            FirstName = ActiveCell.Offset(0, 2)
            GPR       = ActiveCell.Offset(0, 3)

            ' ── MODULE 6 ──────────────────────────────────────
            ' SELECT Student Information and DISPLAY
            Sheets("Student Information").Select

            ActiveCell            = StudentID
            ActiveCell.Offset(0, 1) = NewGroup
            ActiveCell.Offset(0, 2) = "U"
            ActiveCell.Offset(0, 3) = LastName
            ActiveCell.Offset(0, 4) = FirstName
            ActiveCell.Offset(0, 5) = GPR
            ActiveCell.Offset(0, 6) = 0
            ActiveCell.Offset(0, 7) = "Dr. Williams"
            ActiveCell.Offset(0, 8) = "Unknown"
            ActiveCell.Offset(0, 9) = "Unknown"
            ActiveCell.Offset(0, 10) = "Unknown"

            ' ── MODULE 4 ──────────────────────────────────────
            NumberAccepted = NumberAccepted + 1

            ' MOVE to next empty student row
            ActiveCell.Offset(1, 0).Range("PPAData[[#Headers],[StudentID]]").Select

            ' RETURN to applicant list
            Sheets("Applicant Information").Select

        End If

        ' ALWAYS move to next applicant (accepted or denied)
        ActiveCell.Offset(1, 0).Range("ApplicantData[[#Headers],[StudentID]]").Select

    Loop

    ' ── MODULE 1 ────────────────────────────────────────────────
    ' Update Group Summary and refresh
    Sheets("Group Summary").Select
    Range("CurrentGroup") = NewGroup
    ActiveWorkbook.RefreshAll

    ' ── MODULE 2 ────────────────────────────────────────────────
    ' Restore settings and display completion message
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    MsgBox NumberAccepted & " students added and reports updated for Group " & NewGroup

End Sub

Putting It All Together

Once you've completed all nine modules, you have built the complete AddNewStudents macro. Run the full macro with group number 35.

Expected result:

• 20 new students added to Student Information (rows 87–106)
• All new students: TrackCode = "U", Advisor = "Dr. Williams", GradGPR = 0
• Group Summary updated: CurrentGroup = 35
• MsgBox: "20 students added and reports updated for Group 35"

If your results differ, return to the module that covers the section that's wrong and use F8 to debug it. The module guide above links directly to each module's Exam Challenge.