Variable caption on checkboxes using excel VBA

Question

I have the following code that creates checkboxes based on the values from a recordset, which can be A, B,C or D. I want the checkboxes caption to show what those letters mean. For example, A= Excelent, B= Very Good, C= Good, D= Bad. I have those values in a sheet and do a vlookup to get the corresponding name, so the code is currently doing the desired, but is there a way to not have these values in a sheet, perhaps in a variable or in a hidden sheet?

If Not rst.EOF And Not rst.BOF Then
    i = 0
    Do
        With MultiPage1.Pages(2).Controls.Add("Forms.Checkbox.1", "Checkbox" & i)
            .Top = yPos
            .Left = 7
            .Caption = Application.WorksheetFunction.VLookup(rst![Perspect], ThisWorkbook.Sheets("Sheet1").Range("b26:c30"), 2, False)
            .Width = 450
            .Height = 24
            .WordWrap = True
            .Value = False
            yPos = yPos + 17
            .Tag = rst![Perspect]
            i = i + 1
            rst.MoveNext
        End With
    Loop Until rst.EOF
    rst.Close
End If

Answer 1

The best option is to put it in your recordset. If you don't have that option, then I think this is the cleanest way:

Replace the .Caption = ... with

 .Caption = GradeCaption(rst![Perspect])

And then create your function:

Function GradeCaption(Grade As String) As String
    Select Case Grade
        Case "A"
            GradeCaption = "Excellent"
        Case "B"
            GradeCaption = "Very Good"
        Case "C"
            GradeCaption = "Good"
        Case "D"
            GradeCaption = "Bad"
    End Select
End Function