Jumat, 29 Juni 2012

Membuat Fungsi Sendiri dengan VBA Excel

Pada bab ini penulis akan membahas bagaimana cara membuat fungsi yang dibuat sendiri (user define function) pada Excel menggunakan VBA. Hal ini berguna bila kita hendak membutuhkan sebuah fungsi yang tidak disediakan oleh Microsoft Excel. Pada bahasan kali ini, penulis akan membuat fungsi yang akan merubah angka menjadi kata-kata pada Excel 2007. Tulisan ini diinspirasi oleh komentas salah seorang pembacabernama Pak Djoko Prakoso (djoko.prakoso.13@gmail.com)  pada tulisan "Memulai Macro Excel"

MEMBUAT BERKAS YANG MEMILIKI MACRO PADA EXCEL 2007
Karena menggunakan Excel 2007 maka berkas Excel yang kita buat haruslah menggunakan extensi “.xslm”. Sekarang mari kita membuat sebuah berkas baru Excel, berkas tersebut masih dalam format “.xslx”.

Buka berkas tersebut, kemudian simpan sebagai (save as) “fungsi sendiri.xslm”.

Maka kita telah membuat sebuah berkas Excel 2007 yang dapat menggunakan macro (macro enabled). Lalu kita atur keamanan dan privasi Excel kita pada  Macro Settings dan Trusted Location (lokasi terpercaya tempat kita menyimpan berkas-berkas excel ber-macro), sehingga macro yang telah kita buat dapat bekerja. Kedua pengaturan tersebut terdapat pada Excel Option-Trust Center-Trust_Center Settings.

Pada pengaturan Macro, pilih “Disabled All Macro Except Digitally Signed Macros” (pilihan ke 3) atau “Enabled All Macro” (pilihan ke 4), penulis menyarankan pilihan ke-4 bagi pemula.

Kemudian kita atur juga lokasi folder yang terpercaya (Trusted Location), di komputer penulis terletak di “E:\VBA\”. Jangan lupa untuk mencentang pilihan “Subfolder of this location also trusted” agar subfolder dari folder ini juga dipercaya.
MEMBUAT FUNGSI MERUBAH ANGKA KE KATA UNTUK ANGKA TUNGGAL
Untuk membuat fungsi sendiri, maka kita perlu membuat prosedur fungsi (function procedure) VBA pada module di Visual Basic Editor.


Sekarang kita telah membuat sebuah fungsi bernama “angka_ke_kata” yang akan menghasilkan sebuah kata (tipe data string) dengan argumen sebuah angka (tipe data double).  Tipe data string diperuntukkan untuk teks, sedangkan tipe data double diperuntukkan untuk bilangan asli dan pecahan.
Function angka_ke_kata(angka As Double) As String
Dalam fungsi ini pertama kita akan menyiapkan array yang berisi teks yang berassosiasi dengan bilangan yang dimaksud. Array itu kita beri nama “kata_angka”, berukuran 10, yang beranggotakan kata untuk bilangan dari nol sampai sembilan.
Dim kata_angka(10) As String
kata_angka(0) = "nol"
kata_angka(1) = "satu"
kata_angka(2) = "dua"
kata_angka(3) = "tiga"
kata_angka(4) = "empat"
kata_angka(5) = "lima"
kata_angka(6) = "enam"
kata_angka(7) = "tujuh"
kata_angka(8) = "delapan"
kata_angka(9) = "sembilan"


Berdasarkan array ini kita sudah dapat membuat sebuah fungsi yang merubah angka tunggal menjadi kata. Kita cukup menambahkan kode VBA yang mengembalikan kata yang sesuai dengan argumen “angka” ketika fungsi “kata_ke_angka” dipanggil.
angka_ke_kata = kata_angka(angka)


Sekarang kita kembali ke “sheet1” pada workbook Excel untuk mencoba fungsi yang telah dibuat. Isikan angka pada sel “A1” kemudian pada sel “A2” masukkan fungsi excel “angka_ke_kata”. Fungsi ini terletak pada pilihan fungsi “user defined”.




MEMBUAT FUNGSI MERUBAH ANGKA KE KATA UNTUK ANGKA LEBIH DARI 9

Sejauh ini kita telah membuat sebuah fungsi yang merubah angka ke kata. Tapi fugnsi ini bekerja hanya untuk angka-angka tunggal (dari nol sampai Sembilan). Lebih dari itu maka fugnsi init akan menghasilkan kesalahan, sebagai contoh kita tuliskan nilai 10 atau 200 di sel “A1”.



Selanjutnya kita akan melengkapi kode-kode di fungsi yang telah kita buat, sehingga dapat merubah angka lebih dari 9 menjadi kata. Algoritma nya sebenarnya cukup mudah, fungsi yang kita buat pertamaakan menghitung panjang angka yang akan diubah. Setelah itu fungsi tersebut akan merubah tiap anggota angka menggunakan array  “kata_angka” seperti bahaasan sebelumnya, kemudian menggabungkan hasil-hasilnya. Sehingga hasil yang akan didapat nanti adalah berupa gabungan kata-kata, atau lebih tepatnya disebut kalimat.
Dalam Visual Basic dikenal fungsi “Len(pernyataan)” yang berguna untuk mengetahui panjang sebuah teks (kata atau kalimat). Karena angka yang kita ubah berupa bilangan bukan teks, maka kita perlu merubah angka tersebut menjadi teks dengan fungsi “Cstr(pernyataan)”. Bilangan dalam format data double yang telah dikonversi menjadi format data string akan kita simpan dalam variabel “angka_dlm_teks”. Sedangkan panjangnya kita taruh dalam variabel “panjang_angka”.
angka_dlm_teks = CStr(angka)
panjang_angka = Len(angka_dlm_teks)

Sekarang mari kita uji hasilnya dengan angka 5 , 317, 105076. 

Berdasarkan informasi panjang angka tersebut, kita dapat membuat sebuah loop untuk merubah tiap anggota angka menjadi kata dan menggabungkannya kembali. Dalam loop tersebut setiap karakter dalam variabel “angka_dlm_teks” akan kita ambil meggunakan fungsi “Mid(string,awal,jumlah)”. Kemudian memasukan karakter itu ke dalam variabel “index_angka”. Variabel ini akan digunakan untuk mengambil data dari anggota array  “kata_angka” yang berasosiasi dengan karakter tersebut. Untuk menggabungkan hasilnya dari setiap kali proses tersebut diulang, maka digunakan operator penggabungan “&”.
For i = 1 To panjang_angka
    index_angka = Mid(angka_dlm_teks, i, 1)
    angka_ke_kata = angka_ke_kata & " " & kata_angka(index_angka)
Next


Sekarang mari ktia tes fungsi ini dengan angka 1, 720 dan 30827.







61 komentar:

Anonim mengatakan...

Mas Ben, sudah 3 tahun lebih gak update postingan ya... saya suka sama tutorialnya... semoga produktif lagi

Ben mengatakan...

makasih mas....sedang ada kesibukan di kantor jadi waktunya agak sulit buat nulis2 lagi...kalo ada sempat saya kan publih banyak...dan mudah2an update dengan teknologi informasi terkini :)

Anonim mengatakan...

very good!

Ben mengatakan...

thank you...

jujun juanda mengatakan...

Maaf Pak, saya mau tanya nih...
Kalau ingin menghitung rata-rata nilai pada suatu range, tapi hanya yang memiliki nilai lebih dari nol (0), sementara pada range tersebut, terdapat nilai nol (0) dan yang lainnya. gimana caranya ya?
Sebelumnya, saya ucapkan terimakasih...

Ben mengatakan...

kira-kira seperti ini mas...

sub rata()

dim nilai as range
dim sum, i , j as integer
dim rerata as single

set nilai = range("A1:A5")

sum = 0
i = 1
j = 1

do while nilai(i)<>""
if nilai(i) > 0 then
sum = sum + nilai (i)
j = j + 1
end if
i = i +1
loop

rerata = sum/(j-1)

end sub

kafidaya mengatakan...

mau tanya Pak, kalau di sel ada rumusan ='amount(sheet)(sel)!+amount(sheet)sel!.
tetapi kalau kita cari sheetnya dalam 1 file tidak ada padahal nama sheet tsb ada dalam 1 file. mohon bantuannya. terima kasih

kafidaya mengatakan...

Saya mau bertanya apa maksud dari fungsi ='amount(sheet)(sel)!+
tetapi kalau kita cari disheet tsb pada sel yg ditunjuk tidak ada nilanya.

mohon penjelasannya. terima kasih

kafidaya mengatakan...

saya ingin tau apabila ada rumusan
='amount(sheet)(sell)!+

tetapi apabila kita cari pada sheet dan sell yang dituju ternyata tidak ada nilainya.
mohon pencerahannya. terima kasih

Ben mengatakan...

saya baru tahu fungsi "amount" itu mas...

beum pernah memakainya...

thx

alex agung saputra mengatakan...

Mas Ben,saat saya terapkan untuk merubah angka ke kata pada angka tunggal di excel berjalan dengan baik sesuai tutorialnya,tapi saat saya terapkan untuk tutorial selanjutnya(angka >9) kok tak muncul seperti apa yg ada di tutorial tersebut.Padahal tutorial tersebut saya ketik sama persis dengan yang Mas Ben buat...minta tolong solusinya..makasih...

alex agung saputra mengatakan...

Pak Ben,untuk merubah angka ke kata pada angka tunggal berjalan dengan baik tpi saat saya terapkan angka >9 sesuai dengan tutorial kok tak bisa berjalan dengan baik. Mohon bantuannya dan solusinya. Makasih Pak Ben.

Ben mengatakan...

coba kirimkan kode macro yg mas alex agung buat ke email saya...nanti saya lihat....koding yg saya tulis saay sudah coba sendiri sih....dan berhasil....

Anonim mengatakan...

Mas Ben, Help me... saya mau merekap file dari excel ke excel tapi tanpa membuka file tersebut dg sekali klik tombol, bisa terekap sekalian membuat folder dan nafa file sendiri si file rekap ini.
Mohon petunjuknya Mas :)

Ben mengatakan...

sepertinya permasalhan mas sudah pernah dibahas di koment2 di blog ini....

secara prinsip mudah..tinggal perlu belajar dan kerja keras... :)

Andi mengatakan...

ini sangat bermanfaat, thanks gan

Ben mengatakan...

trimakasih gan...

Anonim mengatakan...

Mohon maaf mas saya tanya tentang microsoft visual basic untuk mengerjakan VB excel ini kita dapat di mana?

Ben mengatakan...

kalau sudah ada office excel, tidak perlu install apa2 lagi mas..

Yuri Arfil mengatakan...

Mas saya mau nanya, kalau saya mau buat fungsi untuk menjelaskan nilai "123A2" menjadi "satu dua tiga A dua" gimana ya,, terima kasih sebelumnya..

Yuri Arfil mengatakan...

mas saya mau nanya, kalau saya mau buat fungsi untuk menjelaskan nilai "12A2" menjadi "satu dua A dua" gimana ya? terima kasih.

Ben mengatakan...

tutorial di posting ini dah bisa mas..tinggal rubbah array nya sesuai kebutuhan mas..

Yuri Arfil mengatakan...

maaf mas saya kagak ngerti mau ngerubah yang mana,, bisa minta tolong di jelaskan gak,, soalnya saya butuh banget fungsi yang kayak gini.. terima kasih.

Ben mengatakan...

belajarnya pelan-pelan mas..step by step...nanti mas ketemu kok, dari kode yang saya udah tampilkan di sini mana yg mas bisa ubah untuk tujuan mas..

Yuri Arfil mengatakan...

Makasih mas atas waktu dan ilmunya..

Anonim mengatakan...

mas, ad pertanyaan nie..

gmana ya caranya membuat format cell sendiri secara automatis,,

misalkan saya ketikkan angka pada sebuah cell dengan isian 21002090010608 dan sy tekan enter,,trus angka tadi berubah formatnya secara otomatis menjadi 210.02.09.001060-8...
gmana caranya ya mas??

terima kasih, mohon bantuannya..

uchil mengatakan...

siang mas ben, saya irwan d bintaro mw tanya
jika ada data di workbook [ nama ] [ usia ] [ sekolah ] saya ingin buat workbook dengan macro yg isinya data dr workbook pertama tapi yg usianya 7 th aja.kira2 contoh code macro'y bagaimana y ?

Ben mengatakan...

@mas Anonim : masing-masing elemen string mas jadikan array..lalu dimasukkan ke array string lain yang akan akan Anda tampilkan di cell tujuan

@ mas Uchil : saya juga tinggal di bintaro mas...bisa pake fungsi "if" mas

misal data di worbook 1 sheet 1 kolom B, sedang workbook tujuan di workbook2 shett1 kolom B maka seperti ini kodingnya


if workbook1.sheet1.Columns("B:B").Cells(i).Value = 7 then

workbook2.sheet1.Columns("B:B").Cells(i).Value = workbook1.sheet1.Columns("B:B").Cells(i).Value
end if

Anonim mengatakan...

Mas ben saya mau nanya..gimana yah caranya bikin rumus macro untuk copy cell yang acak di..sheet 2 ke sheet 1 misal saya punya data di sheet 2 cell A5,A10,A15...dst kelipatan lima...saya mau copy ke sheet 1 di range A1:A10, terina kasih

Ben mengatakan...

mainin indeksnya aja mas
contoh seperti ini

for i = 1 to 10
n = i*5
sheet1.range("A1").cells(i,1) = sheet2.range("A5").cells(n,1)

next

selebihnya utak utik aja sesuai keinginan mas.

Anonim mengatakan...

Sudah saya coba mas, berhasil tapi content data yang tercopy adalah isinya yang kelipatan 5, maksud saya copy data cell yang kelipatan lima kayak data di cell A5,A10,A15...gitu mas heheh maf ngerepotin tapi dapet ilmu baru neh jadinya

Anonim mengatakan...

Gak jadi mas dah ketmu saya yang salah.....dikit modif juga...makasihhhh banet mas bennnn.nnnn

Ben mengatakan...

owkeh mas...semoga sukses...

rudi dayat mengatakan...

ya-ha

Ben mengatakan...

thx Gan

Aing Lee mengatakan...

mantap mas..makasih tutorialnya

Ben mengatakan...

terimakasih mas...

tanjung baru mengatakan...

mas kok dijndla vba ane kg ada tuh plhn "anka_ke_kata" yg ada cma deleretion. mas jlasin ya cara munculi plhn2 yg ky gitu seperti clik dll, thanks dah mas

Ben mengatakan...

'"deleretion" itu apa mas...?
pilihan fungsi "angka_ke_kata" ada di pilihan fungsi excel user defined mas....carinya di fungsi2 kek sum, average dan sebagainya...
trimakasih

tanjung baru mengatakan...

maaf slah ketik, dlm ktrngan diatas gmbr yg ke 10 dlm drop dwen ada plhn "anka_ke_kata" di jndla ane cuma declaration doang, biar dlm drop dwen muncul plhan yg ky ditutuo ini gmana, maaf ya mas inggris ane krng dr stndar

Ben mengatakan...

Sudah di save as xlm lum mas.? pake excel brapa? cuba print screan bis itu kiriim ke email ane gan...

dede azzam mengatakan...

makasih ilmunya....

dede azzam mengatakan...

Oke...ilmunya... :)

Ben mengatakan...

sama-sama mas...

Vega Yamaha mengatakan...

mas ben saya mau buat funsi ad in ntuk memunculkan nomor nip seseorang bila merujuk ke nama seseorang gimana caranya?

Ben mengatakan...

pakai look up kan bisa mas....

Unknown mengatakan...

Minta tolong gan,, bagaimana cara menggunakan fungsi index menggunakan Macro VBnya? Tengkyu..

Ben mengatakan...

maaf lama jawabnya gan...ane lagi sibuk tesis nih..indexing maksundya...mengurutkan data berdasarkan urutan tertentu...

muhamadwahono mengatakan...

maaf pak menggangu, mau tanya,
bagaimana cara merubah file excel yang dijadikan file exe atau Application dikembalikan kembali menjadi file xls

Ben mengatakan...

saay belum pernah melakukan itu mas....sepertinya VBA Excel tidak bisa dijadikan exe dan sebaliknya

Tutarni mengatakan...

Ayooo update terus pembelajaran Visual Basic nya...!!!!
Jangan lupa kunjungi Blog Saya juga yaaaa :

http://www.belajarvb.com

Semoga bisa menjadi referensi belajar Visual Basic juga :)

Ben mengatakan...

belum sempat update lagi karena lagi urusin tesis....

oke, nice blog keep the good work

Anonim mengatakan...

mas Ben, mohon bantuannya nih,
kalo saya mau merubah misal :
DL.00.00 menjadi 1
DL.00.01 menjadi 5
DL.00.03 menjadi 9 dst
itu bagaimana ya codenya
saya coba pake code yang angka menjadi kata tapi tidak bisa..
mohon pencerahannya

Ben mengatakan...

fungsi matematika nya gimana mas

kenapa DL 00 00 menjadi 1 dan DL 00 01 menjadi 3??

kalo seperti ini formulasinya berbeda dengan fugnsi yg saya paparkan di tulisan di atas

Anonim mengatakan...

begini mas ben, misal cell A1 saya input DL.00.00 trus di cell B1 saya masukkan function (misal =aktif(A1)) menghasilkan "1"
nah itu gimana ya?
makasih sebelumnya

Rista Kusuma Hidayat mengatakan...

Mau tanya mas.. rumus untuk save as tetapi file namenya langsung ada sesuai dengan judul file entriannya. Misal kolom a1 adl key untuk nama file yg otomatis ke tulis ketika di save as. Tanpa saya nulis nama file otomatis udh ketulis sesuai data di a1 tsb. Mohon petunjuknya..
Suwun.. komeng mgl

Ben mengatakan...

pertama ambil nama filenya dulu, misal nama file ada di range ("A1")

--------
dim teks as string
teks = range ("A1").value
--------

kedua gabungkan namnya dengan folder dan extensi file

--------
teks = "C:\" & teks & ".xlsm
--------

ketiga simpan nama file

-------
ActiveWorkbook.SaveAs teks, fileformat:=52
-------

referensi nya bisa dilihat di sini
http://www.rondebruin.nl/win/s5/win001.htm

nyanya mengatakan...

Selamat siang,
Saya bekerja di salah satu bank swasta, tiap bulan saya melakukan pengecekan data akhir bulan. Saya ingin menggunakan makro excel untuk mempermudah pekerjaan saya. , yang saya ingin tanyakan.
1. Bagaimana dan apa syntax vba yang kita gunakan jika kita ingin mengolah data dari file csv dan jumlah baris file tersebut melebihi kapasitas ms.excel (lebih dari 1.500.000). saya ingin memecah isi file tersebut ke beberapa sheet.
2. Bagaimana dan apa syntax vba yang bisa kita gunakan jika kita ingin mengolah data dari file csv dimana terdapat beberapa file csv (masing-masing file tersebut memiliki jumlah baris yang berbeda) dan file-file tersebut akan kita gabungkan menjadi 1 di sheet excel.

boleh info dan bantuanny Mas..
terimakasih..
salam

Ben mengatakan...

langkah pertama adalh membaca tiap baris dalam berkas csv itu mas, detailnya bisa dilihat di link berikut

http://www.homeandlearn.org/open_a_text_file_in_vba.html

csv itu singkat Comma Separated Value, jadi intinya adalah data dipisahkan dengan koma...di link di atas diinfokan bagaim,ana memisahkan data-data csv lalu memasukkannya ke excel...


berikutnya dalah menginput data ke sheet excel sampai akhir baris ditemukan.jumlah baris maksimum dalam excel ada di link ini mas

http://en.wikipedia.org/wiki/Microsoft_Excel

setealh itu bisa pake for untuk memasukna data sampai baris terakhir.

kalo sudah ketemu akhir baris, maka buat sheet baru dan masuukan data lagi sampai baris terakhir.

membuat sheet baru bisa dilihat di link ini mas..

http://www.mrexcel.com/td0097.html

semoga membantu

nyanya mengatakan...

Dear Mas Ben,

terimakasih untuk informasinya mas, saya akn coba ikuti instruksi mas yaah, kalo masih ada yang perlu saya tanyakan. boleh tanya ke mas lagi yaaah....

salam
Yaman

Ben mengatakan...

oke mas...sama-sama