Selasa, 22 Mei 2012
Senin, 21 Mei 2012
senin kemarin nongol di artikel 'on day monday'.
ini linknya: http://nestofojanto.blogspot.com/2012/05/on-day-monday-40-bad-excel.html
terimakasih ojanto..
^__^
Read More
ini linknya: http://nestofojanto.blogspot.com/2012/05/on-day-monday-40-bad-excel.html
terimakasih ojanto..
^__^
Minggu, 20 Mei 2012
Jumat, 04 Mei 2012
Kamis, 03 Mei 2012
Bad Excel nongol di artikel majalah tiger tales, hehehe.. :)
ini linknya:
http://www.tigertales.sg/features/2012/05/jakartas-smart-artists
Selasa, 24 April 2012
Jumat, 20 April 2012
Kamis, 12 April 2012
Selasa, 10 April 2012
Senin, 09 April 2012
Jumat, 06 April 2012

Udah lama banget gak berenang di ancol, mungkin udah lebih dari 10 tahun..tapi masih aja terekam di ingatan, dahulu waktu kecil kalo mau berenang di ancol itu namanya gelanggang renang jaya ancol, sekarang katanya sih berubah ganti nama jadi atlantis. Kita masuk ke mulut paus dan menemukan kegembiraan kecipak kecipik di balik perutnya.. ouh sungguh asoy.. ;p
Sabtu, 31 Maret 2012
Senin, 20 Februari 2012
Hari Minggu tanggal 19 Februari lalu Kopi Keliling ngadain acara perayaan ulang tahun 1. Temanya Swap Party, lokasinya di That's life Cofee, Jl.Gunawarman jaksel, dimana temen yang dateng bisa tukeran kado berupa karyanya.
Selain itu dipamerkan juga Online Exhibition "2012" yang kebetulan orat-oret gw ini juga kepilih untuk di pajang.. :D
berita lengkapnya bisa disimak disini: http://kopikeliling.com/news/swap-party.html
Read More
Selain itu dipamerkan juga Online Exhibition "2012" yang kebetulan orat-oret gw ini juga kepilih untuk di pajang.. :D
berita lengkapnya bisa disimak disini: http://kopikeliling.com/news/swap-party.html
Kamis, 09 Februari 2012
Kamis, 02 Februari 2012

Kepilih ama Kopling untuk Pameran online #1: "2012"
ini linknya:
http://kopikeliling.com/news/pameran-online-1-2012.html
Jumat, 30 Desember 2011
In this excel tutorial, we are going to learn how to make a search box. After creating search box, we will use it to search and display data from the database. In the above video you can see what we are going to create in this tutorial. We are going to do this in two steps.
- Creating search box.
- Displaying search result.
Creating Search Box
In this section we are going to create search box and the display box. Instruction for creating search box and display box are given below.
- Select cells "C2:E2" and click on "Merge & Center".
- Select all the cell around the merged cells and fill it with black color.
- Click on "B2". Change font color to white, font size to 12 and make it bold then type "Search".
- Select cells B5 to F5, hold Ctrl and select C6:F6, C7:F7, C8:F8, C9:F9, C10:F10, C11:F11 and C12:F12 and click on "Merge & Center".
- Fill with color as shown in the picture above.
- Click "B5" hold Shift and click on F12.
- Click on "Thick Box Border" from the border options.
Displaying Search Result
Here is the sample data which I will be using in this tutorial to show you how you can search and data from the data base.
![]() |
| Click Image to Enlarge |
- Go to Sheet 2 in the same workbook.
- Click on the formula tab and then "Define Names".
- Create the following named ranges i.e. Name, Age, Gender, Phone, Email, Address, and Country and type in the following formulas in "Refers to:" text box respectively
- "=OFFSET(Sheet2!$A$2,0,0,COUNTA(Shee2!$A:$A)-1)"
- "=OFFSET(Sheet2!$B$2,0,0,COUNTA(Shee2!$B:$B)-1)"
- "=OFFSET(Sheet2!$C$2,0,0,COUNTA(Shee2!$C:$C)-1)"
- "=OFFSET(Sheet2!$D$2,0,0,COUNTA(Shee2!$D:$D)-1)"
- "=OFFSET(Sheet2!$E$2,0,0,COUNTA(Shee2!$E:$E)-1)"
- "=OFFSET(Sheet2!$F$2,0,0,COUNTA(Shee2!$F:$F)-1)"
- "=OFFSET(Sheet2!$G$2,0,0,COUNTA(Shee2!$G:$G)-1)"
Formula To Enable Search
- Go back to "Sheet1".
- Click "C6" and type "=IF($C$2="", "", $C$2)".
- Click "C7" and type "=IF($C$2="", "", INDEX(Age,MATCH($C$2,Name,0)))".
- Click "C8" and type "=IF($C$2="", "", INDEX(Gender,MATCH($C$2,Name,0)))".
- Click "C9" and type "=IF($C$2="", "", INDEX(Phone,MATCH($C$2,Name,0)))".
- Click "C10" and type "=IF($C$2="", "", INDEX(Email,MATCH($C$2,Name,0)))".
- Click "C11" and type "=IF($C$2="", "", INDEX(Address,MATCH($C$2,Name,0)))".
- Click "C12" and type "=IF($C$2="", "", INDEX(Country,MATCH($C$2,Name,0)))".
Now click on the search box and type a name you want to search. If the name you are searching is in the database, it will be displayed in the search "Search Result...". If the name you are searching is not in the database it will display "#N/A" in all the fields except name field. If you like this tutorial or if you have a query, leave a comment at the end of this tutorial. Happy searching.
In this excel tutorial, we are going to learn how to make a search box. After creating search box, we will use it to search and display data from the database. In the above video you can see what we are going to create in this tutorial. We are going to do this in two steps.
- Creating search box.
- Displaying search result.
Creating Search Box
In this section we are going to create search box and the display box. Instruction for creating search box and display box are given below.
- Select cells "C2:E2" and click on "Merge & Center".
- Select all the cell around the merged cells and fill it with black color.
- Click on "B2". Change font color to white, font size to 12 and make it bold then type "Search".
- Select cells B5 to F5, hold Ctrl and select C6:F6, C7:F7, C8:F8, C9:F9, C10:F10, C11:F11 and C12:F12 and click on "Merge & Center".
- Fill with color as shown in the picture above.
- Click "B5" hold Shift and click on F12.
- Click on "Thick Box Border" from the border options.
Displaying Search Result
Here is the sample data which I will be using in this tutorial to show you how you can search and data from the data base.
![]() |
| Click Image to Enlarge |
- Go to Sheet 2 in the same workbook.
- Click on the formula tab and then "Define Names".
- Create the following named ranges i.e. Name, Age, Gender, Phone, Email, Address, and Country and type in the following formulas in "Refers to:" text box respectively
- "=OFFSET(Sheet2!$A$2,0,0,COUNTA(Shee2!$A:$A)-1)"
- "=OFFSET(Sheet2!$B$2,0,0,COUNTA(Shee2!$B:$B)-1)"
- "=OFFSET(Sheet2!$C$2,0,0,COUNTA(Shee2!$C:$C)-1)"
- "=OFFSET(Sheet2!$D$2,0,0,COUNTA(Shee2!$D:$D)-1)"
- "=OFFSET(Sheet2!$E$2,0,0,COUNTA(Shee2!$E:$E)-1)"
- "=OFFSET(Sheet2!$F$2,0,0,COUNTA(Shee2!$F:$F)-1)"
- "=OFFSET(Sheet2!$G$2,0,0,COUNTA(Shee2!$G:$G)-1)"
Formula To Enable Search
- Go back to "Sheet1".
- Click "C6" and type "=IF($C$2="", "", $C$2)".
- Click "C7" and type "=IF($C$2="", "", INDEX(Age,MATCH($C$2,Name,0)))".
- Click "C8" and type "=IF($C$2="", "", INDEX(Gender,MATCH($C$2,Name,0)))".
- Click "C9" and type "=IF($C$2="", "", INDEX(Phone,MATCH($C$2,Name,0)))".
- Click "C10" and type "=IF($C$2="", "", INDEX(Email,MATCH($C$2,Name,0)))".
- Click "C11" and type "=IF($C$2="", "", INDEX(Address,MATCH($C$2,Name,0)))".
- Click "C12" and type "=IF($C$2="", "", INDEX(Country,MATCH($C$2,Name,0)))".
Now click on the search box and type a name you want to search. If the name you are searching is in the database, it will be displayed in the search "Search Result...". If the name you are searching is not in the database it will display "#N/A" in all the fields except name field. If you like this tutorial or if you have a query, leave a comment at the end of this tutorial. Happy searching.
Selasa, 27 Desember 2011
Kali ini saya akan menjelaskan langkah demi langkah bagaimana cara membuat Convert Number to Text. Convert Numbers to Text bertujuan untuk merubah angka menjadi huruf terbilang seperti yang biasa terdapat dalam kwitansi. Convert Numbers to Text ini tersedia dalam bahasa Inggris dan bahasa Indonesia.
Kita mulai dengan membuat form terlebih dahulu dengan menekan Alt + F8 (Microsoft Visual Basic) lalu Insert > User Form.
Selanjutnya form kosong di atas didesain seperti gambar di bawah ini dengan menggunakan fungsi-fungsi yang ada dalam Toolbox:
Perhatikan nomor-nomor di atas. Nomor-nomor tersebut adalah untuk menjelaskan masing-masing properties, isi propertiesnya sbb:
1. Form
2. Reference Cell
3. Output Cell
4. Tombol radio English
5. Tombol radio Bahasa Indonesia
6. Tombol Convert
7. Tombol Exit
Untuk scriptnya pilih View > Code lalu gunakan script berikut:
1. Form
Private Sub UserForm_Activate()
opt_english.Value = True
cmd_run.SetFocus
End Sub
Private Sub UserForm_QueryClose _
(cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
cancel = True
End If
End Sub
6. Tombol Convert
Private Sub cmd_run_Click()
If Ref_cell.Value = "" Or Output_cell.Value = "" Then
MsgBox "Reference cell and Output cell must not be empty.", vbCritical + vbOKOnly, "Enter Reference and Output Cell"
Else
If Ref_cell.Value = Output_cell.Value Then
MsgBox "Reference cell and Output cell must not be the same. Please select other cell for Output Cell.", vbCritical + vbOKOnly, "Reference and Output Cell Error"
Else
If opt_english.Value = False Then
Range(Output_cell.Value).Select
ActiveCell.Value = "=dghrf(" & Ref_cell.Value & ")"
Else
Range(Output_cell.Value).Select
ActiveCell.Value = "=txtString(" & Ref_cell.Value & ")"
End If
End If
End If
End Sub
7. Tombol Exit
Private Sub cmd_cancel_Click()
Unload Me
End Sub
Selanjutnya pilih ThisWorkbook pada window Project – VBA Project dan gunakan script berikut pada window sebelah kanan:
Sub Open_Form_Convert2Text()
With Form_Convert2Text
.Show vbModeless
End With
End Sub
Sampai tahap di atas kita baru membuat tampilan interfacenya (GUI), sedang untuk mendefinisikan function =dghrf dan =txtString kita buat dengan 2 (dua) Modules. Pilih Insert > Module dan beri nama Angka2Text pada propertiesnya dan masukkan script di bawah ini:
Dim Huruf(0 To 9) As String
Dim ax(0 To 3) As Double
Function INIT_angka()
Huruf(0) = ""
Huruf(1) = "satu "
Huruf(2) = "dua "
Huruf(3) = "tiga "
Huruf(4) = "empat "
Huruf(5) = "lima "
Huruf(6) = "enam "
Huruf(7) = "tujuh "
Huruf(8) = "delapan "
Huruf(9) = "sembilan "
End Function
Function dgratus(angka As Double) As String
Temp = ""
INIT_angka
panjang = Len(Trim(Str(angka)))
nilai = Right("000", 3 - panjang) + Trim(Str(angka))
For y = 3 To 1 Step -1
ax(y) = Mid(nilai, y, 1)
Next y
Select Case ax(1)
Case Is = 1
Temp = "seratus "
Case Is > 1
Temp = Huruf(Val(ax(1))) + " " + "ratus "
Case Else
Temp = " "
End Select
Select Case ax(2)
Case Is = 0
Temp = Temp + Huruf(Val(ax(3)))
Case Is = 1
Select Case ax(3)
Case Is = 1
Temp = Temp + "sebelas "
Case Is = 0
Temp = Temp + "sepuluh "
Case Else
Temp = Temp + Huruf(Val(ax(3))) + "belas "
End Select
Case Is > 1
Temp = Temp + Huruf(Val(ax(2))) + "puluh "
Temp = Temp + " " + Huruf(Val(ax(3)))
End Select
dgratus = Temp
End Function
Function dghrf(angka As Double) As String
Dim ratusan(0 To 6) As String
Dim sebut(0 To 4) As String
sebut(1) = " ribu "
sebut(2) = " juta "
sebut(3) = " milyar "
sebut(4) = " trilyun "
panjang = Len(Trim(Str(angka)))
kali = Int(panjang / 3)
If Int(panjang / 3) * 3 <> panjang Then
kali = kali + 1
sisa = panjang - Int(panjang / 3) * 3
nilai = Right("000", 3 - sisa) + Trim(Str(angka))
Else
nilai = Trim(Str(angka))
End If
For x = 0 To kali
ratusan(kali - x) = Mid(nilai, x * 3 + 1, 3)
Next x
For y = kali To 1 Step -1
If y = 2 And Val(ratusan(y)) = 1 Then
Temp = Temp + "seribu"
Else
If Val(ratusan(y)) = 0 Then
Temp = Temp
Else
Temp = Temp + dgratus(Val(ratusan(y)))
Temp = Temp + sebut(y - 1)
End If
End If
Next y
dghrf = Application.Trim(Application.Proper(Temp)) + " Rupiah"
End Function
Selanjutnya pilih lagi Insert > Module dan beri nama Convert2Text pada propertiesnya. Masukkan script di bawah ini:
Public Function txtString(jumlah As Long)
jumlah = Application.Round(jumlah, 2)
If Application.IsNumber(jumlah) = True Then
Dim MyNum, Num1, NumLength, txString
Dim Million, Thousand, Hundred, myDecimal
Dim MyDollar, MyCent
Dim Space, Separator
Dim Digit1, Digit2, Digit3, Digit4, Digit5
Dim Digit6, Digit7, Digit8, Digit9
Dim Digit11, Digit12 'Decimals
Dim txRM
Dim Rng, i
Rng = Selection.Rows.Count
For i = 1 To Rng
MyDollar = "IDR " 'Must be within the
MyCent = "Sen " 'For Next Loop
'--------------------------------------------------
Digit1 = 0 'Resets numbers
Digit2 = 0
Digit3 = 0
Digit4 = 0
Digit5 = 0
Digit6 = 0
Digit7 = 0
Digit8 = 0
Digit9 = 0
Digit11 = 0
Digit12 = 0
Million = ""
Thousand = ""
Hundred = ""
myDecimal = ""
'--------------------------------------------------
Num1 = jumlah
MyNum = Format(Num1, "#0.00") 'Shows 2 decimal places
NumLength = Len(MyNum) 'Determines the length of the number
Select Case NumLength 'Length determines the
Case 12 'position of the text boxes
Digit1 = Mid(MyNum, 1, 1)
Digit2 = Mid(MyNum, 2, 1)
Digit3 = Mid(MyNum, 3, 1)
Digit4 = Mid(MyNum, 4, 1)
Digit5 = Mid(MyNum, 5, 1)
Digit6 = Mid(MyNum, 6, 1)
Digit7 = Mid(MyNum, 7, 1)
Digit8 = Mid(MyNum, 8, 1)
Digit9 = Mid(MyNum, 9, 1)
Digit11 = Mid(MyNum, 11, 1)
Digit12 = Mid(MyNum, 12, 1)
Case 11
Digit2 = Mid(MyNum, 1, 1)
Digit3 = Mid(MyNum, 2, 1)
Digit4 = Mid(MyNum, 3, 1)
Digit5 = Mid(MyNum, 4, 1)
Digit6 = Mid(MyNum, 5, 1)
Digit7 = Mid(MyNum, 6, 1)
Digit8 = Mid(MyNum, 7, 1)
Digit9 = Mid(MyNum, 8, 1)
Digit11 = Mid(MyNum, 10, 1)
Digit12 = Mid(MyNum, 11, 1)
Case 10
Digit3 = Mid(MyNum, 1, 1)
Digit4 = Mid(MyNum, 2, 1)
Digit5 = Mid(MyNum, 3, 1)
Digit6 = Mid(MyNum, 4, 1)
Digit7 = Mid(MyNum, 5, 1)
Digit8 = Mid(MyNum, 6, 1)
Digit9 = Mid(MyNum, 7, 1)
Digit11 = Mid(MyNum, 9, 1)
Digit12 = Mid(MyNum, 10, 1)
Case 9
Digit4 = Mid(MyNum, 1, 1)
Digit5 = Mid(MyNum, 2, 1)
Digit6 = Mid(MyNum, 3, 1)
Digit7 = Mid(MyNum, 4, 1)
Digit8 = Mid(MyNum, 5, 1)
Digit9 = Mid(MyNum, 6, 1)
Digit11 = Mid(MyNum, 8, 1)
Digit12 = Mid(MyNum, 9, 1)
Case 8
Digit5 = Mid(MyNum, 1, 1)
Digit6 = Mid(MyNum, 2, 1)
Digit7 = Mid(MyNum, 3, 1)
Digit8 = Mid(MyNum, 4, 1)
Digit9 = Mid(MyNum, 5, 1)
Digit11 = Mid(MyNum, 7, 1)
Digit12 = Mid(MyNum, 8, 1)
Case 7
Digit6 = Mid(MyNum, 1, 1)
Digit7 = Mid(MyNum, 2, 1)
Digit8 = Mid(MyNum, 3, 1)
Digit9 = Mid(MyNum, 4, 1)
Digit11 = Mid(MyNum, 6, 1)
Digit12 = Mid(MyNum, 7, 1)
Case 6
Digit7 = Mid(MyNum, 1, 1)
Digit8 = Mid(MyNum, 2, 1)
Digit9 = Mid(MyNum, 3, 1)
Digit11 = Mid(MyNum, 5, 1)
Digit12 = Mid(MyNum, 6, 1)
Case 5
Digit8 = Mid(MyNum, 1, 1)
Digit9 = Mid(MyNum, 2, 1)
Digit11 = Mid(MyNum, 4, 1)
Digit12 = Mid(MyNum, 5, 1)
Case 4
Digit9 = Mid(MyNum, 1, 1)
Digit11 = Mid(MyNum, 3, 1)
Digit12 = Mid(MyNum, 4, 1)
Case 3
Digit11 = Mid(MyNum, 2, 1)
Digit12 = Mid(MyNum, 3, 1)
Case 2
Digit12 = Mid(MyNum, 2, 1)
End Select
'--------------------------------------------------
'''Do million
If Digit1 <> 0 Or Digit2 <> 0 Or Digit3 <> 0 Then
Million = "Million "
End If
Dim M1
M1 = Digit1
Select Case M1
Case 0
M1 = ""
Case 1
M1 = "One Hundred "
Case 2
M1 = "Two Hundred "
Case 3
M1 = "Three Hundred "
Case 4
M1 = "Four Hundred "
Case 5
M1 = "Five Hundred "
Case 6
M1 = "Six Hundred "
Case 7
M1 = "Seven Hundred "
Case 8
M1 = "Eight Hundred "
Case 9
M1 = "Nine Hundred "
End Select
Dim M2
M2 = Digit2
Select Case M2
Case 0
M2 = ""
Case 1
M2 = ""
Case 2
M2 = "Twenty "
Case 3
M2 = "Thirty "
Case 4
M2 = "Forty "
Case 5
M2 = "Fifty "
Case 6
M2 = "Sixty "
Case 7
M2 = "Seventy "
Case 8
M2 = "Eighty "
Case 9
M2 = "Ninety "
End Select
Dim M3
M3 = Digit3
If Digit2 = 1 Then 'check against the number before
Select Case M3
Case 0
M3 = "Ten "
Case 1
M3 = "Eleven "
Case 2
M3 = "Twelve "
Case 3
M3 = "Thirteen "
Case 4
M3 = "Fourteen "
Case 5
M3 = "Fifteen "
Case 6
M3 = "Sixteen "
Case 7
M3 = "Seventeen "
Case 8
M3 = "Eighteen "
Case 9
M3 = "Nineteen "
End Select
Else
Select Case M3
Case 0
M3 = ""
Case 1
M3 = "One "
Case 2
M3 = "Two "
Case 3
M3 = "Three "
Case 4
M3 = "Four "
Case 5
M3 = "Five "
Case 6
M3 = "Six "
Case 7
M3 = "Seven "
Case 8
M3 = "Eight "
Case 9
M3 = "Nine "
End Select
End If
Million = M1 & M2 & M3 & Million
'----------------------------------------------------
'''Do thousand
If Digit4 <> 0 Or Digit5 <> 0 Or Digit6 <> 0 Then
Thousand = "Thousand "
End If
Dim T1
T1 = Digit4
Select Case T1
Case 0
T1 = ""
Case 1
T1 = "One Hundred "
Case 2
T1 = "Two Hundred "
Case 3
T1 = "Three Hundred "
Case 4
T1 = "Four Hundred "
Case 5
T1 = "Five Hundred "
Case 6
T1 = "Six Hundred "
Case 7
T1 = "Seven Hundred "
Case 8
T1 = "Eight Hundred "
Case 9
T1 = "Nine Hundred "
End Select
Dim T2
T2 = Digit5
Select Case T2
Case 0
T2 = ""
Case 1
T2 = ""
Case 2
T2 = "Twenty "
Case 3
T2 = "Thirty "
Case 4
T2 = "Forty "
Case 5
T2 = "Fifty "
Case 6
T2 = "Sixty "
Case 7
T2 = "Seventy "
Case 8
T2 = "Eighty "
Case 9
T2 = "Ninety "
End Select
Dim T3
T3 = Digit6
If Digit5 = 1 Then 'check against the number before
Select Case T3
Case 0
T3 = "Ten "
Case 1
T3 = "Eleven "
Case 2
T3 = "Twelve "
Case 3
T3 = "Thirteen "
Case 4
T3 = "Fourteen "
Case 5
T3 = "Fifteen "
Case 6
T3 = "Sixteen "
Case 7
T3 = "Seventeen "
Case 8
T3 = "Eighteen "
Case 9
T3 = "Nineteen "
End Select
Else
Select Case T3
Case 0
T3 = ""
Case 1
T3 = "One "
Case 2
T3 = "Two "
Case 3
T3 = "Three "
Case 4
T3 = "Four "
Case 5
T3 = "Five "
Case 6
T3 = "Six "
Case 7
T3 = "Seven "
Case 8
T3 = "Eight "
Case 9
T3 = "Nine "
End Select
End If
Thousand = T1 & T2 & T3 & Thousand
'----------------------------------------------------
'''Do hundred
If Digit6 <> 0 Or Digit7 <> 0 Or Digit8 <> 0 Then
Hundred = "Hundred "
End If
Dim H1
H1 = Digit7
Select Case H1
Case 0
H1 = ""
Case 1
H1 = "One Hundred "
Case 2
H1 = "Two Hundred "
Case 3
H1 = "Three Hundred "
Case 4
H1 = "Four Hundred "
Case 5
H1 = "Five Hundred "
Case 6
H1 = "Six Hundred "
Case 7
H1 = "Seven Hundred "
Case 8
H1 = "Eight Hundred "
Case 9
H1 = "Nine Hundred "
End Select
Dim H2
H2 = Digit8
Select Case H2
Case 0
H2 = ""
Case 1
H2 = ""
Case 2
H2 = "Twenty "
Case 3
H2 = "Thirty "
Case 4
H2 = "Forty "
Case 5
H2 = "Fifty "
Case 6
H2 = "Sixty "
Case 7
H2 = "Seventy "
Case 8
H2 = "Eighty "
Case 9
H2 = "Ninety "
End Select
Dim H3
H3 = Digit9
If Digit8 = 1 Then 'check against the number before
Select Case H3
Case 0
H3 = "Ten "
Case 1
H3 = "Eleven "
Case 2
H3 = "Twelve "
Case 3
H3 = "Thirteen "
Case 4
H3 = "Fourteen "
Case 5
H3 = "Fifteen "
Case 6
H3 = "Sixteen "
Case 7
H3 = "Seventeen "
Case 8
H3 = "Eighteen "
Case 9
H3 = "Nineteen "
End Select
Else
Select Case H3
Case 0
H3 = ""
Case 1
H3 = "One "
Case 2
H3 = "Two "
Case 3
H3 = "Three "
Case 4
H3 = "Four "
Case 5
H3 = "Five "
Case 6
H3 = "Six "
Case 7
H3 = "Seven "
Case 8
H3 = "Eight "
Case 9
H3 = "Nine "
End Select
End If
Hundred = H1 & H2 & H3
'----------------------------------------------------
'Do decimal
Dim D1
D1 = Digit11
Select Case D1
Case 0
D1 = ""
Case 1
D1 = ""
Case 2
D1 = "Twenty "
Case 3
D1 = "Thirty "
Case 4
D1 = "Forty "
Case 5
D1 = "Fifty "
Case 6
D1 = "Sixty "
Case 7
D1 = "Seventy "
Case 8
D1 = "Eighty "
Case 9
D1 = "Ninety "
End Select
Dim D2
D2 = Digit12
If Digit11 = 1 Then 'check against the number before
Select Case D2
Case 0
D2 = "Ten "
Case 1
D2 = "Eleven "
Case 2
D2 = "Twelve "
Case 3
D2 = "Thirteen "
Case 4
D2 = "Fourteen "
Case 5
D2 = "Fifteen "
Case 6
D2 = "Sixteen "
Case 7
D2 = "Seventeen "
Case 8
D2 = "Eighteen "
Case 9
D2 = "Nineteen "
End Select
Else
Select Case D2
Case 0
D2 = ""
Case 1
D2 = "One "
Case 2
D2 = "Two "
Case 3
D2 = "Three "
Case 4
D2 = "Four "
Case 5
D2 = "Five "
Case 6
D2 = "Six "
Case 7
D2 = "Seven "
Case 8
D2 = "Eight "
Case 9
D2 = "Nine "
End Select
End If
myDecimal = D1 & D2
'--------------------------------------------------
'--------------------------------------------------
If Million = "" And Thousand = "" And Hundred = "" Then
MyDollar = ""
Space = ""
Separator = ""
Else: Space = " "
Separator = "And "
End If
If myDecimal = "" Then
MyCent = ""
Separator = ""
End If
If txRM = "" Then
Space = "" 'So that the 1st digit will not have a gap in front
End If
txtString = MyDollar & Space & Million & Thousand & Hundred & Separator & MyCent & myDecimal
'ActiveCell = txtString
'ActiveCell.Offset(1, 0).Select
Next i
Exit Function
Else
MsgBox "The cell must contain a number", vbCritical, "Macro Error"
End If
End Function
Untuk menjalankan macro ini silahkan balik ke window Microsoft Excel lalu tekan Alt + F8 lalu pilih Open_Form_Convert2Text dan klik Run.
Untuk lebih lengkap mengenai penggunaan macro ini, silahkan buka artikel Convert Numbers to Text.
Selamat mencoba!
Read More
Kita mulai dengan membuat form terlebih dahulu dengan menekan Alt + F8 (Microsoft Visual Basic) lalu Insert > User Form.
Selanjutnya form kosong di atas didesain seperti gambar di bawah ini dengan menggunakan fungsi-fungsi yang ada dalam Toolbox:
Perhatikan nomor-nomor di atas. Nomor-nomor tersebut adalah untuk menjelaskan masing-masing properties, isi propertiesnya sbb:
1. Form
2. Reference Cell
3. Output Cell
4. Tombol radio English
5. Tombol radio Bahasa Indonesia
6. Tombol Convert
7. Tombol Exit
Untuk scriptnya pilih View > Code lalu gunakan script berikut:
1. Form
Private Sub UserForm_Activate()
opt_english.Value = True
cmd_run.SetFocus
End Sub
Private Sub UserForm_QueryClose _
(cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
cancel = True
End If
End Sub
6. Tombol Convert
Private Sub cmd_run_Click()
If Ref_cell.Value = "" Or Output_cell.Value = "" Then
MsgBox "Reference cell and Output cell must not be empty.", vbCritical + vbOKOnly, "Enter Reference and Output Cell"
Else
If Ref_cell.Value = Output_cell.Value Then
MsgBox "Reference cell and Output cell must not be the same. Please select other cell for Output Cell.", vbCritical + vbOKOnly, "Reference and Output Cell Error"
Else
If opt_english.Value = False Then
Range(Output_cell.Value).Select
ActiveCell.Value = "=dghrf(" & Ref_cell.Value & ")"
Else
Range(Output_cell.Value).Select
ActiveCell.Value = "=txtString(" & Ref_cell.Value & ")"
End If
End If
End If
End Sub
7. Tombol Exit
Private Sub cmd_cancel_Click()
Unload Me
End Sub
Selanjutnya pilih ThisWorkbook pada window Project – VBA Project dan gunakan script berikut pada window sebelah kanan:
Sub Open_Form_Convert2Text()
With Form_Convert2Text
.Show vbModeless
End With
End Sub
Sampai tahap di atas kita baru membuat tampilan interfacenya (GUI), sedang untuk mendefinisikan function =dghrf dan =txtString kita buat dengan 2 (dua) Modules. Pilih Insert > Module dan beri nama Angka2Text pada propertiesnya dan masukkan script di bawah ini:
Dim Huruf(0 To 9) As String
Dim ax(0 To 3) As Double
Function INIT_angka()
Huruf(0) = ""
Huruf(1) = "satu "
Huruf(2) = "dua "
Huruf(3) = "tiga "
Huruf(4) = "empat "
Huruf(5) = "lima "
Huruf(6) = "enam "
Huruf(7) = "tujuh "
Huruf(8) = "delapan "
Huruf(9) = "sembilan "
End Function
Function dgratus(angka As Double) As String
Temp = ""
INIT_angka
panjang = Len(Trim(Str(angka)))
nilai = Right("000", 3 - panjang) + Trim(Str(angka))
For y = 3 To 1 Step -1
ax(y) = Mid(nilai, y, 1)
Next y
Select Case ax(1)
Case Is = 1
Temp = "seratus "
Case Is > 1
Temp = Huruf(Val(ax(1))) + " " + "ratus "
Case Else
Temp = " "
End Select
Select Case ax(2)
Case Is = 0
Temp = Temp + Huruf(Val(ax(3)))
Case Is = 1
Select Case ax(3)
Case Is = 1
Temp = Temp + "sebelas "
Case Is = 0
Temp = Temp + "sepuluh "
Case Else
Temp = Temp + Huruf(Val(ax(3))) + "belas "
End Select
Case Is > 1
Temp = Temp + Huruf(Val(ax(2))) + "puluh "
Temp = Temp + " " + Huruf(Val(ax(3)))
End Select
dgratus = Temp
End Function
Function dghrf(angka As Double) As String
Dim ratusan(0 To 6) As String
Dim sebut(0 To 4) As String
sebut(1) = " ribu "
sebut(2) = " juta "
sebut(3) = " milyar "
sebut(4) = " trilyun "
panjang = Len(Trim(Str(angka)))
kali = Int(panjang / 3)
If Int(panjang / 3) * 3 <> panjang Then
kali = kali + 1
sisa = panjang - Int(panjang / 3) * 3
nilai = Right("000", 3 - sisa) + Trim(Str(angka))
Else
nilai = Trim(Str(angka))
End If
For x = 0 To kali
ratusan(kali - x) = Mid(nilai, x * 3 + 1, 3)
Next x
For y = kali To 1 Step -1
If y = 2 And Val(ratusan(y)) = 1 Then
Temp = Temp + "seribu"
Else
If Val(ratusan(y)) = 0 Then
Temp = Temp
Else
Temp = Temp + dgratus(Val(ratusan(y)))
Temp = Temp + sebut(y - 1)
End If
End If
Next y
dghrf = Application.Trim(Application.Proper(Temp)) + " Rupiah"
End Function
Selanjutnya pilih lagi Insert > Module dan beri nama Convert2Text pada propertiesnya. Masukkan script di bawah ini:
Public Function txtString(jumlah As Long)
jumlah = Application.Round(jumlah, 2)
If Application.IsNumber(jumlah) = True Then
Dim MyNum, Num1, NumLength, txString
Dim Million, Thousand, Hundred, myDecimal
Dim MyDollar, MyCent
Dim Space, Separator
Dim Digit1, Digit2, Digit3, Digit4, Digit5
Dim Digit6, Digit7, Digit8, Digit9
Dim Digit11, Digit12 'Decimals
Dim txRM
Dim Rng, i
Rng = Selection.Rows.Count
For i = 1 To Rng
MyDollar = "IDR " 'Must be within the
MyCent = "Sen " 'For Next Loop
'--------------------------------------------------
Digit1 = 0 'Resets numbers
Digit2 = 0
Digit3 = 0
Digit4 = 0
Digit5 = 0
Digit6 = 0
Digit7 = 0
Digit8 = 0
Digit9 = 0
Digit11 = 0
Digit12 = 0
Million = ""
Thousand = ""
Hundred = ""
myDecimal = ""
'--------------------------------------------------
Num1 = jumlah
MyNum = Format(Num1, "#0.00") 'Shows 2 decimal places
NumLength = Len(MyNum) 'Determines the length of the number
Select Case NumLength 'Length determines the
Case 12 'position of the text boxes
Digit1 = Mid(MyNum, 1, 1)
Digit2 = Mid(MyNum, 2, 1)
Digit3 = Mid(MyNum, 3, 1)
Digit4 = Mid(MyNum, 4, 1)
Digit5 = Mid(MyNum, 5, 1)
Digit6 = Mid(MyNum, 6, 1)
Digit7 = Mid(MyNum, 7, 1)
Digit8 = Mid(MyNum, 8, 1)
Digit9 = Mid(MyNum, 9, 1)
Digit11 = Mid(MyNum, 11, 1)
Digit12 = Mid(MyNum, 12, 1)
Case 11
Digit2 = Mid(MyNum, 1, 1)
Digit3 = Mid(MyNum, 2, 1)
Digit4 = Mid(MyNum, 3, 1)
Digit5 = Mid(MyNum, 4, 1)
Digit6 = Mid(MyNum, 5, 1)
Digit7 = Mid(MyNum, 6, 1)
Digit8 = Mid(MyNum, 7, 1)
Digit9 = Mid(MyNum, 8, 1)
Digit11 = Mid(MyNum, 10, 1)
Digit12 = Mid(MyNum, 11, 1)
Case 10
Digit3 = Mid(MyNum, 1, 1)
Digit4 = Mid(MyNum, 2, 1)
Digit5 = Mid(MyNum, 3, 1)
Digit6 = Mid(MyNum, 4, 1)
Digit7 = Mid(MyNum, 5, 1)
Digit8 = Mid(MyNum, 6, 1)
Digit9 = Mid(MyNum, 7, 1)
Digit11 = Mid(MyNum, 9, 1)
Digit12 = Mid(MyNum, 10, 1)
Case 9
Digit4 = Mid(MyNum, 1, 1)
Digit5 = Mid(MyNum, 2, 1)
Digit6 = Mid(MyNum, 3, 1)
Digit7 = Mid(MyNum, 4, 1)
Digit8 = Mid(MyNum, 5, 1)
Digit9 = Mid(MyNum, 6, 1)
Digit11 = Mid(MyNum, 8, 1)
Digit12 = Mid(MyNum, 9, 1)
Case 8
Digit5 = Mid(MyNum, 1, 1)
Digit6 = Mid(MyNum, 2, 1)
Digit7 = Mid(MyNum, 3, 1)
Digit8 = Mid(MyNum, 4, 1)
Digit9 = Mid(MyNum, 5, 1)
Digit11 = Mid(MyNum, 7, 1)
Digit12 = Mid(MyNum, 8, 1)
Case 7
Digit6 = Mid(MyNum, 1, 1)
Digit7 = Mid(MyNum, 2, 1)
Digit8 = Mid(MyNum, 3, 1)
Digit9 = Mid(MyNum, 4, 1)
Digit11 = Mid(MyNum, 6, 1)
Digit12 = Mid(MyNum, 7, 1)
Case 6
Digit7 = Mid(MyNum, 1, 1)
Digit8 = Mid(MyNum, 2, 1)
Digit9 = Mid(MyNum, 3, 1)
Digit11 = Mid(MyNum, 5, 1)
Digit12 = Mid(MyNum, 6, 1)
Case 5
Digit8 = Mid(MyNum, 1, 1)
Digit9 = Mid(MyNum, 2, 1)
Digit11 = Mid(MyNum, 4, 1)
Digit12 = Mid(MyNum, 5, 1)
Case 4
Digit9 = Mid(MyNum, 1, 1)
Digit11 = Mid(MyNum, 3, 1)
Digit12 = Mid(MyNum, 4, 1)
Case 3
Digit11 = Mid(MyNum, 2, 1)
Digit12 = Mid(MyNum, 3, 1)
Case 2
Digit12 = Mid(MyNum, 2, 1)
End Select
'--------------------------------------------------
'''Do million
If Digit1 <> 0 Or Digit2 <> 0 Or Digit3 <> 0 Then
Million = "Million "
End If
Dim M1
M1 = Digit1
Select Case M1
Case 0
M1 = ""
Case 1
M1 = "One Hundred "
Case 2
M1 = "Two Hundred "
Case 3
M1 = "Three Hundred "
Case 4
M1 = "Four Hundred "
Case 5
M1 = "Five Hundred "
Case 6
M1 = "Six Hundred "
Case 7
M1 = "Seven Hundred "
Case 8
M1 = "Eight Hundred "
Case 9
M1 = "Nine Hundred "
End Select
Dim M2
M2 = Digit2
Select Case M2
Case 0
M2 = ""
Case 1
M2 = ""
Case 2
M2 = "Twenty "
Case 3
M2 = "Thirty "
Case 4
M2 = "Forty "
Case 5
M2 = "Fifty "
Case 6
M2 = "Sixty "
Case 7
M2 = "Seventy "
Case 8
M2 = "Eighty "
Case 9
M2 = "Ninety "
End Select
Dim M3
M3 = Digit3
If Digit2 = 1 Then 'check against the number before
Select Case M3
Case 0
M3 = "Ten "
Case 1
M3 = "Eleven "
Case 2
M3 = "Twelve "
Case 3
M3 = "Thirteen "
Case 4
M3 = "Fourteen "
Case 5
M3 = "Fifteen "
Case 6
M3 = "Sixteen "
Case 7
M3 = "Seventeen "
Case 8
M3 = "Eighteen "
Case 9
M3 = "Nineteen "
End Select
Else
Select Case M3
Case 0
M3 = ""
Case 1
M3 = "One "
Case 2
M3 = "Two "
Case 3
M3 = "Three "
Case 4
M3 = "Four "
Case 5
M3 = "Five "
Case 6
M3 = "Six "
Case 7
M3 = "Seven "
Case 8
M3 = "Eight "
Case 9
M3 = "Nine "
End Select
End If
Million = M1 & M2 & M3 & Million
'----------------------------------------------------
'''Do thousand
If Digit4 <> 0 Or Digit5 <> 0 Or Digit6 <> 0 Then
Thousand = "Thousand "
End If
Dim T1
T1 = Digit4
Select Case T1
Case 0
T1 = ""
Case 1
T1 = "One Hundred "
Case 2
T1 = "Two Hundred "
Case 3
T1 = "Three Hundred "
Case 4
T1 = "Four Hundred "
Case 5
T1 = "Five Hundred "
Case 6
T1 = "Six Hundred "
Case 7
T1 = "Seven Hundred "
Case 8
T1 = "Eight Hundred "
Case 9
T1 = "Nine Hundred "
End Select
Dim T2
T2 = Digit5
Select Case T2
Case 0
T2 = ""
Case 1
T2 = ""
Case 2
T2 = "Twenty "
Case 3
T2 = "Thirty "
Case 4
T2 = "Forty "
Case 5
T2 = "Fifty "
Case 6
T2 = "Sixty "
Case 7
T2 = "Seventy "
Case 8
T2 = "Eighty "
Case 9
T2 = "Ninety "
End Select
Dim T3
T3 = Digit6
If Digit5 = 1 Then 'check against the number before
Select Case T3
Case 0
T3 = "Ten "
Case 1
T3 = "Eleven "
Case 2
T3 = "Twelve "
Case 3
T3 = "Thirteen "
Case 4
T3 = "Fourteen "
Case 5
T3 = "Fifteen "
Case 6
T3 = "Sixteen "
Case 7
T3 = "Seventeen "
Case 8
T3 = "Eighteen "
Case 9
T3 = "Nineteen "
End Select
Else
Select Case T3
Case 0
T3 = ""
Case 1
T3 = "One "
Case 2
T3 = "Two "
Case 3
T3 = "Three "
Case 4
T3 = "Four "
Case 5
T3 = "Five "
Case 6
T3 = "Six "
Case 7
T3 = "Seven "
Case 8
T3 = "Eight "
Case 9
T3 = "Nine "
End Select
End If
Thousand = T1 & T2 & T3 & Thousand
'----------------------------------------------------
'''Do hundred
If Digit6 <> 0 Or Digit7 <> 0 Or Digit8 <> 0 Then
Hundred = "Hundred "
End If
Dim H1
H1 = Digit7
Select Case H1
Case 0
H1 = ""
Case 1
H1 = "One Hundred "
Case 2
H1 = "Two Hundred "
Case 3
H1 = "Three Hundred "
Case 4
H1 = "Four Hundred "
Case 5
H1 = "Five Hundred "
Case 6
H1 = "Six Hundred "
Case 7
H1 = "Seven Hundred "
Case 8
H1 = "Eight Hundred "
Case 9
H1 = "Nine Hundred "
End Select
Dim H2
H2 = Digit8
Select Case H2
Case 0
H2 = ""
Case 1
H2 = ""
Case 2
H2 = "Twenty "
Case 3
H2 = "Thirty "
Case 4
H2 = "Forty "
Case 5
H2 = "Fifty "
Case 6
H2 = "Sixty "
Case 7
H2 = "Seventy "
Case 8
H2 = "Eighty "
Case 9
H2 = "Ninety "
End Select
Dim H3
H3 = Digit9
If Digit8 = 1 Then 'check against the number before
Select Case H3
Case 0
H3 = "Ten "
Case 1
H3 = "Eleven "
Case 2
H3 = "Twelve "
Case 3
H3 = "Thirteen "
Case 4
H3 = "Fourteen "
Case 5
H3 = "Fifteen "
Case 6
H3 = "Sixteen "
Case 7
H3 = "Seventeen "
Case 8
H3 = "Eighteen "
Case 9
H3 = "Nineteen "
End Select
Else
Select Case H3
Case 0
H3 = ""
Case 1
H3 = "One "
Case 2
H3 = "Two "
Case 3
H3 = "Three "
Case 4
H3 = "Four "
Case 5
H3 = "Five "
Case 6
H3 = "Six "
Case 7
H3 = "Seven "
Case 8
H3 = "Eight "
Case 9
H3 = "Nine "
End Select
End If
Hundred = H1 & H2 & H3
'----------------------------------------------------
'Do decimal
Dim D1
D1 = Digit11
Select Case D1
Case 0
D1 = ""
Case 1
D1 = ""
Case 2
D1 = "Twenty "
Case 3
D1 = "Thirty "
Case 4
D1 = "Forty "
Case 5
D1 = "Fifty "
Case 6
D1 = "Sixty "
Case 7
D1 = "Seventy "
Case 8
D1 = "Eighty "
Case 9
D1 = "Ninety "
End Select
Dim D2
D2 = Digit12
If Digit11 = 1 Then 'check against the number before
Select Case D2
Case 0
D2 = "Ten "
Case 1
D2 = "Eleven "
Case 2
D2 = "Twelve "
Case 3
D2 = "Thirteen "
Case 4
D2 = "Fourteen "
Case 5
D2 = "Fifteen "
Case 6
D2 = "Sixteen "
Case 7
D2 = "Seventeen "
Case 8
D2 = "Eighteen "
Case 9
D2 = "Nineteen "
End Select
Else
Select Case D2
Case 0
D2 = ""
Case 1
D2 = "One "
Case 2
D2 = "Two "
Case 3
D2 = "Three "
Case 4
D2 = "Four "
Case 5
D2 = "Five "
Case 6
D2 = "Six "
Case 7
D2 = "Seven "
Case 8
D2 = "Eight "
Case 9
D2 = "Nine "
End Select
End If
myDecimal = D1 & D2
'--------------------------------------------------
'--------------------------------------------------
If Million = "" And Thousand = "" And Hundred = "" Then
MyDollar = ""
Space = ""
Separator = ""
Else: Space = " "
Separator = "And "
End If
If myDecimal = "" Then
MyCent = ""
Separator = ""
End If
If txRM = "" Then
Space = "" 'So that the 1st digit will not have a gap in front
End If
txtString = MyDollar & Space & Million & Thousand & Hundred & Separator & MyCent & myDecimal
'ActiveCell = txtString
'ActiveCell.Offset(1, 0).Select
Next i
Exit Function
Else
MsgBox "The cell must contain a number", vbCritical, "Macro Error"
End If
End Function
Untuk menjalankan macro ini silahkan balik ke window Microsoft Excel lalu tekan Alt + F8 lalu pilih Open_Form_Convert2Text dan klik Run.
Untuk lebih lengkap mengenai penggunaan macro ini, silahkan buka artikel Convert Numbers to Text.
Selamat mencoba!
Langganan:
Postingan (Atom)
































