16

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,434
  • 13
  • 31
  • 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,644
  • 1
  • 24
  • 26
JMax
  • 25,301
  • 12
  • 66
  • 87
  • 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,511
  • 10
  • 15
  • 3
    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
7

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,231
  • 13
  • 18