Fungsi Terbilang Di MS Excel

Fungsi terbilang adalah salah satu add-ins untuk menterjemahkan angka menjadi kata-kata pada aplikasi MS Excel. 
Fungsi terbilang ini mampu menterjemahkan angka sampai 15 digit dan 2 angka dibelakang koma. 15 digit angka merupakan nilai yang dapat ditampung MS Excel sebelum dirubah ke nilai Exponen.



Lankah-langkahnya adalah sebagai berikut :

• Buka file Excel, masuk ke menu Tools » Macro » Visual Basic Editor atau dengan shortcut ", setelah Visual Basic Editor terbuka, Anda masuk ke menu Insert » Module, kemudian Paste-kan salah satu kode dibawah ini.

Public Function Terbilang(strAngka As String, _ Optional MataUang As String = "Rupiah") As String Dim strJmlHuruf$, intPecahan As Integer Dim strPecahan$, Urai$, Bil1$, strTot$, Bil2$ Dim X As Integer, Y As Integer, z As Integer On Error GoTo Pesan Dim strValid As String, huruf As String * 1 Dim i As Integer strValid = "1234567890" For i% = 1 To Len(strAngka) huruf = Chr(Asc(Mid(strAngka, i%, 1))) If InStr(strValid, huruf) = 0 Then Set AngkaTerbilang = Nothing MsgBox "Harus karakter angka!", _ vbCritical, "Terbilang Berpesan" Exit Function End If Next i% If strAngka = "" Then Exit Function If Len(Trim(strAngka)) > 15 Then GoTo Pesan strJmlHuruf = LTrim(strAngka) If (intPecahan = 0) Then strPecahan = "" Else strPecahan = "" End If X = 0 Y = 0 Urai = "" While (X < Len(strJmlHuruf)) X = X + 1 strTot = Mid(strJmlHuruf, X, 1) Y = Y + Val(strTot) z = Len(strJmlHuruf) - X + 1 Select Case Val(strTot) Case 1 If (z = 1 Or z = 7 Or z = 10 Or z = 13) Then Bil1 = "Satu " ElseIf (z = 4) Then If (X = 1) Then Bil1 = "Se" Else Bil1 = "Satu " End If ElseIf (z = 2 Or z = 5 Or z = 8 Or z = 11 Or z = 14) Then X = X + 1 strTot = Mid(strJmlHuruf, X, 1) z = Len(strJmlHuruf) - X + 1 Bil2 = "" Select Case Val(strTot) Case 0: Bil1 = "Sepuluh " Case 1: Bil1 = "Sebelas " Case 2: Bil1 = "Dua Belas " Case 3: Bil1 = "Tiga Belas " Case 4: Bil1 = "Empat Belas " Case 5: Bil1 = "Lima Belas " Case 6: Bil1 = "Enam Belas " Case 7: Bil1 = "Tujuh Belas " Case 8: Bil1 = "Delapan Belas " Case 9: Bil1 = "Sembilan Belas " End Select Else Bil1 = "Se" End If Case 2: Bil1 = "Dua " Case 3: Bil1 = "Tiga " Case 4: Bil1 = "Empat " Case 5: Bil1 = "Lima " Case 6: Bil1 = "Enam " Case 7: Bil1 = "Tujuh " Case 8: Bil1 = "Delapan " Case 9: Bil1 = "Sembilan " Case Else Bil1 = "" End Select If (Val(strTot) > 0) Then If (z = 2 Or z = 5 Or z = 8 Or z = 11 Or z = 14) Then Bil2 = "Puluh " ElseIf (z = 3 Or z = 6 Or z = 9 Or z = 12 Or z = 15) Then Bil2 = "ratus " Else Bil2 = "" End If Else Bil2 = "" End If If (Y > 0) Then Select Case z Case 4: Bil2 = Bil2 + "ribu " Y = 0 Case 7: Bil2 = Bil2 + "Juta " Y = 0 Case 10: Bil2 = Bil2 + "Milyar " Y = 0 Case 13: Bil2 = Bil2 + "Trilyun " Y = 0 End Select End If Urai = Urai + Bil1 + Bil2 Wend Urai = Urai + strPecahan Terbilang = StrConv((Urai & MataUang), vbProperCase) Exit Function Pesan: Terbilang = "(maksimal 15 digit)" End Function

Public Function Terbilang(xbil As Double)<br /> Dim nilai, i, j, k, hasil$, HasilAkhir$, Bilangan#, Digit, Rp$, Bil$<br /> <br /> If IsNull(xbil) Then<br /> Terbilang = Null<br /> Exit Function<br /> End If<br /> <br /> 'Pengelompokan<br /> Dim Kel$(1 To 6), Angka$(1 To 9), Sat$(1 To 3)<br /> Kel$(1) = "Biliun "<br /> Kel$(2) = "Triliun "<br /> Kel$(3) = "Miliar "<br /> Kel$(4) = "Juta "<br /> Kel$(5) = "Ribu "<br /> Kel$(6) = ""<br /> <br /> 'Data angka<br /> Angka$(1) = "Satu "<br /> Angka$(2) = "Dua "<br /> Angka$(3) = "Tiga "<br /> Angka$(4) = "Empat "<br /> Angka$(5) = "Lima "<br /> Angka$(6) = "Enam "<br /> Angka$(7) = "Tujuh "<br /> Angka$(8) = "Delapan "<br /> Angka$(9) = "Sembilan "<br /> <br /> 'Satuan<br /> Sat$(1) = "Ratus "<br /> Sat$(2) = "Puluh "<br /> Sat$(3) = ""<br /> <br /> 'Mulai<br /> Bilangan# = Val(xbil)<br /> HasilAkhir$ = ""<br /> GoSub HitungHuruf<br /> If hasil$ <> "" Then<br /> HasilAkhir$ = hasil$ + "Rupiah"<br /> End If<br /> <br /> 'Hitung Pecahan<br /> Bilangan# = Fix((Bilangan# - Fix(Bilangan#) + 0.005) * 100#)<br /> If Bilangan# > 0 Then<br /> GoSub HitungHuruf<br /> If hasil$ <> "" Then<br /> HasilAkhir$ = HasilAkhir$ + " " + hasil$ + "Sen"<br /> End If<br /> End If<br /> <br /> Terbilang = HasilAkhir$<br /> Exit Function<br /> <br /> HitungHuruf:<br /> Rp$ = Right$(String$(18, "0") + LTrim$(Str$(Fix(Bilangan#))), 18)<br /> hasil$ = ""<br /> <br /> If Val(Rp$) = 0 Then Return<br /> <br /> 'Bilangan Bulat<br /> For i = 1 To 6<br /> Bil$ = Mid$(Rp$, i * 3 - 2, 3)<br /> <br /> If Val(Bil$) = 1 And i = 5 Then<br /> hasil$ = hasil$ + "Seribu "<br /> <br /> ElseIf Val(Bil$) <> 0 Then<br /> For j = 1 To 3<br /> Digit = Val(Mid$(Bil$, j, 1))<br /> If j = 2 And Right$(Bil$, 2) = "10" Then<br /> hasil$ = hasil$ + "Sepuluh "<br /> Exit For<br /> <br /> ElseIf j = 2 And Right$(Bil$, 2) = "11" Then<br /> hasil$ = hasil$ + "Sebelas "<br /> Exit For<br /> <br /> ElseIf j = 2 And Mid$(Bil$, 2, 1) = "1" Then<br /> hasil$ = hasil$ + Angka$(Val(Right$(Bil$, 1))) + "Belas "<br /> Exit For<br /> <br /> ElseIf Digit = 1 And j = 1 Then<br /> hasil$ = hasil$ + "Seratus "<br /> <br /> ElseIf Digit <> 0 Then<br /> hasil$ = hasil$ + Angka$(Digit) + Sat$(j)<br /> <br /> End If<br /> Next<br /> hasil$ = hasil$ + Kel$(i)<br /> End If<br /> Next<br /> Return<br /> End Function

'Fungsi Terbilang Dengan VBA Untuk MS Office Excel<br /> 'Created By : Dievend<br /> 'Adapted At : www.dievend.blogspot.com<br /> <br /> 'Fungsi Penterjemahan Masing-Masing Angka<br /> Private Function Dievend(Number)<br /> Cyber = Array("", "Satu", "Dua", "Tiga", "Empat", "Lima", "Enam", "Tujuh", "Delapan", "Sembilan")<br /> Dievend = Cyber(Number)<br /> End Function<br /> <br /> 'Mulai Penulisan Fungsi Terbilang<br /> Public Function Terbilang(Nilai, Optional Style = 4, Optional Satuan = "")<br /> Angka = Fix(Abs(Nilai))<br /> <br /> 'Desimal Dibelakang Koma<br /> Cyber1 = Mid(Abs(Nilai), Len(Angka) + 2, 1)<br /> Cyber2 = Mid(Abs(Nilai), Len(Angka) + 3, 1)<br /> <br /> If Cyber2 = "" Then<br /> If Cyber1 = "" Or Cyber1 = "0" Then<br /> Koma = ""<br /> Else<br /> Koma = " Koma " & Dievend(Cyber1)<br /> End If<br /> ElseIf Cyber2 = "0" Then<br /> If Cyber1 = "0" Then<br /> Koma = ""<br /> ElseIf Cyber1 = "1" Then<br /> Koma = " Koma Sepuluh"<br /> Else<br /> Koma = " Koma " & Dievend(Cyber1) & " Puluh"<br /> End If<br /> Else<br /> If Cyber1 = "0" Then<br /> Koma = " Koma Dievl " & Dievend(Cyber2)<br /> ElseIf Cyber1 = "1" Then<br /> If Cyber2 = "1" Then<br /> Koma = " Koma Sebelas"<br /> Else<br /> Koma = " Koma " & Dievend(Cyber2) & " Belas"<br /> End If<br /> Else<br /> Koma = " Koma " & Dievend(Cyber1) & " Puluh " & Dievend(Cyber2)<br /> End If<br /> End If<br /> <br /> 'Misahin Angka<br /> Diev1 = Left(Right(Angka, 1), 1)<br /> Diev2 = Left(Right(Angka, 2), 1)<br /> Diev3 = Left(Right(Angka, 3), 1)<br /> Diev4 = Left(Right(Angka, 4), 1)<br /> Diev5 = Left(Right(Angka, 5), 1)<br /> Diev6 = Left(Right(Angka, 6), 1)<br /> Diev7 = Left(Right(Angka, 7), 1)<br /> Diev8 = Left(Right(Angka, 8), 1)<br /> Diev9 = Left(Right(Angka, 9), 1)<br /> Diev10 = Left(Right(Angka, 10), 1)<br /> Diev11 = Left(Right(Angka, 11), 1)<br /> Diev12 = Left(Right(Angka, 12), 1)<br /> Diev13 = Left(Right(Angka, 13), 1)<br /> Diev14 = Left(Right(Angka, 14), 1)<br /> Diev15 = Left(Right(Angka, 15), 1)<br /> <br /> 'Satuan<br /> If Len(Angka) >= 1 Then<br /> If Len(Angka) = 1 And Diev1 = 1 Then<br /> Number1 = "Satu"<br /> ElseIf Len(Angka) = 1 And Diev1 = 0 Then<br /> Number1 = "Dievl"<br /> ElseIf Diev2 = "1" Then<br /> If Diev1 = "1" Then<br /> Number1 = "Sebelas"<br /> ElseIf Diev1 = "0" Then<br /> Number1 = "Sepuluh"<br /> Else<br /> Number1 = Dievend(Diev1) & " Belas"<br /> End If<br /> <br /> Else<br /> Number1 = Dievend(Diev1)<br /> End If<br /> Else<br /> Number1 = ""<br /> End If<br /> <br /> 'Puluhan<br /> If Len(Angka) >= 2 Then<br /> If Diev2 = 1 Or Diev2 = "0" Then<br /> Number2 = ""<br /> Else<br /> Number2 = Dievend(Diev2) & " Puluh "<br /> End If<br /> Else<br /> Number2 = ""<br /> End If<br /> <br /> 'Ratusan<br /> If Len(Angka) >= 3 Then<br /> If Diev3 = "1" Then<br /> Number3 = "Seratus "<br /> ElseIf Diev3 = "0" Then<br /> Number3 = ""<br /> Else<br /> Number3 = Dievend(Diev3) & " Ratus "<br /> End If<br /> Else<br /> Number3 = ""<br /> End If<br /> <br /> 'Ribuan<br /> If Len(Angka) >= 4 Then<br /> If Diev6 = "0" And Diev5 = "0" And Diev4 = "0" Then<br /> Number4 = ""<br /> ElseIf (Diev4 = "1" And Len(Angka) = 4) Or (Diev6 = "0" And Diev5 = "0" And Diev4 = "1") Then<br /> Number4 = "Seribu "<br /> ElseIf Diev5 = "1" Then<br /> If Diev4 = "1" Then<br /> Number4 = "Sebelas Ribu "<br /> ElseIf Diev4 = "0" Then<br /> Number4 = "Sepuluh Ribu "<br /> Else<br /> Number4 = Dievend(Diev4) & " Belas Ribu "<br /> End If<br /> <br /> Else<br /> Number4 = Dievend(Diev4) & " Ribu "<br /> End If<br /> Else<br /> Number4 = ""<br /> End If<br /> <br /> 'Puluhan Ribu<br /> If Len(Angka) >= 5 Then<br /> If Diev5 = "1" Or Diev5 = "0" Then<br /> Number5 = ""<br /> Else<br /> Number5 = Dievend(Diev5) & " Puluh "<br /> End If<br /> Else<br /> Number5 = ""<br /> End If<br /> <br /> 'Ratusan Ribu<br /> If Len(Angka) >= 6 Then<br /> If Diev6 = "1" Then<br /> Number6 = "Seratus "<br /> ElseIf Diev6 = "0" Then<br /> Number6 = ""<br /> Else<br /> Number6 = Dievend(Diev6) & " Ratus "<br /> End If<br /> Else<br /> Number6 = ""<br /> End If<br /> <br /> 'Jutaan<br /> If Len(Angka) >= 7 Then<br /> If Diev9 = "0" And Diev8 = "0" And Diev7 = "0" Then<br /> Number7 = ""<br /> ElseIf Diev7 = "1" And Len(Angka) = 7 Then<br /> Number7 = "Satu Juta "<br /> ElseIf Diev8 = "1" Then<br /> If Diev7 = "1" Then<br /> Number7 = "Sebelas Juta "<br /> ElseIf Diev7 = "0" Then<br /> Number7 = "Sepuluh Juta "<br /> Else<br /> Number7 = Dievend(Diev7) & " Belas Juta "<br /> End If<br /> <br /> Else<br /> Number7 = Dievend(Diev7) & " Juta "<br /> End If<br /> Else<br /> Number7 = ""<br /> End If<br /> <br /> 'Puluhan Juta<br /> If Len(Angka) >= 8 Then<br /> If Diev8 = "1" Or Diev8 = "0" Then<br /> Number8 = ""<br /> Else<br /> Number8 = Dievend(Diev8) & " puluh "<br /> End If<br /> Else<br /> Number8 = ""<br /> End If<br /> <br /> 'Ratusan Juta<br /> If Len(Angka) >= 9 Then<br /> If Diev9 = "1" Then<br /> Number9 = "Seratus "<br /> ElseIf Diev9 = "0" Then<br /> Number9 = ""<br /> Else<br /> Number9 = Dievend(Diev9) & " Ratus "<br /> End If<br /> Else<br /> Number9 = ""<br /> End If<br /> <br /> 'Milyar<br /> If Len(Angka) >= 10 Then<br /> If Diev12 = "0" And Diev11 = "0" And Diev10 = "0" Then<br /> Number10 = ""<br /> ElseIf Diev10 = "1" And Len(Angka) = 10 Then<br /> Number10 = "Satu Milyar "<br /> ElseIf Diev11 = "1" Then<br /> If Diev10 = "1" Then<br /> Number10 = "Sebelas Milyar "<br /> ElseIf Diev10 = "0" Then<br /> Number10 = "Sepuluh Milyar "<br /> Else<br /> Number10 = Dievend(Diev10) & " Belas Milyar "<br /> End If<br /> <br /> Else<br /> Number10 = Dievend(Diev10) & " Milyar "<br /> End If<br /> Else<br /> Number10 = ""<br /> End If<br /> <br /> 'Puluhan Milyar<br /> If Len(Angka) >= 11 Then<br /> If Diev11 = "1" Or Diev11 = "0" Then<br /> Number11 = ""<br /> Else<br /> Number11 = Dievend(Diev11) & " Puluh "<br /> End If<br /> Else<br /> Number11 = ""<br /> End If<br /> <br /> 'Ratusan Milyar<br /> If Len(Angka) >= 12 Then<br /> If Diev12 = "1" Then<br /> Number12 = "Seratus "<br /> ElseIf Diev12 = "0" Then<br /> Number12 = ""<br /> Else<br /> Number12 = Dievend(Diev12) & " Ratus "<br /> End If<br /> Else<br /> Number12 = ""<br /> End If<br /> <br /> 'Triliun<br /> If Len(Angka) >= 13 Then<br /> If Diev15 = "0" And Diev14 = "0" And Diev13 = "0" Then<br /> Number13 = ""<br /> ElseIf Diev13 = "1" And Len(Angka) = 13 Then<br /> Number13 = "Satu Triliun "<br /> ElseIf Diev14 = "1" Then<br /> If Diev13 = "1" Then<br /> Number13 = "Sebelas Triliun "<br /> ElseIf Diev13 = "0" Then<br /> Number13 = "Sepuluh Triliun "<br /> Else<br /> Number13 = Dievend(Diev13) & " Belas Triliun "<br /> End If<br /> <br /> Else<br /> Number13 = Dievend(Diev13) & " Triliun "<br /> End If<br /> Else<br /> Number13 = ""<br /> End If<br /> <br /> 'Puluhan Triliun<br /> If Len(Angka) >= 14 Then<br /> If Diev14 = "1" Or Diev14 = "0" Then<br /> Number14 = ""<br /> Else<br /> Number14 = Dievend(Diev14) & " Puluh "<br /> End If<br /> Else<br /> Number14 = ""<br /> End If<br /> <br /> 'Ratusan Triliun<br /> If Len(Angka) >= 15 Then<br /> If Diev15 = "1" Then<br /> Number15 = "Seratus "<br /> ElseIf Diev15 = "0" Then<br /> Number15 = ""<br /> Else<br /> Number15 = Dievend(Diev15) & " Ratus "<br /> End If<br /> Else<br /> Number15 = ""<br /> End If<br /> <br /> If Len(Angka) > 15 Then<br /> Bilang = "Digit Angka Terlalu Banyak"<br /> Else<br /> If IsNull(Nilai) Then<br /> Bilang = ""<br /> ElseIf Nilai < 0 Then Bilang = "minus " & Trim(Number15 & Number14 & Number13 & Number12 & Number11 & Number10 & Number9 & Number8 & Number7 _ & Number6 & Number5 & Number4 & Number3 & Number2 & Number1 & Koma & " " & Satuan) Else Bilang = Trim(Number15 & Number14 & Number13 & Number12 & Number11 & Number10 & Number9 & Number8 & Number7 _ & Number6 & Number5 & Number4 & Number3 & Number2 & Number1 & Koma & " " & Satuan) End If End If If Style = 4 Then Terbilang = StrConv(Left(Bilang, 1), 1) & StrConv(Mid(Bilang, 2, 1000), 2) Else Terbilang = StrConv(Bilang, Style) End If Terbilang = Replace(Terbilang, " ", " ", 1, 1000, vbTextCompare) End Function

• Kemudian click Save dan Close (Visual Basic editor). Sekarang fungsi buatan terbilang sudah siap digunakan.

• Fungsi diatas hanya berlaku untuk satu Workbook / satu file saja, kalau anda menginginkan fungsi buatan tersebut bisa diaplikasikan ke semua file Excel, anda harus menyimpan file tersebut menjadi File Add-in, caranya klik Save/Save as, setelah muncul kotak dialog, pada bagian Save as Type pilih Microsoft Office excel Add-ins, simpan dengan nama file "Terbilang",setelah disimpan sekarang anda klik menu Tools » add-Ins » klik Browse dan buka file "Terbilang" yang baru anda simpan.
• Untuk penulisan rumus secara lansung dengan Formula =Terbilang(Cels) » Enter, atau pilih Insert » Function, dari daftar dalam Function Category, pilihlah User Defined, sehingga dalam daftar di Function name muncul nama fungsi Terbilang.

• Selesai.