How to get the real character when ? is displayed

[gonen]

Hello
I need to get the real character value of a string which contains non-English character (Nordic)

please note the second character !

In the Msgbox it is displayed as S?mna,Norway
In Debug.Print - the same

When I put its value in a userform textbox it is displayed correctly as Sømna,Norway


Can someone advice how to loop on the string with S?mna,Norway (in VBA) and get the value ø instead of the ?

Thanks !!!

[Paul_Hossler]

Option Explicit

Sub test()
Const S As String = "Sømna,Norway"
Dim i As Long

For i = 1 To Len(S)
Debug.Print i & " --- " & Asc(Mid(S, i, 1))
Next i
Stop
End Sub


I don't think the MsgBox font can be changed, and I guess it doesn't handle higher order ASCII chars


Paul

[gonen]

I tried

It says that the single char string is ? and its ASC(..) is 63

This is not the ASC of the Nordic letter I guess....


Do you know which function converts back 63 into ?



Any other idea. ?

thank you for help !!!

[shrivallabha]

There are some basic functions in Excel that will work
If you insert this symbol Ø in Cell A1 then in Cell B1 type formula
=CODE(A1)
which will return the corresponding code.

And to see what exactly 63 is use either CHAR function in Excel or following code:
Option Explicit
Sub test2()
Msgbox Chr(63)
End Sub

[gonen]

I tried this:
I put in Cell the char ø - =code(..) gave 63
I put in another Cell char ? - =code(..) gave 63 as well !!!

How can it be that two different chars give same result with the =code() function ?

maybe this will give a hint of my problem ?

thank you !!!

[mdmackillop]

Try 248

For a list of Fonts and test for differences try this
Sub ShowInstalledFonts()
'Based on http://j-walk.com/ss/excel/tips/tip79.htm
Dim Num

Num = InputBox("Enter ascii number", "Characters", 248)
Set FontList = Application.CommandBars("Formatting").FindControl(ID:=1728)

' If Font control is missing, create a temp CommandBar
If FontList Is Nothing Then
Set TempBar = Application.CommandBars.Add
Set FontList = TempBar.Controls.Add(ID:=1728)
End If

' Put the fonts into column A
Range("A:A").ClearContents
Cells(i + 1, 1) = "Font"
Cells(i + 1, 2) = Num

For i = 0 To FontList.ListCount - 1
Cells(i + 2, 1) = FontList.List(i + 1)

With Cells(i + 2, 2)
.Value = Chr(Num)
.Font.Name = Cells(i + 2, 1)
End With
Next i

' Delete temp CommandBar if it exists
On Error Resume Next
TempBar.Delete

End Sub

[gonen]

I think I missed something...

I opened a new workbook

added your sub
ran it

got a list of fonts

now what ?

sorry - but what did I miss ?


p.s.
the case where I get 63 for both characters happens on two machines.
one is xp with office 2003
sec is win 7 with office 2010

please keep in mind that the Nordic characters are well displayed in a textbox in a userform using
me.textbox1.value = stringA

it is my VBA code that does not know how to deal with the Nordic char.

thanks !!!

[mdmackillop]

For me ø is showing as 248. The code will show the character number entered in the Inputbox in Column B to show possible alternatives.

[gonen]

Got you

248 for me is showing an Hebrew character.....

is it something with regional settings ?

[mdmackillop]

Probably. Have you looked at your Character Map?

[gonen]

its the regional settings. I set Norwegian as my Non-Unicode language program and now the 248 displays the nordic char.

but...

does it mean that I am unable to use my excel with characters coming from different languages ? I need to manage Nordic / German and more languages at the same workbook....

Any solution to this problem ?

[shrivallabha]

There's an option in Excel for Language settings.
In 2007,
Its on Excel Button >> Excel Options >> Popular >> Language Settings.

What version of excel you have? (In 2003, it would be somewhere in tools).

I have never ventured into them as we use default (US) system in India. See if changing settings helps!

[gonen]

Thanks

This seems to deal with proofing (spelling etc.) tooltips language etc...

I am on Office 2010