declaring a unicode string in vba in excel

Question

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?

Solution 1

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

Solution 2

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

Solution 3

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.

Comments

[Stavros]

Still, it doesn't work. maybe it's the way I am declaring the variable. Have you managed to make this work?

[JMax]

@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)

[Stavros]

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.

[JMax]

@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...

[Istiaque Ahmed]

@JMax, may I ask you to have a look at this unicode related question SO please : stackoverflow.com/questions/11116963/…?

[JMax]

@IstiaqueAhmed: I've had a look but I'm afraid I can't help you there, sorry.

[Br.Bill]

Note that this solution only can work in Windows, according to Microsoft documentation for StrConv().

[aelveborn]

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" string. For English-only strings, it looks like there is a vbNullChar inserted after each character; for national strings, the result is complete garbage.

[aelveborn]

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".