This VBA (Visual Basic for Applications) code performs the following...

August 28, 2025 at 05:56 PM


Sub RecalculateTopMatches()
    Dim wsEval As Worksheet
    Dim wsTop As Worksheet
    Dim lastRow As Long
    Dim mentorDict As Object
    Set mentorDict = CreateObject("Scripting.Dictionary")
    
    Set wsEval = ThisWorkbook.Sheets("Evaluation")
    Set wsTop = ThisWorkbook.Sheets("Top Matches")
    
    ' Clear previous data
    wsTop.Cells.ClearContents
    wsTop.Cells(1, 1).Value = "Mentor ID"
    wsTop.Cells(2, 1).Value = "Top Mentee"
    wsTop.Cells(3, 1).Value = "Score"
    
    lastRow = wsEval.Cells(wsEval.Rows.Count, 1).End(xlUp).Row
    
    Dim i As Long
    For i = 2 To lastRow
        Dim mentor As String, mentee As String, score As Double
        mentor = wsEval.Cells(i, 1).Value
        mentee = wsEval.Cells(i, 2).Value
        score = wsEval.Cells(i, 3).Value
        
        If Not mentorDict.exists(mentor) Then
            mentorDict.Add mentor, Array(mentee, score)
        Else
            If score > mentorDict(mentor)(1) Then
                mentorDict(mentor) = Array(mentee, score)
            End If
        End If
    Next i
    
    Dim col As Integer: col = 2
    Dim key As Variant
    For Each key In mentorDict.Keys
        wsTop.Cells(1, col).Value = key
        wsTop.Cells(2, col).Value = mentorDict(key)(0)
        wsTop.Cells(3, col).Value = mentorDict(key)(1)
        col = col + 1
    Next key
End Sub

This VBA (Visual Basic for Applications) code performs the following operations:

Purpose:

The Sub RecalculateTopMatches() procedure identifies the "top mentee" for each mentor based on the highest score. This information is taken from a worksheet named "Evaluation" and the results are written to another worksheet named "Top Matches".

Step-by-Step Explanation:

Variable Initialization:
- wsEval and wsTop: References for the "Evaluation" and "Top Matches" worksheets, respectively.
- lastRow: Tracks the last row of data in the "Evaluation" sheet.
- mentorDict: A dictionary object (key-value storage) for mapping Mentor IDs to their top mentees and highest scores.
Clearing and Setting Up the "Top Matches" Sheet:
- Clears all existing contents in the "Top Matches" worksheet using wsTop.Cells.ClearContents.
- Assigns the title row in the "Top Matches" sheet:
  - Row 1: "Mentor ID"
  - Row 2: "Top Mentee"
  - Row 3: "Score"
Identifying "Top Mentees" in the "Evaluation" Sheet:
- Determines the last row of data in the "Evaluation" worksheet (lastRow).
- Iterates through each row of data in the "Evaluation" sheet (starting from row 2 up to the last row).
- For every row, the following columns are extracted:
  - Column 1: Mentor ID
  - Column 2: Mentee Name
  - Column 3: Score
- Checks the dictionary to see if the Mentor ID already exists:
  - If it doesn't exist, the mentorDict is updated with the current mentee and score.
  - If it exists, compares the stored score with the current score. If the current score is higher, updates the dictionary for that mentor.
Writing the Results to the "Top Matches" Worksheet:
- Iterates through all keys (Mentor IDs) in the dictionary.
- For each key, writes:
  - Row 1: Mentor ID
  - Row 2: Top mentee for that mentor
  - Row 3: Highest score achieved by that mentee for the mentor.
- Each new Mentor ID occupies a new column, starting from column 2.

Output:

After this macro runs:

The "Top Matches" worksheet will contain:
- Row 1: Mentor IDs (starting from column 2).
- Row 2: Names of the top mentee for each mentor.
- Row 3: Highest scores for the top mentees.

Use Case:

This code is useful when handling evaluation data to identify the best mentee (based on scores) for each mentor in a structured manner.

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr