Problem convert the marks from number to rating in Arabic using VBA

Question

I tried to convert the marks from number to rating ( i.e (90 - 100 ) change to (Excellent) , (80 - 89 ) change to (very good)) and so .. The code working fine without any problem , but when i tried to write the rating in Arabic language , the rating appear in the Excel sheet unknown as shown below

I already changed my local system language to Arabic but the same ..

My code as below

Private Sub Convert_Click()

Dim n As Integer

n = Application.WorksheetFunction.CountA(Range("a:a"))

For i = 2 To n

 If Worksheets("Sheet1").Range("c" & i).Value >= 90 Then
 Worksheets("Sheet2").Range("c" & i).Value = "امتياز"

 Else

 Worksheets("Sheet2").Range("c" & i).Value = " جيد جدا "
 
 End If
 
Next i

End Sub

FYA ,

امتياز >> meaning in english  "Excellent"
جيد جدا >> meaning in english "very good"

Kind Regards

Answer 1

You should avoid entering non-ascii characters directly in the vba editor. Even if it works for you (sometimes) due to your locale settings, it won't work for people who are trying to help you.

In this case I would suggest you enter contstant names in excel with values such as:

Name       Value
Excellent  "إمتياز"
VeryGood   "جيد جدا"
Good       "جيد"
... and so on

Then change your vba code to something like this

Private Sub Convert_Click()

    Dim n As Integer

    n = Application.WorksheetFunction.CountA(Range("a:a"))

    For i = 2 To n

        If Worksheets("Sheet1").Range("c" & i).Value >= 90 Then
            Worksheets("Sheet2").Range("c" & i).Value = [Excellent]

        ElseIf Worksheets("Sheet1").Range("c" & i).Value >= 80
            Worksheets("Sheet2").Range("c" & i).Value = [VeryGood]

        ElseIf Worksheets("Sheet1").Range("c" & i).Value >= 70
            Worksheets("Sheet2").Range("c" & i).Value = [Good]

        End If
 
    Next i
End Sub

Edit (following answer from OP)

Another (similar) approach is to have a dedicated sheet where you define global variables used by the whole application. In this case the code above will still work without change and [Excellent] is more readable than Sheets(Sheet4.Name).Range("A1").Value. You can alternatively use "Sheets("Globals").Range("Excellent")

Aside: Instead Sheets(Sheet4.Name).Range("A1").Value you can simply use Sheet4.Range("A1").Value