Custom built formula module incorrectly outputs special characters

G

geriz

New Member
Joined
Apr 21, 2015
Messages
2
Hello,

I have a custom built formula module which converts numbers into Lithuanian words (i.e., it would convert "22" to "twenty two", only in Lithuanian). The code is provided bellow.

But the formula can not output special Lithuanian characters, such as ą, č, ę, ė, į, š, ų, ū. It gives strange symbols instead.

In VBA console (when viewing the module code) these symbols at first were misinterpreted too, but I fixed it by going to Tools>Options>EditorFormat and selecting "Arial Unicode MS (Baltic)" as a display font. But excel sheet still outputs wrong characters, even if the same "Arial Unicode MS" is selected as font.

How to make the formula to output special Lithuanian characters correctly?

The code (sorry, it looks like I am not allowed to attach files):
Code: 
Public Function SumaZodziu(Number) As String
Dim StrNumber As String, SumZod As String, Ilgis As Integer, EilNr As Integer
Dim Centai As Integer, Litai As Currency


Litai = Fix(Number)
StrNumber = Right(Str$(Litai), Len(Str$(Litai)) - 1)
Ilgis = Len(StrNumber)
EilNr = Ilgis


Centai = Fix((Number - Litai) * 100)
If (Centai >= 0) And (Centai <= 9) Then
    SumZod = "Lt, 0" & Right(Str$(Centai), 1) & " ct"
Else
    SumZod = "Lt, " & Right(Str$(Centai), 2) & " ct"
End If


If Litai = 0 Then
    SumZod = "Nulis " & SumZod
    GoTo FEnd
End If


Vien:
'vienietai
If Ilgis > 1 Then If Mid(StrNumber, EilNr - 1, 1) = "1" Then GoTo Desm
If Ilgis >= 1 Then
    If Mid(StrNumber, EilNr, 1) <> "0" Then
        Select Case Mid(StrNumber, EilNr, 1)
        Case "1": SumZod = "vienas " & SumZod
        Case "2": SumZod = "du " & SumZod
        Case "3": SumZod = "trys " & SumZod
        Case "4": SumZod = "keturi " & SumZod
        Case "5": SumZod = "penki " & SumZod
        Case "6": SumZod = "ðeði " & SumZod
        Case "7": SumZod = "septyni " & SumZod
        Case "8": SumZod = "aðtuoni " & SumZod
        Case "9": SumZod = "devyni " & SumZod
        End Select
    End If
End If


Desm:
'desimtys
If Ilgis >= 2 Then
    EilNr = EilNr - 1
    If (Mid(StrNumber, EilNr, 1) <> "0") And (Mid(StrNumber, EilNr, 1) <> "1") Then
        Select Case Mid(StrNumber, EilNr, 1)
        Case "1": SumZod = "deðimt " & SumZod
        Case "2": SumZod = "dvideðimt " & SumZod
        Case "3": SumZod = "trisdeðimt " & SumZod
        Case "4": SumZod = "keturiasdeðimt " & SumZod
        Case "5": SumZod = "penkiasdeðimt " & SumZod
        Case "6": SumZod = "ðeðiasdeðmt " & SumZod
        Case "7": SumZod = "septyniasdeðimt " & SumZod
        Case "8": SumZod = "aðtuoniasdeðimt " & SumZod
        Case "9": SumZod = "devyniasdeðimt " & SumZod
        End Select
    ElseIf Mid(StrNumber, EilNr, 1) = "1" Then
        Select Case Mid(StrNumber, EilNr, 2)
        Case "10": SumZod = "deðimt " & SumZod
        Case "11": SumZod = "vienuolika " & SumZod
        Case "12": SumZod = "dvylika " & SumZod
        Case "13": SumZod = "trylika " & SumZod
        Case "14": SumZod = "keturiolika " & SumZod
        Case "15": SumZod = "penkiolika " & SumZod
        Case "16": SumZod = "ðeðiolika " & SumZod
        Case "17": SumZod = "septyniolika " & SumZod
        Case "18": SumZod = "aðtuoniolika " & SumZod
        Case "19": SumZod = "devyniolika " & SumZod
        End Select
    End If
End If


Simt:
'simtai
If Ilgis >= 3 Then
    EilNr = EilNr - 1
    If Mid(StrNumber, EilNr, 1) <> "0" Then
        Select Case Mid(StrNumber, EilNr, 1)
        Case "1": SumZod = "vienas ðimtas " & SumZod
        Case "2": SumZod = "du ðimtai " & SumZod
        Case "3": SumZod = "trys ðimtai " & SumZod
        Case "4": SumZod = "keturi ðimtai " & SumZod
        Case "5": SumZod = "penki ðimtai " & SumZod
        Case "6": SumZod = "ðeði ðimtai " & SumZod
        Case "7": SumZod = "septyni ðimtai " & SumZod
        Case "8": SumZod = "aðtuoni ðimtai " & SumZod
        Case "9": SumZod = "devyni ðimtai " & SumZod
        End Select
    End If
End If


'tukstanciai
If ((Len(StrNumber) - Ilgis) = 0) And (Ilgis >= 4) Then
    Ilgis = Ilgis - 3
    EilNr = EilNr - 1
    If Mid(StrNumber, EilNr, 1) = "1" Then
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "1" Then
                SumZod = "tûkstanèiø " & SumZod
            Else
                SumZod = "tûkstantis " & SumZod
            End If
        Else
            SumZod = "tûkstantis " & SumZod
        End If
    ElseIf Mid(StrNumber, EilNr, 1) = "0" Then
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "0" Then
                If Ilgis >= 3 Then
                    If Mid(StrNumber, EilNr - 2, 1) <> "0" Then
                        SumZod = "tûkstanèiø " & SumZod
                    End If
                End If
            Else
                SumZod = "tûkstanèiø " & SumZod
            End If
        End If
    Else
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "1" Then
                SumZod = "tûkstanèiø " & SumZod
            Else
                SumZod = "tûkstanèiai " & SumZod
            End If
        Else
            SumZod = "tûkstanèiai " & SumZod
        End If
    End If
    GoTo Vien
End If


'milijonai
If ((Len(StrNumber) - Ilgis) = 3) And (Ilgis >= 4) Then
    Ilgis = Ilgis - 3
    EilNr = EilNr - 1
    If Mid(StrNumber, EilNr, 1) = "1" Then
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "1" Then
                SumZod = "milijonø " & SumZod
            Else
                SumZod = "milijonas " & SumZod
            End If
        Else
            SumZod = "milijonas " & SumZod
        End If
    ElseIf Mid(StrNumber, EilNr, 1) = "0" Then
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "0" Then
                If Ilgis >= 3 Then
                    If Mid(StrNumber, EilNr - 2, 1) <> "0" Then
                        SumZod = "milijonø " & SumZod
                    End If
                End If
            Else
                SumZod = "milijonø " & SumZod
            End If
        End If
    Else
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "1" Then
                SumZod = "milijonø " & SumZod
            Else
                SumZod = "milijonai " & SumZod
            End If
        Else
            SumZod = "milijonai " & SumZod
        End If
    End If
    GoTo Vien
End If


'milijardai
If ((Len(StrNumber) - Ilgis) = 6) And (Ilgis >= 4) Then
    Ilgis = Ilgis - 3
    EilNr = EilNr - 1
    If Mid(StrNumber, EilNr, 1) = "1" Then
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "1" Then
                SumZod = "milijardø " & SumZod
            Else
                SumZod = "milijardas " & SumZod
            End If
        Else
            SumZod = "milijardas " & SumZod
        End If
    ElseIf Mid(StrNumber, EilNr, 1) = "0" Then
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "0" Then
                If Ilgis >= 3 Then
                    If Mid(StrNumber, EilNr - 2, 1) <> "0" Then
                        SumZod = "milijardø " & SumZod
                    End If
                End If
            Else
                SumZod = "milijardø " & SumZod
            End If
        End If
    Else
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "1" Then
                SumZod = "milijardø " & SumZod
            Else
                SumZod = "milijardai " & SumZod
            End If
        Else
            SumZod = "milijardai " & SumZod
        End If
    End If
    GoTo Vien
End If


SumZod = UCase(Left(SumZod, 1)) & Right(SumZod, Len(SumZod) - 1)


FEnd:
SumaZodziu = SumZod
End Function




Public Function SumaZodziuSv(Number) As String
Dim StrNumber As String, SumZod As String, Ilgis As Integer, EilNr As Integer


Number = Fix(Number)
If (Number = 0) Then
    SumZod = "Nulis"
    GoTo FEnd
End If
StrNumber = Right(Str$(Number), Len(Str$(Number)) - 1)
Ilgis = Len(StrNumber)
EilNr = Ilgis
SumZod = ""


Vien:
'vienietai
If Ilgis > 1 Then If Mid(StrNumber, EilNr - 1, 1) = "1" Then GoTo Desm
If Ilgis >= 1 Then
    If Mid(StrNumber, EilNr, 1) <> "0" Then
        Select Case Mid(StrNumber, EilNr, 1)
        Case "1": SumZod = "vienas " & SumZod
        Case "2": SumZod = "du " & SumZod
        Case "3": SumZod = "trys " & SumZod
        Case "4": SumZod = "keturi " & SumZod
        Case "5": SumZod = "penki " & SumZod
        Case "6": SumZod = "ðeði " & SumZod
        Case "7": SumZod = "septyni " & SumZod
        Case "8": SumZod = "aðtuoni " & SumZod
        Case "9": SumZod = "devyni " & SumZod
        End Select
    End If
End If


Desm:
'desimtys
If Ilgis >= 2 Then
    EilNr = EilNr - 1
    If (Mid(StrNumber, EilNr, 1) <> "0") And (Mid(StrNumber, EilNr, 1) <> "1") Then
        Select Case Mid(StrNumber, EilNr, 1)
        Case "1": SumZod = "deðimt " & SumZod
        Case "2": SumZod = "dvideðimt " & SumZod
        Case "3": SumZod = "trisdeðimt " & SumZod
        Case "4": SumZod = "keturiasdeðimt " & SumZod
        Case "5": SumZod = "penkiasdeðimt " & SumZod
        Case "6": SumZod = "ðeðiasdeðmt " & SumZod
        Case "7": SumZod = "septyniasdeðimt " & SumZod
        Case "8": SumZod = "aðtuoniasdeðimt " & SumZod
        Case "9": SumZod = "devyniasdeðimt " & SumZod
        End Select
    ElseIf Mid(StrNumber, EilNr, 1) = "1" Then
        Select Case Mid(StrNumber, EilNr, 2)
        Case "10": SumZod = "deðimt " & SumZod
        Case "11": SumZod = "vienuolika " & SumZod
        Case "12": SumZod = "dvylika " & SumZod
        Case "13": SumZod = "trylika " & SumZod
        Case "14": SumZod = "keturiolika " & SumZod
        Case "15": SumZod = "penkiolika " & SumZod
        Case "16": SumZod = "ðeðiolika " & SumZod
        Case "17": SumZod = "septyniolika " & SumZod
        Case "18": SumZod = "aðtuoniolika " & SumZod
        Case "19": SumZod = "devyniolika " & SumZod
        End Select
    End If
End If


Simt:
'simtai
If Ilgis >= 3 Then
    EilNr = EilNr - 1
    If Mid(StrNumber, EilNr, 1) <> "0" Then
        Select Case Mid(StrNumber, EilNr, 1)
        Case "1": SumZod = "vienas ðimtas " & SumZod
        Case "2": SumZod = "du ðimtai " & SumZod
        Case "3": SumZod = "trys ðimtai " & SumZod
        Case "4": SumZod = "keturi ðimtai " & SumZod
        Case "5": SumZod = "penki ðimtai " & SumZod
        Case "6": SumZod = "ðeði ðimtai " & SumZod
        Case "7": SumZod = "septyni ðimtai " & SumZod
        Case "8": SumZod = "aðtuoni ðimtai " & SumZod
        Case "9": SumZod = "devyni ðimtai " & SumZod
        End Select
    End If
End If


'tukstanciai
If ((Len(StrNumber) - Ilgis) = 0) And (Ilgis >= 4) Then
    Ilgis = Ilgis - 3
    EilNr = EilNr - 1
    If Mid(StrNumber, EilNr, 1) = "1" Then
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "1" Then
                SumZod = "tûkstanèiø " & SumZod
            Else
                SumZod = "tûkstantis " & SumZod
            End If
        Else
            SumZod = "tûkstantis " & SumZod
        End If
    ElseIf Mid(StrNumber, EilNr, 1) = "0" Then
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "0" Then
                If Ilgis >= 3 Then
                    If Mid(StrNumber, EilNr - 2, 1) <> "0" Then
                        SumZod = "tûkstanèiø " & SumZod
                    End If
                End If
            Else
                SumZod = "tûkstanèiø " & SumZod
            End If
        End If
    Else
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "1" Then
                SumZod = "tûkstanèiø " & SumZod
            Else
                SumZod = "tûkstanèiai " & SumZod
            End If
        Else
            SumZod = "tûkstanèiai " & SumZod
        End If
    End If
    GoTo Vien
End If


'milijonai
If ((Len(StrNumber) - Ilgis) = 3) And (Ilgis >= 4) Then
    Ilgis = Ilgis - 3
    EilNr = EilNr - 1
    If Mid(StrNumber, EilNr, 1) = "1" Then
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "1" Then
                SumZod = "milijonø " & SumZod
            Else
                SumZod = "milijonas " & SumZod
            End If
        Else
            SumZod = "milijonas " & SumZod
        End If
    ElseIf Mid(StrNumber, EilNr, 1) = "0" Then
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "0" Then
                If Ilgis >= 3 Then
                    If Mid(StrNumber, EilNr - 2, 1) <> "0" Then
                        SumZod = "milijonø " & SumZod
                    End If
                End If
            Else
                SumZod = "milijonø " & SumZod
            End If
        End If
    Else
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "1" Then
                SumZod = "milijonø " & SumZod
            Else
                SumZod = "milijonai " & SumZod
            End If
        Else
            SumZod = "milijonai " & SumZod
        End If
    End If
    GoTo Vien
End If


'milijardai
If ((Len(StrNumber) - Ilgis) = 6) And (Ilgis >= 4) Then
    Ilgis = Ilgis - 3
    EilNr = EilNr - 1
    If Mid(StrNumber, EilNr, 1) = "1" Then
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "1" Then
                SumZod = "milijardø " & SumZod
            Else
                SumZod = "milijardas " & SumZod
            End If
        Else
            SumZod = "milijardas " & SumZod
        End If
    ElseIf Mid(StrNumber, EilNr, 1) = "0" Then
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "0" Then
                If Ilgis >= 3 Then
                    If Mid(StrNumber, EilNr - 2, 1) <> "0" Then
                        SumZod = "milijardø " & SumZod
                    End If
                End If
            Else
                SumZod = "milijardø " & SumZod
            End If
        End If
    Else
        If Ilgis >= 2 Then
            If Mid(StrNumber, EilNr - 1, 1) = "1" Then
                SumZod = "milijardø " & SumZod
            Else
                SumZod = "milijardai " & SumZod
            End If
        Else
            SumZod = "milijardai " & SumZod
        End If
    End If
    GoTo Vien
End If


SumZod = UCase(Left(SumZod, 1)) & Right(SumZod, Len(SumZod) - 1)


FEnd:
SumaZodziuSv = SumZod
End Function

Regards,
Gerry
Windows 8.1, MS Office 2013.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
And it looks like code copy/paste (in the main thread) gave the same result. i.e. "deðimt" should actually be "dešimt".
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Change date format inside textbox userform
Replies
1
Views
82
Akuini
Akuini
L
  • Question
Formula & Sub to format phone numbers working by itself but not in conjunction with other code.
Replies
4
Views
190
Scott Huish
Scott Huish
S
  • Question
VBA to ignore certain MAC Address values but process all others
Replies
1
Views
191
myall_blues
myall_blues
N
  • Question
Myrna Larson-Permutations and Combinations
Replies
0
Views
131
Norman A
N
E
  • Question
Change Log With VBA
2
Replies
19
Views
373
Joe4
Joe4

Forum statistics

Threads
1,214,833
Messages
6,121,868
Members
449,053
Latest member
Mesh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top