배움터  
  HOME > 배움터 > 무료강좌
무료강좌
 
엑셀, 곽승주님의 오튜공구함 제작으로 배워보는 VBA 이야기, Excel
  

11. 숫자를 한글/한자/영어로 변환 -Ⅰ
자료다운로드 : 오튜공구함011.xls 

안녕하세요 오튜가족 여러분!

이번 시간에는 숫자로 표시된 금액을 한글, 한자 또는 영어로 변경하는 기능을 만들어 보려구 합니다. 제 업무에서는 이러한 기능을 사용할 일이 없어 정확히 이런 기능이 왜 필요한 지는 모르겠습니다. 다만 회계관련 부분에서는 필요한 걸로 막연히 추측하고 있습니다. 일단 오늘은 영문으로 변환하는 것을 만들어 보려고 합니다. 그런데 이번에는 직접 만들지 않고 이미 다른 사람들이 공들여 만든 것을 이용해볼려고 합니다. 관련자료를 찾아보니 마이크로소프트의 기술지원자료에 두 가지 예제가 있더군요.

따라서 저는 핵심모듈은 외부에서 가져오고 껍데기만 만들려고 합니다.

Option Explicit

     '****************
     ' Main Function *
     '****************          

     Function SpellNumber(ByVal MyNumber)
          Dim Dollars, Cents, Temp
             Dim DecimalPlace, Count   

             ReDim Place(9) As String
             Place(2) = " Thousand "
             Place(3) = " Million "
             Place(4) = " Billion "
             Place(5) = " Trillion " 

          ' String representation of amount.
             MyNumber = Trim(Str(MyNumber))

             ' Position of decimal place 0 if none. 
             DecimalPlace = InStr(MyNumber, ".")
            ' Convert cents and set MyNumber to dollar amount.
            If DecimalPlace > 0 Then
               Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) &  "00", 2))
                   MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
            End If

         Count = 1
         Do While MyNumber <> ""
               Temp = GetHundreds(Right(MyNumber, 3))
                   If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
                   If Len(MyNumber) > 3 Then
                     MyNumber = Left(MyNumber, Len(MyNumber) - 3)
                   Else
                         MyNumber = ""
                   End If
                   Count = Count + 1
          Loop

          Select Case Dollars
               Case ""
                    Dollars = "No Dollars"
               Case "One"
                    Dollars = "One Dollar"
               Case Else
                    Dollars = Dollars & " Dollars"
          End Select

          Select Case Cents
               Case ""
                    Cents = " and No Cents"
               Case "One"
                    Cents = " and One Cent"
               Case Else
                    Cents = " and " & Cents & " Cents"
          End Select

          SpellNumber = Dollars & Cents
          End Function

     '*******************************************
     ' Converts a number from 100-999 into text *
     '*******************************************

     Function GetHundreds(ByVal MyNumber)
     Dim Result As String

          If Val(MyNumber) = 0 Then Exit Function
          MyNumber = Right("000" & MyNumber, 3)

          ' Convert the hundreds place.
          If Mid(MyNumber, 1, 1) <> "0" Then
               Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
          End If

          ' Convert the tens and ones place.
          If Mid(MyNumber, 2, 1) <> "0" Then
                Result = Result & GetTens(Mid(MyNumber, 2))
          Else
               Result = Result & GetDigit(Mid(MyNumber, 3))
          End If

          GetHundreds = Result
     End Function

     '*********************************************
     ' Converts a number from 10 to 99 into text. *
     '*********************************************

     Function GetTens(TensText)
          Dim Result As String

          Result = "" ' Null out the temporary function value.
          If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...

               Select Case Val(TensText)
                    Case 10: Result = "Ten"
                    Case 11: Result = "Eleven"
                    Case 12: Result = "Twelve"
                    Case 13: Result = "Thirteen"
                    Case 14: Result = "Fourteen"
                    Case 15: Result = "Fifteen"
                    Case 16: Result = "Sixteen"
                    Case 17: Result = "Seventeen"
                    Case 18: Result = "Eighteen"
                    Case 19: Result = "Nineteen"
                    Case Else
               End Select
          Else ' If value between 20-99...
               Select Case Val(Left(TensText, 1))
                    Case 2: Result = "Twenty "
                    Case 3: Result = "Thirty "
                    Case 4: Result = "Forty "
                    Case 5: Result = "Fifty "
                    Case 6: Result = "Sixty "
                    Case 7: Result = "Seventy "
                    Case 8: Result = "Eighty "
                    Case 9: Result = "Ninety "
                    Case Else
               End Select
               Result = Result & GetDigit _
                    (Right(TensText, 1)) ' Retrieve ones place.
          End If
          GetTens = Result
     End Function

     '*******************************************
     ' Converts a number from 1 to 9 into text. *
     '*******************************************

     Function GetDigit(Digit)
          Select Case Val(Digit)
               Case 1: GetDigit = "One"
               Case 2: GetDigit = "Two"
               Case 3: GetDigit = "Three"
               Case 4: GetDigit = "Four"
               Case 5: GetDigit = "Five"
               Case 6: GetDigit = "Six"
               Case 7: GetDigit = "Seven"
               Case 8: GetDigit = "Eight"
               Case 9: GetDigit = "Nine"
               Case Else: GetDigit = ""
          End Select
     End Function 
     Function ConvertCurrencyToEnglish(ByVal MyNumber)
          Dim Temp
          Dim Dollars, Cents
          Dim DecimalPlace, Count

          ReDim Place(9) As String
          Place(2) = " Thousand "
          Place(3) = " Million "
          Place(4) = " Billion "
          Place(5) = " Trillion "

          ' Convert MyNumber to a string, trimming extra spaces.
          MyNumber = Trim(Str(MyNumber))

          ' Find decimal place.
          DecimalPlace = InStr(MyNumber, ".")

          ' If we find decimal place...
          If DecimalPlace > 0 Then
               ' Convert cents
               Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
               Cents = ConvertTens(Temp)

              ' Strip off cents from remainder to convert.
              MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
          End If

          Count = 1
          Do While MyNumber <> ""
               ' Convert last 3 digits of MyNumber to English dollars.
               Temp = ConvertHundreds(Right(MyNumber, 3))
               If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
               If Len(MyNumber) > 3 Then
                    ' Remove last 3 converted digits from MyNumber.
                    MyNumber = Left(MyNumber, Len(MyNumber) - 3)
               Else
                    MyNumber = ""
               End If
               Count = Count + 1
          Loop

          ' Clean up dollars.
          Select Case Dollars
               Case ""
                    Dollars = "No Dollars"
               Case "One"
                    Dollars = "One Dollar"
               Case Else
                    Dollars = Dollars & " Dollars"
          End Select

          ' Clean up cents.
          Select Case Cents
               Case ""
                    Cents = " And No Cents"
               Case "One"
                    Cents = " And One Cent"
               Case Else
                    Cents = " And " & Cents & " Cents"
          End Select

          ConvertCurrencyToEnglish = Dollars & Cents
     End Function
     Private Function ConvertHundreds(ByVal MyNumber)
          Dim Result As String

          ' Exit if there is nothing to convert.
          If Val(MyNumber) = 0 Then Exit Function

          ' Append leading zeros to number.
          MyNumber = Right("000" & MyNumber, 3)

          ' Do we have a hundreds place digit to convert?
          If Left(MyNumber, 1) <> "0" Then
               Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
          End If

         ' Do we have a tens place digit to convert?
          If Mid(MyNumber, 2, 1) <> "0" Then
              Result = Result & ConvertTens(Mid(MyNumber, 2))
          Else
               ' If not, then convert the ones place digit.
               Result = Result & ConvertDigit(Mid(MyNumber, 3))
          End If

          ConvertHundreds = Trim(Result)
     End Function

     Private Function ConvertTens(ByVal MyTens)
          Dim Result As String

          ' Is value between 10 and 19?
          If Val(Left(MyTens, 1)) = 1 Then
              Select Case Val(MyTens)
                    Case 10: Result = "Ten"
                    Case 11: Result = "Eleven"
                    Case 12: Result = "Twelve"
                    Case 13: Result = "Thirteen"
                    Case 14: Result = "Fourteen"
                    Case 15: Result = "Fifteen"
                    Case 16: Result = "Sixteen"
                    Case 17: Result = "Seventeen"
                    Case 18: Result = "Eighteen"
                    Case 19: Result = "Nineteen"
                    Case Else
               End Select
          Else
               ' .. otherwise it's between 20 and 99.
               Select Case Val(Left(MyTens, 1))
                    Case 2: Result = "Twenty "
                    Case 3: Result = "Thirty "
                    Case 4: Result = "Forty "
                    Case 5: Result = "Fifty "
                    Case 6: Result = "Sixty "
                    Case 7: Result = "Seventy "
                    Case 8: Result = "Eighty "
                    Case 9: Result = "Ninety "
                    Case Else
               End Select

               ' Convert ones place digit.
               Result = Result & ConvertDigit(Right(MyTens, 1))
          End If

          ConvertTens = Result
     End Function

     Private Function ConvertDigit(ByVal MyDigit)
          Select Case Val(MyDigit)
               Case 1: ConvertDigit = "One"
               Case 2: ConvertDigit = "Two"
               Case 3: ConvertDigit = "Three"
               Case 4: ConvertDigit = "Four"
               Case 5: ConvertDigit = "Five"
               Case 6: ConvertDigit = "Six"
               Case 7: ConvertDigit = "Seven"
               Case 8: ConvertDigit = "Eight"
               Case 9: ConvertDigit = "Nine"
               Case Else: ConvertDigit = ""
          End Select
     End Function

Function SpellNumber(ByVal MyNumber)와 Function ConvertCurrencyToEnglish(ByVal MyNumber) 두가지 함수가 있는데 어느 것을 사용해도 상관없는데 Function ConvertCurrencyToEnglish를 사용하기로 하죠.

목차 | 이전 | 다음