Numbers to Words in an invoice (1 Viewer)

H

hassanlabban

Registered User.
Local time
Tomorrow, 02:20
Joined
Oct 25, 2014
Messages
23
Hi all
I am trying to show on an invoice the deceimals instead of 100s to be in 1000s. i am using the below vb but the text i am getting is only 50/100 incase the number am converting is 50 cents.
appreciate if you can advise if the below needs to be modified or i should use another vb and what is your recommendation for such a task.



Option Compare Database

Function English(ByVal N As Currency) As String

Const Thousand = 1000@
Const Million = Thousand * Thousand
Const Billion = Thousand * Million
Const Trillion = Thousand * Billion

If (N = 0@) Then English = "zero": Exit Function

Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
Dim Frac As Currency: Frac = Abs(N - Fix(N))
If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))
Dim AtLeastOne As Integer: AtLeastOne = N >= 1

If (N >= Trillion) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & " trillion"
N = N - Int(N / Trillion) * Trillion
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Billion) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & " billion"
N = N - Int(N / Billion) * Billion
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Million) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N \ Million) & " million"
N = N Mod Million
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Thousand) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N \ Thousand) & " thousand"
N = N Mod Thousand
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= 1@) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N)
End If

If (Frac = 0@) Then
Buf = Buf & " exactly"
ElseIf (Int(Frac * 100@) = Frac * 100@) Then
If AtLeastOne Then Buf = Buf & " and "
Buf = Buf & Format$(Frac * 100@, "00") & "/100"
Else
If AtLeastOne Then Buf = Buf & " and "
Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000"
End If

English = Buf
End Function

Private Function EnglishDigitGroup(ByVal N As Integer) As String

Const Thousand = " Thousand"
Const Hundred = " Hundred"
Const One = "One"
Const Two = "Two"
Const Three = "Three"
Const Four = "Four"
Const Five = "Five"
Const Six = "Six"
Const Seven = "Seven"
Const Eight = "Eight"
Const Nine = "Nine"
Dim Buf As String: Buf = ""
Dim Flag As Integer: Flag = False

Select Case (N \ 1000)
Case 0: Buf = "": Flag = False
Case 1: Buf = One & Thousand: Flag = True
Case 2: Buf = Two & Thousand: Flag = True
Case 3: Buf = Three & Thousand: Flag = True
Case 4: Buf = Four & Thousand: Flag = True
Case 5: Buf = Five & Thousand: Flag = True
Case 6: Buf = Six & Thousand: Flag = True
Case 7: Buf = Seven & Thousand: Flag = True
Case 8: Buf = Eight & Thousand: Flag = True
Case 9: Buf = Nine & Thousand: Flag = True
End Select

If (Flag <> False) Then N = N Mod 1000
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & " "
Else
EnglishDigitGroup = Buf
Exit Function
End If

Select Case (N \ 100)
Case 0: Buf = "": Flag = False
Case 1: Buf = One & Hundred: Flag = True
Case 2: Buf = Two & Hundred: Flag = True
Case 3: Buf = Three & Hundred: Flag = True
Case 4: Buf = Four & Hundred: Flag = True
Case 5: Buf = Five & Hundred: Flag = True
Case 6: Buf = Six & Hundred: Flag = True
Case 7: Buf = Seven & Hundred: Flag = True
Case 8: Buf = Eight & Hundred: Flag = True
Case 9: Buf = Nine & Hundred: Flag = True
End Select

If (Flag <> False) Then N = N Mod 100
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & " "
Else
EnglishDigitGroup = Buf
Exit Function
End If

Select Case (N \ 10)
Case 0, 1: Flag = False
Case 2: Buf = Buf & "Twenty": Flag = True
Case 3: Buf = Buf & "Thirty": Flag = True
Case 4: Buf = Buf & "Forty": Flag = True
Case 5: Buf = Buf & "Fifty": Flag = True
Case 6: Buf = Buf & "Sixty": Flag = True
Case 7: Buf = Buf & "Seventy": Flag = True
Case 8: Buf = Buf & "Eighty": Flag = True
Case 9: Buf = Buf & "Ninety": Flag = True
End Select

If (Flag <> False) Then N = N Mod 10
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & "-"
Else
EnglishDigitGroup = Buf
Exit Function
End If

Select Case (N)
Case 0:
Case 1: Buf = Buf & One
Case 2: Buf = Buf & Two
Case 3: Buf = Buf & Three
Case 4: Buf = Buf & Four
Case 5: Buf = Buf & Five
Case 6: Buf = Buf & Six
Case 7: Buf = Buf & Seven
Case 8: Buf = Buf & Eight
Case 9: Buf = Buf & Nine
Case 10: Buf = Buf & "Ten"
Case 11: Buf = Buf & "Eleven"
Case 12: Buf = Buf & "Twelve"
Case 13: Buf = Buf & "Thirteen"
Case 14: Buf = Buf & "Fourteen"
Case 15: Buf = Buf & "Fifteen"
Case 16: Buf = Buf & "Sixteen"
Case 17: Buf = Buf & "Seventeen"
Case 18: Buf = Buf & "Eighteen"
Case 19: Buf = Buf & "Nineteen"
End Select

EnglishDigitGroup = Buf

End Function
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:20
Joined
May 7, 2009
Messages
19,230
Here only commented out /100 and added cent:



Option Compare Database

Function English(ByVal N As Currency) As String

Const Thousand = 1000@
Const Million = Thousand * Thousand
Const Billion = Thousand * Million
Const Trillion = Thousand * Billion

If (N = 0@) Then English = "zero": Exit Function

Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
Dim Frac As Currency: Frac = Abs(N - Fix(N))
If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))
Dim AtLeastOne As Integer: AtLeastOne = N >= 1

If (N >= Trillion) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & " trillion"
N = N - Int(N / Trillion) * Trillion
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Billion) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & " billion"
N = N - Int(N / Billion) * Billion
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Million) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N \ Million) & " million"
N = N Mod Million
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Thousand) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N \ Thousand) & " thousand"
N = N Mod Thousand
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= 1@) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N)
End If

If (Frac = 0@) Then
Buf = Buf & " exactly"
ElseIf (Int(Frac * 100@) = Frac * 100@) Then
If AtLeastOne Then Buf = Buf & " and "
Buf = Buf & Format$(Frac * 100@, "00") & "cents" '/100"
Else
If AtLeastOne Then Buf = Buf & " and "
Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000"
End If

English = Buf
End Function

Private Function EnglishDigitGroup(ByVal N As Integer) As String

Const Thousand = " Thousand"
Const Hundred = " Hundred"
Const One = "One"
Const Two = "Two"
Const Three = "Three"
Const Four = "Four"
Const Five = "Five"
Const Six = "Six"
Const Seven = "Seven"
Const Eight = "Eight"
Const Nine = "Nine"
Dim Buf As String: Buf = ""
Dim Flag As Integer: Flag = False

Select Case (N \ 1000)
Case 0: Buf = "": Flag = False
Case 1: Buf = One & Thousand: Flag = True
Case 2: Buf = Two & Thousand: Flag = True
Case 3: Buf = Three & Thousand: Flag = True
Case 4: Buf = Four & Thousand: Flag = True
Case 5: Buf = Five & Thousand: Flag = True
Case 6: Buf = Six & Thousand: Flag = True
Case 7: Buf = Seven & Thousand: Flag = True
Case 8: Buf = Eight & Thousand: Flag = True
Case 9: Buf = Nine & Thousand: Flag = True
End Select

If (Flag <> False) Then N = N Mod 1000
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & " "
Else
EnglishDigitGroup = Buf
Exit Function
End If

Select Case (N \ 100)
Case 0: Buf = "": Flag = False
Case 1: Buf = One & Hundred: Flag = True
Case 2: Buf = Two & Hundred: Flag = True
Case 3: Buf = Three & Hundred: Flag = True
Case 4: Buf = Four & Hundred: Flag = True
Case 5: Buf = Five & Hundred: Flag = True
Case 6: Buf = Six & Hundred: Flag = True
Case 7: Buf = Seven & Hundred: Flag = True
Case 8: Buf = Eight & Hundred: Flag = True
Case 9: Buf = Nine & Hundred: Flag = True
End Select

If (Flag <> False) Then N = N Mod 100
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & " "
Else
EnglishDigitGroup = Buf
Exit Function
End If

Select Case (N \ 10)
Case 0, 1: Flag = False
Case 2: Buf = Buf & "Twenty": Flag = True
Case 3: Buf = Buf & "Thirty": Flag = True
Case 4: Buf = Buf & "Forty": Flag = True
Case 5: Buf = Buf & "Fifty": Flag = True
Case 6: Buf = Buf & "Sixty": Flag = True
Case 7: Buf = Buf & "Seventy": Flag = True
Case 8: Buf = Buf & "Eighty": Flag = True
Case 9: Buf = Buf & "Ninety": Flag = True
End Select

If (Flag <> False) Then N = N Mod 10
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & "-"
Else
EnglishDigitGroup = Buf
Exit Function
End If

Select Case (N)
Case 0:
Case 1: Buf = Buf & One
Case 2: Buf = Buf & Two
Case 3: Buf = Buf & Three
Case 4: Buf = Buf & Four
Case 5: Buf = Buf & Five
Case 6: Buf = Buf & Six
Case 7: Buf = Buf & Seven
Case 8: Buf = Buf & Eight
Case 9: Buf = Buf & Nine
Case 10: Buf = Buf & "Ten"
Case 11: Buf = Buf & "Eleven"
Case 12: Buf = Buf & "Twelve"
Case 13: Buf = Buf & "Thirteen"
Case 14: Buf = Buf & "Fourteen"
Case 15: Buf = Buf & "Fifteen"
Case 16: Buf = Buf & "Sixteen"
Case 17: Buf = Buf & "Seventeen"
Case 18: Buf = Buf & "Eighteen"
Case 19: Buf = Buf & "Nineteen"
End Select

EnglishDigitGroup = Buf

End Function
 
You must log in or register to reply here.

Similar threads

T
Solved Convert amount to words
Replies
18
Views
994
Tiprof
T
N
Solved How to grab fields from Json string to be part of the invoice
Replies
14
Views
445
Edgar_
E
A
VBA - How to get the number of decimal places for a table field
Replies
16
Views
1,164
MajP
MajP
N
Challenges with Ms Access VBA with socket programming
Replies
11
Views
4,663
The_Doc_Man
The_Doc_Man
T
Any Improvements to be made?
2
Replies
39
Views
1,311
tmyers
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom