Membuat Fungsi Terbilang pada Excel 2003

10 06 2012
   1,983.00 = Seribu Sembilan Ratus Delapan Puluh Tiga

A. Membuat Module
1. Buka microsoft Excel
2. Buka Menu Tools – Macro – Visual Basic Editor
3. Buat module baru dengan cara : Klik menu Insert – Module
4. Masukkan (Copy-Paste) fungsi terbilang berikut ini :

Public Function Terbilang(ByVal nNilai As Currency) As String
Dim Grade As Variant
Dim strTerbilang As String
Dim strPart As String
Dim iGrade As Byte

Grade = Array(“Milyar “, “Juta “, “Ribu “, “”)

strTerbilang = “”
If Len(CStr(nNilai)) > 12 Then
strTerbilang = “Melewati batas konversi”
Else
strPart = Format(nNilai, String(12, “0”))

For iGrade = 1 To 4
If Val(Mid(strPart, (iGrade – 1) * 3 + 1, 3)) > 0 Then
strTerbilang = strTerbilang & _
GetRatus(Mid(strPart, (iGrade – 1) * 3 + 1, 3), iGrade)
strTerbilang = strTerbilang & Grade(iGrade – 1)
End If
Next iGrade
End If

‘ Kembalikan nilai melalui nama fungsi-nya
Terbilang = strTerbilang & ” Rupiah”
End Function

Public Function GetRatus(ByVal strPart As String, _
ByVal iGrade As Byte) As String
Dim Angka1 As Variant, Angka2 As Variant
Dim i As Integer
Dim strHasil As String
Dim nTemp As Byte

Angka1 = Array(“Satu “, “Dua “, “Tiga “, “Empat “, _
“Lima “, “Enam “, “Tujuh “, “Delapan “, “Sembilan “)
Angka2 = Array(“Ratus “, “Puluh “, “”)

For i = 1 To 3
nTemp = Val(Mid(strPart, i, 1))
If nTemp = 1 Then
If i = 1 Then
strHasil = “Seratus ”
ElseIf i = 2 Then

i = i + 1
nTemp = Val(Mid(strPart, i, 1))
If nTemp = 0 Then
strHasil = strHasil & “Sepuluh ”
ElseIf nTemp = 1 Then
strHasil = strHasil & “Sebelas ”
Else
strHasil = strHasil & _
Angka1(nTemp – 1) & “Belas ”
End If

ElseIf Val(strPart) = 1 And iGrade = 3 Then
strHasil = strHasil & “Se”
Else
strHasil = strHasil & “Satu ”
End If
ElseIf nTemp <> 0 Then
strHasil = strHasil + Angka1(nTemp – 1) + Angka2(i – 1)
End If
Next i
GetRatus = strHasil
End Function

Public Function angk2Str(ByVal NumKom As Double, ByVal PosDig As Long) As String
angk2Str = Space(PosDig – Len(Trim(Format(NumKom, “#,##0”)))) & Trim(CStr(Format(NumKom, “#,##0”)))
End Function

Public Function angk2tStr(ByVal NumKom As Double, ByVal PosDig As Long, ByVal strTambahan) As String
angk2tStr = Space(PosDig – Len(Trim(Format(NumKom, “#,##0”)))) & strTambahan & Trim(CStr(Format(NumKom, “#,##0”)))
End Function

Public Function CenChar(Char2Cen, PosDig) As String
CenChar = Space(PosDig – Len(Trim(Char2Cen))) \ 2 & Trim(Char2Cen)
End Function

5. Lakukan proses pengecekan pada lembar sheet Excel.
Contoh : – Ketik angka 1000 pada sel B2.
– Pada sel B3, ketik fungsi =terbilang(B2)
– Jika pada sel B3 berubah menjadi “seribu rupiah” , maka fungsi diatas telah benar

B. Membuat Deskripsi Fungsi
1. Pada lembar kerja Excel, klik menu Tools – Macros
2. Ketikkan nama fungsi terbilang dan pastikan tombol Option menyala
3. Klik Option, dan ketik Deskripsi fungsi terbilang anda pada kotak Descripsion
4. Klik OK

C. Membuat Add Ins
1. Tutup Visual Basic Editor
2. Simpan Dokumen Excel dengan klik menu File – Save As
3. Ubah Save As Type menjadi Microsoft Excel Add In (*.xla)
4. Beri nama bebas, misal : terbilang.xla
5. Klik OK

D. Menginstall Add-In
1. Buka Menu Tools – Add Ins
2. Pilih dan centang pilihan “terbilang” pada list Add Ins
3. Klik OK

Proses selesai…Fungsi dapat digunakan !!!
Selamat Mencoba !!!


Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: