Membuat Rumus Terbilang Pada Miscrosoft Excel

———————————————————————————–
1. BUka excel
2. Tools –> Macro –> Visual Basic Editor
3. Click kanan pada Microsoft Excell Object : Pilih Insert –> Object
4. Pada Screen Object Copykan semua procedure dibawah ini
5. Tutup Visual Basic Editor
6. Untuk membuat terbilang masukkan suatu angka di sel tertentu misal di cell A2
7. Kemudian buat Formula =terbilang(A2)
———————————————————————————–

Option Explicit

Public Function TERBILANG(x As Double) As String
Dim tampung As Double
Dim teks As String
Dim bagian As String
Dim i As Integer
Dim tanda As Boolean

Dim letak(5)
letak(1) = “RIBU “
letak(2) = “JUTA “
letak(3) = “MILYAR “
letak(4) = “TRILYUN “

If (x < 0) Then
TERBILANG = “”
Exit Function
End If

If (x = 0) Then
TERBILANG = “NOL”
Exit Function
End If

If (x < 2000) Then
tanda = True
End If
teks = “”

If (x >= 1E+15) Then
TERBILANG = “NILAI TERLALU BESAR”
Exit Function
End If

For i = 4 To 1 Step -1
tampung = Int(x / (10 ^ (3 * i)))
If (tampung > 0) Then
bagian = ratusan(tampung, tanda)
teks = teks & bagian & letak(i)
End If
x = x – tampung * (10 ^ (3 * i))
Next

teks = teks & ratusan(x, False)
TERBILANG = teks & ” RUPIAH “
End Function

Function ratusan(ByVal y As Double, ByVal flag As Boolean) As String
Dim tmp As Double
Dim bilang As String
Dim bag As String
Dim j As Integer

Dim angka(9)
angka(1) = “SE “
angka(2) = “DUA “
angka(3) = “TIGA “
angka(4) = “EMPAT “
angka(5) = “LIMA “
angka(6) = “ENAM “
angka(7) = “TUJUH “
angka(8) = “DELAPAN “
angka(9) = “SEMBILAN “

Dim posisi(2)
posisi(1) = “PULUH “
posisi(2) = “RATUS “

bilang = “”
For j = 2 To 1 Step -1
tmp = Int(y / (10 ^ j))
If (tmp > 0) Then
bag = angka(tmp)
If (j = 1 And tmp = 1) Then
y = y – tmp * 10 ^ j
If (y >= 1) Then
posisi(j) = “BELAS “
Else
angka(y) = “SE”
End If
bilang = bilang & angka(y) & posisi(j)
ratusan = bilang
Exit Function
Else
bilang = bilang & bag & posisi(j)
End If
End If
y = y – tmp * 10 ^ j
Next

If (flag = False) Then
angka(1) = “SATU “
End If
bilang = bilang & angka(y)
ratusan = bilang
End Function

About these ads

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: