Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

declaring a unicode string in vba in excel [duplicate]

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?

like image 724
Stavros Avatar asked Sep 01 '11 11:09

Stavros


People also ask

How do I insert Unicode in Excel?

To insert a Unicode character, type the character code, press ALT, and then press X. For example, to type a dollar symbol ($), type 0024, press ALT, and then press X. For more Unicode character codes, see Unicode character code charts by script.

What encoding is VBA?

VBA uses ANSI. So whenever doing any interaction with the OS VBA Unicode strings are converted to ANSI.

How do you declare a variable constant in VBA?

You use the Const statement to declare a constant and set its value. After a constant is declared, it cannot be modified or assigned a new value. You can declare a constant within a procedure or at the top of a module, in the Declarations section.

What is Strconv in VBA?

Description. The Microsoft Excel STRCONV function returns a string converted to uppercase, lowercase, proper case or Unicode. The STRCONV function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a VBA function (VBA) in Excel.


3 Answers

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
like image 185
JMax Avatar answered Sep 28 '22 22:09

JMax


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

like image 12
z̫͋ Avatar answered Sep 28 '22 23:09

z̫͋


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.

like image 8
tricasse Avatar answered Sep 28 '22 21:09

tricasse