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.