Special characters (letters čćžšđ) in Excel 2013 VBA code

Question

I made a program in Excel 2010 VBA that contains letters like ć č š...

Msgbox("Čiča gliša") works.

Looks like Excel 2013 supports those letters in cells and formulas, but not in VBA.

VBA replaces them with some symbols which aren't even on the keyboard.

I get errors executing the code.

I believe it's something to do with language settings.

As you have observed already, Excel supports full Unicode character set in Cells. So one way around this problem is to **use a hidden sheet** for storing your resources. Then in your VBA code load strings from this hidden sheet into a string variable and then use it anyway you like — xmojmr, Dec 11 '14 at 15:00

score 10 · Accepted Answer · edited Nov 07 '22 at 06:46

10

As BambiLongGone stated, weird string are not likely to work. I would say your best shot is looking at this article. There 's a tool called Unicode to VBA that you can use to convert all your string in your application. For example :

Čiča gliša

will be converted to

ChrW$(&H10C) & "i" & ChrW$(&H10D) & "a gli" & ChrW$(&H161) & "a"

edited Nov 07 '22 at 06:46

DecimalTurn

3,243
3
16
36

answered Dec 11 '14 at 10:57

Đức Thanh Nguyễn

9,127
3
21
27

Thanks, but I won't use this. Program is for material accounting in warehouse, with cca 1000 sheets and many moduls with codes, so it wont be practical to convert it, as it's already work on few machines(PCs) which have Office 2010, only problem is on new machine with Office 2013. – Jelovac Dec 12 '14 at 10:17
The mentioned website is currently down. Here's [a blog post](http://onezeronull.com/2019/04/25/polish-and-other-non-ascii-special-characters-in-vba-and-excels-macros/) that mentions [a working counterpart](https://www.compart.com/en/unicode/). – trejder Apr 25 '19 at 13:34
FYI Note that the ChrW charcode argument is a *Long* that identifies a character, *but* doesn't allow values greater than 65535 (hex value &HFFFF) - see [MS Help](https://learn.microsoft.com/de-de/office/vba/language/reference/user-interface-help/chr-function). In order to express symbols via Unicode characters found only in hexadecimal code blocks greater than FFFF see the five workarounds at [Get Unicode characters with CharCode values greater than hex FFFF](https://stackoverflow.com/questions/56008191/get-unicode-characters-with-charcode-values-greater-hex-ffff) :-) – T.M. Sep 19 '19 at 11:27

BambiLongGone · Answer 2 · 2014-12-11T08:38:41.987

VBA is ANSI. Ps in it's interactions with Windows. It's UTF16 internaly and COM is also UTF 16. But it's file format is also ANSI so typing wierd strings are not likely to work (because they can't be saved as is).

So character conversion happen automatically with a million rules controlling it (mostly undocumented in an accessible fashion).

If in trouble assign to a byte array. Maybe you bneed toread from unicode file to bypass form's ANSI.

Yourstring() = "blah blah"

VB treats byte arrays as strings if passed to string functions.

score 2 · Answer 3 · answered Mar 12 '19 at 20:53

2

Problem is in windows regional settings: Region/Administrative/Language for non-unicode programs, which must be set to language that can handle your special characters.

answered Mar 12 '19 at 20:53

Miroslav Kajan

21
1

1

This is an alternate solution. For those who can't or don't want to alter Windows settings, dealing with VBA code (above answers) is the only solution. – trejder Apr 25 '19 at 13:34

score 1 · Answer 4 · answered Oct 25 '16 at 22:03

You can write manually the accents in every piece of code they appear, and you can use the "find & replace" to do it faster. If you have something like:

MsgBox "Código único"

Then you can find and replace:

[ó] to [" & Chr(243) & "]

[ú] to [" & Chr(250) & "]

And so on...

And you will get:

MsgBox "C" & Chr(243) & "digo " & Chr(250) & "nico"

If you don't know the code for each accented letter, then you can use excel with the function "CODE" (Function Char does the opposite)

Also, you could use a list from the internet like this one:

ASCII Code - The extended ASCII table

I just had the same problem and did this procedure. Worked fine using Visual Studio Code and very fast.

Note that this won't work for example for [Polish national characters](http://onezeronull.com/2019/04/25/polish-and-other-non-ascii-special-characters-in-vba-and-excels-macros/), because they are out of ASCII/ANSI scope. — trejder, Apr 25 '19 at 13:33

score 0 · Answer 5 · edited Sep 19 '19 at 11:08

Function CroatianCharacters(CroatianCharacterOrderNumber As Integer, Optional UpperCase As Boolean = True, Optional DisplayAll As Boolean = False) As String

   'ISO Latin1 Extended A decimal code
    Dim AllCharacters
    UpperCaseLetters = "A,B,C," & ChrW(268) & "," & ChrW(262) & "," & "D," & "D" & ChrW(382) & "," & ChrW(272) & "," & _
        "E,F,G,H,I,J,K,L,Lj,M,N,Nj,O,P,R,S," & ChrW(352) & ",T,U,V,Z," & ChrW(381)
    LowerCaseLetters = "a,b,c," & ChrW(269) & "," & ChrW(263) & "," & "d," & "d" & ChrW(382) & "," & ChrW(273) & "," & _
        "e,f,g,h,i,j,k,l,lj,m,n,nj,o,p,r,s," & ChrW(353) & ",t,u,v,z," & ChrW(382)
    CroatianCharacters = LowerCaseLetters

    If (UpperCase = True) Then
        If (DisplayAll = False) Then
            AllCharacters = Split(UpperCaseLetters, ",")
            CroatianCharacters = AllCharacters(CroatianCharacterOrderNumber - 1)
        Else
            CroatianCharacters = UpperCaseLetters
        End If
    Else
        If (DisplayAll = False) Then
            AllCharacters = Split(LowerCaseLetters, ",")
            CroatianCharacters = AllCharacters(CroatianCharacterOrderNumber - 1)
        Else
            CroatianCharacters = LowerCaseLetters
        End If
    End If

End Function

score 0 · Answer 6 · answered Jan 14 '22 at 11:53

Explanation:

The problem is linked with character encoding - see BambiLongGone's answer.

Special characters such as "č,ř,ě" are part of Unicode Standard. However, VBA uses Windows-native ("ANSI") code pages and these may or may not support special characters. Thus, special characters used directly in VBA strings may or may not work properly.

Solution on end-user side:

If you are trying to use a file with VBA codes and wrong characters are being displayed, you can often resolve this problem by changing your Windows regional settings. Go to: Control Panel - > Region -> Administrative -> Language for non-unicode programs -> Change system locale... and change Current system locale to a language supporting the special characters.

Solution on developer side:

If you are making a file with VBA codes and your strings with special characters are not being displayed correctly, you need to define the characters using a character codes. You can write the string the "normal" way using special charaters directly and then convert the string using StrConv function or a custom function - see below.

Converting using StrConv function:

Sub mySub()
    Dim myText As String
    myText = "Mý něw čář"
    myText = StrConv(myText, vbFromUnicode)
    Range("O17").Value2 = myText 
End Sub

Converting using a custom function with all needed special characters (for all Unicode chars see Wikipedia):

Sub mySub()
    Dim myText As String
    Dim myTextConv As String
    myText = "Mý něw čář"
    myTextConv = UNItoANSI(myText)
    Range("A1").Value2 = myTextConv
End Sub

Private Function UNItoANSI(text As String) As String
    text = Replace(text, "á", "" & ChrW$(225) & "")
    text = Replace(text, "ý", "" & ChrW$(253) & "")
    text = Replace(text, "ě", "" & ChrW$(283) & "")
    text = Replace(text, "č", "" & ChrW$(269) & "")
    text = Replace(text, "ř", "" & ChrW$(345) & "")
    UNItoANSI = text
End Function

Special characters (letters čćžšđ) in Excel 2013 VBA code

6 Answers6

Linked