C# to VBA macro in excel

[alisarab]

Hi everyone, I have the following code which convert English to farsi (Finglish)
I want to convert the C# codes into the vb codes which i can use them in excel to convert finglish

for (int counter = 0; counter < sourceText.Length; counter++)            {
                switch (sourceText[counter])
                {
                    case 'a':
                        if (counter != 0 && sourceText[counter - 1] == 'a')
                            temp = temp.Substring(0, temp.Length - 1) + 'ا';
                        else
                            temp += "َ";
                        break;
                    case 'A':
                        temp += "آ";
                        break;
                    case 'b':
                        temp += "ب";
                        break;
                    case 'c':
                        temp += "ث";
                        break;
                    case 'd':
                        temp += "د";
                        break;
                    case 'e':
                        if (counter != 0 && sourceText[counter - 1] == 'e')
                            temp = temp.Substring(0, temp.Length - 1) + 'ه';
                        else
                            temp += "ِ";
                        break;
                    case 'E':
                        temp += "ع";
                        break;
                    case 'f':
                        temp += "ف";
                        break;
                    case 'g':
                        temp += 'گ';
                        break;
                    case 'h':
                        if (counter != 0 && sourceText[counter - 1] == 'c')
                            temp = temp.Substring(0, temp.Length - 1) + 'چ';
                        else if (counter != 0 && sourceText[counter - 1] == 'g')
                            temp = temp.Substring(0, temp.Length - 1) + 'ق';
                        else if (counter != 0 && sourceText[counter - 1] == 'G')
                            temp = temp.Substring(0, temp.Length - 1) + 'غ';
                        else if (counter != 0 && sourceText[counter - 1] == 'k')
                            temp = temp.Substring(0, temp.Length - 1) + 'خ';
                        else if (counter != 0 && sourceText[counter - 1] == 'j')
                            temp = temp.Substring(0, temp.Length - 1) + 'ژ';
                        else if (counter != 0 && sourceText[counter - 1] == 's')
                            temp = temp.Substring(0, temp.Length - 1) + 'ش';
                        else
                            temp += 'ه';
                        break;
                    case 'H':
                        temp += 'ح';
                        break;
                    case 'i':
                        if (counter != 0 && sourceText[counter - 1] == 'e')
                            temp = temp.Substring(0, temp.Length - 1) + 'ئ';
                        else
                            temp += 'ی';
                        break;
                    case 'I':
                        temp += "ای";
                        break;
                    case 'j':
                        temp += "ج";
                        break;
                    case 'k':
                        temp += "ک";
                        break;
                    case 'l':
                        temp += "ل";
                        break;
                    case 'm':
                        temp += "م";
                        break;
                    case 'n':
                        temp += "ن";
                        break;
                    case 'o':
                        if (counter != 0 && sourceText[counter - 1] == 'o')
                            temp = temp.Substring(0, temp.Length - 1) + 'و';
                        else
                            temp += "ُ";
                        break;
                    case 'p':
                        temp += "پ";
                        break;
                    case 'r':
                        temp += "ر";
                        break;
                    case 's':
                        temp += "س";
                        break;
                    case 'S':
                        temp += "ص";
                        break;
                    case 't':
                        temp += "ت";
                        break;
                    case 'T':
                        temp += "ط";
                        break;
                    case 'u':
                        temp += "و";
                        break;
                    case 'v':
                        temp += "و";
                        break;
                    case 'w':
                        temp += "";
                        break;
                    case 'x':
                        temp += "س";
                        break;
                    case 'y':
                        temp += "ی";
                        break;
                    case 'z':
                        temp += "ز";
                        break;
                    case 'Z':
                        temp += "ذ";
                        break;
                    default:
                        temp += sourceText[counter];
                        break;

i could find switch command in formula of excel and it must be couple with for loop
please help me about the followings
1- for counter until counter++ (0: i=i+1 in excel) which expression is suitable inside excel
2- each case of switch command in C# has if condition
please introduce the appropriate expression for this too in the excel
thanks a lot

[mancubus]

welcome to the forum.

you may try an online converter and adapt the .Net output to vba.


https://converter.telerik.com/
http://www.carlosag.net/Tools/CodeTranslator/

[SamT]

Use a Scripting.Dictionary for a one to one crossover of each character.

[p45cal]

A guess since I don't know C#:

sourceText = "aabcdefg"
temp = ""
'translation:
For i = 1 To Len(sourceText)
  Select Case Mid(sourceText, i, 1)
    Case "a"
      If i > 1 Then    'if it happens to be first letter in sourcetext there isn't a previous letter.
        If Mid(sourceText, i - 1, 1) = "a" Then temp = Left(temp, Len(temp) - 1) & "ا" Else temp = temp & "َ"
      Else
        temp = temp & "َ"
      End If
    Case "A"
      temp = temp & "آ"
    Case "b"
      temp = temp & "ب"
.
.
.
.
    Case "e"
      If i > 1 Then    'if it happens to be first letter in sourcetext there isn't a previous letter.
        If Mid(sourceText, i - 1, 1) = "e" Then temp = Left(temp, Len(temp) - 1) & "ه" Else temp = temp & "ِ"
      Else    'it was the first letter in sourceText:
        temp = temp & "ِ"
      End If
.
.
.
.
    Case "h"
      If i > 1 Then    'if it happens to be first letter in sourcetext there isn't a previous letter.
        Select Case Mid(sourceText, i - 1, 1)
          Case "c"
            temp = Left(temp, Len(temp) - 1) & "?"
          Case "g"
            temp = Left(temp, Len(temp) - 1) & "?"
          Case "G"
            temp = Left(temp, Len(temp) - 1) & "?"
          Case "k"
            temp = Left(temp, Len(temp) - 1) & "?"
          Case "j"
            temp = Left(temp, Len(temp) - 1) & "?"
          Case "s"
            temp = Left(temp, Len(temp) - 1) & "?"
          Case Else
            temp = temp & "?"
        End Select
      Else    'it was the first letter in sourceText:
        temp = temp & "?"
      End If
.
.
.
.
.
.
    Case Else
      temp = temp & Mid(sourceText, i, 1)
  End Select
Next i

OK, I got bored trying to substitute your characters (my vbe only translated them to "?") so I'll leave you to do that.
I don't think you need the vba equivalent of the break; lines.
i in the code above is equivalent to your counter.

[p45cal]

You might consider using less convoluted code such as:

sourceText = "aabcdefg"
temp = Replace(sourceText, "aa", "?")
temp = Replace(temp, "ee", "?")
temp = Replace(temp, "ch", "?")
temp = Replace(temp, "gh", "?")
temp = Replace(temp, "Gh", "?")
temp = Replace(temp, "kh", "?")
temp = Replace(temp, "jh", "?")
temp = Replace(temp, "sh", "?")
temp = Replace(temp, "ie", "?")
temp = Replace(temp, "jh", "?")
temp = Replace(temp, "oo", "?")
'
'
'
'
temp = Replace(temp, "a", "?")
temp = Replace(temp, "b", "?")
temp = Replace(temp, "c", "?")
'
'
temp = Replace(temp, "x", "?")
temp = Replace(temp, "y", "?")
temp = Replace(temp, "z", "?")
'
'
temp = Replace(temp, "A", "?")
temp = Replace(temp, "B", "?")
temp = Replace(temp, "C", "?")
temp = Replace(temp, "D", "?")
'
'
'
'
temp = Replace(temp, "W", "?")
temp = Replace(temp, "X", "?")
temp = Replace(temp, "Y", "?")
temp = Replace(temp, "Z", "?")

where you deal with the double letters first then deal with the single letters left afterwards.