Macro Excel untuk Penulisan Rapot SD-SMP-SMA

Pada suatu waktu ditanya oleh seseorang tentang menggunakan Excel untuk membuat rapot sekolah. Yang diinginkan yaitu mengubah angka menjadi kata, misal angka 89,08 menjadi Delapan Sembilan koma Nol Delapan.

Dari pembuatan macro sebelumnya yaitu mengubah angka menjadi kalimat untuk kebutuhan keuangan seperti dari posting sebelumnya dengan judul Macro Excel: Membuat fungsi terbilang, kemudian mencoba modifikasi beberapa saat maka dapatlah kami bagikan kepada pembaca seperti pada kode dibawah. Kode asli saya ambil dari Macro Excel: Number to Word conversion for various purpose in English. Sumber utama dari www.ozgrid.com.

Macro dibawah dapat didownload file excelnya, sehingga tinggal pakai saja.

Function toword(ByVal MyNumber)

Dim Temp

         Dim Number, Cents

         Dim DecimalPlace, Count

         ReDim Place(9) As String

         ' 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 Number.

            Temp = ConvertHundreds(Right(MyNumber, 3))

            If Temp <> "" Then Number = Temp & Place(Count) & Number

            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 number.

        Select Case Number

            Case ""

               Number = "Nol"

            Case "Satu"

               Number = "Satu"

            Case Else

               Number = Number

         End Select

         ' Clean up cents.

         Select Case Cents

            Case ""

               Cents = " koma Nol"

            Case "Satu"

               Cents = " koma Satu"

            Case Else

               Cents = " koma " & Cents

         End Select

         toword = Number & 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)) & " Nol "

         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 = "Satu Nol"

               Case 11: Result = "Satu Satu"

               Case 12: Result = "Satu Dua"

               Case 13: Result = "Satu Tiga"

               Case 14: Result = "Satu Empat"

               Case 15: Result = "Satu Lima"

               Case 16: Result = "Satu Enam"

               Case 17: Result = "Satu Tujuh"

               Case 18: Result = "Satu Delapan"

               Case 19: Result = "Satu Sembilan"

               Case Else

            End Select

         Else

            ' .. otherwise it's between 20 and 99.

            Select Case Val(Left(MyTens, 1))

               Case 0: Result = "Nol "

               Case 2: Result = "Dua "

               Case 3: Result = "Tiga "

               Case 4: Result = "Empat "

               Case 5: Result = "Lima "

               Case 6: Result = "Enam "

               Case 7: Result = "Tujuh "

               Case 8: Result = "Delapan "

               Case 9: Result = "Sembilan "

               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 0: ConvertDigit = "Nol"

            Case 1: ConvertDigit = "Satu"

            Case 2: ConvertDigit = "Dua"

            Case 3: ConvertDigit = "Tiga"

            Case 4: ConvertDigit = "Empat"

            Case 5: ConvertDigit = "Lima"

            Case 6: ConvertDigit = "Enam"

            Case 7: ConvertDigit = "Tujuh"

            Case 8: ConvertDigit = "Delapan"

            Case 9: ConvertDigit = "Sembilan"

            Case Else: ConvertDigit = ""

         End Select

End Function

About mdin