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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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 Question
VBA support : need to remove extra space
Replies
2
Views
168
sksanjeev786
S
S
  • Question Question
Change date format inside textbox userform
Replies
1
Views
264
Akuini
Akuini
D
  • Solved
Class Module to include one decimal place in userform textboxes
Replies
3
Views
54
dlee83
D
M
  • Question Question
Using VBA code in a protected excel sheet
Replies
2
Views
468
MagicCarpet
M
C
  • Question Question
Searching using TextBox and ListBox
Replies
1
Views
197
campbell1093
C

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

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