This VBA code defines a subroutine named `RecalculateScores`, which is...

August 28, 2025 at 06:03 PM


Sub RecalculateScores()
    Dim wsMentor As Worksheet, wsMentee As Worksheet, wsEval As Worksheet
    Dim mentorRow As Long, menteeRow As Long, evalRow As Long
    Dim score As Double
    Dim mentorIndustry As String, mentorComm As String, mentorAvail As String, mentorPersonality As String, mentorSkills As String
    Dim menteeIndustry As String, menteeComm As String, menteeAvail As String, menteePersonality As String, menteeGoals As String
    Dim skill As Variant, matchFound As Boolean

    ' Define weights
    Const WEIGHT_INDUSTRY As Double = 0.3
    Const WEIGHT_SKILLS As Double = 0.3
    Const WEIGHT_COMM As Double = 0.15
    Const WEIGHT_AVAIL As Double = 0.15
    Const WEIGHT_PERSONALITY As Double = 0.1

    Set wsMentor = ThisWorkbook.Sheets("Mentor Input")
    Set wsMentee = ThisWorkbook.Sheets("Mentee Input")
    Set wsEval = ThisWorkbook.Sheets("Evaluation")

    ' Clear old evaluation data
    wsEval.Range("A2:C" & wsEval.Cells(wsEval.Rows.Count, "A").End(xlUp).Row).ClearContents
    evalRow = 2

    For mentorRow = 2 To wsMentor.Cells(wsMentor.Rows.Count, "A").End(xlUp).Row
        mentorIndustry = Trim(wsMentor.Cells(mentorRow, 2).Value)
        mentorSkills = wsMentor.Cells(mentorRow, 3).Value
        mentorComm = Trim(wsMentor.Cells(mentorRow, 4).Value)
        mentorAvail = Trim(wsMentor.Cells(mentorRow, 5).Value)
        mentorPersonality = Trim(wsMentor.Cells(mentorRow, 6).Value)

        For menteeRow = 2 To wsMentee.Cells(wsMentee.Rows.Count, "A").End(xlUp).Row
            menteeIndustry = Trim(wsMentee.Cells(menteeRow, 2).Value)
            menteeGoals = wsMentee.Cells(menteeRow, 3).Value
            menteeComm = Trim(wsMentee.Cells(menteeRow, 4).Value)
            menteeAvail = Trim(wsMentee.Cells(menteeRow, 5).Value)
            menteePersonality = Trim(wsMentee.Cells(menteeRow, 6).Value)

            score = 0

            ' Industry match
            If LCase(mentorIndustry) = LCase(menteeIndustry) Then
                score = score + WEIGHT_INDUSTRY
            End If

            ' Skills vs Goals match
            matchFound = False
            For Each skill In Split(mentorSkills, ",")
                If InStr(1, LCase(menteeGoals), LCase(Trim(skill))) > 0 Then
                    matchFound = True
                    Exit For
                End If
            Next skill
            If matchFound Then
                score = score + WEIGHT_SKILLS
            End If

            ' Communication match
            If LCase(mentorComm) = LCase(menteeComm) Then
                score = score + WEIGHT_COMM
            End If

            ' Availability match
            If LCase(mentorAvail) = LCase(menteeAvail) Then
                score = score + WEIGHT_AVAIL
            End If

            ' Personality match
            If LCase(mentorPersonality) = LCase(menteePersonality) Then
                score = score + WEIGHT_PERSONALITY
            End If
            ' Write to Evaluation sheet
            wsEval.Cells(evalRow, 1).Value = wsMentor.Cells(mentorRow, 1).Value
            wsEval.Cells(evalRow, 2).Value = wsMentee.Cells(menteeRow, 1).Value
            wsEval.Cells(evalRow, 3).Value = Round(score * 100, 0)
            evalRow = evalRow + 1
        Next menteeRow
    Next mentorRow
End Sub

This VBA code defines a subroutine named RecalculateScores, which is designed to calculate compatibility scores between mentors and mentees based on specific criteria. Here's a detailed explanation of the code's functionality:

Overview:
- This program compares data from two worksheets, "Mentor Input" and "Mentee Input," and writes the matching scores to a third worksheet called "Evaluation."
- Each mentor-mentee pair is evaluated against several compatibility metrics, and a score is calculated based on weighted contributions of different matching aspects.
Data Setup:
- wsMentor, wsMentee, and wsEval are worksheet objects initialized to represent the "Mentor Input," "Mentee Input," and "Evaluation" sheets within the current workbook.
- The procedure also defines variables to represent the rows and specific fields for mentors and mentees, as well as weights for various matching dimensions.
Weights Used:
- Industry match: 30% (0.3)
- Skills vs. Goals match: 30% (0.3)
- Communication style match: 15% (0.15)
- Availability match: 15% (0.15)
- Personality trait match: 10% (0.1)
Clearing Existing Data:
- Before recalculating the scores, the code clears old evaluation data from rows 2 to the last used row in the "Evaluation" worksheet.
Iterating Over Mentors and Mentees:
- For each mentor (starting from row 2 in the "Mentor Input" sheet), the program goes through all mentees (starting from row 2 in the "Mentee Input" sheet).
- The relevant data for each mentor and mentee is read into variables (industry, communication style, availability, skills/goals, personality).
Scoring Logic:
- Compatibility is calculated based on the following matches:
  - Industry Match: Compares mentor and mentee industries.
  - Skills vs Goals Match: Checks if any of the mentor's skills (split by commas) match the mentee's goals.
  - Communication Style Match: Compares communication preferences of mentor and mentee.
  - Availability Match: Compares availability preferences.
  - Personality Match: Compares personalities.
- A score is incrementally updated based on weighted contributions from each dimension.
Storing Results:
- For each mentor-mentee pair, the results are written to the "Evaluation" sheet:
  - Column A: Mentor name (from column 1 of "Mentor Input").
  - Column B: Mentee name (from column 1 of "Mentee Input").
  - Column C: Compatibility score as a percentage (rounded to the nearest whole number).
Each evaluation result is written row-by-row, incrementally.
How Scores Are Calculated:
- If all attributes match perfectly, the maximum possible score is 100%.
- Partial matches reduce the score based on the weights assigned to each dimension.

Purpose of the Code:

This program is essentially a mentor-mentee matching algorithm designed for situations where you want to pair mentors with mentees based on compatibility in multiple aspects such as industry, skills, communication, availability, and personality. The output is a scored evaluation of each possible mentor-mentee pairing, saved in the "Evaluation" sheet.

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr