This code defines two components: 1. A helper function (`MBTICompatible`): ...

September 2, 2025 at 02:53 PM


Function MBTICompatible(mbti1 As String, mbti2 As String) As Boolean
    If Len(mbti1) <> 4 Or Len(mbti2) <> 4 Then
        MBTICompatible = False
        Exit Function
    End If
    Dim i As Integer, matchCount As Integer
    matchCount = 0
    For i = 1 To 4
        If Mid(mbti1, i, 1) = Mid(mbti2, i, 1) Then
            matchCount = matchCount + 1
        End If
    Next i
    MBTICompatible = (matchCount >= 2)
End Function

Sub EvaluateMatchesWeighted()
    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 maxScore As Double: maxScore = 100
    
    ' Define weights
    Dim wInterests As Double: wInterests = 30
    Dim wComm As Double: wComm = 20
    Dim wAvail As Double: wAvail = 20
    Dim wStrengths As Double: wStrengths = 20
    Dim wMBTI As Double: wMBTI = 10
    
    Set wsMentor = ThisWorkbook.Sheets("Mentor Input")
    Set wsMentee = ThisWorkbook.Sheets("Mentee Input")
    
    ' Create or clear Evaluation sheet
    On Error Resume Next
    Set wsEval = ThisWorkbook.Sheets("Evaluation")
    If Not wsEval Is Nothing Then
        wsEval.Cells.Clear
    Else
        Set wsEval = ThisWorkbook.Sheets.Add
        wsEval.Name = "Evaluation"
    End If
    On Error GoTo 0
    
    wsEval.Range("A1:H1").Value = Array("Mentor", "Mentee", "Match Score (%)", _
        "Interests (30%)", "Communication (20%)", "Availability (20%)", _
        "Strengths (20%)", "MBTI (10%)")
    evalRow = 2
    
    For mentorRow = 2 To wsMentor.Cells(wsMentor.Rows.Count, 1).End(xlUp).Row
        Dim mName As String, mInterests As String, mMBTI As String, mStrengths As String, mComm As String, mAvail As String
        mName = wsMentor.Cells(mentorRow, 1).Value
        mInterests = wsMentor.Cells(mentorRow, 3).Value
        mMBTI = wsMentor.Cells(mentorRow, 4).Value
        mStrengths = wsMentor.Cells(mentorRow, 5).Value
        mComm = wsMentor.Cells(mentorRow, 6).Value
        mAvail = wsMentor.Cells(mentorRow, 7).Value
        
        For menteeRow = 2 To wsMentee.Cells(wsMentee.Rows.Count, 1).End(xlUp).Row
            Dim tName As String, tInterests As String, tMBTI As String, tStrengths As String, tComm As String, tAvail As String
            tName = wsMentee.Cells(menteeRow, 1).Value
            tInterests = wsMentee.Cells(menteeRow, 3).Value
            tMBTI = wsMentee.Cells(menteeRow, 4).Value
            tStrengths = wsMentee.Cells(menteeRow, 5).Value
            tComm = wsMentee.Cells(menteeRow, 6).Value
            tAvail = wsMentee.Cells(menteeRow, 7).Value
            
            score = 0
            Dim sInterests As Double, sComm As Double, sAvail As Double, sStrengths As Double, sMBTI As Double
            sInterests = IIf(mInterests = tInterests, wInterests, 0)
            sComm = IIf(mComm = tComm, wComm, 0)
            sAvail = IIf(mAvail = tAvail, wAvail, 0)
            sStrengths = IIf(mStrengths = tStrengths, wStrengths, 0)
            sMBTI = IIf(MBTICompatible(mMBTI, tMBTI), wMBTI, 0)
            
            score = sInterests + sComm + sAvail + sStrengths + sMBTI
            
            wsEval.Cells(evalRow, 1).Value = mName
            wsEval.Cells(evalRow, 2).Value = tName
            wsEval.Cells(evalRow, 3).Value = score
            wsEval.Cells(evalRow, 4).Value = sInterests
            wsEval.Cells(evalRow, 5).Value = sComm
            wsEval.Cells(evalRow, 6).Value = sAvail
            wsEval.Cells(evalRow, 7).Value = sStrengths
            wsEval.Cells(evalRow, 8).Value = sMBTI
            evalRow = evalRow + 1
        Next menteeRow
    Next mentorRow
End Sub

This code defines two components:

A helper function (MBTICompatible):
- It takes two 4-character MBTI (Myers-Briggs Type Indicator) strings (mbti1 and mbti2) and determines if they are compatible based on how many characters match.
- If either of the MBTI strings is not exactly 4 characters long, it returns False.
- It counts the number of matching characters at the same positions across the two MBTI strings.
- If at least 2 characters match, the function returns True (indicating compatibility). Otherwise, it returns False.

A subroutine (EvaluateMatchesWeighted):
- It creates a scored evaluation of how well mentors and mentees match across several criteria. The scores are output in a new or existing "Evaluation" sheet.

Detailed Explanation of the Subroutine:

Setup and environment preparation:
- It assumes the existence of two sheets in the workbook:
  - "Mentor Input" containing mentor data.
  - "Mentee Input" containing mentee data.
- A new "Evaluation" sheet is created (or cleared if it exists). Evaluation results are written here.
- The "Evaluation" sheet has 8 columns: Mentor, Mentee, Match Score (%), and scores for criteria (Interests, Communication, Availability, Strengths, MBTI).
Weights:
- Each criterion has a predefined weight that contributes to the total score:
  - Interests (30%), Communication (20%), Availability (20%), Strengths (20%), and MBTI Compatibility (10%).
Mentor-Mentee Pair Matching:
- The subroutine loops through all rows in the "Mentor Input" sheet to extract mentor data.
- Within this loop, it iterates over all rows in the "Mentee Input" sheet to extract mentee data, effectively comparing every mentor to every mentee.
- For each pair, it calculates a score for each criterion:
  - Interests: 30% added to the score if the mentor's and mentee's interests match.
  - Communication Style: 20% added if communication styles match.
  - Availability: 20% added if availability matches.
  - Strengths: 20% added if strengths match.
  - MBTI: 10% added if the helper function (MBTICompatible) evaluates the MBTI types as compatible.
- The total score is the weighted sum of these individual criteria scores.
Results Storage:
- The mentor’s name, mentee’s name, final match score (0-100), and individual scores for each criterion are stored in the Evaluation sheet, row by row.

Final Output:

The subroutine compares all pairs of mentors and mentees and generates a table in the "Evaluation" sheet where:

Each row corresponds to one mentor-mentee pairing.
Columns include:
- Mentor name.
- Mentee name.
- Final overall score as a percentage.
- Individual scores for each of the weighted criteria.

This code can be useful in scenarios like matching mentors and mentees based on compatibility in workplaces or mentoring programs.

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr

This code defines two components: 1. A **helper function (`MBTICompatible`)**: ...

Detailed Explanation of the Subroutine:

Final Output:

This code defines two components: 1. A helper function (`MBTICompatible`): ...