numeric data to text 1

[mortgsteps]

Does anyone know where i can get a code sample to change $100.00 to one hundred dollars and zero cents.<br>Thanks<br>

 

[JimConrad]

<FONT FACE=monospace><br>Function NumberToWords(InputNumber As Double, Optional NeedCents As Boolean = False) As Variant<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim varWords&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Variant<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Billions&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Double<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Millions&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Double<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Thousands&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Double<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Hundreds&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Double<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Cents&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim varNumber&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Variant<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim OneBillion&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Double<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If Not IsNumeric(InputNumber) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NumberToWords = Null<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Exit Function<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If InputNumber = 0 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NumberToWords = &quot;Zero &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Exit Function<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;OneBillion = 1000000000<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Billions = Int(InputNumber / OneBillion)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Millions = InputNumber - (Billions * OneBillion)<br>&nbsp;&nbsp;&nbsp;&nbsp;Millions = Int(Millions / 1000000)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Thousands = InputNumber - (Billions * OneBillion) - (Millions * 1000000)<br>&nbsp;&nbsp;&nbsp;&nbsp;Thousands = Int(Thousands / 1000)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Hundreds = InputNumber - (Billions * OneBillion) - (Millions * 1000000) - (Thousands * 1000)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Cents = (InputNumber - Int(InputNumber)) * 100<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;varWords = SmallNumberToWords(Billions) + &quot;Billion, &quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SmallNumberToWords(Millions) + &quot;Million, &quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SmallNumberToWords(Thousands) + &quot;Thousand, &quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SmallNumberToWords(Hundreds)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If NeedCents Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;varWords = varWords & &quot;And &quot; & IIf(Cents &lt; 10, &quot;0&quot; & Cents, Cents) & &quot;/100&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;NumberToWords = varWords<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Function<br><br>Private Function SmallNumberToWords(SmallNumber As Double) As Variant<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Hundreds&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Tens&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Units&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim HundredsWords&nbsp;&nbsp;&nbsp;As Variant<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim TensWords&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Variant<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim UnitsWords&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Variant<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Hundreds = Int(SmallNumber / 100)<br>&nbsp;&nbsp;&nbsp;&nbsp;Tens = SmallNumber - (Hundreds * 100)&nbsp;&nbsp;&nbsp;' Part 1 - Get rid of hundreds<br>&nbsp;&nbsp;&nbsp;&nbsp;Tens = Int(Tens / 10) * 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Part 2 - Truncate Units<br>&nbsp;&nbsp;&nbsp;&nbsp;Units = (SmallNumber - (Hundreds * 100)) - Tens<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If Tens &lt;= 19 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tens = Tens + Units<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Units = 0<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Select Case Hundreds<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 1: HundredsWords = &quot;One Hundred &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 2: HundredsWords = &quot;Two Hundred &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 3: HundredsWords = &quot;Three Hundred &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 4: HundredsWords = &quot;Four Hundred &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 5: HundredsWords = &quot;Five Hundred &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 6: HundredsWords = &quot;Six Hundred &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 7: HundredsWords = &quot;Seven Hundred &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 8: HundredsWords = &quot;Eight Hundred &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 9: HundredsWords = &quot;Nine Hundred &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case Else: HundredsWords = Null<br>&nbsp;&nbsp;&nbsp;&nbsp;End Select<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Select Case Tens<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 1: TensWords = &quot;One &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 2: TensWords = &quot;Two &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 3: TensWords = &quot;Three &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 4: TensWords = &quot;Four &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 5: TensWords = &quot;Five &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 6: TensWords = &quot;Six &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 7: TensWords = &quot;Seven &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 8: TensWords = &quot;Eight &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 9: TensWords = &quot;Nine &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 10: TensWords = &quot;Ten &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 11: TensWords = &quot;Eleven &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 12: TensWords = &quot;Twelve &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 13: TensWords = &quot;Thirteen &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 14: TensWords = &quot;Fourteen &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 15: TensWords = &quot;Fifteen &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 16: TensWords = &quot;Sixteen &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 17: TensWords = &quot;Seventeen &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 18: TensWords = &quot;Eighteen &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 19: TensWords = &quot;Nineteen &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 20: TensWords = &quot;Twenty &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 30: TensWords = &quot;Thirty &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 40: TensWords = &quot;Forty &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 50: TensWords = &quot;Fifty &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 60: TensWords = &quot;Sixty &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 70: TensWords = &quot;Seventy &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 80: TensWords = &quot;Eighty &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 90: TensWords = &quot;Ninety &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case Else: TensWords = Null<br>&nbsp;&nbsp;&nbsp;&nbsp;End Select<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Select Case Units<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 1: UnitsWords = &quot;One &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 2: UnitsWords = &quot;Two &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 3: UnitsWords = &quot;Three &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 4: UnitsWords = &quot;Four &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 5: UnitsWords = &quot;Five &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 6: UnitsWords = &quot;Six &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 7: UnitsWords = &quot;Seven &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 8: UnitsWords = &quot;Eight &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 9: UnitsWords = &quot;Nine &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case Else: UnitsWords = Null<br>&nbsp;&nbsp;&nbsp;&nbsp;End Select<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;'Debug.Print &quot;Hundreds &quot; & Hundreds; HundredsWords<br>&nbsp;&nbsp;&nbsp;&nbsp;'Debug.Print &quot;Tens &quot; & Tens; TensWords<br>&nbsp;&nbsp;&nbsp;&nbsp;'Debug.Print &quot;Ones &quot; & Units; UnitsWords<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;SmallNumberToWords = HundredsWords & TensWords & UnitsWords<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>End Function<br><br></font> <p>Jim Conrad<br><a href=mailto:JimConrad@Consultant.com>JimConrad@Consultant.com</a><br><a href= > </a><br>

 

[DougP]

Man Jimbo,<br>Thats quite a function you got there.<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.