Another useful code for converting numbers to text:
'Attribute VB_Name = "SpellNumber"
Option Explicit
' Ivan Soto / Sunday 5 January 2014
Public Function SpellIntegerEn(ByVal dCandidate As Double) As String
Dim dSign As Double
Dim dCand As Double
Dim dTemp As Double
Dim sTemp As String
dSign = VBA.Interaction.IIf(dCandidate < 0#, -1, VBA.Interaction.IIf(dCandidate > 0, 1, 0))
dCand = dSign * VBA.Conversion.Fix(dCandidate)
If dCand > 999999999999999# Then
SpellIntegerEn = "Number to spell exceeds 999999999999999. Unable to spell it."
ElseIf dCand < 1# Then ' zero, just say so
SpellIntegerEn = "zero"
Else
' from 1 to 999 billion, positions shown as 9's here: 999,xxx,xxx,xxx,xxx
dTemp = VBA.Conversion.Fix(dCand / 1000000000000#)
If dTemp > 0 Then
sTemp = SpellZeroTo999(dTemp, "billion")
End If
' thousands of millions, positions shown as 9's here: xxx,999,xxx,xxx,xxx
dTemp = VBA.Conversion.Fix(FPremainder(dCand, 1000000000000#) / 1000000000#)
If dTemp > 0 Then
sTemp = sTemp & VBA.Interaction.IIf(sTemp = "", "", " ") & SpellZeroTo999(dTemp, "thousand")
End If
' 1 to 999 of millions, positions shown as 9's here: xxx,xxx,999,xxx,xxx
dTemp = VBA.Conversion.Fix(FPremainder(dCand, 1000000000#) / 1000000#)
If dTemp > 0 Then
sTemp = sTemp & VBA.Interaction.IIf(sTemp = "", "", " ") & SpellZeroTo999(dTemp, "")
End If
If VBA.Conversion.Fix(FPremainder(dCand, 1000000000000#) / 1000000#) > 0 Then
sTemp = sTemp & VBA.Interaction.IIf(sTemp = "", "", " ") & "million"
End If
' thousands positions shown as 9's here: xxx,xxx,xxx,999,xxx
dTemp = VBA.Conversion.Fix(FPremainder(dCand, 1000000#) / 1000#)
If dTemp > 0 Then
sTemp = sTemp & VBA.Interaction.IIf(sTemp = "", "", " ") & SpellZeroTo999(dTemp, "thousand")
End If
' the rightmost three positions shown as 9's here: xxx,xxx,xxx,xxx,999
dTemp = FPremainder(dCand, 1000#)
If dTemp > 0 Then
sTemp = sTemp & VBA.Interaction.IIf(sTemp = "", "", " ") & SpellZeroTo999(dTemp, "")
End If
If dSign < 0 Then
SpellIntegerEn = "minus " & sTemp
Else
SpellIntegerEn = sTemp
End If
End If
End Function
' Ivan Soto / Sunday 5 January 2014
Private Function SpellZeroTo999(ByVal dCandidate As Double, ByVal sSuffix As String)
Dim sTemp As String
If dCandidate > 0# And dCandidate < 1000# Then
If dCandidate > 99# Then
sTemp = SpellZeroToNineteen(VBA.Conversion.Fix(dCandidate / 100#)) & " hundred"
End If
If (dCandidate Mod 100#) > 19# Then
sTemp = sTemp & VBA.Interaction.IIf(sTemp = "", "", " ") & Spell20To99(dCandidate Mod 100#)
ElseIf (dCandidate Mod 100#) > 0# Then
sTemp = sTemp & VBA.Interaction.IIf(sTemp = "", "", " ") & SpellZeroToNineteen(dCandidate Mod 100#)
End If
SpellZeroTo999 = sTemp & VBA.Interaction.IIf(sSuffix = "", "", " " & sSuffix)
End If
End Function
' Ivan Soto / Sunday 5 January 2014
Private Function SpellZeroToNineteen(ByVal dCandidate As Double) As String
' dCandidate is assumed to be a non-negative integer
If dCandidate < 1 Then
SpellZeroToNineteen = "zero"
ElseIf (dCandidate Mod 100#) < 20# Then
SpellZeroToNineteen = VBA.Interaction.Choose((dCandidate Mod 100#) + 1, _
"", "one", "two", "three", "four", "five", _
"six", "seven", "eight", "nine", "ten", "eleven", _
"twelve", "thirteen", "fourteen", "fifteen", "sixteen", _
"seventeen", "eighteen", "nineteen")
End If
End Function
' Ivan Soto / Sunday 5 January 2014
Private Function Spell20To99(ByVal dCandidate As Double) As String
Dim dCand As Double
Dim dUnits As Double
Dim sTemp As String
dCand = (dCandidate Mod 100#)
If dCand > 19# Then
dUnits = dCand Mod 10#
sTemp = VBA.Interaction.Choose(VBA.Conversion.Fix(dCand / 10#), "", "twenty", "thirty", "forty", "fifty", _
"sixty", "seventy", "eighty", "ninety")
If dUnits > 0# Then sTemp = sTemp & "-" & SpellZeroToNineteen(dUnits)
End If
Spell20To99 = sTemp
End Function
' Ivan Soto / Sunday 5 January 2014
Private Function FPremainder(ByVal dCandidate As Double, ByVal dDivisor As Double) As Double
Dim dX As Double
dX = VBA.Conversion.Fix(dCandidate / dDivisor)
FPremainder = dCandidate - (dX * dDivisor)
End Function
No comments:
Post a Comment