VBA for Excel 2007 tutorial-function to translate numbers


Create function to translate numbers

In this example, you will learn VBA code to create your own function to translate an input one or two-digit number to English words.
To run this example VBA code, you will need to create a standard module and copy and paste the VBA code below to the block of the module(Module1).Then in the Excel sheet, you can call the function to perform its task.

Example output:
Enter a one or two-digit number: 

87 Eighty Seven


VBA code for this example:

Function Digit1(val As Variant) As String
If Right(val, 1) = "0" Then
    Digit1 = "zero"
ElseIf Right(val, 1) = "1" Then
    Digit1 = "one"
ElseIf Right(val, 1) = "2" Then
    Digit1 = "two"
ElseIf Right(val, 1) = "3" Then
    Digit1 = "three"
ElseIf Right(val, 1) = "4" Then
    Digit1 = "four"
ElseIf Right(val, 1) = "5" Then
    Digit1 = "five"
ElseIf Right(val, 1) = "6" Then
    Digit1 = "six"
ElseIf Right(val, 1) = "7" Then
    Digit1 = "seven"
ElseIf Right(val, 1) = "8" Then
    Digit1 = "eight"
Else: Digit1 = "nine"
 
End If
 
End Function
Function Digit2(val As Variant)
 
If Left(Right(val, 2), 1) = "0" Then
Digit2 = ""
ElseIf Left(Right(val, 2), 1) = "1" Then
    Select Case Digit1(val)
        Case "zero": Digit2 = "Ten"
        Case "one": Digit2 = "Eleven"
        Case "two": Digit2 = "Twelve"
        Case "three": Digit2 = "Thirteen"
        Case "four": Digit2 = "Fourteen"
        Case "five": Digit2 = "Fifteen"
        Case "six": Digit2 = "Sixteen"
        Case "seven": Digit2 = "Seventeen"
        Case "eight": Digit2 = "Eighteen"
        Case "nine": Digit2 = "Nineteen"
       
    End Select
   
ElseIf Left(Right(val, 2), 1) = "2" Then
    Digit2 = "Twenty"
ElseIf Left(Right(val, 2), 1) = "3" Then
    Digit2 = "Thirty"
ElseIf Left(Right(val, 2), 1) = "4" Then
    Digit2 = "Forty"
ElseIf Left(Right(val, 2), 1) = "5" Then
    Digit2 = "Fifty"
ElseIf Left(Right(val, 2), 1) = "6" Then
    Digit2 = "Sixty"
ElseIf Left(Right(val, 2), 1) = "7" Then
    Digit2 = "Seventy"
ElseIf Left(Right(val, 2), 1) = "8" Then
    Digit2 = "Eighty"
Else: Digit2 = "Ninety"
 
End If
 
End Function
 
Function TRAN(val As Variant) As String
If Len(val) = 1 Then
    TRAN = Digit1(val)
ElseIf Len(val) = 2 And Left(val, 1) <> "1" Then
    If Right(val, 1) <> "0" Then
    TRAN = Digit2(val) & Space(2) & Digit1(val)
    Else: TRAN = Digit2(val)
    End If
ElseIf Left(val, 1) = "1" And Len(Val)<=2 Then
    TRAN = Digit2(val)
 
Else: TRAN = "Only two-digit number is allowed."
End If
End Function


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.