Sunday, March 29, 2009

fungsi terbilang pada exel

Untuk memudahkan dalam menulis dari angka kehurup kita bisa menggunakan dengan visual basic caranya sebagai berikut:

1. Buka Exel, File New
2. Klik menu ->Tools > Makro -> Visual Basic Editor (Alt+F11)
3. Klik menu ->Insert > Modul
4. Klik menu ->Insert > File -> Klik Terbilang.txt
5. Klik menu ->File -> Close and Return To Microsoft Exel (Alt+Q)
6. Masukkan Angka yang mau di konversi Contoh di A1
7. Pindahkan Kursor ke Cell yang akan diisi Hasil Conversi Terbilang
8. Ketik =Terbilang(A1) Enter


copy file dibawah ini ke notepad dan beri nama terbilang:


Option Explicit
Function Terbilang(ByVal Rp As Currency) As String
Dim Pos, Uang, Ket, Rpx

Uang = Array(1000000000000#, 1000000000, 1000000, 1000, 1, 0)
Ket = Array("Triliun ", "Miliar ", "Juta ", "Ribu ", "", "")

Do
For Pos = 0 To 4
If Rp >= Uang(Pos) Then
Rpx = Rp / Uang(Pos)
Rpx = IIf(Rpx >= 100, Left(Rpx, 3), IIf(Rpx >= 10, Left(Rpx, 2), Left(Rpx, 1)))
Exit For
End If
Next Pos
Terbilang = Terbilang & Konversi(Rpx, Pos, Len(CStr(Rp)))
Rp = Rp - (Rpx * Uang(Pos))
If Not (Rpx = 1 And Pos = 3) Then Terbilang = Terbilang & Ket(Pos)
If Pos = 4 Or Rp < 1 Then Exit Do
Loop

If Rp > 0 Then
Rp = Mid(Rp, 3)
Terbilang = Terbilang & "koma " & Konversi(Rp, 0, Len(CStr(Rp)))
End If
Terbilang = terbilang & "Rupiah "

End Function

Function Konversi(ByVal Rp As Integer, ByVal Pos As Byte, ByVal Pjg As Integer) As String
Dim Bilangan
Bilangan = Array("", "Satu ", "Dua ", "Tiga ", "Empat ", "Lima ", "Enam ", "Tujuh ", "Delapan ", "Sembilan ")
If Rp >= 100 Then
If Left(Rp, 1) = 1 Then Konversi = "Seratus " Else Konversi = Konversi & Bilangan(Left(Rp, 1)) & "Ratus "
Rp = Rp - (Left(Rp, 1) * 100)
End If
Select Case Rp
Case Is < 10
If Pjg = 4 Then
If Rp = 1 And Pos = 3 Then Konversi = Konversi & "Seribu " Else Konversi = Konversi & Bilangan(Rp)
Else
If Rp = 1 And Pos = 3 Then Konversi = Konversi & "Satu " Else Konversi = Konversi & Bilangan(Rp)
End If
Case Is = 10
Konversi = Konversi & "Sepuluh "
Case Is = 11
Konversi = Konversi & "Sebelas "
Case 12 To 19
Konversi = Konversi & Bilangan(Right(Rp, 1)) & "Belas "
Case Else
Konversi = Konversi & Bilangan(Left(Rp, 1)) & "Puluh "
If Right(Rp, 1) > 0 Then Konversi = Konversi & Bilangan(Right(Rp, 1))
End Select
End Function

0 comments:


Post a Comment