How to keep diacritical marks when sending vba string to excel/outlook?

Question

I'd like to get your opinion on the problem. It comes back to me every time I want to send vba string, which contains my native language diacritical marks to excel or outlook.

I've already changed the font on the vba editor, so it shows proper marks in code and it works just fine when entering these marks manually on worksheet/outlook message as well. The problem occurs, when the macro is executed, like the office was wrongly encoding the actions which vba sends to programs.

Example: I can type "Ł" letter into cell.

I can type in editor as well:

Sub example()
Range("A1").Value = "Ł"
End Sub

But when executing the macro, range A1 shows "£".

Do you have any idea where is the problem?

Btw the same happened when I copied the code from vba to this window. (the "Ł" in code was transformed to "£".

Thanks a lot! Sebastian.

Answer 1

If you know the Unicode value of the character you want to place in the cell then use the ChrW function e.g. for an A with an umlaut:

ActiveSheet.Cells(1, 1).Value = ChrW(&H00c4)

Note that the input to the function is hexadecimal. For your character you want to try and find the Unicode value and prefix it with &H to let VBA know you are passing an hexadecimal value.