17

I am trying to create a substitute() that will convert greek characters to latin.

The problem is that after declaring 

Dim Source As String
Source = "αβγδεζηικλμνξοπρστθφω"

Source is interpreted as "áâãäåæçéêëìíîïðñóôõöù"
is there any way use unicode at declaration level?

Community
  • 1
  • 1
Stavros
  • 5,802
  • 13
  • 32
  • 45
  • "convert greek characters to latin" What does this mean? α becomes a, β becomes b, γ becomes g etc.? If so, what should ζ, η, ξ be converted to? – Jean-François Corbett Sep 02 '11 at 09:43
  • I have my own Target string, where I have the conversions. It basically, doesn't matter.. – Stavros Sep 09 '11 at 07:34
  • Related: this method cannot be used in const. See [excel - Unicode string literals in VBA - Stack Overflow](https://stackoverflow.com/questions/23678033/unicode-string-literals-in-vba) ___________________________________________________________________________________________ Some functions (MsgBox for example) doesn't support Unicode -- see [ms access - How do I display a messagebox with unicode characters in VBA? - Stack Overflow](https://stackoverflow.com/questions/55210315/how-do-i-display-a-messagebox-with-unicode-characters-in-vba) – user202729 Sep 28 '21 at 14:44
  • @GSerg Perhaps you should reverse the duplicate closure? This one is both higher voted and older. – user202729 Sep 28 '21 at 14:51
  • 1
    @user202729 Neither is relevant. What is relevant is the correctness of the answers. The accepted answer here is not correct as it produces [double Unicode](https://stackoverflow.com/a/14292880/11683) which doesn't make sense and corrupts the data, and to add insult to injury, it does so based on the regular string literal which, due to the non-Unicodeness of the IDE, will only even *appear* to work only on a computer with Greek locale, as I [noted](https://stackoverflow.com/questions/7269399/declaring-a-unicode-string-in-vba-in-excel?noredirect=1#comment100415900_7270463) under that answer. – GSerg Sep 28 '21 at 15:02

3 Answers3

14

You can try StrConv:

StrConv("αβγδεζηικλμνξοπρστθφω", vbUnicode)

Source : http://www.techonthenet.com/excel/formulas/strconv.php

[EDIT] Another solution:

You can get every greek character (lower and upper case) thanks to this procedure:

Sub x()
    Dim i As Long

    For i = 913 To 969
        With Cells(i - 912, 1)
            .Formula = "=dec2hex(" & i & ")"
            .Offset(, 1).Value = ChrW$(i)
        End With
    Next i
End Sub

You can create an array to find the char for instance.

Source: http://www.excelforum.com/excel-programming/636544-adding-greek-letters.html

[EDIT 2] Here is a sub to build the string you wanted:

Sub greekAlpha()
Dim sAlpha As String
Dim lLetter As Long

For lLetter = &H3B1 To &H3C9
    sAlpha = sAlpha & ChrW(lLetter)
Next
End Sub
Jon Peltier
  • 5,895
  • 1
  • 27
  • 27
JMax
  • 26,109
  • 12
  • 69
  • 88
  • 1
    Still, it doesn't work. maybe it's the way I am declaring the variable. Have you managed to make this work? – Stavros Sep 01 '11 at 13:43
  • @Stavros: indeed, i couldn't make it work in a full example. i added another solution (which works - depending on what you want to do) – JMax Sep 01 '11 at 14:09
  • I don't want to use Cells from the spreadsheet. Everything should be in VB code. and it's not the whole alphabet that I want to convert. Only the letters I have in my example as Source. – Stavros Sep 02 '11 at 08:45
  • @Stavros: i built a procedure that will create the string with the right character but as i still don't know what you are trying to achieve, i can only assess an try... – JMax Sep 02 '11 at 08:59
  • @JMax, may I ask you to have a look at this unicode related question SO please : http://stackoverflow.com/questions/11116963/bangla-language-not-displayed-in-the-unicoded-csv-file? – Istiaque Ahmed Jun 20 '12 at 11:49
  • @IstiaqueAhmed: I've had a look but I'm afraid I can't help you there, sorry. – JMax Jun 20 '12 at 15:30
  • Note that this solution only can work in Windows, according to Microsoft documentation for StrConv(). – Br.Bill Jun 06 '18 at 00:30
  • 4
    `StrConv("string literal", vbUnicode)` is *absolutely* wrong. What it does: it first creates a *Unicode* string containing the literal (and if the literal contained characters not representable in the current ANSI codepage, it will *already be garbage at this point*), then converts it to Unicode *again*, pretending that it was in ANSI. This results in a ["double Unicode"](https://stackoverflow.com/a/14292880/11683) string. For English-only strings, it looks like there is a `vbNullChar` inserted after each character; for national strings, the result is complete garbage. – GSerg Jul 08 '19 at 15:08
12

As previously mentioned, VBA does support unicode strings, however you cannot write unicode strings inside your code, because the VBA editor only allows VBA files to be encoded in the 8-bit codepage Windows-1252.

You can however convert a binary equivalent of the unicode string you wish to have:

str = StrConv("±²³´µ¶·¹º»¼½¾¿ÀÁÃĸÆÉ", vbFromUnicode)
'str value is now "αβγδεζηικλμνξοπρστθφω"

Use notepad to convert the string: copy-paste the unicode string, save the file as unicode (not utf-8) and open it as ASCII (which is in fact Windows-1252), then copy-paste it into the VBA editor without the first two characters (ÿþ), which is the BOM marker

z̫͋
  • 1,531
  • 10
  • 15
  • 4
    A clever trick, but it suffers from exactly the same problem. The resulting clever characters may easily be not representable in the current ANSI codepage of the computer. E.g. when I paste that string into my VBA editor, I get `"±???µ¶·??»????AAAA??E"`. – GSerg Jul 08 '19 at 15:14
  • I was able to make this method work to some extent in Notepad++.. open new file in Notepad++, change encoding to UCS-2, paste unicode text, change encoding to Windows-1252, it will ask to save file, save it. Close in Notepad++ and open file in Windows Notepad, while opening select Encoding as ANSI. Use this text in VBA as mentioned above. However, problem comes when the ANSI text has the double quote that is used as string delimited in VBA. For few character, VBA ChrW() function can also be used with unicode hex converted to decimal.. e.g. to embed Unicode minus sign "−" use ChrW(8722). – Uttam Aug 29 '22 at 18:40
8

You say that your source is interpreted as "áâãäåæçéêëìíîïðñóôõöù".

Note that the Visual Basic Editor doesn't display Unicode, but it does support manipulating Unicode strings:

Dim strValue As String
strValue = Range("A1").Value
Range("B1").Value = Mid(strValue, 3)
Range("C1").Value = StrReverse(strValue)

If A1 contains Greek characters, B1 and C1 will contain Greek characters too after running this code.

You just can't view the values properly in the Immediate window, or in a MsgBox.

tricasse
  • 1,299
  • 13
  • 18