Selva’s Site

I came across the following codes at www.dailydoseofexcel.com. This code builds a UDF (User defined function) that can be used as a Excel Worksheet function, for e.g., Write “=ToWords(10284)” in a cell and enter, Excel calculates and returns a string “Ten Thousand Two Hundred Eighty Four”…
- Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

Attribute VB_Name = "Module1"
Option Explicit
'  ____________________________________________________________
Public Function UnitString(ByVal Ind As Integer) As String
   Dim ToStr As Variant
   ToStr = Array("Zero", "One", "Two", _
                "Three", "Four", "Five", _
                "Six", "Seven", "Eight", _
                "Nine", "Ten", "Eleven", _
                "Tweleve", "Thirteen", _
                "Fourteen", "Fifteen", _
               "Sixteen", "Seventeen", _
               "Eighteen", "Nineteen", "Twenty")
   UnitString = ToStr(Ind)
End Function
'  ____________________________________________________________
Public Function TenString( _
                ByVal Ind As Integer _
                ) As String
   Dim ToStr As Variant
   ToStr = Array("Ten", "Twenty", "Thirty", _
            "Fourty", "Fifty", "Sixty", _
            "Seventy", "Eighty", "Ninety")
   TenString = ToStr(Ind)
End Function
'  ____________________________________________________________
Public Function PlaceString( _
            ByVal Ind As Integer _
            ) As String
   Dim ToStr As Variant
   ToStr = Array("Unit", "Tenth", "Hundred", _
                  "Thousand", "Lakh", "Crore", _
                  "Hundred" _
                 )
   PlaceString = ToStr(Ind)

End Function
'  ____________________________________________________________
Public Function ToWords(ByVal No As Long) _
                    As String
   Dim ToStr As String
   'Dim No As Long
   Dim Value As Long
   Dim Divisor As Long
   Dim i As Long

   Divisor = 1000000000

   i = 6
   Do While (No >= 1000)
      Value = Int(No / Divisor)
      No = No Mod Divisor
      Divisor = Int(Divisor / 100)
      If (Value <> 0) Then
         ToStr = ToStr & NumberToWords100(Value) _
                    & " " & PlaceString(i) + " "
      End If
      i = i - 1
   Loop

   Value = Int(No / 100)
   No = No Mod 100

   If (Value <> 0) Then
      ToStr = ToStr & NumberToWords100(Value) _
                & " " & PlaceString(i) + " "
   End If
   i = i - 1

   If No <> 0 Then
      ToStr = ToStr & NumberToWords100(No)
   End If
   ToWords = Trim(ToStr)
End Function
'  ____________________________________________________________
Private Function NumberToWords100( _
                ByVal No As Integer _
                ) As String
   Dim ToStr As String
   Dim Ten As Integer, Unit As Integer

   If (No <= 20) Then
      NumberToWords100 = UnitString(No)
      Exit Function
   End If
   No = No Mod 100
   Ten = Int(No / 10)
   No = No Mod 10
   Unit = No
   If (Ten >= 2) Then
      ToStr = ToStr & " " & TenString(Ten - 1)
      If (Unit > 0) Then
         ToStr = ToStr & " " & UnitString(Unit)
      End If
   ElseIf (Ten > 0) Then
      ToStr = ToStr & " " & UnitString((Ten * 10) + Unit)
   ElseIf (Unit > 0) Then
      ToStr = ToStr & " " & UnitString(Unit)
   End If

   NumberToWords100 = Trim(ToStr)
End Function
'  ____________________________________________________________
Private Sub Form_Load()
     MsgBox ToWords(10023)
End Sub