Angka menjadi terbilang dalam Excel 2007

Dalam pebuatan RAB saya biasanya kerepotan membuat angka rupiah menjadi terbilang. Setiap ada perubahan rupiah maka harus mengedit juga terbilangnya. Gw cari di google akhirnya ketemu neh buat macro terbilang. gak perlu edit edit terus untuk buat terbilang.

list programnya seperti dibawah ini, menggunakan visual basic editornya excel.

==================================================================
‘****************
‘ Main Function *
‘****************
Function Terbilang(ByVal MyNumber)
Dim Rupiah, sen, Temp
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = ” Ribu “
Place(3) = ” Juta “
Place(4) = ” Milyar “
Place(5) = ” Trilyun “

‘ String representation of amount
MyNumber = Trim(Str(MyNumber))

‘ Position of decimal place 0 if none
DecimalPlace = InStr(MyNumber, “.”)
‘Convert sen and set MyNumber to dollar amount
If DecimalPlace > 0 Then
sen = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & “00″, 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace – 1))
End If

Count = 1
Do While MyNumber <> “”
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> “” Then Rupiah = Temp & Place(Count) & Rupiah
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) – 3)
Else
MyNumber = “”
End If
Count = Count + 1
Loop

Select Case Rupiah
Case “”
Rupiah = “Nol Rupiah”
Case “Satu”
Rupiah = “Satu Rupiah”
Case Else
Rupiah = Rupiah & ” Rupiah”
End Select

Select Case sen
Case “”
sen = “”
Case “Satu”
sen = ” dan Satu Sen”
Case Else
sen = ” dan ” & sen & ” Sen”
End Select
If Left(Trim(Rupiah), 9) = “Satu Ribu” Then
Rupiah = “Seribu ” & Mid(Rupiah, 11)
End If
Terbilang = “Terbilang : ” + Rupiah + sen
End Function

‘*******************************************
‘ Converts a number from 100-999 into text *
‘*******************************************
Function GetHundreds(ByVal MyNumber)
Dim Result As String

If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right(“000″ & MyNumber, 3)

‘Convert the hundreds place
If Mid(MyNumber, 1, 1) <> “0″ Then
If Mid(MyNumber, 1, 1) = “1″ Then
Result = “Seratus “

Else
Result = GetDigit(Mid(MyNumber, 1, 1)) & ” Ratus “
End If
End If
‘Convert the tens and ones place
If Mid(MyNumber, 2, 1) <> “0″ Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If

GetHundreds = Result
End Function

‘*********************************************
‘ Converts a number from 10 to 99 into text. *
‘*********************************************
Function GetTens(TensText)
Dim Result As String

Result = “”           ‘null out the temporary function value
If Val(Left(TensText, 1)) = 1 Then   ‘ If value between 10-19
Select Case Val(TensText)
Case 10: Result = “Sepuluh”
Case 11: Result = “Sebelas”
Case 12: Result = “Duabelas”
Case 13: Result = “Tigabelas”
Case 14: Result = “Empatbelas”
Case 15: Result = “Limabelas”
Case 16: Result = “Enambelas”
Case 17: Result = “Tujuhbelas”
Case 18: Result = “Delapanbelas”
Case 19: Result = “Sembilanbelas”
Case Else
End Select
Else                                 ‘ If value between 20-99
Select Case Val(Left(TensText, 1))
Case 2: Result = “Dua Puluh “
Case 3: Result = “Tiga Puluh “
Case 4: Result = “Empat Puluh “
Case 5: Result = “Lima Puluh “
Case 6: Result = “Enam Puluh “
Case 7: Result = “Tujuh Puluh “
Case 8: Result = “Delapan Puluh “
Case 9: Result = “Sembilan Puluh “
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1))  ‘Retrieve ones place
End If
GetTens = Result
End Function

‘*******************************************
‘ Converts a number from 1 to 9 into text. *
‘*******************************************
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = “Satu”
Case 2: GetDigit = “Dua”
Case 3: GetDigit = “Tiga”
Case 4: GetDigit = “Empat”
Case 5: GetDigit = “Lima”
Case 6: GetDigit = “Enam”
Case 7: GetDigit = “Tujuh”
Case 8: GetDigit = “Delapan”
Case 9: GetDigit = “Sembilan”
Case Else: GetDigit = “”
End Select
End Function

================================================================

copy aja semua ke vb editor

About these ads

Berikan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Logout / Ubah )

Twitter picture

You are commenting using your Twitter account. Logout / Ubah )

Facebook photo

You are commenting using your Facebook account. Logout / Ubah )

Google+ photo

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

Ikuti

Get every new post delivered to your Inbox.

%d blogger menyukai ini: