Penyusun : Achmad Affandi ( )

training___________________________________________________________

Programming in Microsoft

Excel 2000

Penyusun : Achmad Affandi ( 0044-30-0902 )

Programming in Microsoft Excel 2000

Production Engineering Department PT. Semarang Autocomp

Manufacturing

Indonesia Semarang 2005 Daftar Isi Modul 1 : Formula dan Function............................................................................................3 Formula (rumus).........................................................................................................3 Penyusunan Formula di dalam Excel........................................................................3 Operator perhitungan............................................................................................4 Urutan proses perhitungan.....................................................................................4 Array Formula......................................................................................................5 Cell/Range References..................................................................................................6 Kenapa menggunakan pengalamatan yang tidak relative...........................................7 Notasi R1C1.........................................................................................................7 Pengalamatan Worksheet atau Workbook lain...........................................................8 Nama Sebagai Referensi........................................................................................8 Manfaat Lain dari Sistem Penamaan Cell/Range........................................................9 Formula Errors..........................................................................................................10 Function (fungsi).......................................................................................................12 Fungsi di dalam Fungsi........................................................................................12 Lebih lanjut dengan fungsi IF................................................................................13 Menggunakan fungsi COUNTIF dan SUMIF..............................................................16 Menggunakan Array Formula untuk COUNT dan SUM...............................................17 Beberapa Fungsi Matematika dalam Excel.....................................................................18 Beberapa Fungsi Statistik dalam Excel..........................................................................22 Beberapa Fungsi Tanggal dan Waktu dalam Excel...........................................................25 Beberapa Fungsi Logika dalam Excel............................................................................27 Beberapa Fungsi Teks dalam Excel...............................................................................28 Beberapa Fungsi LookUp dan Alamat dalam Excel..........................................................32 Beberapa Fungsi Information dalam Excel.....................................................................36 MegaFormula............................................................................................................41 Modul 2 : Pengenalan VBA..................................................................................................44 Sekilas Pemrograman VBA..........................................................................................44 Visual Basic Editor...............................................................................................46 Membuat kode VBA.............................................................................................47 Memasukkan kode dengan cara biasa....................................................................48

Production Engineering Department

2

Programming in Microsoft Excel 2000 Memasukkan kode dengan Macro Recorder.............................................................49 Relative atau Absolute.........................................................................................51 Membersihkan hasil perekaman macro...................................................................53 Objek dan Koleksi...............................................................................................54 Mengetahui Objek yang Aktif................................................................................55 Bekerja dengan objek Range.................................................................................56 Properti Range.............................................................................................57 Properti Cells.............................................................................................57 Properti Offset...........................................................................................58 Pemrograman Dasar VBA............................................................................................59 Bahasa VBA : Sekilas Pandang..............................................................................59 Komentar...........................................................................................................60 Variabel, Jenis Data, dan Konstanta.......................................................................61 Menentukan Jenis Data.................................................................................62 Mendeklarasikan Variabel..............................................................................63 Menentukan Jenis Data.................................................................................63 Memaksa Pendeklarasian Semua Variabel........................................................64 Lingkup Suatu Variabel........................................................................................64 Konstanta..........................................................................................................66 Jenis Data String................................................................................................66 Jenis Data Date..................................................................................................66 Operator Perhitungan, Logika, dan Perbandingan....................................................67 Array.................................................................................................................67 Variabel Objek....................................................................................................69 Built-In Function.................................................................................................69 Struktur Dasar Pemrograman......................................................................................71 With-End With...................................................................................................71 For-Next...........................................................................................................71 Do-While...........................................................................................................74 Do-Until...........................................................................................................74 If-Then.............................................................................................................76 Select-Case......................................................................................................77 Bekerja Dengan Prosedur............................................................................................78 Mendeklarasikan Prosedur....................................................................................78 Menjalankan Prosedur.........................................................................................79 Bekerja Dengan Fungsi...............................................................................................84 Mendeklarasikan Fungsi.......................................................................................85 Menjalankan Fungsi.............................................................................................86 Argumen/Parameter...................................................................................................87 By Value atau By Reference..................................................................................88 Contoh Prosedur dan Fungsi........................................................................................88 Teknik Penanganan Kesalahan.....................................................................................91 ‘Menangkap’ Error...............................................................................................91 On Error Resume Next................................................................................91 On Error Goto error_label........................................................................92 Manfaat ‘Menangkap’ Kesalahan.....................................................................93


3

Programming in Microsoft Excel 2000 Debugging.........................................................................................................93

4



Modul 1 : Formula dan Function Formula (rumus) Sebagian pengguna Excel menganggap bahwa Formula dan Function adalah sama atau sejenis. Padahal formula (rumus) adalah satu atau beberapa rumus yang dibuat oleh pengguna, sedangkan function (fungsi) adalah suatu rumus tunggal yang sudah dibuat oleh Excel dan memiliki fungsi dan maksud tertentu. Suatu formula yang kita buat dapat terdiri dari beberapa fungsi, tetapi sangat tidak mungkin di dalam fungsi yang sudah ada kita sisipkan formula yang kita buat (karena kita tidak bisa membuka isi dari fungsi tersebut.) Sebuah formula dapat berupa perhitungan matematik, membandingkan nilai antar worksheet, atau menggabungkan teks. Formula juga dapat berisi referensi ke cell lain yang : berada dalam worksheet yang sama; berada dalam worksheet yang berbeda tetapi masih dalam satu workbook; atau dalam workbook yang berbeda. Contoh berikut ini akan menambahkan nilai 25 dan nilai cell B4 kemudian membagi hasilnya dengan jumlah (Sum) nilai D5, E5, dan F5. Alamat cell Nilai konstan Fungsi (function)

=(B4+25)/SUM(D5:F5) Alamat range operator bagi operator tambah

CATATAN … Formula yang kita buat akan segera dihitung oleh Excel setelah kita selesai memasukkannya. Ini akan terjadi jika Calculation Mode (lihat Tools → Options) diset Automatic. Jika formula tersebut berhubungan dengan formula cell lain, maka formula lain tersebut dihitung terlebih dahulu oleh Excel. Terkadang kita memiliki worksheet dengan ratusan atau ribuan formula yang kompleks dimana proses perhitungan akan memakan waktu yang lama. Jika ini terjadi, kita sebaiknya menset Calculation Mode menjadi Manual. Ketika dalam mode ini, Excel akan menampilkan teks Calculate di status bar, jika dalam worksheet kita terdapat formula yang belum diproses oleh Excel. Kita dapat memerintah Excel untuk melakukan proses perhitungan dengan menekan : F9, untuk menghitung formula di seluruh workbook yang terbuka. Shift-F9, untuk menghitung formula di worksheet yang aktif saja. Ctrl-Shift-F9, untuk memaksa Excel memproses semua perhitungan yang ada. Cara ini tidak disarankan, gunakan hanya jika Excel tidak melakukan proses perhitungan secara benar. Ketika kita mengubah Calculation Mode, seluruh workbook yang dibuka akan terpengaruh.

Penyusunan Formula di dalam Excel Formula di dalam Excel selalu dimulai dengan tanda = (sama dengan). Tanda = ini akan memberi tahu bahwa karakter-karakter berikutnya adalah suatu rumus di mana terdiri dari operand (elemen yang akan dihitung) yang dipisahkan oleh operator (jenis perhitungan). Excel akan melakukan perhitungan mulai dari kiri ke kanan, berdasarkan tingkatan masing-masing operator di dalam rumus. Kita bisa mengubah urutan perhitungan dengan menggunakan tanda


5

Programming in Microsoft Excel 2000 kurung. Pada contoh sebelumnya, tanda kurung akan memaksa Excel untuk menghitung B4+25 terlebih dahulu lalu kemudian membaginya dengan hasil penjumlahan nilai D5, E5, dan F5.

Operator perhitungan Operator akan menandakan jenis perhitungan apa yang akan dilakukan terhadap operand. Excel memiliki jenis 4 operator, yaitu : Arithmetic (operator untuk perhitungan biasa); Comparison (operator untuk perbandingan); Text concatenation (operator untuk menggabungkan dua buah teks); dan Reference (operator untuk mengkombinasikan alamat cell/range). Arithmetic

operators

digunakan

untuk

melakukan

perhitungan

matematik

seperti

penjumlahan, pengurangan, perkalian, atau untuk menghasilkan hasil berupa nilai. Operator

Maksud

Contoh

+

Penjumlahan

3+3

–

Pengurangan Tanda Negatif

3–1 –1

*

Perkalian

3*3

/

Pembagian

3/3

%

Persentase

20%

^

Pangkat

3^2 (sama dengan 3*3)

Comparison operators digunakan untuk memperbandingkan dua buah nilai yang hasil perbandingannya akan berupa nilai TRUE (Benar) atau FALSE (Salah). Operator

Maksud

6

Contoh

=

sama dengan

A1=B1

>

lebih besar dari

A1>B1

<

lebih kecil dari

A1
>=

lebih besar atau sama dengan dari

A1>=B1

<=

lebih kecil atau sama dengan dari

A1<=B1

<>

tidak sama dengan

A1<>B1

Text concatenation operators digunakan untuk menggabungkan beberapa teks menjadi satu. Operator &

Maksud

Contoh

Menggabungkan dua teks menjadi satu

"North" & "wind" menghasilkan "Northwind"

Reference operators digunakan untuk menggabung alamat beberapa cell/range menjadi satu. Operator

Maksud

Contoh

:

Operator range, yang menandakan satu alamat untuk seluruh cell yang terdapat di antara dua alamat cell, termasuk kedua cell tersebut

B5:B15

,

Operator penggabungan banyak range menjadi satu range

SUM(B5:B15,D5:D15)

Urutan proses perhitungan Jika kita menggabungkan beberapa operator dalam satu formula, Excel akan melakukan perhitungan sesuai dengan urutan yang ada pada tabel di bawah. Jika dalam suatu formula terdapat beberapa operator yang memiliki tingkatan yang sama – sebagai contoh misalkan formula mengandung operator perkalian dan pembagian – Excel akan menghitung mulai dari kiri


Programming in Microsoft Excel 2000 sampai ke kanan. Untuk mengubah urutan perhitungan bagian tertentu, tutuplah bagian yang ingin dihitung terlebih dahulu dengan menggunakan tanda kurung.

Operator

Maksud

Level

: (spasi) ,

operator reference (alamat cell/range)

–

tanda negatif

%

persentase

^

pangkat

* dan /

perkalian dan pembagian

+ dan –

penjumlahan dan pengurangan

&

penggabungan teks

= < > <= >= <>

perbandingan

↑ +           ↓ -

Array Formula Array Formula adalah fasilitas dari Excel yang memungkinkan kita untuk mengisi satu rumus langsung ke dalam satu atau lebih range di mana Excel akan menjaga rumus tersebut untuk selalu sama. Array Formula memungkinkan kita untuk melakukan beberapa perhitungan dan mengembalikan satu atau beberapa hasil. Kita bisa membuat array formula dengan mengisikan formula biasa, tetapi kita harus menekan Ctrl+Shift+Enter ketika selesai memasukkan formula. Menghitung formula untuk menghasilkan satu nilai. Terkadang Excel harus melakukan beberapa perhitungan untuk menghasilkan satu nilai. Sebagai contoh, worksheet di bawah ini menunjukkan satu perusahaan yang memiliki kantor penjualan di Eropa dan Amerika yang masing-masing kantor memiliki 3 divisi produk. Untuk mencari rata-rata pendapatan kantor Eropa pada tahun 1992, kita membutuhkan sebuah array formula. B 3 4 5 6 7 8 9 10 11 12 13 14 15 16

C

D 1992

Elektronik Eropa Amerika Total Elektronik Akustik Eropa Amerika Total Akustik Perlengkapan Studio Eropa Amerika Total Perlengkapan Studio =AVERAGE(IF(C5:C14=“Eropa”,D5:D14))

E 1997

100.600 133.100 233.700

161.000 198.200 359.200

129.200 150.500 279.700

160.700 190.100 350.800

89.900 112.300 202.200

153.900 190.700 344.600

Cell C16 berisi array formula =AVERAGE(IF(C5:C14=“Eropa”,D5:D14)), yang akan mencari seluruh cell pada range C5:C14 yang berisi teks “Eropa” dan menghitung rata-rata pada D5:D14. Menghitung formula untuk menghasilkan beberapa nilai. Untuk menghasilkan beberapa nilai dengan menggunakan array formula, kita harus memilih range di mana nilai-nilai akan ditaruh. Contoh berikut, kita akan menghitung 10 % total penjualan per bulan secara langsung. B

C

D

E


7

Programming in Microsoft Excel 2000 3 4 5 6

10 %

Juni 7234 =C4:E4*0.1

Juli 1200

Agustus 525

Beberapa keuntungan dari array formula adalah sebagai berikut : menggunakan memori yang lebih sedikit; pekerjaan kita menjadi lebih efisien; dan dapat menghilangkan kebutuhan akan formula perantara (intermediate formulas). Sedangkan kerugian dari array formula adalah sebagai berikut : dapat memperlambat kinerja kalkulasi worksheet kita; worksheet kita sulit dimengerti oleh orang lain; dan harus mengingat untuk menekan Ctrl+Shift+Enter setelah selesai mengisi formula.

CATATAN … Yang perlu diingat adalah sebelum kita menuliskan formula di atas, pastikan kita sudah memilih range C5:E5 terlebih dahulu. Dan setelah memasukkan formulanya, pastikan untuk menekan tombol Ctrl+Shift+Enter untuk mengakhirinya. Excel secara otomatis akan mengubah formulanya menjadi {=C4:E4*0.1} sebagai tanda bahwa formula yang bersangkutan adalah array formula. Jika kita mencoba untuk mengubah formula pada salah satu cell, maka Excel akan menampilkan pesan kesalahan bahwa kita tidak bisa mengubah isi salah satu array (You cannot change part of an array). Untuk mengubah formula tersebut, kita harus terlebih dahulu memilih range array formula yang bersangkutan, yaitu C5:E5, kemudian mengubahnya. Dan ingat, setelah mengubah formulanya, tekan Ctrl+Shift+Enter untuk mengakhirinya. Disinilah

kelebihan suatu array formula, bahwa

perubahannya tidak bisa per bagian, melainkan harus semua bagian. Jika kita menekan Enter ketika selesai mengetik formula pada cell/range, maka formulanya hanya akan diisi pada cell yang aktif saja. Dan satu hal yang penting lagi adalah Excel secara otomatis akan menambahkan tanda kurung kurawal ({}) sebagai penanda bahwa formula yang ada adalah array formula. Dan kita jangan sekali-kali menambahkan tanda kurung kurawal secara manual.

LATIHAN Buatlah dua buah array 1 x 5 dan isikan sembarang nilai, lalu kalikan kedua buah array tersebut dengan menggunakan array formula ! Buatlah daftar sepuluh hewan di kolom A, lalu tampilkan jumlah huruf masing-masing hewan di kolom B dengan menggunakan array formula ! Setelah itu tampilkan jumlah huruf keseluruhan hewan dengan satu array formula tanpa perlu bantuan panjang huruf masingmasing hewan !

Cell/Range References Sebagian besar formula yang kita buat terdiri dari alamat cell/range (cell/range references). Pengalamatan ini memiliki 4 jenis, yaitu : Relative. Pengalamatan ini sepenuhnya relatif. Ketika suatu formula dicopy, alamat cell di dalam formula akan berubah sesuai dengan alamat barunya. Contoh : A1. Absolute. Pengalamatan ini sepenuhnya absolut. Ketika suatu formula dicopy, alamat cell di dalam formula tidak akan berubah. Contoh : $A$1. Row Absolute. Pengalamatan ini absolut sebagian. Ketika suatu formula dicopy, alamat cell di dalam formula akan berubah bagian kolomnya saja. Contoh : A$1.


8

Programming in Microsoft Excel 2000 Column Absolute. Pengalamatan ini absolut sebagian. Ketika suatu formula dicopy, alamat cell di dalam formula akan berubah bagian barisnya saja. Contoh : $A1. Secara default, alamat cell/range adalah relative. Untuk mengubah menjadi yang lain, kita harus secara manual menambahkan tanda dolar ($) ke dalam alamatnya.

Kenapa menggunakan pengalamatan yang tidak relative Kalau kita pikir-pikir, satu-satunya alasan kita untuk mengubah jenis pengalamatan adalah ketika kita berencana untuk mengcopy formula tersebut ke cell/range lain. Perhatikan contoh di bawah ini dimana cell C4 berisi formula =C$3*$B4.

Lebar

A 1 2 3 4 5 6 7 8 9

B

C

3 4 5 6 7

12 36 48 60 72 84

D Panjang 14 42 56 70 84 98

E

F

16 48 64 80 96 112

18 54 72 90 108 126

G

Formula di atas digunakan untuk menghitung luas untuk lebar (terdapat pada kolom B) dan panjang (terdapat pada baris 3). Setelah kita mengisi formula di C4, kita bise mengcopy hingga C8 dan F8. Karena formula tersebut menggunakan alamat absolut untuk baris 3 dan kolom B dan alamat relatif untuk kolom dan baris lain, maka setiap formula yang dicopy akan menghasilkan alamat yang benar. Jika formula di C4 hanya menggunakan alamat relatif, maka ketika dicopy alamat formula cell tujuannya akan berubah sesuai dengan alamat barunya – dan akan menghasilkan nilai yang salah.

Notasi R1C1 Selain pengalamatan normal dengan menggunakan notasi A1 (terdiri dari huruf kolom dan angka baris), kita juga bisa menggunakan notasi R1C1. Pada sistem ini, cell A1 sama dengan cell R1C1, dan A2 sama dengan R1C2, dan begitu seterusnya. Untuk mengubah setting menjadi notasi R1C1, akses opsi Tools → Options… → General → R1C1 reference style. Tabel di bawah ini memberikan contoh formula dengan notasi standar dan notasi R1C1 dimana formula yang dimasukkan berada di cell B2 (atau cell R1C2).

Formula Sederhana Dengan Menggunakan Dua Notasi Standar =A1+1 =$A$1+1 =$A1+1 =A$1+1 =SUM(A1:A10) =SUM($A$1:$A$10)

R1C1 =RC[-1]+1 =R1C1+1 =RC1+1 =R1C[-1]+1 =SUM(RC[-1]:R[9]C[-1]) =SUM(R1C1:R10C1)

Angka di dalam kurung siku merupakan alamat relatif terhadap cell yang aktif. Misalnya, R[5]C[-3] menandakan alamat cell 5 baris ke atas dan tiga kolom ke kiri. Sedangkan, R[5]C[3] menandakan alamat cell 5 baris ke bawah dan tiga kolom ke kanan.


9


CATATAN … Jika kita merasa bahwa kita kesulitan menggunakan notasi R1C1, maka kita tidak sendirian. Notasi R1C1 tidak terlalu memusingkan untuk pengalamatan absolut, tetapi lain halnya jika kita berbicara mengenai pengalamatan relatif dimana kita harus menggunakan tanda kurung siku. Biasanya notasi R1C1 digunakan untuk memeriksa hasil suatu formula ketika kita meng-copy-nya. Coba dan latihlah !

Pengalamatan Worksheet atau Workbook lain Untuk mereferensikan alamat cell/range di worksheet lain, kita bisa menggunakan tanda seru (!) sebagai pemisah antara worksheet dan cell/range, misalnya : =Sheet1!A1+1 Kita juga bisa mereferensikan alamat cell/range yang ada di workbook lain dengan menggunakan tanda kurung siku ([]) sebagai pemisah antara workbook dan worksheet, misalnya : =[Budget.xls]Sheet1!A1+1 Jika terdapat spasi pada referensi nama workbooknya, maka kita harus menggunakan tanda kutip tunggal (‘ ’) untuk mengapit nama workbook dan worksheetnya, misalnya : =‘[Budget for 2001]Sheet1’!A1+A1 Tetapi jika workbook tersebut tidak dalam keadaan terbuka, maka kita harus menambahkan path lengkapnya, misalnya : =‘C:\MSOffice\Excel\[Budget for 2001]Sheet1’!A1+A1

10 CATATAN … Jika kita memiliki workbook yang rusak (corrupted) dan tidak bisa membukanya, kita bisa membuat formula di workbook baru kita yang berisi referensi alamat ke workbook yang rusak tersebut dan mendapatkan semua nilai yang ada (tetapi bukan formulanya). Kita dapat melakukan ini karena file yang rusak tersebut tidak perlu dibuka. Kita hanya perlu, misalnya, membuat formula di cell A1 worksheet baru seperti =[Badfile.xls]Sheet1!A1 lalu mengcopynya ke cell lain sebanyak yang kita ingin. Jika data yang ada di workbook yang rusak sampai cell AA123, maka copylah formula tersebut hingga cell AA123.

Nama Sebagai Referensi Salah satu fasilitas Excel yang dinilai sangat berguna adalah kemampuan untuk mengganti alamat cell/range (bisa juga komponen lain seperti chart) dengan nama tertentu yang memiliki arti bagi kita. Kita bisa memberi nama cell/range tertentu dengan mengakses menu Insert → Name → Define (atau dengan menekan Ctrl+F3) lalu memasukkan namanya dan alamat cell/range yang ditunjuk oleh nama tersebut. Cara yang lebih cepat untuk memberi nama

cell/range adalah dengan mengubah di kotak isian Name box

yang

terletak di samping kiri formula bar). Kita juga bisa mengakses menu Insert → Name → Create untuk menciptakan nama secara otomatis jika kita memiliki range yang memiliki judul baris atau kolom. Contohnya bisa dilihat pada worksheet dan gambar 1.1 di bawah ini. A 1 2 3

North South

B Qtr1 311 193

C Qtr2 213 486

D Qtr3 112 398

E Qtr4 446 509

F


Programming in Microsoft Excel 2000 4 5

West East

594 753

698 811

677 911

231 710

Gambar 1.1 Kotak dialog Create Names

Setelah itu kita akan mendapatkan bahwa range B2:B5 akan memiliki nama Qtr1, B2:E2 akan memiliki nama North, dan seterusnya. Dan jika kita memasukkan formula ke dalam sebuah cell, =Qtr2 South Excel secara otomatis akan menghasilkan nilai 486. Fasilitas ini disediakan oleh Excel dengan menggunakan operator spasi, yang diberi nama intersection operator. Kita juga bisa mendapatkan total untuk West dengan menggunakan formula =SUM(West)

11

CATATAN … Sistem penamaan ini sangat berguna terutama saat nanti kita mempergunakan kode program VBA yang menggunakan alamat cell atau range. Kenapa ? Karena kode program VBA tidak secara otomatis memperbaharui alamat cell/range jika terjadi pergeseran atau pemindahan cell/range yang bersangkutan. Misalnya, jika kode VBA kita menggunakan alamat Range(“C4”), maka kode kita akan salah jika kita menyisipkan baris atau kolom baru di atas C4. Dengan menggunakan nama alamat seperti Range(“InterestRate”), hal tersebut akan bisa kita hindari.

Excel 2000 sebenarnya sudah menambahkan fasilitas baru yang bisa mengubah formula secara otomatis jika kita menyisipkan baris atau kolom baru. Sebagai contoh, misalkan kita mempunyai formula di cell A5 sebagai berikut : =SUM(A1:A4) Lalu kita sisipkan baris baru di atas baris 5, maka formulanya akan secara otomatis menjadi : =SUM(A1:A5)

Manfaat Lain dari Sistem Penamaan Cell/Range Selain manfaat-manfaat yang dimiliki di atas, sistem penamaan yang disediakan oleh Excel juga bisa digunakan sebagai fasilitas variabel dan konstanta. Nama digunakan sebagai konstanta. Kita bisa membuat satu nama untuk menampilkan nilai tertentu. Bisa dilihat pada gambar 1.2, nama LST memiliki referensi ke nilai =“Laporan Stock Barang Tahun 2001”. Dan jika pada salah satu cell, kita memasukkan formula =LST, maka cell tersebut akan menampilkan judul secara penuh. Nama digunakan sebagai variabel. Kita bisa membuat satu nama untuk menampilkan nilai berdasarkan perhitungan terhadap cell-cell tertentu. Bisa dilihat pada gambar 1.3, nama


Programming in Microsoft Excel 2000 PANGKAT memiliki perhitungan =A1^B1 (nilai cell A1 dipangkatkan dengan nilai cell B1). Dan jika pada salah satu cell, kita memasukkan formula =PANGKAT, maka cell tersebut akan menampilkan hasil formula pangkat tersebut.

Gambar 1.2 Nama digunakan sebagai konstanta. Menggunakan nilai secara eksplisit.

Gambar 1.3 Nama digunakan sebagai variabel. Menggunakan nilai dari cell/range tertentu atau variabel yang lain.

Formula Errors Terkadang kita mendapatkan nilai kesalahan (error value) jika formula yang kita buat memiliki kesalahan dalam perhitungan, parameter/argumen fungsi, atau referensi cell/range. Jika formula yang kita buat memiliki referensi ke cell/range yang memiliki error value, maka formula tersebut juga akan memiliki error value. Hal ini disebut gejala riak (ripple effect) – di mana satu cell yang memiliki error value dapat menyebabkan banyak cell yang menggunakan alamat cell sebelumnya mengalami error value juga. Di bawah ini akan dijelaskan beberapa error value yang mungkin muncul dalam suatu formula.


12

Programming in Microsoft Excel 2000 Error Value dalam Excel Error Value #####

#DIV/0!

#N/A

#NAME?

#NULL!

#NUM!

#REF! #VALUE!

Penjelasan Error ini muncul jika cell yang bersangkutan berisi nilai yang lebarnya melebihi lebar cell atau berisi formula tanggal/waktu yang menghasilkan bilangan negatif. Untuk mengatasi ini, kita bisa : menambah lebar kolom; mengubah number format menjadi yang lain; atau mengubah formula tanggal/waktu agar benar. Terdapat pembagian dengan nol (suatu operasi yang tidak diperbolehkan di planet ini) dalam formula yang bersangkutan. Misalnya =A1/0. Error ini juga muncul jika formula mencoba membagi dengan cell yang kosong. Untuk mengatasi masalah ini kita bisa : jika nilai 0 digunakan secara eksplisit sebagai bilangan pembagi, ubahlah menjadi nilai selain 0, misalnya dari formula =A1/0 menjadi =A1/2; atau jika formula yang ada mencoba membagi dengan cell yang kosong atau berisi nilai 0 (misalnya B5), ubahlah formula yang bersangkutan misalnya menjadi seperti =IF(B5=0,"",A5/B5). Formula yang bersangkutan memiliki referensi (secara langsung maupun tidak langsung) ke cell yang memiliki nilai N/A. N/A ini menandakan bahwa data tidak tersedia (not available). Ini biasanya terjadi ketika kita menggunakan fungsi LOOKUP dan terdapat kondisi : cell berisi nilai yang tidak terdapat di tabel LOOKUP; atau tabel LOOKUP yang ada tidak dalam keadaan terurut. Formula menggunakan nama atau pengenal (identifier) yang tidak dikenal oleh Excel. Ini dapat terjadi jika kita : menghapus sebuah nama yang masih digunakan dalam formula; salah mengetik nama atau fungsi yang ada; lupa menggunakan tanda titik dua (:) pada alamat range; atau teks yang ada di dalam formula tidak diapit oleh kutip. Formula yang bersangkutan menggunakan intersection operator (operator spasi – baca “Nama sebagai referensi” untuk keterangan lebih lanjut) terhadap dua range tapi tidak saling bersinggungan satu sama lain. Ini juga mungkin terjadi jika kita lupa menambahkan tanda koma (,) sebagai pemisah range. Untuk mengatasi masalah ini kita bisa : memeriksa ulang dua range yang menggunakan intersection operator, misalnya terdapat formula =A1:A5 B1:E1 dimana tidak terdapat cell yang merupakan persinggungan. Kita bisa mengubahnya menjadi =A1:A5 A1:E1; atau memastikan bahwa tanda koma digunakan sebagai pemisah dua range, misalnya kita ingin mentotal nilai yang ada di range A1:A5 dan range C1:C5 maka formula yang benar harus berbentuk =SUM(A1:A5,C1:C5) dan bukannya =SUM(A1:A5 C1:C5). Error ini muncul jika terjadi kesalahan menggunakan bilangan dalam formula atau fungsi yang ada. Ini biasanya muncul jika : mengirimkan parameter/argumen yang jenisnya bukan bilangan untuk parameter/argumen yang membutuhkan bilangan; atau membuat formula yang menghasilkan bilangan yang terlalu besar atau terlalu kecil yang Excel bisa tampilkan. Untuk mengatasi masalah ini pastikan bahwa hasil dari formula yang ada harus berkisar antara –1*10307 sampai 1*10307. Formula yang ada menggunakan referensi alamat cell/range yang tidak benar. Ini biasanya terjadi jika kita menghapus cell/range yang masih digunakan oleh formula lain. Error ini akan muncul jika kita mengirimkan parameter atau operand yang jenisnya berbeda dengan yang dibutuhkan. Ini biasanya terjadi jika : kita memasukkan data teks sedangkan yang dibutuhkan adalah data bilangan atau data logika. Misalnya : cell A5 berisi 15 dan cell A6 berisi “Contoh”, kemudian kita mencoba membuat formula di cell A7 menjadi =A5+A6; atau kita mengirimkan alamat range sedangkan yang dibutuhkan adalah data tunggal atau satu alamat cell.

CATATAN … Kita bisa menggunakan fungsi ISERROR untuk mendeteksi terjadinya salah satu dari error-error di atas.


13


Function (fungsi) Function (fungsi) adalah sederetan atau sekumpulan formula yang sudah disediakan oleh Excel untuk melakukan operasi tertentu dengan menggunakan nilai yang disebut argument. Sebagai contoh, fungsi SUM akan menjumlahkan nilai seluruh cell pada range. Argument (argumen) dapat berupa bilangan, teks, nilai logika (TRUE atau FALSE), array, atau alamat cell/range. Argumen yang kita berikan jenisnya harus sesuai dengan ketentuan yang terdapat pada fungsi yang bersangkutan. Argumen dapat berupa nilai konstan, formula, atau fungsi lain. Tanda sama dengan Nama fungsi Argumen

=SUM(A10,B5:B10,50,37) Tanda koma yang memisahkan masing-masing argumen Tanda kurung

Fungsi di dalam Fungsi Terkadang kita dihadapkan pada kondisi di mana kita harus memasukkan satu fungsi sebagai argumen fungsi yang lain. Dan ini menyebabkan kita harus menulis fungsi di dalam fungsi – yang biasanya disebut nested function (fungsi bersarang/bertingkat). Sebagai contoh, formula di bawah menggunakan fungsi AVERAGE dan membandingkan hasilnya dengan 50.

14

nested function

=IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0) Sebuah nested function harus menghasilkan jenis nilai yang sama dengan yang dibutuhkan oleh argumen tersebut. Misalnya, suatu argumen membutuhkan nilai logika (TRUE atau FALSE), maka nested function tersebut harus menghasilkan nilai logika. Jika tidak, maka Excel akan menghasilkan kesalahan #VALUE! error.

CATATAN … Sebuah formula dapat terdiri hingga tujuh tingkat nested function. Contohnya bisa dilihat di atas, fungsi AVERAGE dan SUM keduanya adalah fungsi level kedua karena mereka merupakan argumen bagi fungsi IF. Jika terdapat fungsi lain di dalam fungsi AVERAGE, maka fungsi tersebut merupakan fungsi level ketiga.

Kita juga bisa menggunakan Formula Palette (fasilitas yang disediakan Excel untuk menyunting formula. Fasilitas ini berada di bawah formula bar ketika kita klik tombol Edit Formula

klik tombol Paste Function

atau

) dalam membuat nested function ini. Sebagai contoh bisa

dilihat pada gambar 1.4 di bawah dalam penyusunan formula sebelumnya untuk fungsi SUM.


Programming in Microsoft Excel 2000 Gambar 1.4 Contoh penggunaan Formula Palette

Untuk berpindah antar fungsi di dalam Formula Palette, klik nama fungsi dalam formula bar. Sebagai contoh, untuk mengubah range argumen fungsi AVERAGE di gambar 1.4, klik AVERAGE di formula bar.

15

Lebih lanjut dengan fungsi IF Seperti kita ketahui bahwa fungsi IF digunakan untuk menseleksi kondisi dan menghasilkan nilai tertentu berdasarkan nilai TRUE (benar) atau FALSE (salah). Syntax (bentuk umum) dari fungsi IF adalah sebagai berikut : =IF(kondisi,nilai_jika_benar,nilai_jika_salah) Kondisi adalah satu atau lebih persamaan yang menghasilkan nilai TRUE atau FALSE. Contohnya, A10 = 100 adalah kondisi; jika cell A10 berisi nilai 100 maka kondisi akan menghasilkan nilai TRUE; sebaliknya menghasilkan nilai FALSE. Kondisi disini bisa menggunakan operator perbandingan (comparison operators) yang sudah dibicarakan pada bagian “Operator Perhitungan” sebelumnya. Nilai_jika_benar adalah nilai yang akan dihasilkan jika kondisinya menghasilkan nilai TRUE. Contohnya, jika parameter/argumen ini diisi dengan teks “Within Budget” dan kondisi menghasilkan nilai TRUE, maka fungsi IF ini akan menghasilkan teks “Within Budget”. Nilai_jika_benar juga bisa diisi dengan fungsi lain. Nilai_jika_salah adalah nilai yang akan dihasilkan jika kondisinya menghasilkan nilai FALSE. Contohnya,

jika

parameter/argumen

ini

diisi

dengan

teks

“Over

Budget”

dan

kondisi

menghasilkan nilai FALSE, maka fungsi IF ini akan menghasilkan teks “Over Budget”. Nilai_jika_salah juga bisa diisi dengan fungsi lain.



CATATAN … Sebenarnya Excel menyediakan fungsi tambahan yang bisa digunakan untuk menghitung data berdasarkan kondisi tertentu. Misalnya, untuk menghitung total nilai berdasarkan teks tertentu, kita bisa menggunakan fungsi SUMIF. Fungsi SUMIF (dan juga COUNTIF) ini akan dibahas pada bagian selanjutnya. Kita dapat menyusun fungsi IF lagi di dalam nilai_jika_benar atau nilai_jika_salah (nested function) hingga 7 level.

LATIHAN Jika cell A10 berisi dosis obat dalam cc, buatlah fungsi IF di cell lain untuk menseleksi apakah dosis tersebut “Dosis Normal” (dosis <= 1000 cc) atau “Over Dosis” (dosis > 1000 cc) ! Buatlah fungsi IF untuk menseleksi nilai cell A1. Jika A1 berisi 100, maka tampilkan total nilai yang ada di B5:B10, sebaliknya jangan tampilkan apa-apa ! Misalkan kita mempunyai beberapa orang sales untuk menjual produk kita yang seminggu sekali melaporkan hasil penjualannya. Kita sebagai pemilik perusahaan telah menentukan pembagian bonus terhadap hasil penjualan mereka per minggu. Jika lebih besar dari $ 1,000, maka bonusnya adalah 10 % dari penjualan tersebut, sebaliknya bonusnya adalah 7.5 % dari penjualan tersebut. Untuk menghitung secara otomatis, kita harus membuat tabel hasil penjualan satu bulan untuk (andaikan saja) 3 orang sales dengan kolom-kolom : Name Sales, Penjualan, dan Bonus yang diterima (dalam $). Buatlah tabel tersebut dengan fungsi IF-nya ! Buatlah fungsi IF untuk menyatakan nilai huruf berdasarkan nilai bilangan tertentu dengan ketentuan sebagai berikut : Jika Nilai Tampilkan Huruf > 89 A 80 – 89 B 70 – 79 C 60 – 69 D < 60 E Kita bisa menggunakan nested function IF untuk menyelesaikan masalah ini. Pada fungsi IF sebelumnya kita hanya menggunakan kondisi tunggal sebagai seleksinya, misalkan A10=100. Padahal sering kali dalam kehidupan nyata, kita dihadapkan pada lebih dari satu kondisi, misalkan : “Saya boleh masuk bioskop jika umur saya lebih dari 17 tahun dan mempunyai karcis” atau “saya mau minum jika ada air jeruk atau ada air teh”. Bisa dilihat pada paragraf sebelumnya, kalimat “jika ada air jeruk atau ada air teh” terdiri dari dua kondisi yaitu “ada air jeruk” dan “ada air teh” dengan penghubung kondisi “atau”. Pada dasarnya untuk menghubungkan dua buah kondisi digunakan operator AND (Dan) dan OR (Atau) dengansatu tambahan operator NOT (Bukan). Berikut adalah tabel logika operator tersebut yang dikenal dengan nama logika boolean. Kondisi A FALSE FALSE TRUE TRUE

Kondisi B FALSE TRUE FALSE TRUE

A AND B FALSE FALSE FALSE TRUE

A OR B FALSE TRUE TRUE TRUE

NOT A TRUE TRUE FALSE FALSE

Operator AND hanya akan menghasilkan nilai TRUE jika keseluruh logika adalah TRUE, sedangkan operator OR hanya akan menghasilkan nilai FALSE jika keseluruh logika adalah FALSE. Operator NOT digunakan untuk membalik kondisi.


16


Excel sudah menyediakan fungsi tersendiri sebagai pengganti operator logika Boolean ini, yaitu : Fungsi AND(kondisi1, kondisi2, …) yang bisa berisi hingga 30 kondisi. Parameter yang diberikan harus menghasilkan nilai TRUE atau FALSE atau bisa juga berupa array atau alamat range yang berisi nilai TRUE atau FALSE. Fungsi OR(kondisi1, kondisi2, …) yang bisa berisi hingga 30 kondisi. Parameter yang diberikan harus menghasilkan nilai TRUE atau FALSE atau bisa juga berupa array atau alamat range yang berisi nilai TRUE atau FALSE. Fungsi NOT(kondisi). Parameter yang diberikan harus menghasilkan TRUE atau FALSE. Untuk lebih jelasnya, perhatikan contoh berikut : AND(TRUE,TRUE) menghasilkan TRUE AND(2+2=4,2+3=6) menghasilkan FALSE Jika A1:A3 berisi nilai TRUE,FALSE, dan TRUE, maka OR(A1:A3) menghasilkan TRUE NOT(1+1=2) menghasilkan FALSE Excel sebenarnya juga menyediakan operator logika Boolean (walaupun tidak terdokumentasi di dalam Excel Reference Guide) seperti berikut : + menandakan operator OR. Kita bisa memisahkan kondisi dengan tanda + ini. * menandakan operator AND. Kita bisa memisahkan kondisi dengan tanda * ini. Untuk lebih jelasnya, bandingkanlah kedua contoh yang memiliki tujuan yang sama, berikut ini : =IF(AND(A1<10,B1<10), “OK”, “NG”) =IF((A1<10)*(B1<10), “OK”, “NG”)

17

CATATAN … Jika menggunakan operator logika Boolean * dan +, pastikan untuk menutup masing-masing logika dengan tanda kurung.

LATIHAN Jika cell A1 bisa diisikan sembarang nilai, buatlah fungsi di B1 untuk memeriksa apakah isi cell A1 berada “dalam batas” (100 <= nilai <= 1000) atau “luar batas” !

Jika kita memiliki tabel sebagai berikut : A

B

C

D

1 Nama Barang Harga Jenis Discount 2 Celana CutBrai 75000 A 3 Lem Tikus 20000 C Kolom Jenis bisa berisi A, B, C, D, dan E. Isilah kolom Discount dengan ketentuan sebagai berikut : Jika Jenis A atau B dan harga < 50000, maka Discount = 5% * Harga Jika Jenis A atau B dan harga >= 50000, maka Discount = 10% * Harga Jika Jenis C, D, atau E dan harga < 50000, maka Discount = 10% * Harga Jika Jenis C, D, atau E dan harga >= 50000, maka Discount = 15% * Harga

Menggunakan fungsi COUNTIF dan SUMIF


Programming in Microsoft Excel 2000 Formula di dalam Excel, biasanya, akan menghitung seluruh nilai yang terdapat pada range yang diberikan. Tetapi, terkadang kita menginginkan Excel untuk menghitung nilai yang memenuhi kondisi tertentu saja. Sebagai contoh, misalkan kita memiliki dua rumah kontrakan, yang setiap bulannya memiliki pengeluaran yang berbeda-beda. Sebagai pemilik rumah kontrakan yang baik kita harus menghitung total pengeluaran listrik dan air per tahun untuk masing-masing rumah agar kita bisa menentukan tarif kontrak rumah untuk bulan depan. Seandainya kita menyusun dalam bentuk tabel, mungkin bentuknya akan sebagai berikut : A 1 2 3 4 5 6 7

B

Rumah Kontrakan Jl. H. Eman Gg. Pemuda Jl. H. Eman Jl. H. Eman Gg. Pemuda Gg. Pemuda

Pengeluaran 150000 195000 125000 75000 100000 175000

Kita bisa membuat total pengeluaran untuk rumah “Jl. H. Eman” dengan menggunakan fungsi SUMIF (yang memiliki syntax =SUMIF(range,criteria,sum_range)) sebagai berikut : =SUMIF(A2:A7,“Jl. H. Eman”,B2:B7) Atau menghitung berapa kali pengeluaran untuk rumah “Gg. Pemuda” dengan menggunakan fungsi COUNTIF (yang memiliki syntax =COUNTIF(range,criteria)) sebagai berikut :

18

=COUNTIF(A2:A7,“Gg. Pemuda”) Sehingga kita bisa menyusunnya menjadi seperti bentuk tabel sebagai berikut :

11 12 13

A

B

C

Rumah Kontrakan Jl. H. Eman Gg. Pemuda

Total Pengeluaran =SUMIF(A2:A7,A12,B2:B7) =SUMIF(A2:A7,A13,B2:B7)

Berapa Kali Pengeluaran =COUNTIF(A2:A7,A12) =COUNTIF(A2:A7,A13)

CATATAN … Criteria bisa diisi dengan sembarang teks atau bilangan, misalnya 32, “32”, “>32”, atau “apel”.

Berikut adalah contoh lain dari penggunaan fungsi COUNTIF dan SUMIF beserta penjelasannya. Yang perlu diingat adalah formula di bawah mengasumsikan bahwa kita sudah memiliki range dengan nama data dan range yang akan dijumlah dengan nama jumlah.

Contoh Penggunaan COUNTIF dan SUMIF Formula =COUNTIF(data,12) =SUMIF(data,“<>0”,jumlah) =COUNTIF(data,“yes”) =SUMIF(data,“*”,jumlah) =COUNTIF(data,“???”)

Nilai yang dikembalikan Jumlah cell yang berisi nilai 12 Total nilai yang tidak sama dengan nol. Jumlah cell yang berisi teks yes (tidak case-sensitive) Total nilai yang berisi teks apa saja Jumlah cell yang berisi teks berjumlah 3 huruf

LATIHAN Buatlah fungsi untuk menghitung jumlah cell yang berisi nilai 1 atau 12 !


Programming in Microsoft Excel 2000 Buatlah fungsi untuk menghitung total nilai yang isi cellnya lebih besar dari 0 ! Buatlah fungsi untuk menghitung jumlah cell yang berisi nilai antara 1 sampai 10 ! Buatlah fungsi untuk menghitung total nilai yang isi cellnya terdapat satu huruf “s” !

Menggunakan Array Formula untuk COUNT dan SUM Jika metode perhitungan standard tidak bisa diterapkan pada suatu kasus tertentu, kita bisa menggunakan metode Array Formula. Dan jangan lupa untuk menekan Ctrl+Shift+Enter setelah selesai memasukkan formulanya. Berikut akan diberikan contoh-contoh penggunaan Array Formula pada kasus-kasus yang unik. Untuk menghitung jumlah cell yang berisi bilangan (tidak akan menghitung teks dan cell kosong), gunakan formula berikut : =SUM(IF(ISNUMBER(A2:A10),1,0)) Untuk menghitung jumlah cell yang memiliki error value, gunakan formula berikut : =SUM(IF(ISERR(A2:A10),1,0))

Contoh lain jika kita memiliki tabel sebagai berikut : A 1 Bulan 2 Jan 3 Jan 4 Jan 5 Feb 6 Feb 7 Feb 8 Mar 9 Mar 10 Mar 11 maka kita bisa membuat formula-formula

B

C

Daerah Utara Selatan Barat Utara Selatan Barat Utara Selatan Barat

Penjualan 100 200 300 150 250 350 200 300 400

19

berikut :

Array Formula menggunakan fungsi SUM Formula =SUM((A2:A10=“Jan”)*(B2:B10=“Utara”)*C2:C10)

Nilai yang dikembalikan Total penjualan untuk bulan “Jan” di

=SUM((A2:A10=“Jan”)*(B2:B10<>“Utara”)*C2:C10

daerah “Utara” Total penjualan untuk bulan “Jan” di

) =SUM((A2:A10=“Jan”)*(B2:B10=“Utara”))

daerah selain “Utara” Jumlah penjualan untuk bulan “Jan”

=SUM((A2:A10=“Jan”)*(C2:C10>=200)*(C2:C10))

di daerah “Utara” Total penjualan bulan “Jan” untuk

=SUM((C2:C10>=300)*(C2:C10<=400)*(C2:C10))

yang penjualannya >= 200 Total penjualan untuk

=SUM((C2:C10>=300)*(C2:C10<=400))

penjualannya antara 300 dan 400 Jumlah penjualan untuk yang

yang

penjualannya antara 300 dan 400

Beberapa Fungsi Matematika dalam Excel Berikut adalah fungsi matematika dalam Excel yang sering digunakan dalam pekerjaan :



ABS Penjelasan : Nilai absolut dari suatu bilangan. Syntax

: ABS(bilangan)

Contoh

:

=ABS(2) menghasilkan 2 =ABS(-2) menghasilkan 2 Jika A1 berisi nilai –16, maka =SQRT(ABS(A1)) menghasilkan 4

CEILING Penjelasan : Pembulatan ke atas suatu nilai berdasarkan besaran tertentu, menjauhi 0. Syntax

: CEILING(bilangan,besaran_pembulatan)

Contoh

:

=CEILING(2.5,1) menghasilkan 3 =CEILING(-2.5,-2) menghasilkan –4 =CEILING(-2.5,2) menghasilkan #NUM! =CEILING(0.234,0.01) menghasilkan 0.24

EVEN Penjelasan : Pembulatan ke atas suatu nilai menjadi bilangan genap terdekat, menjauhi 0. Syntax

: EVEN(bilangan)

Contoh

:

=EVEN(1.5) menghasilkan 2 =EVEN(-1) menghasilkan –2 =EVEN(3) menghasilkan 4 =EVEN(2) menghasilkan 2

FLOOR Penjelasan : Pembulatan ke bawah suatu nilai berdasarkan besaran tertentu, mendekati 0. Syntax

: FLOOR(bilangan,besaran_pembulatan)

Contoh

:

=FLOOR(2.5,1) menghasilkan 2 =FLOOR(-2.5,-2) menghasilkan –2 =FLOOR(-2.5,2) menghasilkan #NUM! =FLOOR(0.234,0.01) menghasilkan 0.23

INT Penjelasan : Pembulatan ke bawah suatu nilai menjadi bilangan bulat terdekat. Syntax

: INT(bilangan)

Contoh

:

=INT(8.9) menghasilkan 8 =INT(-8.9) menghasilkan –9 Formula ini bisa digunakan untuk mendapatkan nilai desimal suatu bilangan positif yang terdaat di cell A1 : =A1-INT(A1)

MOD Penjelasan : Hasil sisa bagi suatu pembagian. Fungsi MOD ini juga bisa diekspresikan dengan menggunakan fungsi INT : MOD(n,d) = n – d*INT(n/d). Syntax

: MOD(bilangan,pembagi)

Contoh

:

=MOD(11,3) menghasilkan 2 =MOD(-7,2) menghasilkan 1


20

Programming in Microsoft Excel 2000 =MOD(3,-2) menghasilkan –1 =MOD(-6,-4) menghasilkan –2 ODD Penjelasan : Pembulatan ke atas suatu nilai menjadi bilangan ganjil terdekat, menjauhi 0. Syntax

: ODD(bilangan)

Contoh

:

=ODD(1.5) menghasilkan 3 =ODD(3) menghasilkan 3 =ODD(-1) menghasilkan –1 =ODD(-2) menghasilkan –3

POWER Penjelasan : Pemangkatan suatu bilangan. Operator “^” sebenarnya bisa digunakan untuk menggantikan fungsi ini, seperti 5^2. Syntax

: POWER(bilangan,pangkat)

Contoh

:

=POWER(5,2) menghasilkan 25 =POWER(98.6,3.2) menghasilkan 2401077 =POWER(4,5/4) menghasilkan 5.656854

PRODUCT Penjelasan : Mengalikan semua bilangan yang diberikan. Syntax

: PRODUCT(bilangan1,bilangan2,…)

Contoh

:

Jika cell A2:C2 berisi nilai 5, 15, dan 30, maka : =PRODUCT(A2:C2) menghasilkan 2250 =PRODUCT(A2:C2,2) menghasilkan 4500

ROUND Penjelasan : Membulatkan suatu bilangan pada posisi digit yang kita tentukan. Syntax

: ROUND(bilangan,posisi_digit)

Jika posisi_digit > 0, bilangan akan dibulatkan pada bagian desimalnya. Jika posisi_digit = 0, bilangan akan dibulatkan pada bilangan bulat terdekat. Jika posisi_digit < 0, bilangan akan dibulatkan pada bagian kiri titik desimal. Contoh

:

=ROUND(2.15,1) menghasilkan 2.2 =ROUND(2.149,1) menghasilkan 2.1 =ROUND(-1.475,2) menghasilkan –1.48 =ROUND(21.5,-1) menghasilkan 20 =ROUND(-5.93,0) menghasilkan –6 =ROUND(1575,-2) menghasilkan 1600

ROUNDDOWN Penjelasan : Membulatkan suatu bilangan ke bawah pada posisi digit yang kita tentukan. Syntax

: ROUNDDOWN(bilangan,posisi_digit)


:

=ROUNDDOWN(2.15,1) menghasilkan 2.1 =ROUNDDOWN(2.149,1) menghasilkan 2.1


21

Programming in Microsoft Excel 2000 =ROUNDDOWN(-1.475,2) menghasilkan –1.47 =ROUNDDOWN(21.5,-1) menghasilkan 20 =ROUNDDOWN(-5.93,0) menghasilkan –5 =ROUNDDOWN(1575,-2) menghasilkan 1500 ROUNDUP Penjelasan : Membulatkan suatu bilangan ke atas pada posisi digit yang kita tentukan. Syntax

: ROUNDUP(bilangan,posisi_digit)


:

=ROUNDUP(2.15,1) menghasilkan 2.2 =ROUNDUP(2.149,1) menghasilkan 2.2 =ROUNDUP(-1.475,2) menghasilkan –1.48 =ROUNDUP(21.5,-1) menghasilkan 30 =ROUNDUP(-5.93,0) menghasilkan –6 =ROUNDUP(1575,-2) menghasilkan 1600

SQRT Penjelasan : Mendapatkan akar pangkat dari nilai tertentu. Syntax

: SQRT(bilangan)

Contoh

:

=SQRT(16) menghasilkan 4 =SQRT(-16) menghasilkan #NUM! =SQRT(ABS(-16)) menghasilkan 4

SUBTOTAL Penjelasan : Menghitung nilai subtotal. Sebenarnya untuk menghitung SUBTOTAL, lebih mudah melalui menu Data → Subtotals…. Perbedaan fungsi SUBTOTAL dari fungsi yang lain (seperti SUM) adalah fungsi ini tidak akan menghitung cell yang berisi fungsi SUBTOTAL lain, sehingga tidak akan terjadi perhitungan dua kali. Syntax

: SUBTOTAL(no_fungsi,ref1,ref2,…)

ref1, ref2, adalah alamat range (bisa sampai 29 range) di mana SUBTOTAL akan dihitung. no_fungsi adalah bilangan antara 1 sampai 11 yang masing-masing memiliki jenis perhitungan sebagai berikut : no_fungsi 1 2 3 4 5 6 7 8 9 10 11 Contoh

:

jenis perhitungan AVERAGE COUNT COUNTA MAX MIN PRODUCT STDDEV STDDEVP SUM VAR VARP

=SUBTOTAL(9,C3:C5) menghasilkan nilai subtotal yang ada di range C3:C5


22

Programming in Microsoft Excel 2000 dengan menggunakan jenis perhitungan SUM SUM Penjelasan : Menjumlahkan seluruh bilangan yang ada di parameter/argumen. Syntax

: SUM(bilangan1, bilangan2,…)

Contoh

:

=SUM(3,2) menghasilkan 5 =SUM(“3”,2,TRUE) menghasilkan 6, karena teks akan diterjemahkan terlebih dahulu menjadi bilangan dan TRUE diterjemahkan menjadi angka 1 Jika A1:B1 berisi “3”, TRUE, maka =SUM(A1,B1,2) menghasilkan 2, karena nilai yang terdapat di dalam cell tidak akan diterjemahkan menjadi bilangan Jika cell A2:E2 berisi 5, 15, 30, 40, dan 50, maka : =SUM(A2:C2) menghasilkan 50 =SUM(B2:E2, 15) menghasilkan 150

SUMIF Baca bagian “Menggunakan fungsi COUNTIF dan SUMIF” untuk keterangan lebih lanjut. TRUNC Penjelasan : Menghilangkan bagian desimal suatu bilangan. TRUNC dan INT kurang lebih sama, jika TRUNC menghilangkan bagian desimal bilangan, maka INT membulatkan beilangan ke bawah. TRUNC dan INT berbeda ketika menggunakan bilangan negatif:

=TRUNC(-4.3)

akan

menghasilkan

–4,

tetapi

=INT(-4.3)

akan

menghasilkan bilangan –5. Syntax

: TRUNC(bilangan,jumlah_desimal)

Contoh

:

=TRUNC(8.963824) menghasilkan 8 =TRUNC(8.963824,3) menghasilkan 8.963 =TRUNC(-8.9,2) menghasilkan –8.9

Beberapa Fungsi Statistik dalam Excel Berikut adalah fungsi statistik dalam Excel yang sering digunakan dalam pekerjaan : AVERAGE Penjelasan : Mendapatkan nilai rata-rata untuk seluruh nilai yang diberikan. Syntax

: AVERAGE(bilangan1, bilangan2,…)

Contoh

:

Jika range A1:A5 diberi nama Nilai dan berisi nilai 10, 7, 9, 27, dan 2, maka : =AVERAGE(A1:A5) menghasilkan 11 =AVERAGE(Nilai) menghasilkan 11 =AVERAGE(A1:A5, 5) menghasilkan 10

=AVERAGE(A1:A5) sebenarnya sama dengan formula =SUM(A1:A5)/COUNT(A1:A5) COUNT Penjelasan : Menghitung jumlah cell atau parameter/argumen yang berisi bilangan. Parameter/ argumen yang berupa bilangan, tanggal, maupun teks yang merupakan bilangan akan dihitung; selain itu tidak akan dihitung.


23

Programming in Microsoft Excel 2000 Syntax

: COUNT(nilai1, nilai2,…)

Contoh

:

Jika A1:A7 berisi “Sales”, 12/8/90, , 19, 22.24, TRUE, dan #DIV/0! maka : =COUNT(A1:A7) menghasilkan 3 =COUNT(A4:A7) menghasilkan 2 =COUNT(A1:A7, 2) menghasilkan 4

COUNTA Penjelasan : Menghitung jumlah cell atau parameter/argumen yang tidak kosong. Syntax

: COUNTA(nilai1, nilai2,…)

Contoh

:

Jika A1:A7 berisi “Sales”, 12/8/90, , 19, 22.24, TRUE, dan #DIV/0! maka : =COUNTA(A1:A7) menghasilkan 6 =COUNTA(A4:A7) menghasilkan 4 =COUNTA(A1:A7,“Data”) menghasilkan 7

COUNTBLANK Penjelasan : Menghitung jumlah cell atau parameter/argumen yang kosong. Syntax

: COUNTBLANK(range)

Contoh

:

Jika A1:A7 berisi “Sales”, 12/8/90, , 19, 22.24, TRUE, dan #DIV/0! maka : =COUNTA(A1:A7) menghasilkan 1

COUNTIF Baca bagian “Menggunakan fungsi COUNTIF dan SUMIF” untuk keterangan lebih lanjut. FORECAST Penjelasan : Menghitung, atau memperkirakan, nilai yang belum diketahui berdasarkan nilainilai yang sudah diketahui. Nilai yang sudah diketahui terdiri dari nilai-x dan nilai-y (sama seperti chart) sedangkan fungsi akan menghasilkan nilai-y yang belum diketahui untuk nilai-x yang dimasukkan. Syntax

: FORECAST(x_dicari, y_diketahui, x_diketahui)

Contoh

:

Misalnya kita sebagai orang yang bertanggung jawab dalam menyusun jadwal produksi suatu perusahaan tempe MENDOWANTM. Perusahaan kita memiliki mesin tempe otomatis SETENGAHMATENG®

sebanyak 5 buah. Kita

kemudian mengumpulkan data jumlah tempe yang dihasilkan per jam (dalam ribuan) untuk jumlah mesin yang berbeda sebagai berikut : 1 2 3 4 5 6 7

A

B

Jumlah Mesin 1 2 3 4 5

Jumlah Tempe per Hari (x 1000) 15 27 41 60 95

C

Karena negara sedang mengadakan TEMPEISASI, maka perusahaan menerima pesanan sebanyak 200 ribu tempe / hari. Untuk memenuhi pesanan tersebut, perusahaan akan menambahkan jumlah mesin yang ada menjadi 10 buah. Tetapi kita tidak yakin apa 10 mesin dapat menghasilkan 200 ribu tempe, karena itu kita dapat mencoba untuk memperkirakannya dengan memasukkan formula di dalam Excel pada cell C4 sebagai berikut :


24

Programming in Microsoft Excel 2000 =FORECAST(10,B2:B6,A2:A6) Apakah hasilnya benar ? Berapakah jumlah mesin minimal untuk menghasilkan minimal 200 ribu tempe / hari ? FREQUENCY Penjelasan : Menghitung kemunculan data dengan batasan tertentu pada daftar data yang kita berikan dan mengembalikan kumpulan nilai berupa array vertikal. Karena fungsi FREQUENCY ini menghasilkan nilai berupa array, maka dalam memasukkan formula ini harus menekan Ctrl+Shift+Enter. Syntax

: FREQUENCY(array_sumber_data,array_batasan_data)

Contoh

:

Misalnya kita sebagai dosen mempunyai data nilai mahasiswa yang kita masukkan di range A1:A10 (array_sumber_data) sebagai berikut 79, 54, 87, 49, 66, 91, 72, 83, 95, dan 65. Kemudian kita ingin mengelompokan nilai-nilai tersebut berdasarkan batasan 0-65, 66-75, 76-85, dan 86-100. Maka kita masukkan di range B1:B3 (array_batasan_data) sebagai berikut 65, 75, dan 85 (yang terakhir tidak perlu dimasukkan). Lalu kita harus membuat array formula di

range

C1:C4

dan

memasukkan

formula

=FREQUENCY(A1:A10,B1:B3).

Hasilnya kurang lebih akan seperti berikut ini : A

B

C

1 79 65 2 54 75 3 87 85 4 49 5 66 6 91 7 72 8 83 9 95 10 65 11 Walaupun penggunaan fungsi FREQUENCY ini lumayan sulit,

3 2 2 3

25

fungsinya akan sangat terasa

membantu sekali saat kita berhubungan dengan banyak data. GROWTH Penjelasan : Fungsi ini sangat menarik untuk digunakan. Ia dapat menghitung “nilai pertumbuhan” dengan menggunakan data yang sudah diketahui. Fungsi ini akan menghasilkan deretan nilai-y untuk deretan nilai-x yang kita masukkan dengan bantuan nilai-x dan nilai-y yang sudah diketahui. Karena fungsi GROWTH ini menghasilkan nilai berupa array, maka dalam memasukkan formula ini harus menekan Ctrl+Shift+Enter. Syntax

: GROWTH(y_diketahui, x_diketahui, x_dicari)

Contoh

:

Kita bicara mengenai perusahaan tempe MENDOWANTM

kembali. Setelah

sukses dalam tahun-tahun terakhir, terkadang perusahaan masih tetap kewalahan dalam menangani pesanan yang terus meningkat per bulannya. Untuk itulah, kita—yang sekarang sudah diangkat menjadi Marketing Director—dituntut untuk bisa membaca

peluang

pasar.

Kita

kemudian

menyusun

data

penjualan tempe 6 bulan ke belakang (bulan 11 – 16) sebagai


Programming in Microsoft Excel 2000 berikut : A

B

C

1 Bulan Jumlah Tempe Terjual (x 1000) 2 11 331 3 12 473 4 13 690 5 14 1020 6 15 1500 7 16 2200 8 17 9 18 Lalu untuk mengetahui peluang pasar 2 bulan berikutnya (bulan 17 – 18), kita bisa memasukkan array formula di B8:B9 seperti berikut : =GROWTH(B2:B7,A2:A7,A8:A9) Dan kita mendapatkan bahwa peluang penjualan di bulan ke-17 adalah 3201.967 (ribu) tempe dan di bulan ke-18 adalah 4685.361 (ribu) tempe. MAX Penjelasan : Mencari bilangan terbesar di dalam sekumpulan data yang diberikan. Syntax

: MAX(bilangan1,bilangan2,…)

Contoh

:

Jika A1:A5 berisi 10, 7, 9, 27, dan 2 maka : =MAX(A1:A5) menghasilkan 27 =MAX(A1:A5, 30) menghasilkan 30

MEDIAN Penjelasan : Mencari median (bilangan tengah) di dalam sekumpulan data yang diberikan. Syntax

: MEDIAN(bilangan1,bilangan2,…)

Contoh

:

=MEDIAN(2,4,1,3,5) menghasilkan 3 =MEDIAN(2,4,1,3,5,6) menghasilkan 3.5 → rata-rata dari 3 dan 4

MIN Penjelasan : Mencari bilangan terkecil di dalam sekumpulan data yang diberikan. Syntax

: MIN(bilangan1,bilangan2,…)

Contoh

:

Jika A1:A5 berisi 10, 7, 9, 27, dan 2 maka : =MIN(A1:A5) menghasilkan 2

MODE Penjelasan : Mencari modus (bilangan yang paling sering muncul) di dalam sekumpulan data yang diberikan. Syntax

: MODE(bilangan1,bilangan2,…)

Contoh

:

=MODE({5.6,4,4,3,2,4}) menghasilkan 4

PERMUT Penjelasan : Menghitung banyaknya permutasi (banyaknya kombinasi bilangan). Syntax

: PERMUT(jumlah_bilangan,jumlah_dipilih)

Contoh

:

Dengan rumus ini kita bisa mengetahui banyaknya kendaraan bermotor yang memiliki nomor polisi B xxxx QQ. Kita mengetahui bahwa nomor normal kendaraan bermotor adalah 4 angka dengan kombinasi angka 0 sampai 9 (sebanyak 10 angka). Maka kita bisa memasukkan formula =PERMUT(10,4) dan hasilnya adalah 5040 kendaraan motor.


26

Programming in Microsoft Excel 2000 QUARTILE Penjelasan : Mencari kuartir (nilai paruh) tertentu dari sekumpulan data yang diberikan. Kuartir diperlukan misalnya untuk menentukan 25 % pemasukkan tertinggi. Syntax

: QUARTILE(data,no_kuartir)

Contoh

no_kuartir Nilai yang dihasilkan 0 nilai paling kecil (sama dengan fungsi MIN) 1 kuartir pertama - 25 % 2 kuartir kedua - 50 % (sama dengan fungsi MEDIAN) 3 kuartir ketiga - 75 % 4 nilai paling besar (sama dengan fungsi MAX) : =QUARTILE({1,2,4,7,8,9,10,12}, 1) menghasilkan 3.5

Beberapa Fungsi Tanggal dan Waktu dalam Excel Berikut adalah fungsi tanggal dan waktu dalam Excel yang sering digunakan dalam pekerjaan : DATE Penjelasan : Menghasilkan tanggal berdasarkan data yang kita berikan. Syntax

: DATE(tahun,bulan,angka_hari)

Contoh

:

=DATE(2001,10,1) menghasilkan “10/1/2001”

DAY Penjelasan : Mendapatkan angka hari dari tanggal yang diberikan. Nilai yang dihasilkan akan berkisar antara 1 sampai 31. Syntax

: DAY(tanggal)

Contoh

:

=DAY(“4-Jan”) menghasilkan 4 =DAY(“8/11/1998”) menghasilkan 11

HOUR Penjelasan : Mendapatkan jam dari waktu yang diberikan. Nilai yang dikembalikan berkisar antara 0 (12:00 AM) sampai 23 (11:00 PM). Syntax

: HOUR(waktu) waktu disini bisa berisi teks yang menyatakan waktu; bilangan desimal (misalnya, 12 PM dinyatakan sebagai 0.5, karena dianggap setengah hari); atau hasil dari fungsi lain.

Contoh

:

=HOUR(0.7) menghasilkan 16 =HOUR(“3:30:30 PM”) menghasilkan 15

MINUTE Penjelasan : Mendapatkan menit dari waktu yang diberikan. Nilai yang dikembalikan berkisar antara 0 sampai 59. Syntax

: MINUTE(waktu)

Contoh

:

=MINUTE(“4:48:00 PM”) menghasilkan 48 =MINUTE(0.01) menghasilkan 14

MONTH Penjelasan : Mendapatkan angka bulan dari tanggal yang diberikan. Nilai yang dihasilkan akan


27

Programming in Microsoft Excel 2000 berkisar antara 1 (January) sampai 12 (December). Syntax

: MONTH(tanggal)

Contoh

:

=MONTH(“6-May”) menghasilkan 5 =MONTH(“2004/04/01”) menghasilkan 4

NOW Penjelasan : Menghasilkan tanggal dan waktu sekarang. Syntax

: NOW()

SECOND Penjelasan : Mendapatkan detik dari waktu yang diberikan. Nilai yang dikembalikan berkisar antara 0 sampai 59. Syntax

: SECOND(waktu)

Contoh

:

=SECOND(“4:48:18 PM”) menghasilkan 18 =SECOND(0.01) menghasilkan 24

TIME Penjelasan : Menghasilkan waktu berdasarkan data yang kita berikan. Syntax

: DATE(jam,menit,detik)

Contoh

:

=TIME(16,48,10) menghasilkan “4:48:10 PM”

TODAY Penjelasan : Menghasilkan tanggal sekarang. Syntax

: TODAY()

WEEKDAY Penjelasan : Menghasilkan urutan hari dalam satu minggu dari tanggal yang kita berikan. Syntax

Contoh

: WEEKDAY(tanggal,jenis)

:

jenis Nilai yang dihasilkan 1 (default) angka 1 (Minggu) sampai 7 (Sabtu) 2 angka 1 (Senin) sampai 7 (Minggu) 3 angka 0 (Senin) sampai 6 (Minggu) =WEEKDAY(“2/14/1998”) menghasilkan 7 (Sabtu)

YEAR Penjelasan : Mendapatkan angka tahun dari tanggal yang diberikan. Nilai yang dihasilkan akan berkisar antara 1900 sampai 9999. Syntax

: YEAR(tanggal)

Contoh

:

=YEAR(“7/5/1998”) menghasilkan 1998

Beberapa Fungsi Logika dalam Excel Berikut adalah fungsi logika dalam Excel yang sering digunakan dalam pekerjaan : AND Penjelasan : Menghasilkan TRUE jika semua parameter/argumen bernilai TRUE. Untuk lebih jelasnya lihat bagian “Lebih lanjut dengan fungsi IF”.


28


: AND(kondisi1,kondisi2,…)

Contoh

:

lihat bagian “Lebih lanjut dengan fungsi IF”

FALSE Penjelasan : Menghasilkan nilai logika FALSE. Syntax

: FALSE()

IF Penjelasan

: Menghasilkan suatu nilai jika kondisinya TRUE dan nilai lainnya jika kondisinya

Syntax

: =IF(kondisi,nilai_jika_benar,nilai_jika_salah)

Contoh

:

FALSE. Untuk lebih jelasnya lihat bagian “Lebih lanjut dengan fungsi IF”. lihat bagian “Lebih lanjut dengan fungsi IF”

NOT Penjelasan : Membalik logika. Untuk lebih jelasnya lihat bagian “Lebih lanjut dengan fungsi IF”. Syntax

: NOT(kondisi)

Contoh

:

OR Penjelasan

: Menghasilkan FALSE jika semua parameter/argumen bernilai FALSE. Untuk lebih

Syntax

: OR(kondisi1,kondisi2,…)

Contoh

:

lihat bagian “Lebih lanjut dengan fungsi IF”

jelasnya lihat bagian “Lebih lanjut dengan fungsi IF”. lihat bagian “Lebih lanjut dengan fungsi IF”

29 TRUE Penjelasan : Menghasilkan nilai logika TRUE. Syntax

: TRUE()

Beberapa Fungsi Teks dalam Excel Berikut adalah fungsi teks dalam Excel yang sering digunakan dalam pekerjaan : CONCATENATE Penjelasan : Menggabungkan beberapa teks menjadi satu. Sebenarnya fungsi ini bisa digantikan oleh operator “&”. Syntax

: CONCATENATE(teks1,teks2,…)

Contoh

:

Jika range A11:C11 berisi “spesies”, “ikan bandeng”, dan 32, maka : =CONCATENATE(“Populasi untuk ”,A11,“ ”,B11,“ adalah ”,C11,“/mil”) akan menghasilkan “Populasi untuk spesies ikan bandeng adalah 32/mil.

DOLLAR Penjelasan : Mengubah bilangan menjadi teks dengan menggunakan format currency, dengan pembulatan bilangan desimal sebanyak yang kita tentukan. Format yang digunakan sebenarnya adalah $#,##0.00_);($#,##0.00). Syntax

: DOLLAR(bilangan,desimal)

Contoh

:

=DOLLAR(1234.567, 2) menghasilkan “$1,234.57”


Programming in Microsoft Excel 2000 =DOLLAR(1234.567, -2) menghasilkan “$1,200” =DOLLAR(-1234.567, -2) menghasilkan “($1,200)” =DOLLAR(-0.123, 4) menghasilkan “($0.1230)” EXACT Penjelasan : Membandingkan dua teks dan menghasilkan nilai TRUE jika kedua teks benarbenar

sama,

sebaliknya

FALSE.

EXACT

adalah

case-sensitive

tetapi

menghiraukan pemformatan yang ada. Syntax

: EXACT(teks1,teks2)

Contoh

:

=EXACT(“excel”,“excel”) menghasilkan TRUE =EXACT(“Excel”,“excel”) menghasilkan FALSE =EXACT(“e xcel”, “excel”) menghasilkan FALSE

Jika kita membuat worksheet untuk orang lain dan kita ingin memastikan agar orang tersebut memasukkan data dengan benar, kita bisa buat array formula berikut untuk memeriksanya : {=AND(EXACT(nilai_panduan,range_yang_dibandingkan) Misalnya saja kita memiliki range tempat orang memasukkan datanya pada B2:D4, dan pada cell A1 terdapat kata kunci di mana orang harus memasukkan data sesuai dengan isi cell tersebut, maka kita bisa membuat array formula di A3 sebagai berikut : =AND(EXACT(A1,B2:D4))

Untuk lebih jelasnya perhatikan worksheet di bawah ini. A

B

C

D

1 kg 2 kg kg 3 =AND(EXACT(A1,B2:D4)) kg kg 4 kg kg Jika kita mengisi salah satu cell dengan isi selain ‘kg’, maka cell A3

kg kg kg akan bernilai FALSE.

Sebaliknya jika range B2:D4 berisi ‘kg’ semua, maka A3 akan bernilai TRUE. FIND Penjelasan : Mencari satu teks di dalam teks yang lainnya dan menghasilkan posisi karakter pertamanya. Kita juga bisa menggunakan fungsi SEARCH untuk mencari teks tertentu, tetapi tidak seperti SEARCH, fungsi FIND adalah case-sensitive dan tidak mengizinkan adanya karakter wildcard. Jika teks yang dicari tidak terdapat dalam teks yang lainnya, maka Excel akan memberikan nilai kesalahan #VALUE!. Syntax

: FIND(teks_yang_dicari,teks_tempat_mencari,posisi_mulai)

Contoh

:

=FIND(“c”,“Microsoft Excel”) menghasilkan 3 =FIND(“c”,“Microsoft Excel”,5) menghasilkan 13 =FIND(“Ex”,“Microsoft Excel”) menghasilkan 11

Misalkan kita mempunyai daftar komponen dan nomor serinya, dan kita ingin memisahkan nama komponen tersebut dari nomor serinya pada setiap cell. Andaikan saja range A2:A4 berisi “Ceramic Insulators #124-TD45-87”, “Copper Coils #12-671-6772”, dan “Variable Resistors #116010”. Maka kita bisa membuat formula =MID(A2,1,FIND(" #",A2)-1) di cell B2 misalnya lalu meng-copy-nya ke cell B3 dan B4. Kita juga bisa membuat array formula di range B2:B4 seperti {=MID(A6:A8,1,FIND(" #",A6:A8)-1)}. FIXED Penjelasan :

Membulatkan

suatu

bilangan

pada

posisi

digit

yang

mengembalikan nilainya sebagai teks. Syntax

: FIXED(bilangan,posisi_digit)


kita

tentukan

dan

30

Programming in Microsoft Excel 2000 Contoh

:

=FIXED(1234.567,1) menghasilkan “1234.6” =FIXED(-1234.567,-1) menghasilkan “-1230” =FIXED(44.332) menghasilkan “44.33”

LEFT Penjelasan : Mengambil beberapa karakter pertama (dari kiri) dalam suatu teks. Syntax

: LEFT(teks,jumlah_karakter)

Contoh

:

=LEFT(“Sale Price”,4) menghasilkan “Sale”

Beberapa program atau aplikasi terkadang menghasilkan bilangan negatif dengan tanda negatif (-) di sebelah kanan nilai. Ini akan menjadi masalah ketika kita melakukan import file dari aplikasi tersebut ke dalam Excel. Berikut adalah program untuk mengubah nilai negatif, misalnya “156-”, yang terdapat di cell A2 agar menjadi nilai –156 : =LEFT(A2,LEN(A2)-1)*-1 LEN Penjelasan : Menghitung jumlah karakter yang terdapat di dalam (atau panjangnya) teks. Syntax

: LEN(teks)

Contoh

:

=LEN(“Phoenix Pinion”) menghasilkan 14 =LEN(“”) menghasilkan 0

LOWER Penjelasan : Mengganti semua huruf yang terdapat di dalam teks menjadi huruf kecil. Syntax

: LOWER(teks)

Contoh

:

=LOWER(“PT. Gajah Tunggal”) menghasilkan “pt. gajah tunggal”

MID Penjelasan : Mengambil beberapa karakter dari sebuah teks. Syntax

: MID(teks,posisi_mulai,banyaknya_karakter)

Contoh

:

=MID(“PT. Gajah Tunggal”,5,5) menghasilkan “Gajah” =MID(“ABC”,2,5) menghasilkan “BC” =MID(“ABC”,4,2) menghasilkan “” (teks kosong) =MID(“ABC”,0,2) menghasilkan #VALUE!

PROPER Penjelasan : Meng-hurufbesar-kan huruf pertama pada masing-masing kata, dan menghurufkecil-kan sisanya. Syntax

: PROPER(teks)

Contoh

:

=PROPER(“ini adaLah JUDUL”) menghasilkan “Ini Adalah Judul” =PROPER(“2-orang anak”) menghasilkan “2-Orang Anak” =PROPER(“76BudGet”) menghasilkan “76Budget”

REPLACE Penjelasan : Akan mengganti bagian pada posisi tertentu dalam suatu teks dengan bagian lain. Syntax

: REPLACE(teks,posisi_diganti,jumlah_kar_diganti,teks_pengganti)

Contoh

:

=REPLACE(“abcdefghijk”,6,5,“*”) menghasilkan “abcde*k” =REPLACE(“abcdefghijk”,6,1,”12345”) menghasilkan “abcde12345ghijk” =REPLACE(“abcdefghijk”,6,3,””) menghasilkan “abcdeijk”


31

Programming in Microsoft Excel 2000 REPT Penjelasan : Mengulang teks sebanyak yang kita tentukan. Syntax

: REPT(teks,banyaknya_pengulangan)

Contoh

:

=REPT(“+-”,3) menghasilkan “+-+-+-” =REPT(“Sales”,2.9) menghasilkan “SalesSales”

RIGHT Penjelasan : Mengambil beberapa karakter terakhir (dari kanan) dalam suatu teks. Syntax

: RIGHT(teks,jumlah_karakter)

Contoh

:

=RIGHT(“Sale Price”,3) menghasilkan “ice”

SEARCH Penjelasan : Mencari satu teks di dalam teks yang lainnya dan menghasilkan posisi karakter pertamanya. Fungsi ini mengizinkan adanya karakter wildcard yaitu ? untuk mewakilkan satu karakter bebas dan * untuk mewakilkan beberapa karakter bebas. Fungsi ini tidak case-sensitive jadi tidak akan membedakan antara huruf besar dan huruf kecil. Jika teks yang dicari tidak terdapat dalam teks yang lainnya, maka Excel akan memberikan nilai kesalahan #VALUE!. Syntax

: SEARCH(teks_yang_dicari,teks_tempat_mencari,posisi_mulai)

Contoh

:

=SEARCH(“C”,“Microsoft Excel”) menghasilkan 3 =SEARCH(“??c”,“Microsoft Excel”,5) menghasilkan 11 =SEARCH(“*c”,“Microsoft Excel”,5) menghasilkan 5

SUBSTITUTE Penjelasan : Akan mengganti bagian tertentu dalam suatu teks dengan bagian lain. Gunakan SUBSTITUTE jika kita ingin mengganti suatu teks dalam teks tertentu. Dan gunakan REPLACE jika kita ingin mengganti suatu teks pada posisi tertentu dalam teks tertentu. Syntax

: SUBSTITUTE(teks,teks_lama,teks_baru,urutan_yang_diganti)

Contoh

:

=SUBSTITUTE(“Sales Data”,“Sales”,“Cost”) menghasilkan “Cost Data” =SUBSTITUTE(“1 Januari 1991”,“1”,“2”) menghasilkan “2 Januari 2992” =SUBSTITUTE(“1 Januari 1991”,“1”,“2”,1) menghasilkan “2 Januari 1991”

TEXT Penjelasan : Mengubah nilai menjadi teks yang memiliki format tertentu. Syntax

: TEXT(nilai,format_teks)

Contoh

:

=TEXT(2.715,“$0.00”) menghasilkan “$2.72” =TEXT(“4/15/91”,“mmmm dd, yyyy”) menghasilkan “April 15, 1991”

TRIM Penjelasan : Menghilangkan spasi lebih yang terdapat di antara kata. Syntax

: TRIM(teks)

Contoh

:

=TRIM(“ Saya

Suka Buah

”) menghasilkan “Saya Suka Buah”

UPPER Penjelasan : Mengganti semua huruf yang terdapat di dalam teks menjadi huruf besar. Syntax

: UPPER(teks)

Contoh

:

=UPPER(“PT. Gajah Tunggal”) menghasilkan “PT. GAJAH TUNGGAL”


32


VALUE Penjelasan : Mengubah teks yang menggambarkan nilai menjadi nilai murni. Teks dapat diisikan dengan sembarang format nilai, tanggal atau waktu. Jika diisikan dengan format yang tidak dikenal oleh Excel, maka nilai #VALUE! akan dihasilkan. Syntax

: VALUE(teks)

Contoh

:

=VALUE(“$1,000”) menghasilkan 1,000 =VALUE(“16:48:00”)-VALUE(“12:00:00”) menghasilkan 0.2.

Beberapa Fungsi LookUp dan Alamat dalam Excel Berikut adalah fungsi LookUp dan pengalamatan cell/range dalam Excel yang sering digunakan dalam pekerjaan : ADDRESS Penjelasan : Menghasilkan alamat cell sebagai teks sesuai dengan nilai kolom dan nilai baris yang dimasukkan. Syntax

: ADDRESS(no_urut_baris,no_urut_kolom,kode_abs,a1,nama_sheet) kode_abs Jenis pengalamatan yang dihasilkan 1 (default) absolute 2 absolute row; relative column 3 relative row; absolute column 4 relative Jika abs diisi dengan TRUE, maka pengalamatan yang dihasilkan akan dalam bentuk notasi A1. Jika diisi dengan FALSE, maka pengalamatan yang dihasilkan akan

dalam

bentuk

notasi

R1C1.

nama_sheet

bisa

diisi

jika

ingin

mencantumkan nama worksheet pada hasilnya Contoh

:

=ADDRESS(2,3) menghasilkan “$C$2” =ADDRESS(2,3,2) menghasilkan “C$2” =ADDRESS(2,3,2,FALSE) menghasilkan “R2C[3]” =ADDRESS(2,3,1,FALSE,“[Book1]Sheet1”) hasilkan “[Book1]Sheet1!R2C3” =ADDRESS(2,3,1,FALSE,“EXCEL SHEET”) hasilkan “‘EXCEL SHEET’!R2C3”

AREAS Penjelasan : Menghasilkan jumlah area (cell dan/atau range) dalam suatu alamat. Syntax

: AREAS(alamat)

Contoh

:

=AREAS((B2:D4,E5,F6:I9)) menghasilkan 3 Jika nama Harga mengacu pada area B1:D4, B2, dan E1:E10, maka : =AREAS(Harga) menghasilkan 3

CHOOSE Penjelasan : Menghasilkan nilai tertentu sesuai dengan daftar parameter/argumen. CHOOSE bisa digunakan untuk memilih satu di antara 29 nilai berdasarkan urutan yang akan diambil. Syntax

: CHOOSE(urutan_index,nilai1,nilai2,…)

Contoh

:

=CHOOSE(2,“kg”,“cm”,“cc”,“pcs”) menghasilkan “cm”


33

Programming in Microsoft Excel 2000 =SUM(A1:CHOOSE(3,A10,A20,A30)) menghasilkan =SUM(A1:A30) Jika SLama adalah nama yang mengacu pada nilai 10000, maka : =CHOOSE(2,SBaru,SLama,SBudget) menghasilkan 10000 COLUMN Penjelasan : Menghasilkan nomor kolom berdasarkan alamat yang diberikan. Syntax

: COLUMN(alamat)

Contoh

:

=COLUMN(A3) menghasilkan 1 Jika dibuat sebagai array formula di 3 cell berderet horizontal, maka : =COLUMN(A3:C5) menghasilkan {1,2,3} Jika fungsi COLUMN dimasukkan di cell C5, maka : =COLUMN() sama dengan =COLUMN(C5) dan menghasilkan 3

COLUMNS Penjelasan : Menghasilkan banyaknya kolom dalam alamat yang diberikan. Syntax

: COLUMNS(alamat)

Contoh

:

=COLUMNS(A1:C4) menghasilkan 3 =COLUMNS({1,2,3;4,5,6}) menghasilkan 3

CATATAN : Tanda , (koma) digunakan sebagai pemisah kolom dan tanda ; (titik koma) digunakan sebagai pemisah baris. HLOOKUP Penjelasan : Mencari nilai yang terdapat di baris paling atas dari suatu tabel, dan menghasilkan nilai yang terdapat pada kolom yang sama (dengan nilai yang kita cari). Gunakan HLOOKUP jika nilai yang dibandingkan terdapat pada baris paling atas tabel dan ingin mencari data pada baris di bawahnya. Gunakan VLOOKUP jika nilai yang dibandingkan terdapat pada kolom paling kiri tabel dan ingin mencari data pada kolom di kanannya. Fungsi HLOOKUP ini tidak case-sensitive. Syntax

: HLOOKUP(nilai_yang_dicari, tabel,baris_diinginkan,range_lookup) tabel harus diisi dengan nama atau alamat sumber data. Nilai pada baris pertama pada tabel dapat berupa teks, bilangan, atau nilai logika. Jika range_lookup diisi dengan TRUE, maka nilai pada baris paling atas harus dalam keadaan ascending order (urut dari nilai terkecil ke nilai terbesar: …-2,-1,0,1, 2,…, A-Z). Jika range_lookup diisi dengan FALSE, maka nilai pada baris paling atas tidak harus dalam keadaan urut. baris_diinginkan diisi dengan nomor baris yang datanya akan diambil. Jika diisi dengan 1, maka nilai yang dikembalikan akan diambil dari baris pertama dalam tabel, dan begitu juga seterusnya. Jika diisi dengan nilai yang lebih kecil dari 1, maka HLOOKUP akan mengembalikan nilai #VALUE!; sedangkan jika diisi dengan nilai yang lebih besar dari jumlah baris yang ada dalam tabel, maka HLOOKUP akan menghasilkan nilai #REF!. range_lookup bisa diisi dengan TRUE (default) maka HLOOKUP akan mencari dengan metode approximate match, dimana jika nilai_yang_dicari tidak ditemukan dalam tabel, maka yang akan dihasilkan adalah nilai terdekat dengan nilai_yang_dicari (nilai terbesar yang kurang dari nilai_yang_dicari). Jika diisi dengan FALSE maka HLOOKUP akan mencari dengan metode exact match, dimana jika nilai_yang_dicari tidak ditemukan dalam tabel, maka yang


34

Programming in Microsoft Excel 2000 akan dihasilkan adalah nilai #N/A. Contoh

:

Seandainya kita memiliki worksheet seperti di bawah ini : A 1 2 3 4 5 6 Lalu cell A6

B

nama umur status

C

D

Amir

Budi

Cahyo

21 bujangan

24 menikah

22 bujangan

amir diisi dengan formula berikut :

=HLOOKUP(“Budi”,B1:D3,3) menghasilkan “menikah” =HLOOKUP(A5,B1:D3,2,TRUE) menghasilkan 21 =HLOOKUP(“Agung”,B1:D3,3) menghasilkan #N/A =HLOOKUP(“bagyo”,B1:D3,2,TRUE) menghasilkan 21 =HLOOKUP(“Cica”,B1:D3,2,FALSE) menghasilkan #N/A Tabel juga dapat berbentuk array yang berisi nilai konstan, seperti : =HLOOKUP(3,{1,2,3;“a”,“b”,“c”;“d”,“e”,“f”},2,TRUE) menghasilkan “c” CATATAN : Tanda , (koma) digunakan sebagai pemisah kolom dan tanda ; (titik koma) digunakan sebagai pemisah baris. MATCH Penjelasan : Mencari urutan posisi sebuah nilai yang terdapat pada tabel. Gunakan fungsi MATCH, dan bukannya HLOOKUP atau VLOOKUP, ketika yang kita butuhkan adalah posisi nilai dan bukan nilai itu sendiri. Fungsi ini tidak case-sensitive. Syntax

: MATCH(nilai_yang_dicari,tabel,jenis) jenis 1

metode pencarian yang dilakukan mencari nilai terbesar yang lebih kecil dari atau sama dengan

(default

nilai_yang_dicari. Tabel harus dalam keadaan ascending order

)

(urut dari nilai terkecil ke nilai terbesar: …-2,-1,0,1,2,…, A-Z). mencari nilai yang benar-benar sama dengan nilai_yang_dicari.

0

-1

Tabel tidak harus dalam keadaan terurut. mencari nilai terkecil yang lebih besar dari atau sama dengan nilai_yang_dicari. Tabel harus dalam keadaan descending order (urut dari nilai terkecil ke nilai terbesar: …2,1,0,-1,-2,…, Z-A). Jika MATCH tidak berhasil menemukan nilai_yang_dicari, maka nilai yang akan dihasilkan adalah #N/A. Jika jenis diisi dengan 0 dan nilai_yang_dicari berjenis teks, maka kita bisa menggunakan karakter wildcard (? untuk mewakilkan satu karakter bebas dan * untuk mewakilkan beberapa karakter bebas).

Contoh

:

Jika kita memiliki worksheet seperti di bawah ini : A

B

1 Pendapatan (Yen) U.S. Dollar 2 JPY 5,365,000.00 $37,000 3 JPY 5,510,000.00 $38,000 4 JPY 5,655,000.00 $39,000 5 JPY 5,800,000.00 $40,000 6 JPY 5,945,000.00 $41,000 =MATCH(39000,B2:B6,1) menghasilkan 3

C Tax Rate 21.50% 21.67% 21.84% 21.99% 22.14%

=MATCH(38000,B2:B6,0) menghasilkan 2 =MATCH(39000,B2:B6,-1) menghasilkan #N/A, karena range B2:B8 tidak dalam keadaan descending order, melainkan ascending order Jika range A2:A8 diberi nama Yen, range A2:C8 diberi nama YenDollar, dan


35

Programming in Microsoft Excel 2000 sebuah cell yang berisi nilai JPY 6,031,126.33 diberi nama MyIncome, maka : =“Your tax rate is ” & VLOOKUP(MyIncome,YenDollar,3) & “, which places you in tax bracket number ” & MATCH(MyIncome,Yen) & “.” menghasilkan “Your tax rate is 22.41%, which places you in tax bracket number 7.” OFFSET Penjelasan : Mereferensikan ke alamat cell/range tertentu berdasarkan posisi baris dan kolom dari cell/range tertentu. Referensi yang dihasilkan dapat berupa cell tunggal atau range. Kita juga bisa menentukan jumlah baris dan kolom yang ingin dihasilkan. Syntax

: OFFSET(alamat_awal,pos_baris,pos_kolom,tinggi,lebar) alamat_awal harus diisi dengan alamat cell/range awal sebagai panduan. alamat_awal harus diisi dengan alamat cell/range yang berdekatan; jika tidak maka OFFSET akan menghasilkan nilai #VALUE!. pos_baris diisi dengan jumlah baris pergerakan, ke atas (bilangan negatif) atau ke bawah (bilangan positif). pos_kolom diisi dengan jumlah kolom pergerakan, ke kiri (bilangan negatif) atau ke kanan (bilangan positif). Jika pos_baris dan pos_kolom melewati batas-batas sebuah worksheet, maka OFFSET akan menghasilkan nilai #REF! tinggi dan lebar masing-masing menyatakan jumlah baris dan jumlah kolom yang ingin dihasilkan. Jika tinggi dan lebar dihilangkan, maka OFFSET akan menjadikan hasilnya memiliki baris dan kolom yang sama dengan alamat_awal.

Contoh

:

=OFFSET(C3,2,3,1,1) menghasilkan isi cell F5 =OFFSET(C3:E5,-1,0,3,3) menghasilkan isi cell C2:E4 =OFFSET(C3:E5,0,-3,3,3) menghasilkan isi cell #REF! =SUM(OFFSET(C2,1,2,3,1)) sama dengan =SUM(E3:E5)

ROW Penjelasan : Menghasilkan nomor baris berdasarkan alamat yang diberikan. Syntax

: ROW(alamat)

Contoh

:

=ROW(A3) menghasilkan 3 Jika dibuat sebagai array formula di 3 cell berderet horizontal, maka : =ROW(A3:C5) menghasilkan {3,4,5} Jika fungsi ROW dimasukkan di cell C5, maka : =ROW() sama dengan =ROW(C5) dan menghasilkan 5

ROWS Penjelasan : Menghasilkan banyaknya baris dalam alamat yang diberikan. Syntax

: ROWS(alamat)

Contoh

:

=ROWS(A1:C4) menghasilkan 4 =ROWS({1,2,3;4,5,6}) menghasilkan 2

CATATAN : Tanda , (koma) digunakan sebagai pemisah kolom dan tanda ; (titik koma) digunakan sebagai pemisah baris. TRANSPOSE Penjelasan : Mengubah range vertical menjadi range horizontal, dan begitu juga sebaliknya. TRANSPOSE harus dimasukkan sebagai array formula.


36


: TRANSPOSE(alamat)

Contoh

:

Misalkan kita memiliki range A1:C1 berisi nilai 1, 2, dan 3. Dan jika kita memasukkan array formula berikut ke dalam range A3:A5 : =TRANSPOSE(A1:C1) maka range A3:A5 akan berisi nilai 1, 2, dan 3.

VLOOKUP Penjelasan : Mencari nilai yang terdapat di kolom paling kiri dari suatu tabel, dan menghasilkan nilai yang terdapat pada baris yang sama (dengan nilai yang kita cari). Gunakan HLOOKUP jika nilai yang dibandingkan terdapat pada baris paling atas tabel dan ingin mencari data pada baris di bawahnya. Gunakan VLOOKUP jika nilai yang dibandingkan terdapat pada kolom paling kiri tabel dan ingin mencari data pada kolom di kanannya. Fungsi VLOOKUP ini tidak case-sensitive. Syntax

: VLOOKUP(nilai_yang_dicari, tabel,kolom_diinginkan,range_lookup) tabel harus diisi dengan nama atau alamat sumber data. Nilai pada kolom pertama pada tabel dapat berupa teks, bilangan, atau nilai logika. Jika range_lookup diisi dengan TRUE, maka nilai pada kolom paling kiri harus dalam keadaan ascending order (urut dari nilai terkecil ke nilai terbesar: …-2,-1,0,1, 2,…, A-Z). Jika range_lookup diisi dengan FALSE, maka nilai pada kolom paling kiri tidak harus dalam keadaan urut. baris_diinginkan diisi dengan nomor kolom yang datanya akan diambil. Jika diisi dengan 1, maka nilai yang dikembalikan akan diambil dari kolom pertama dalam tabel, dan begitu juga seterusnya. Jika diisi dengan nilai yang lebih kecil dari 1, maka VLOOKUP akan mengembalikan nilai #VALUE!; sedangkan jika diisi dengan nilai yang lebih besar dari jumlah kolom yang ada dalam tabel, maka VLOOKUP akan menghasilkan nilai #REF!. range_lookup bisa diisi dengan TRUE (default) maka VLOOKUP akan mencari dengan metode approximate match, dimana jika nilai_yang_dicari tidak ditemukan dalam tabel, maka yang akan dihasilkan adalah nilai terdekat dengan nilai_yang_dicari (nilai terbesar yang kurang dari nilai_yang_dicari). Jika diisi dengan FALSE maka VLOOKUP akan mencari dengan metode exact match, dimana jika nilai_yang_dicari tidak ditemukan dalam tabel, maka yang akan dihasilkan adalah nilai #N/A.

Contoh

:

Seandainya kita memiliki worksheet seperti di bawah ini : A 1 2 3 4 5 6 7 Lalu cell A6 diisi

pn# ABDAA NAAAA NRAAB WTAAAAAZ

B u/m kg roll kg pcs

C qty 12300 50 25800 125000

NRAAAB dengan formula berikut :

=VLOOKUP(“NAAAA”,A2:C5,3) menghasilkan 50 =VLOOKUP(A7,A2:C5,2,TRUE) menghasilkan “kg” =VLOOKUP(“Abcaa”,A2:C5,3) menghasilkan #N/A =VLOOKUP(“nraaa”,A2:C5,2,TRUE) menghasilkan roll =VLOOKUP(“NRAAC”,A2:C5,2,FALSE) menghasilkan #N/A


37


Beberapa Fungsi Information dalam Excel Berikut adalah fungsi Information dalam Excel yang sering digunakan dalam pekerjaan : CELL Penjelasan : Menghasilkan informasi yang berisi formatting, lokasi, atau isi cell paling kiri atas dari referensi alamat yang kita masukkan. Fungsi CELL ini biasanya digunakan untuk menjaga kompatibilitas dengan pengolah lembar kerja (spreadsheet) lain, seperti Lotus 123, Quattro Pro, dan lain-lain. Syntax

: CELL(jenis_info,alamat) jenis_info “address” “col”

nilai yang dikembalikan alamat cell pertama dalam alamat, sebagai teks. nomor kolom cell pertama dalam alamat 1 jika cell diformat warna untuk bilangan negatif; sebaliknya

“color”

akan mengembalikan 0 (nol). isi dari cell pertama dalam alamat nama file (full path) alamat yang bersangkutan. Mengem-

“contents” “filename”

balikan teks kosong (“”) jika filenya belum disimpan. teks yang merupakan number format dari cell bersangkutan.Teks

untuk

masing-masing

format

yang dapat

dilihat pada tabel di bawah. Jika cell diformat warna untuk “format”

bilangan negatif, maka pada akhir teks akan ditambahkan karakter “-”. Jika cell diformat menggunakan tanda kurung untuk bilangan positif atau bilangan lain, maka pada akhir teks akan ditambahkan karakter “()”. 1 jika cell diformat menggunakan tanda kurung untuk

“parentheses”

bilangan positif atau bilangan lain, sebaliknya 0 (nol). teks yang merupakan label prefix dari cell yang bersangkutan. Mengembalikan tanda kutip tunggal (') jika cell berisi teks rata kiri, kutip ganda (") jika cell berisi teks rata kanan,

“prefix”

caret (^) jika cell berisi teks rata tengah, backslash (\) jika cell berisi pengulangan karakter, dan teks kosong (“”) jika

“protect” “row”

cell berisi yang lain. 0 jika cell tidak dikunci dan 1 jika cell dikunci. nomor baris cell pertama dalam alamat teks yang merupakan jenis data yang terdapat dalam cell.

“type”

Mengembalikan “b” (blank) jika cellnya kosong, “l” (label)

jika cell berisi teks, dan “v” (value) jika berisi yang lain. width” lebar cell dibulatkan ke bawah alamat disini adalah cell yang kita ingin dapatkan informasinya. Jika dikosongkan, maka akan diambil cell yang terakhir kali kita ubah. Daftar berikut ini adalah nilai yang akan dikembalikan oleh fungsi CELL jika jenis_info diisi dengan “format” dan alamat adalah cell yang berisi bilangan dan memiliki number format (yang sudah disediakan Excel). Jika formatnya adalah

CELL menghasilkan

General

"G"

0

"F0"

#,##0

",0"


38

Programming in Microsoft Excel 2000 0.00

"F2"

#,##0.00

",2"

$#,##0_);($#,##0)

"C0'

$#,##0_);[Red]($#,##0)

"C0-"

$#,##0.00_);($#,##0.00)

"C2"

$#,##0.00_);[Red]($#,##0.00) "C2-" 0%

"P0"

0.00%

"P2"

0.00E+00

"S2"

# ?/? atau # ??/??

"G"

m/d/yy atau m/d/yy h:mm atau mm/dd/yy

"D4"

d-mmm-yy atau dd-mmm-yy

"D1"

d-mmm atau dd-mmm

"D2"

mmm-yy

"D3"

mm/dd

"D5"

h:mm AM/PM

"D7"

h:mm:ss AM/PM

"D6"

h:mm

"D9"

h:mm:ss

"D8"

Jika info_type diisi dengan “format”, dan cell baru diformat kemudian dengan custom format, maka kita harus melakukan kalkulasi ulang (calculation) agar hasil perubahan dikenal oleh fungsi CELL. Contoh

:

=CELL(“row”,A20) menghasilkan 20 Jika cell B12 diformat “d-mmm”, maka =CELL(“format”,B12) hasilkan “D2”

ERROR.TYPE Penjelasan : Menghasilkan bilangan berdasarkan nilai kesalahan yang diberikan. Kita dapat menggunakan fungsi ini dan menggabungkannya dengan fungsi IF untuk memeriksa nilai kesalahan dan menampilkan pesan yang kita tentukan. Syntax

Contoh

: ERROR.TYPE(nilai_kesalahan)

:

nilai_kesalahan nilai yang dikembalikan #NULL! 1 #DIV/0! 2 #VALUE! 3 #REF! 4 #NAME? 5 #NUM! 6 #N/A 7 selain di atas #N/A Formula berikut ini digunakan untuk memeriksa cell E50 apakah isinya nilai kesalahan #NULL! atay #DIV/0!. Jika benar, maka tampilkan pesan kesalahan. =IF(ERROR.TYPE(E50)<3,CHOOSE(ERROR.TYPE(E50),“Range

tidak

terhu-

bung”,“Terjadi pembagian dengan 0”)) INFO Penjelasan : Menghasilkan informasi yang berhubungan dengan lingkungan operasi yang aktif. Syntax

: INFO(jenis_info)


39

Programming in Microsoft Excel 2000 jenis_info “directory” “memavail” “memused” “numfile” “origin”

byte jumlah memory yang digunakan untuk keperluan data di Excel. jumlah worksheet yang aktif. alamat (dalam bentuk absolut) paling kiri atas

“osversion” “recalc” “release”

dari worksheet yang aktif. nama dan versi sistem operasi yang digunakan. mode Calculation (“Automatic” atau “Manual”). versi dari Microsoft Excel, sebagai teks. nama dari lingkungan operasi.

“system”

Macintosh = “mac”

“totmem” Contoh

nilai yang dikembalikan path dari directory/folder yang aktif. jumlah memory yang bisa digunakan, dalam

:

Windows = “pcdos” total memory yang ada di komputer (termasuk

yang sedang digunakan), dalam byte. Formula berikut ini akan menghasilkan nilai 2 jika ada dua worksheet yang dibuka bersamaan : =INFO(“numfile”)

IS… Penjelasan : Terdapat sembilan jenis fungsi IS… yang digunakan untuk memeriksa jenis atau keadaan suatu nilai atau alamat. Masing-masing fungsi akan menghasilkan nilai TRUE atau FLASE tergantung dari masukkan yang diberikan. Sebagai contoh fungsi ISBLANK akan menghasilkan nilai TRUE jika cell yang diberikan kosong. Syntax

:

ISBLANK(nilai) ISERR(nilai) ISERROR(nilai) ISLOGICAL(nilai) ISNA(nilai) ISNONTEXT(nilai) ISNUMBER(nilai) ISREF(nilai) ISTEXT(nilai) nilai yang kita masukkan dapat berupa cell kosong, nilai kesalahan, logika, teks, bilangan, atau nama/alamat cell/range.

fungsi ISBLANK ISERR ISERROR ISLOGICAL ISNA ISNONTEXT

Contoh

menghasilkan TRUE jika nilai/cell-nya kosong. memiliki nilai kesalahan selain #N/A. memiliki nilai kesalahan #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, atau #NULL!. memiliki nilai berjenis logika (TRUE atau FALSE). memiliki nilai kesalahan #N/A. memiliki nilai berjenis selain teks. Jika nilainya kosong maka

fungsi ini juga akan menghasilkan nilai TRUE. ISNUMBER memiliki nilai berjenis bilangan. ISREF memiliki nilai berjenis referensi/alamat. ISTEXT memiliki nilai berjenis teks. : =ISNUMBER(19) menghasilkan TRUE =ISNUMBER(“19”) menghasilkan FALSE =ISLOGICAL(TRUE) menghasilkan TRUE =ISLOGICAL(“TRUE”) menghasilkan FALSE Jika range C1:C5 berisi nilai “Gajah”,“Tunggal”, #REF!,$330.92, #N/A, maka :


40

Programming in Microsoft Excel 2000 =ISBLANK(C1) menghasilkan FALSE =ISTEXT(C2) menghasilkan TRUE =ISERROR(C3) menghasilkan TRUE =ISNA(C3) menghasilkan FALSE =ISERR(C5) menghasilkan FALSE =ISNUMBER(C4)

menghasilkan

TRUE

(jika

$330.92

dimasukkan

sebagai

bilangan dan bukan teks) =ISREF(Daerah1) menghasilkan TRUE (jika Daerah1 adalah nama range) Contoh yang lain adalah jika kita, misalnya, ingin menghitung rata-rata range A1:A4, tetapi kita tidak yakin apakah semua cell di A1:A4 berisi bilangan. Formula =AVERAGE(A1:A4) akan menghasilkan #DIV/0! jika di range A1:A4 sama sekali tidak terdapat bilangan. Untuk mengatasi hal ini kita bisa menggunakan formula di bawah ini : =IF(ISERROR(AVERAGE(A1:A4)),“Tidak ada bilangan”,AVERAGE(A1:A4)) TYPE Penjelasan : Mengembalikan jenis data dari nilai yang kita berikan. TYPE ini sangat berfungsi ketika kita akan menggunakan fungsi yang bisa menerima jenis data yang berbeda-beda. Kita juga bisa menggunakan TYPE ini untuk mencari tahu jenis data yang dikembalikan oleh fungsi atau formula tertentu. Kita tidak bisa menentukan apakah suatu cell berisi formula atau tidak, kita hanya bisa menentukan jenis nilai yang dihasilkan oleh formula tersebut. Syntax

:

TYPE(nilai) nilai yang kita masukkan dapat berupa nilai kesalahan, logika, teks, bilangan, atau nama/alamat cell/range.

Contoh

:

jenis nilai nilai yang dihasilkan bilangan 1 teks 2 logika 4 kesalahan 16 array 64 Jika cell A1 berisi “Kodok”, maka : =TYPE(A1) atau =TYPE(“Smith”) menghasilkan 2 =TYPE(2+A1) sama dengan =TYPE(#VALUE!) menghasilkan 16 =TYPE({1,2,3,4}) menghasilkan 64

MegaFormula Terkadang lembar kerja yang kita buat memerlukan intermediate formula (formula perantara) untuk menghasilkan keluaran tertentu. Misalnya untuk menghasilkan nilai Z, kita harus membuat formula dengan nilai A yang menghasilkan nilai B, lalu kita buat formula lagi dengan nilai B yang menghasilkan nilai C, dan begitu seterusnya hingga menghasilkan nilai Z. Dengan kata lain, sebuah formula terkadang bergantung kepada hasil dari formula lain. Setelah kita—mungkin— merasa yakin bahwa seluruh formula yang kita buat benar, kita bisa saja menghilangkan semua intermediate formula dan menciptakan satu megaformula, yaitu satu formula besar yang merupakan gabungan dari banyak formula. Keuntungannya ? Kita bisa menghemat jumlah cell


41

Programming in Microsoft Excel 2000 yang terpakai, dan perhitungan (calculation) yang dilakukan oleh Excel akan berjalan lebih cepat. Lagipula, tentu orang lain akan kagum terhadap kemampuan membuat formula yang kita miliki. Kerugiannya ? Formulanya menjadi rumit dan sulit untuk dimengerti dan diubah. Perhatikan contoh berikut: Bayangkan kita memiliki worksheet yang berisi nama-nama orang lalu kita diminta untuk menghilangkan nama tengah masing-masing orang. Dengan mengubah satu persatu cell tentunya akan memakan waktu banyak, dan penyelesaian satu-satunya adalah dengan menggunakan formula. Dan formula yang dibutuhkan melibatkan beberapa intermediate formula. Coba lihat worksheet di bawah ini yang terdiri dari 6 intermediate formula (kolom B sampai kolom G). Nama awalnya terdapat di kolom A dan hasil akhirnya terdapat di kolom H.

1 2 3 4 5 6

A John Q. Public Bob Smith Jill Marie Jackson Amy Young R.J. Smith R. Jay Smith

B John Q. Public Bob Smith Jill Marie Jackson Amy Young R.J. Smith R. Jay Smith

C 5 4 5 4 5 3

D 8 #VALUE! 11 #VALUE! #VALUE! 7

E 8 4 11 4 5 7

F John Bob Jill Amy R.J. R.

G Public Smith Jackson Young Smith Smith

H John Public Bob Smith Jill Jackson Amy Young R.J. Smith R. Smith

Intermediate Formula yang ditulis pada baris pertama Cell B1 C1 D1 E1 F1 G1 H1

Intermediate Formula =TRIM(A1) =FIND(“ ”,B1,1) =FIND(“ ”,B1,C1+1) =IF(ISERROR(D1),C1,D1) =LEFT(B1,C1) =RIGHT(B1,LEN(B1)-E1) =F1&G1

Maksud Menghilangkan spasi lebih Mencari spasi pertama Mencari spasi kedua Gunakan spasi pertama jika tidak ada spasi kedua Ambil nama depan Ambil nama belakang Gabungkan kedua nama

Sebenarnya kita bisa saja menghilangkan seluruh intermediate formula lalu menciptakan megaformula yang langsung menghasilkan nilai akhir yang diinginkan sebagai berikut : =LEFT(TRIM(A1),FIND(“ ”,TRIM(A1),1))&RIGHT(TRIM(A1),LEN(TRIM(A1))IF(ISERROR(FIND(“ ”,TRIM(A1),FIND(“ ”,TRIM(A1),1)+1)), FIND(“ ”,TRIM(A1),1),FIND(“ ”,TRIM(A1),FIND(“ ”,TRIM(A1),1)+1))) Ketika kita sudah puas dan yakin bahwa megaformula yang kita ciptakan berjalan dengan baik, kita bisa menghapus semua intermediate formula yang ada karena sudah tidak digunakan lagi. Megaformula di atas melakukan hal yang sama dengan semua intermediate formula yang ada— walaupun terlihat sangat sulit sekali dipahami maksudnya, bahkan oleh penulis sekalipun. Untuk lebih amannya, copy-lah seluruh intermediate formula ke tempat lain sehingga jika ada perubahan atau terdapat kesalahan pada formula yang kita susun, kita dapat dengan mudah mengubahnya.

CATATAN … Untuk menciptakan megaformula, mulailah dari formula yang terakhir lalu ubahlah setiap parameter yang ada menjadi urutan formula sebelumnya dan begitu seterusnya hingga dicapai cell yang berisi nilai awal atau nilai yang tidak bisa diurai lagi. Keterbatasan megaformula adalah bahwa formula dalam Excel hanya mampu menampung 1024 karakter.

Mungkin kita beranggapan bahwa penggunaan megaformula akan memperlambat proses perhitungan yang dilakukan oleh Excel, karena megaformula terlihat sangat kompleks. Sebenarnya tidak juga. Untuk lebih jelasnya lihatlah perbandingan pada tabel di bawah ini yang


42

Programming in Microsoft Excel 2000 memperbandingkan

worksheet

yang

menggunakan

megaformula

sebanyak

65,536

kali,

worksheet yang menggunakan 6 intermediate formula, dan yang menggunakan fungsi VBA.

Intermediate Formula versus Megaformula Metode Intermediate formula Megaformula fungsi di VBA

Waktu perhitungan 7 detik 5 detik 66 detik

Ukuran file 23.2 MB 8.2 MB 800 MB

LATIHAN Jika kita memiliki worksheet yang berisi status stok harian material X, seperti ini : A

B

C

D

1 Tanggal Masuk Keluar Stok 2 1/1/2002 50 3 2/1/2001 20 30 40 4 3/1/2001 5 4/1/2001 6 5/1/2001 7 6/1/2001 Buatlah formula untuk mengisi kolom Stok secara otomatis jika kolom Masuk dan kolom Keluar diisi secara manual !

Jika kita memiliki daftar material sebagai berikut : A

B

C

1 Product Class Part Number Nama Material 2 AIAR BRDCA AIAR BRDCA 3 AIBF CBAAC 4 AIDC ALCBA 5 AIEC SCJCA 6 AIGV VBAMA Buatlah formula untuk menggabungkan nama Product Class dan Part Number dan tampilkan hasilnya di kolom Nama Material ! Buatlah sebuah formula untuk menampilkan tanggal hari ini dengan bentuk “Tanggal Hari Ini Adalah : 1-Jan-2002” !

Jika kita memiliki daftar kenaikan nilai berdasarkan persentase sebagai berikut : A

B

C

1 Persentase : 5% 2 10 3 10.5 Buatlah daftar otomatis kenaikan nilai (10 baris saja) berdasarkan isi cell C1 !

Perhatikan worksheet daftar komputer di bawah ini : A

B

C

D

1 PC Code Status Active RAM 2 PC001 W A 32 MB 3 PC002 A NA 16 MB Buatlah formula untuk menghitung jumlah Workstation (Status=“W”) dan Stand Alone (Status=“A”) ! Buat juga formula untuk mengetahui jumlah Workstation yang aktif (Active=“A”) dan yang tidak aktif (Active=“NA”) ! Setelah itu, hitung total RAM yang ada untuk Stand Alone yang sudah tidak aktif lagi ! Buatlah array formula untuk menghitung jumlah baris yang berada di range B5:C25 dimana


43

Programming in Microsoft Excel 2000 range B5:B25 harus berisi teks “Aku” dan range C5:C25 berisi teks “Dia” ! Seorang staff Gudang Bahan diharuskan membuat lembar order material untuk bulan ini yang terdiri dari data : rata-rata pemakaian bulan lalu (AC) stok bulan lalu (LS) safety stock per material dalam persentase (SS) jumlah material yang harus diorder untuk bulan ini (O) Jika kondisi untuk pengorderan material adalah sebagai berikut : “Jika jumlah AC ditambah dengan jumlah SS lebih besar dari LS, maka lakukan pengorderan dimana O sama dengan AC ditambah dengan SS dikurangi LS. Sebaliknya jangan lakukan pengorderan.” Buatlah daftar perhitungan pengorderan 5 material seperti kondisi di atas ! Sekarang kita akan mencoba untuk membuat sebuah worksheet yang berisi rencana produksi satu shift yang biasa disusun oleh PPC. Kita diharapkan membuat sebuah sheet yang berisi data sebagai berikut : safety stock untuk seluruh ban (SS) kebutuhan masing-masing ban (REQ) stok masing-masing ban yang ada (S) rencana produksi hasil perhitungan sementara/kasar (CSC), yang memiliki ketentuan perhitungan sebagai berikut : “Jika S lebih besar dari REQ ditambah SS maka jangan buat rencana produksi, sebaliknya buat rencana produksi dengan perhitungan REQ ditambah SS dikurangi S.” rencana produksi akhir (SCH) yang akan menentukan berapa jumlah ban yang akan diproduksi, dimana memiliki perhitungan : “Jika total S dari seluruh ban yang ada kurang dari 1 maka isi SCH dengan nilai 0. Jika (REQ kurang dari 1 dan S lebih besar dari 0, maka isi SCH dengan nilai “#Stock?”. Jika CSC kurang dari atau sama dengan 1 maka isi SCH dengan nilai 1. Jika tidak memenuhi ketiga kriteria sebelumnya, maka isi SCH dengan CSC. Sekarang susunlah formula-formula rencana produksi yang dibutuhkan worksheet di atas untuk, misalkan, 5 jenis ban saja ! (credits given to ppc plant d for their inputs)


44


Modul 2 : Pengenalan VBA VBA (Visual Basic for Application) merupakan bahasa pemrograman yang bisa digunakan untuk melakukan manipulasi objek dan proses yang terdapat di dalam Excel. VBA merupakan kembangan dari bahasa BASIC (Beginner’s All-purpose Symbolic Instruction Code) yang dari namanya pun kita sudah ketahui bukan bahasa profesional. BASIC mengalami ‘evolusi’ dan perkembangan yang pesat khususnya tahun 1991 ketika Microsoft memperkenalkan bahasa Visual Basic untuk sistem operasi Windows. Excel 5 merupakan produk pertama yang menggunakan VBA sebagai bahasa pemrogramannya. VBA dikenal sebagai ‘bahasa ibu’ untuk program-program aplikasi Microsoft, yang juga disertakan pada paket Microsoft Office 2000—dan bahkan juga aplikasi dari vendor lain. Sehingga, jika kita menguasai VBA dengan Excel, kita bisa juga untuk memprogram aplikasi lain dari Microsoft (seperti Word) dan non-Microsoft (seperti AutoCad 2000). Bahkan, kita bisa membuat program VBA yang saling berhubungan antar aplikasi yang berbeda.

Sekilas Pemrograman VBA Program (biasa disebut kode) VBA yang kita tulis, disimpan dalam sebuah modul VBA. Modul VBA disimpan di dalam workbook Excel bersama dengan worksheet dan objek lain, dan jika kita ingin melihat atau menyuntingnya kita harus masuk ke dalam menu Tools → Macro → Visual Basic Editor. Modul VBA terdiri dari prosedur-prosedur (procedures). Sebuah prosedur adalah sekumpulan kode untuk melakukan tugas atau aksi tertentu. Berikut adalah contoh prosedur sederhana yang bernama Test : Sub Test() Sum = 1 + 1 MsgBox “Hasilnya adalah ” & Sum End Sub Modul VBA juga dapat terdiri dari prosedur fungsi (function). Fungsi adalah jenis prosedur yang bisa mengembalikan nilai tertentu. Fungsi bisa dipanggil dari prosedur VBA lain atau formula di dalam worksheet. Ini adalah contohnya, fungsi Tambah : Function Tambah(Nilai1, Nilai2) Tambah = Nilai1 + Nilai2 End Function VBA berfungsi untuk memanipulasi objek (object) yang terdapat di dalam Excel. Excel menyediakan lebih dari 1000 objek untuk dimanipulasi. Contoh objek antara lain workbook, worksheet, range, atau chart. Objek di dalam Excel disusun secara hirarki. Objek dapat berfungsi sebagai penampung objek yang lain. Sebagai contoh Excel adalah objek yang disebut Application, yang didalamnya terdiri dari objek lain, seperti Workbook dan CommandBar. Objek Workbook


45

Programming in Microsoft Excel 2000 terdiri dari objek lain seperti Worksheet dan Chart. Objek Worksheet terdiri dari objek lain seperti Range, PivotTable, dan lain-lain. Objek yang sejenis membentuk suatu koleksi (collection). Contoh, koleksi Worksheets terdiri dari semua worksheet yang ada di dalam workbook. Koleksi CommandBars terdiri dari semua objek CommandBar. Koleksi itu sendiri sebenarnya merupakan objek juga. Ketika kita ingin menggunakan ‘anggota’ dari suatu objek atau koleksi, kita harus memisahkan objek dan ‘anggota’nya dengan menggunakan tanda titik (.). Dan tentu saja, pemisahan ini harus sesuai dengan hirarki yang ada. Contoh, untuk mengakses ke workbook yang bernama Book1.xls, kita menggunakan kode : Application.Workbooks(“Book1.xls”) Kode di atas menggunakan workbook Book1.xls di dalam koleksi Workbooks yang terdapat

di

dalam

objek

Application.

Turun

ke

level

selanjutnya,

kita

bisa

menggunakan Sheet1 di dalam Book1, seperti : Application.Workbooks(“Book1.xls”).Worksheets(“Sheet1”) Kita juga bisa turun ke level selanjutnya untuk menggunakan cell A1 sebagai berikut : Application.Workbooks(“Book1.xls”).Worksheets(“Sheet1”).Range(“A1”) Jika kita menghilangkan penggunaan objek tertentu, maka Excel akan mengambil objek yang saat itu sedang aktif. Jadi jika Book1 adalah workbook yang aktif, kode sebelumnya dapat disederhanakan menjadi : Worksheets(“Sheet1”).Range(“A1”) Dan jika kita tahu bahwa Sheet1 adalah worksheet yang aktif, kita juga bisa menyederhanakan kode sebelumnya menjadi : Range(“A1”) Sebuah objek memiliki properti (properties). Properti adalah setting, sifat, atau nilai yang dikandung oleh suatu objek. Sebagai contoh, objek Range memiliki properti Value untuk mengakses nilai yang dimilikinya. Kita bisa menggunakan kode VBA untuk mengubah nilai suatu properti. Untuk mengakses properti suatu objek, kita bisa menggabungkan objek dengan propertinya dengan dipisahkan oleh tanda titik. Sebagai contoh, jika kita ingin menggunakan nilai yang terdapat di dalam cell A1, maka kodenya sebagai berikut : Worksheets(“Sheet1”).Range(“A1”).Value Kita juga bisa mengambil atau memberikan nilai dari/ke suatu properti. Berikut adalah contoh untuk mengambil nilai cell A1 dan menaruhnya di variabel Interest dan contoh untuk mengisi nilai cell A1 dengan suatu bilangan : Interest = Worksheets(“Sheet1”).Range(“A1”).Value Worksheets(“Sheet1”).Range(“A1”).Value = 7 Selain properti, objek juga memiliki metode (methods). Metode adalah kegiatan atau aksi tertentu yang dilakukan suatu objek. Sebagai contoh, salah satu metode yang dimiliki oleh objek Range adalah ClearContents yang berfungsi untuk menghapus isi suatu Range. Untuk mengakses metode suatu objek, kita bisa menggabungkan objek dengan metodenya dengan dipisahkan oleh tanda titik. Contoh di bawah ini akan menghapus isi cell A1 : Range(“A1”).ClearContents VBA juga mendukung semua struktur pemrograman modern, seperti larik (array), pengulangan (looping), dan sebagainya.


46

Programming in Microsoft Excel 2000 Percaya atau tidak, penjabaran singkat di atas mencakup sebagian besar penjelasan tentang VBA. Sekarang hanya tinggal masalah mempelajari detil yang ada, yang akan dibahas pada bagian-bagian berikutnya.

Sebuah Analogi

Jika kita merasa agak kesulitan dalam memahami pengertian tentang ‘tetek-bengek’ objek ini, mungkin analogi di bawah ini bisa membantu. Kita akan mencoba membandingkan Excel dengan pabrik manufaktur. Objek dasar yang dimiliki oleh Excel adalah Workbook. Sedangkan analogi yang kita miliki dapat berupa sebuah pabrik tunggal. Dalam Excel, kita bisa menambahkan atau menutup workbook, dan semua workbook yang terbuka merupakan suatu koleksi Workbooks. Sama saja dengan analogi kita, manajemen perusahaan dapat menambah pabrik baru atau menutup pabrik yang sudah ada—dan semua pabrik yang ada tergabung dalam koleksi Pabrik. Workbook dalam Excel adalah sebuah objek, dan juga sebagai penampung objek lain seperti Worksheet, Chart, dan sebagainya. Lebih jauh, sebuah objek di dalam Workbook dapat terdiri dari objek-objek lain. Misalnya, objek Worksheet terdiri dari objek Range, PivotTable, dan sebagainya. Dan di dalam analogi kita, sebuah Pabrik dapat terdiri dari objek Produksi, Pekerja, dan lain-lain. Dan lagi, manajemen dapat menambah atau menghilangkan objek yang terdapat dalam Pabrik. Sebagai contoh, manajemen dapat menambah objek Taman. Masing-masing objek ini bisa terdiri dari objek lain lagi. Misalnya, objek Produksi memiliki objek Mesin, Lori, dan sebagainya. Sejauh ini, sepertinya analogi yang kita buat bisa diterima. OK, akan kita lanjutkan. Objek di dalam Excel memiliki properti. Contoh, objek Range memiliki properti Value serta Name, dan objek Shape memiliki properti Width dan Height. Begitu juga dengan objek-objek di dalam Pabrik. Mesin memiliki properti Suhu ataupun Berat. Selain properti, objek di dalam Excel juga memiliki metode, untuk melakukan suatu kegiatan atau aksi tertentu. Contoh, metode ClearContents akan menghapus isi yang terdapat dalam objek Range. Begitu juga dengan objek analogi kita, Mesin memiliki metode Nyalakan atau Putar. Dalam Excel, terkadang metode dapat mengubah isi suatu properti. Metode ClearContents dalam objek Range, contohnya, akan mengubah properti Value dalam objek Range itu sendiri. Begitu juga dengan metode Nyalakan dalam objek Mesin akan mengubah properti Suhu dalam objek Mesin itu sendiri. Sudah jelas sekarang ?

Visual Basic Editor Dalam Excel, kita bisa menggunakan Visual Basic Editor (VBE) untuk melihat atau menyunting modul VBA. Kita tidak bisa menjalankan VBE tanpa membuka Excel terlebih dahulu, karena VBE merupakan satu kesatuan dengan Excel. Untuk berpindah ke VBE, kita bisa melakukan satu di antara cara berikut : Tekan Alt+F11 Pilih menu Tools → Macro → Visual Basic Editor Klik tombol VBE

, yang ada di toolbar Visual Basic

CATATAN … Jangan keliru antara VBE dengan Microsoft Script Editor yang digunakan untuk menyunting kode HTML.

Tampilan dari VBE bisa dilihat pada gambar 2.1 di bawah ini, yang terdiri dari komponen MenuBar dan ToolBar yang berbeda dengan menubar Excel, Project Explorer, Code Window, dan Immediate Window. Di dalam Project Explorer kita bisa menambah modul VBA baru, menghapus modul VBA yang sudah ada ataupun export/import modul VBA dari lain file. Sedangkan di Code Window, kita bisa memasukkan kode program.


47

Programming in Microsoft Excel 2000 Gambar 2.1 VBE

Bisa dilihat pada Project Explorer, terdapat berbagai macam objek yang masing-masing memiliki Code Window tersendiri. Secara garis besar objek yang terdapat di sini adalah : Workbook itu sendiri (ThisWorkbook di dalam Project Explorer atau PE) Worksheet atau chart di dalam workbook (contoh: Sheet1, Chart1 di dalam PE) Modul VBA Class Module (modul khusus yang memungkinkan kita menciptakan kelas objek baru) UserForm

Membuat kode VBA Secara umum, Code Window dapat menampung empat jenis kode VBA : Procedure Function Property procedure Declaration, yang berisi informasi tentang variabel yang dibutuhkan oleh kode VBA. Kita juga bisa menentukan jenis data untuk variabel yang kita deklarasikan. Pengaturan modul-modul VBA adalah semuanya terserah pada pribadi masing-masing. Beberapa orang memilih untuk menaruh seluruh kode dalam satu modul VBA, dimana sebagian orang memilih untuk memisah-misah kode ke dalam beberapa modul. Kita bisa membuat kode VBA dalam tiga cara, yaitu : Memasukkan kode dengan cara biasa—mengetiknya dengan keyboard. Menggunakan fasilitas macro recorder milik Excel untuk merekam tindakan dan aksi kita untuk kemudian mengubahnya menjadi kode VBA. meng-copy kode dari modul lain dan mem-paste ke dalam modul yang sedang kita kerjakan.


48

Programming in Microsoft Excel 2000 Memasukkan kode dengan cara biasa Memasukkan dan menyunting teks untuk kode VBA sama persis dengan penyuntingan teks yang biasa kita lakukan dengan Word atau Excel. Kita bisa melakukan operasi cut, copy, atau paste. Satu kode perintah dalam VBA bisa sepanjang yang kita inginkan. Tetapi biasanya untuk kenyamanan dalam membaca, satu perintah yang panjang dipecah menjadi beberapa baris dengan menggunakan tanda underscore (_). Untuk lebih jelasnya perhatikan contoh berikut : MsgBox “File yang anda inginkan tidak terdapat dalam ” & _ ThisWorkbook.Path & vbCrLf & vbCrLf & _ “Pastikan untuk meng-copy-nya kembali”, vbCritical Perhatikan bahwa dua baris terakhir kode diatas ditulis agak menjorok ke dalam. Ini dimaksudkan bahwa baris-baris tersebut merupakan kelanjutan dari baris sebelumnya. Coba ini : Masukkan kode program di bawah ini ke dalam salah satu modul VBA : Sub Ucapan() Msg = “Apakah nama anda ” & Application.UserName & “?” Ans = MsgBox(Msg, vbYesNo) If Ans = vbNo Then MsgBox “Oh,... Maaf” Else MsgBox “Saya pasti cenayang” End If End Sub

CATATAN … Ketika kita memasukkan kode program, terkadang VBE melakukan perubahan atas teks yang kita masukkan. Sebagai contoh, ketika kita tidak mengetik spasi sebelum dan sesudah tanda sama dengan (=), VBE akan secara otomatis menambahkan spasi, dan warna teks-teks tertentu akan berubah. Semua itu adalah sangat normal, dan kita akan memahami fungsi dan keuntungannya jika sudah terbiasa dengan kode VBA.

Untuk menjalankan prosedur Ucapan, pastikan untuk memposisikan pointer di sembarang tempat pada kode yang kita masukkan. Lalu lakukan salah satu dari langkah-langkah berikut : Tekan F5 Pilih menu Run→Run Sub/UserForm Klik tombol Run Sub/UserForm

, yang terdapat pada toolbar Standard

Jika kode yang kita masukkan benar, maka akan tampil kotak dialog seperti terlihat pada gambar 2.2 di bawah ini.

Gambar 2.2 Hasil dari contoh kode di atas

Kode program di atas, sebagai catatan, menggunakan konsep berikut ini :


49

Programming in Microsoft Excel 2000 Deklarasi prosedur (Sub Ucapan()) Memberi nilai ke dalam variabel (Msg dan Ans) Menggabungkan string (dengan menggunakan operator &) Menggunakan fungsi VBA yang sudah tersedia (MsgBox) Menggunakan konstanta VBA yang sudah tersedia (vbYesNo dan vbNo) Menggunakan struktur pemrograman If-Then-Else-End If Mengakhiri prosedur (End Sub) Cukup bagus untuk seorang pemula, bukan ?

Memasukkan kode dengan Macro Recorder Cara lain untuk membuat kode VBA adalah dengan menggunakan fasilitas dari Excel yang diberi nama macro recorder. Merekam macro bisa dibilang sangat berguna, walaupun banyak keterbatasannya (contohnya kita tidak akan pernah bisa membuat kode VBA sebelumnya dengan menggunakan macro recorder). Pada kenyataannya, ketika kita merekam macro kita sering membuat penyesuaian kode ataupun menghapus beberapa kode secara manual. Contoh berikut akan menunjukkan cara sederhana merekam macro dimana Page Setup akan diubah orientasinya menjadi Landscape. Kita mulai dengan menyiapkan satu workbook kosong, dan ikuti langkah-langkah berikut : Aktifkan sembarang worksheet. Pilih menu Tools→Macro→Record New Macro…. Excel kemudian akan menampilkan kotak dialog Record Macro. Klik OK untuk menerima setting yang default. Excel secara otomatis akan menambahkan modul VBA ke dalam projek kita. Mulai langkah ini, Excel akan mengubah aksi atau tindakan kita menjadi kode VBA. Selama dalam proses perekaman, Excel akan menampilkan kata Recording di status bar dan menampilkan floating toolbar yang berisi dua tombol (Stop Recording dan Relative Reference). Pilih menu File→Page Setup…. Excel lalu akan menampilkan kotak dialog Page Setup. Pilih opsi Landscape, dan klik OK untuk menutup kotak dialog. Klik tombol Stop Recording atau pilih menu Tools→Macro→Stop Recording. Excel akan berhenti merekam kegiatan aksi kita. Untuk melihat hasil perekaman macro-nya, aktifkan VBE, lalu klik pada Module1 untuk menampilkan kodenya (jika projek yang ada sudah memiliki Module1, maka macro yang baru kita ciptakan tadi akan diberi nama Module2). Berikut adalah listing program hasil perekaman macro yang baru kita lakukan : Sub Macro1() With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1)


50

Programming in Microsoft Excel 2000 .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 End With End Sub Wow… kita mungkin terkejut dengan jumlah kode yang begitu banyak hanya dengan satu perintah. Walaupun kita hanya mengubah satu opsi sederhana di kotak dialog Page Setup, Excel menghasilkan kode yang menggambarkan seluruh opsi yang ada di dalam kotak dialog. Jika kita ingin macro yang kita buat hanya mengubah opsi Landscape, kita bisa menyederhanakan dengan menghapus beberapa kode yang tidak dibutuhkan sehingga lebih mudah dibaca dan lebih cepat ketika dilaksanakan, menjadi sebagai berikut : Sub Macro1() With ActiveSheet.PageSetup .Orientation = xlLandscape End With End Sub Seperti bisa dilihat di atas, kita sudah menghapus semua kode kecuali kode yang berhubungan dengan properti Orientation. Bahkan sebenarnya, kode di atas bisa lebih disederhanakan lagi karena struktur With-End With tidak perlu digunakan jika hanya ada satu properti saja : Sub Macro1() ActiveSheet.PageSetup.Orientation = xlLandscape End Sub Dalam contoh diatas, macro akan mengubah properti Orientation di objek PageSetup di sheet yang aktif. Omong-omong, sebenarnya xlLandscape adalah konstanta yang sudah disediakan oleh Excel (built-in constant) untuk memudahkan dalam melakukan pemrograman. xlLandscape memiliki nilai 2, dan xlPortrait memiliki nilai 1. Sebagian besar programmer lebih senang menggunakan konstanta nama ini daripada harus menghapalkan nilai-nilai. Kita bisa menggunakan fasilitas Help yang disediakan oleh Excel untuk mengetahui nama seluruh built-in constant yang terdapat dalam Excel. Kita juga bisa memasukkan kode di atas secara langsung tanpa harus menggunakan macro recorder. Tetapi, kita harus tahu jenis objek, properti yang ada, dan metode yang harus digunakan untuk melaksanakan tugas yang kita inginkan. Kenyataannya, memang lebih cepat untuk membuat program dengan menggunakan macro recorder, dan juga kita bisa mendapatkan ‘bonus lain’ : kita menjadi tahu bahwa objek PageSetup memiliki beberapa properti seperti di atas, yang mungkin nanti perlu kita gunakan.

CATATAN …


51

Programming in Microsoft Excel 2000 Penulis merasa bahwa menggunakan macro recorder adalah cara yang terbaik dalam belajar VBA. Ketika kita merasa ragu dan tidak begitu yakin perintah apa yang harus ditulis, cobalah menggunakan macro recorder. Walaupun hasilnya mungkin tidak sesuai dengan yang kita inginkan, tetapi hasil akhirnya akan selalu benar. Kita juga bisa menggunakan fasilitas Help yang disediakan untuk mengetahui lebih lanjut tentang suatu objek.

Relative atau Absolute Ketika

sedang

merekam

tindakan

dan

aksi

kita,

Excel

secara

default

akan

membuat

pengalamatan absolute jika terdapat tindakan yang berhubungan dengan cell atau range. Perhatikan contoh berikut ini : Aktifkan sembarang worksheet dan nyalakan macro recorder. Aktifkan cell B1 dan masukkan kata Jan. Pindah ke cell C1 dan masukkan kata Feb. teruskan langkah di atas sampai kita sudah memasukkan nama 6 bulan di range B1:G1. Klik cell B1 untuk mengaktifkannya kembali dan hentikan macro recorder. Excel kemudian akan menghasilkan kode berikut ini : Sub Macro1() Range("B1").Select ActiveCell.FormulaR1C1 Range("C1").Select ActiveCell.FormulaR1C1 Range("D1").Select ActiveCell.FormulaR1C1 Range("E1").Select ActiveCell.FormulaR1C1 Range("F1").Select ActiveCell.FormulaR1C1 Range("G1").Select ActiveCell.FormulaR1C1 Range("B1").Select End Sub

= "Jan" = "Feb" = "Mar" = "Apr" = "May" = "Jun"

Lalu coba jalankan macro di atas dengan memilih menu Tools→Macro→Macros… (atau tekan Alt+F8), pilih Macro1 (atau apapun nama yang kita berikan), dan klik tombol Run. Ketika dijalankan, macro akan mengulang aksi yang sama dengan saat ketika kita merekamnya. Hasil yang didapat ketika kita menjalankan berulang-ulang adalah sama tidak peduli di cell mana kita sedang berada. Merekam macro dengan menggunakan pengalamatan absolute (absolute reference) akan selalu menghasilkan hasil yang sama. Terkadang kita menginginkan untuk merekam macro yang tindakannya relatif terhadap posisi cell yang aktif. Sebagai contoh, misalkan kita ingin merekam macro untuk mengisi nama-nama bulan mulai dari cell yang aktif. Untuk kasus ini, kita bisa menggunakan pengalamatan relatif (relative reference). Seperti sudah kita ketahui, ketika sedang merekam macro, muncul floating toolbar yang berisi tombol Stop Recording dan Relative Reference. Tombol terakhir inilah yang digunakan untuk berpindah antara relative reference dan absolute reference. Ketika dalam posisi ditekan, yang aktif adalah relative reference, sebaliknya absolute reference. Kita bisa berpindah jenis pengalamatan setiap saat, bahkan ketika sedang merekam macro. Untuk melihat kerja dari tombol ini, hapus isi cell B1:G1, dan lakukan langkah berikut ini : Aktifkan cell B1. Aktifkan sembarang worksheet dan nyalakan macro recorder.


52

Programming in Microsoft Excel 2000 Klik tombol Relative reference untuk berpindah ke pengalamatan relatif. Masukkan nama 6 bulan pada range B1:G1, seperti contoh sebelumnya. Klik cell B1 untuk mengaktifkannya kembali dan hentikan macro recorder. Excel kemudian akan menghasilkan kode berikut ini : Sub Macro2() ActiveCell.FormulaR1C1 = "Jan" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "Feb" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "Mar" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "Apr" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "May" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "Jun" ActiveCell.Offset(0, -5).Range("A1").Select End Sub Sekarang kita bisa menjalankan macro ini pada cell tertentu untuk mulai menulis hasilnya di cell yang bersangkutan. Perlu diperhatikan juga, bahwa sebelum merekam macro kita harus memilih cell tertentu terlebih dahulu sebagai cell awalnya. Ini merupakan langkah penting dalam perekaman macro jika kita ingin menggunakan suatu cell sebagai cell awal. Walaupun macro di atas terlihat cukup aneh, sebenarnya cukup sederhana. Baris pertama akan memasukkan Jan ke dalam cell yang aktif. Beris berikutnya menggunakan metode Offset untuk berpindah ke cell di sebelah kanannya. Beris berikutnya memasukkan teks lagi, dan begitu seterusnya. Dan yang terakhir, cell awalnya dipilih dengan menggunakan pemindahan cell secara relatif ketimbang alamat cell absolut.

CATATAN … Kita bisa perhatikan bahwa Excel menghasilkan kode di atas dengan menggunakan pengalamatan cell A1—yang mana terlihat aneh karena macro kita sebenarnya tidak memiliki hubungan apapun dengan cell A1. Ini bisa dikatakan sebagai efek samping dari perekaman macro yang dijalankan. Sampai saat ini, yang perlu kita ketahui adalah macronya bekerja dengan benar.

Sebenarnya, kode yang dihasilkan Excel bisa dibilang terlalu kompleks dari yang seharusnya. Macro berikut ini dibuat secara manual dan merupakan cara yang lebih efisien dan sederhana untuk melakukan aksi yang sama. Contoh berikut ini juga mendemonstrasikan bahwa VBA tidak selalu harus memilih cell tertentu sebelum menulis data. Sub Macro3() ActiveCell.Offset(0, ActiveCell.Offset(0, ActiveCell.Offset(0, ActiveCell.Offset(0, ActiveCell.Offset(0, ActiveCell.Offset(0, End Sub

0) 1) 2) 3) 4) 5)

= = = = = =

“Jan” “Feb” “Mar” “Apr” “May” “Jun”

Bahkan kita bisa lebih menyederhanakan lagi kode di atas menjadi lebih efisien dengan menggunakan struktur With-End With sebagai berikut :


53

Programming in Microsoft Excel 2000 Sub Macro4() With ActiveCell .Offset(0, .Offset(0, .Offset(0, .Offset(0, .Offset(0, .Offset(0, End With End Sub

0) 1) 2) 3) 4) 5)

= = = = = =

“Jan” “Feb” “Mar” “Apr” “May” “Jun”

Personal Macro Workbook

Jika kita merasa bahwa macro-macro yang kita buat sangat berguna dan akan sering digunakan di worksheet lain, kita bisa menaruh macro-macro tersebut di dalam Personal Macro Workbook (PMW). Workbook ini (Personal.xls) harus ditaruh di folder XLStart. Kapan saja kita memulai Excel, workbook ini akan dipanggil secara otomatis dan tersembunyi sehingga tidak bisa disunting. Ketika kita membuat macro, salah satu opsi yang ada mengizinkan kita untuk menyimpannya di PMW. Yang perlu dicatat adalah Personal Macro Workbook ini tidak akan tercipta jika tidak ada macro yang disimpan di dalamnya.

Membersihkan hasil perekaman macro Dalam contoh sebelumnya, kita bisa melihat bahwa hanya dengan satu aksi sederhana (merubah opsi Landscape) bisa dihasilkan kode VBA yang begitu banyak. Memang, dalam kebanyakan kasus, kode VBA hasil perekaman macro mengandung kode ‘ekstra’ (yang tidak dibutuhkan) yang tentu bisa kita hapus. Perlu dimengerti juga bahwa kode yang dihasilkan oleh macro recorder tidak selalu menghasilkan kode yang paling efisien. Jika kita meneliti beberapa kode yang dihasilkan oleh macro recorder, kita bisa lihat bahwa Excel menghasilkan kode berdasarkan objek yang sedang dipilih dan menggunakan objek Selection pada kode-kode selanjutnya. Sebagai contoh, di bawah adalah kode yang dihasilkan macro recorder ketika kita memilih range tertentu lalu mengubah number formatting dan menebalkan (bold) dan memiringkan (italic) huruf melalui toolbar Formatting : Range(“A1:C5”).Select Selection.NumberFormat = “#,##0.00” Selection.Font.Bold = True Selection.Font.Italic = True

CATATAN … Jika kita menggunakan kotak dialog Formatting dalam merekam macro ini, kita akan mendapatkan lebih banyak lagi kode ekstra. Merekam macro dengan menggunakan tombol di toolbar akan menghasilkan kode yang lebih efisien.

Kode di atas sebenarnya bisa disederhanakan lagi dengan menggunakan struktur With-End With menjadi sebagai berikut : Range(“A1:C5”).Select With Selection .NumberFormat = “#,##0.00” .Font.Bold = True .Font.Italic = True End With Atau kita bisa menghilangkan penggunaan metode Selection dan menulis seperti ini : With Range(“A1:C5”) .NumberFormat = “#,##0.00” .Font.Bold = True .Font.Italic = True End With


54

Programming in Microsoft Excel 2000 Kita, tentu saja, harus mengerti VBA terlebih dahulu sebelum mulai untuk membersihkan kode VBA hasil rekaman. Tapi untuk sekarang ini, kita perlu tahu bahwa kode VBA hasil rekaman dengan macro recorder bukanlah yang terbaik.

Objek dan Koleksi Jika kita membaca modul 2 ini dari bagian pertama, kita tahu bahwa Excel terdiri dari komponen yang disebut object (objek) dan collection (koleksi). Kita juga tahu bahwa Excel memiliki hirarki dalam penyusunan objek dan koleksi yang ada. Contohnya Worksheet adalah objek dari koleksi Worksheets. Jika kita ingin mengakses salah satu anggota objek dari suatu koleksi, Worksheet misalnya, kita bisa melakukannya dengan cara : Worksheets(“Sheet1”) ← menunjuk ke Worksheet bernama Sheet1 Atau : Worksheets(1) ← menunjuk ke Worksheet dengan urutan pertama

CATATAN … Selain itu ada satu koleksi yang bernama Sheets, merupakan koleksi dari seluruh sheet yang ada (Worksheet atau Chartsheet).

Ketika kita mereferensikan objek dengan menggunakan VBA, kita harus menggunakan tanda titik (disebut juga ‘operator titik – dot operator) sebagai penghubung antara objek. Sekarang bagaimana jika terdapat dua workbook yang terbuka dan masing-masing memiliki worksheet yang namanya sama-sama Sheet ? Penyelesaiannya adalah dengan menambahkan referensi objek penampung (container) yang dimiliki objek tersebut sebagai berikut : Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“A1”) atau Application.Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“A1”)

CATATAN … Berbeda dengan dugaan kita, Excel tidak menyediakan objek Cell yang menunjuk ke satu cell tunggal. Satu cell adalah sama saja denga objek Range yang hanya memiliki satu elemen.

Kita juga sudah tahu bahwa objek memiliki properties (properti). Contohnya objek Range yang memiliki properti Value, dimana dengan properti ini kita bisa mendapatkan nilai dari range tertentu atau mengisi nilai ke range tertentu. Perhatikan contoh aplikasinya : Sub TampilkanNilai() Answer = Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“A1”) MsgBox Answer End Sub Sub UbahNilai() Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“A1”) = 123 End Sub Selain properti, objek juga memiliki methods (metode). Contohnya objek Range yang memiliki metode Clear untuk menghapus isi range tertentu atau metode Copy untuk meng-copy isi range tertentu ke range yang lainnya. Perhatikan contoh aplikasinya :


55

Programming in Microsoft Excel 2000 Sub HapusRange() Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“A1:C3”).Clear End Sub Sub CopyRange() Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“A1”).Copy _ Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“B1”) End Sub

Menentukan argumen untuk Metode dan Properti

Salah satu hal yang kadang bisa membuat bingung programmer adalah penggunaan argumen/parameter yang dibutuhkan oleh metode dan properti. Metode terkadang memerlukan argumen sebagai bantuan dalam menjalankan proses yang terdapat di dalamnya, dan properti memerlukan argumen sebagai bantuan dalam pengubahan nilai yang dikandungnya. Untuk beberapa metode dan properti, penggunaan argumen bersifat opsional. Untuk metode yang menggunakan argumen, tempatkan argumen setelah nama metode dengan dipisahkan tanda koma untuk masing-masing argumen. Jika metodenya menggunakan argumen yang opsional, kita bisa menyisipkan argumen kosong. Contohnya adalah metode Protect milik objek Workbook. Coba buka fasilitas Help milik Excel, dan kita bisa melihat bahwa metode Protect memiliki 3 argumen : Password, Structure, dan Windows. Argumen tersebut merujuk ke opsi yang ada di kotak dialog Protect Workbook. Berikut adalah contoh penggunaan argumen untuk metode Protect : Workbook(“MyBook.xls”).Protect “xxx”, True, True Jika kita tidak ingin memberi password, maka kita bisa menggunakan perintah seperti : Workbook(“MyBook.xls”).Protect , True, True Perhatikan bahwa argumen pertama dihilangkan pada perintah di atas tetapi tetap menyertakan tanda koma sebagai pemisah dengan argumen berikutnya. Tetapi ada pendekatan yang lebih baik yang menjadikan kode VBA kita mudah dibaca, yaitu dengan menggunakan nama argumen yang bersangkutan. Ini contoh yang lain dari perintah sebelumnya : Workbook(“MyBook.xls”).Protect Structure:=True, Windows:=True Penggunaan nama argumen seperti di atas dapat sangat membantu, khususnya jika berhubungan dengan metode yang memiliki banyak argumen dan kita hanya perlu menggunakan sedikit saja. Untuk properti yang memerlukan argumen, kita harus menempatkan argumen tersebut dengan diapit oleh tanda kurung. Sebagai contoh, properti Address yang dimiliki oleh objek Range memiliki lima argumen, dan semuanya adalah opsional. Perintah berikut ini adalah salah karena tanda kurungnya dihilangkan : MsgBox Range(“A1”).Address False

‘perintah yang salah

Yang benar adalah yang menggunakan tanda kurung sebagai berikut : MsgBox Range(“A1”).Address(False) Atau dengan menyertakan nama argumen seperti berikut : MsgBox Range(“A1”).Address(rowAbsolute:=False) Konsep ini akan semakin kita pahami nanti dengan ‘menyelam’ lebih jauh ke dalam VBA.

Menggunakan Sistem Help Excel Cara termudah untuk mendapatkan informasi dan bantuan mengenai suatu objek, properti, atau metode adalah dengan mengetik kata yang bersangkutan di Code Window lalu menekan F1. Jika kata yang kita ketik memiliki banyak kesamaan topik bantuan, maka akan tampil kotak dialog yang berisi daftar topik dimana kita bisa memilih topik mana yang akan kita tampilkan. Tapi sayangnya terkadang daftar yang diberikan tidak begitu jelas maksudnya, sehingga kita perlu untuk mencoba satu-persatu hingga ketemu topik yang kita inginkan. Contohnya adalah jika kita mengetik kata Comment maka akan muncul dua daftar topik, yang pertama adalah objek Comment dan yang kedua adalah properti Comment.

Mengetahui Objek yang Aktif Seperti kita tahu, ketika kita bekerja dengan Excel, hanya satu workbook dan satu sheet yang bisa aktif dalam satu waktu, dan terkadang hanya satu cell yang aktif dalam satu waktu. VBA mengizinkan kita untuk mengakses objek yang aktif dengan cara yang mudah. Ini terkadang sangat berguna karena kita tidak selalu tahu workbook, worksheet, atau range mana yang aktif dan sedang digunakan. VBA menangani ini dengan menyediakan beberapa properti yang


56

Programming in Microsoft Excel 2000 terdapat di dalam objek Application. Misalnya ActiveCell yang digunakan untuk mengetahui cell yang aktif. Perintah berikut ini akan memberikan nilai 1 ke cell yang aktif : ActiveCell.Value = 1 Perhatikan bahwa penggunaan objek Application dihilangkan pada perintah di atas, karena sebenarnya sudah digunakan secara default. Dan perlu diperhatikan bahwa perintah di atas akan gagal jika sheet yang aktif bukan worksheet. Jika VBA menjalankan perintah di atas ketika chartsheet sedang aktif, maka kita akan menerima pesan kesalahan. Dan jika yang aktif adalah range, maka cell yang berada di sudut pertama (sudut yang dipilih tergantung akan bagaimana cara kita memilih range yang bersangkutan) akan dipilih. Dengan kata lain, ActiveCell akan selalu mengambil satu cell tunggal. Objek Application juga memiliki properti Selection yang mengembalikan referensi ke segala jenis objek, yang dapat berupa cell tunggal, range, atau objek lain seperti ChartObject, TextBox, atau Shape. Tabel di bawah ini memberikan beberapa properti milik Application yang berguna ketika kita bekerja dengan cell dan range.

Beberapa properti milik objek Application Properti ActiveCell ActiveSheet ActiveWindow ActiveWorkbook RangeSelection Selection ThisWorkbook

Objek yang dikembalikan Cell yang aktif. Sheet (worksheet atau chartsheet) yang aktif. Jendela yang aktif. Workbook yang aktif. Range/cell (saja) yang dipilih di worksheet dalam jendela tertentu, tidak termasuk objek grafik yang ada. Objek yang dipilih (dapat berupa Range, Shape, dan lain-lain). Workbook dimana kode VBA yang sedang dijalankan berada.

Keuntungan dari menggunakan properti di atas adalah kita tidak perlu tahu cell, worksheet, atau workbook mana yang sedang aktif. Perhatikan contoh-contoh berikut ini : ActiveCell.ClearContents MsgBox ActiveSheet.Name MsgBox ActiveWorkbook.Name

'menghapus isi suatu cell yang sedang aktif. 'menampilkan nama sheet yang sedang aktif. 'menampilkan nama workbook yang sedang aktif.

Jika suatu range dalam worksheet sedang dipilih, kita bisa mengisi seluruh cell yang ada di range tersebut dengan suatu nilai hanya menggunakan satu perintah. Properti Selection pada contoh berikut akan mengembalikan objek Range yang sedang dipilih dan mengubah properti Value dengan suatu nilai : Selection.Value = 12 Perlu diperhatikan juga bahwa jika yang sedang dipilih bukan objek Range (seperti ChartObject atau Shape), maka kemungkinan VBA akan menghasilkan pesan kesalahan karena tidak semua objek memiliki properti Value. Sekarang perhatikan contoh berikut yang hanya mengisi nilai 12 ke dalam objek Range terakhir sebelum objek non-Range dipilih. Jika kita mencarinya di fasilitas Help, kita bisa melihat bahwa properti ini hanya bekerja di objek Window saja. ActiveWindow.RangeSelection.Value = 12

Bekerja dengan objek Range


57

Programming in Microsoft Excel 2000 Sebagian besar hal yang akan terlibat jika kita bekerja dengan VBA adalah cell dan range di dalam worksheet. Ada 3 cara dalam mereferensikan objek Range di dalam kode VBA, yaitu : menggunakan properti Range; menggunakan properti Cells; dan menggunakan properti Offset. Properti Range Jika kita melihat fasilitas Help, properti Range memiliki dua syntax, yaitu : object.Range(cell1) object.Range(cell1, cell2) Jika kita menggunakan syntax pertama, maka alamat range ditulis secara langsung di argumen cell1, tetapi jika menggunakan syntax kedua, maka cell kiri atas range ditulis di argumen cell1 dan cell kanan bawah range ditulis di argumen cell2. Berikut adalah contoh-contoh penggunaan properti Range yang terdapat pada objek Worksheet : Worksheets(“Sheet1”).Range(“A1”).Value = 1 ‘memberi nilai 1 di cell A1 Worksheets(“Sheet1”).Range(“Input”).Value = 1 ‘memberi nilai 1 di alamat Input ActiveSheet.Range(“A1:B10”).Value = 2 ‘memberi nilai 2 di range A1:B10 Range(“A1”, “B10”) = 2 ‘memberi nilai 2 di range A1:B10 Range(“C1:C10 A6:E6) = 3 ‘memberi nilai 3 pada cell perpotongan range C1:C10 dengan A6:E6 Range(“A1,A3,A5,A7,A9”) = 4 ‘memberi nilai 4 pada cell yang berbeda-beda Sejauh ini kita sudah mengetahui beberapa contoh penggunaan properti Range yang terdapat pada objek Worksheet. Seperti yang sudah saya sebutkan sebelumnya, kita juga bisa menggunakan properti Range yang terdapat pada objek Range. Bingung ? Perhatikan penjelasan berikut. Berikut adalah contoh dimana sebuah range dipilih (anggap saja B2:C5) pada worksheet yang aktif, dan jika kita menggunakan properti ActiveCell maka ia akan menunjuk pada cell B2. Lalu kita ingin mengisi cell C3 dengan nilai 10, maka perintahnya dapat seperti ini : ActiveCell.Range(“B1”).Value = 10 Bisa dilihat di atas bahwa ActiveCell menunjuk pada cell B2, dan jika kita menggunakan properti Range(“B1”) maka yang akan dirujuk adalah satu cell di kanan bawah cell B2, yaitu cell C3. Jadi pengalamatan Range(“B1”) di atas adalah relatif terhadap cell B2. Untuk lebih jelasnya, coba perhatikan beberapa contoh di bawah ini : Range("C3").Range("A1") Range("C3").Range("E2") Range("C3:E7").Range("E2") Range("C3:E7").Range("A1:D3")

‘sama ‘sama ‘sama ‘sama

dengan dengan dengan dengan

Range(“C3”) Range(“G4”) Range(“G4”) Range(“C3:F5”)

Masih bingung ? Untung masih ada satu cara yang lebih mudah untuk pengalamatan relatif, yaitu dengan menggunakan properti Offset yang akan kita bahas kemudian. Properti Cells


58

Programming in Microsoft Excel 2000 Cara lain untuk mengakses alamat pada objek Range adalah dengan menggunakan properti Cells. Sama seperti properti Range, properti Cells juga terdapat dalam objek Worksheet dan Range. Bukalah sistem Help, dan kita bisa melihat bahwa terdapat 3 syntax properti Cells : object.Cells(rowindex,columnindex) object.Cells(rowindex) object.Cells Seperti kita tahu bahwa satu worksheet di dalam Excel terdiri dari 256 kolom (nomor urut 1-256 atau A-IV) dan 65536 baris (nomor urut 1-65536). Nah, kita akan melihat beberapa contoh penggunaan properti Cells yang menggunakan dua argumen sebagai berikut : Worksheets(“Sheet1”).Cells(1,1) = 9 ‘mengisi cell A1 ActiveSheet.Cells(3,4) = 7 ‘mengisi cell D3 ActiveCell.Cells(2,1) = 5 ‘mengisi cell yang terletak dua baris di bawah dan satu kolom di kanan cell yang aktif. Syntax kedua dari properti Cells adalah yang menggunakan satu argumen yang berkisar antara 1 sampai 16777216 (angka ini merupakan total keseluruhan cell yang dalam worksheet : 256 kolom x 65536 baris). Nomor urutan cell dimulai dari cell A1 dan lanjut ke kanan lalu ke bawah menuju baris berikutnya. Cell ke-256 adalah cell IV1; ke-257 adalah cell A2. Berikut ini contoh untuk memasukkan nilai 2 ke cell H3 (yang merupakan cell ke-520) di worksheet yang aktif : ActiveSheet.Cells(520) = 2 Range(“A1:D10”).Cells(5) = 2000

‘mengisi cell H3 ‘mengisi cell A2

Syntax ketiga dari properti Cells adalah yang tidak menggunakan argumen apapun. Syntax ini digunakan untuk mengembalikan seluruh cell yang terdapat dalam satu worksheet. Misalnya kode berikut digunakan untuk menghapus seluruh cell yang ada di dalam worksheet yang aktif : ActiveSheet.Cells.ClearContents Properti Offset Properti Offset (sama seperti properti Range dan Cells) juga mengembalikan objek Range. Hanya bedanya, properti Offset hanya terdapat dalam objek Range tapi tidak terdapat dalam objek Worksheet. Syntaxnya adalah sebagai berikut : object.Offset(rowOffset,columnOffset) Properti Offset membutuhkan dua argumen yang menentukan posisi relatif kolom dan baris dari cell kiri atas pada objek Range yang bersangkutan. Kedua argumen yang dibutuhkan bisa berupa bilangan negatif atau positif atau nilai 0. Contoh berikut ini akan memperjelas maksudnya : ActiveCell.Offset(1,0).Value = 12 ‘mengisi satu cell di bawah cell yang aktif ActiveCell.Offset(-1,0).Value = 12 ‘mengisi satu cell di atas cell yang aktif

CATATAN … Jika posisi cell hasil dari properti Offset berada di luar batas maka Excel akan menampilkan pesan kesalahan. Contohnya : MsgBox Range(“C1”).Offset(-1,0).Address

‘error


59


LATIHAN Buatlah prosedur untuk membuat 6 nama bulan berturut-turut yang ditempatkan mulai dari cell A1 lalu bergerak diagonal ke B2 dan seterusnya ! Modifikasilah program nomor 1 sehingga ketika dijalankan hasilnya tidak dimulai di cell A1, melainkan di cell yang aktif saat itu. Buatlah program untuk mengisi satu cell tertentu dengan sembarang nilai lalu copylah isi cell tersebut ke kanan sebanyak 5 kolom dan ke kiri sebanyak 5 baris dengan menggunakan properti Range di dalam objek Range ! Modifikasilah program nomor 3 dengan menggunakan properti Offset ! Buatlah program untuk mengisi nilai 1 – 12 pada range A1:D3 dengan menggunakan properti Cells (dengan satu parameter) di dalam objek Range ! Modifikasilah program nomor 5 agar hasilnya tidak selalu pada range A1:D3 melainkan pada range mana saja yang memiliki ukuran 4 kolom x 3 baris !

Pemrograman Dasar VBA Pada bagian sebelumnya, kita sudah berkenalan dengan VBA, sekarang kita akan memasuki dunia pemrograman VBA yang lebih mendalam. Pada bagian ini kita akan membahas beberapa elemen dan konsep pemrograman dalam VBA. Bagi yang sudah belajar bahasa pemrograman tertentu, mungkin akan cepat menyesuaikan. Dan bagi yang belum, siap-siap saja memasuki ‘dunia lain’ dari ilmu komputer.

Bahasa VBA : Sekilas Pandang Dalam bagian ‘Sekilas Pemrograman VBA’ sudah kita bahas beberapa penggunaan objek, properti, dan metode. Tetapi kita belum membahas bagaimana cara memanipulasi objek-objek tersebut sehingga menjadi sesuatu yang berguna. Nah, bagian ini akan ‘mengupas’ dan membantu kita ‘menjelajahi’ elemen-elemen bahasa VBA, perintah, dan struktur pemrograman VBA yang akan kita gunakan dalam menyusun modul-modul VBA. Untuk memulainya, kita akan coba melihat pada prosedur sederhana yang tersimpan dalam modul VBA. Prosedur di bawah ini yang memiliki fungsi untuk menghitung total nilai 1 sampai 100. Ketika sudah selesai, prosedur ini akan menampilkan pesan yang berisi hasilnya. Sub VBA_Demo() ' Ini adalah contoh VBA yang sederhana Total = 0 For I = 1 To 100 Total = Total + I Next I MsgBox Total End Sub Prosedur di atas menggunakan beberapa elemen bahasa pemrograman standar, yaitu : Komentar (Comment), yaitu baris yang didahului oleh tanda kutip tunggal ('); Variabel (Variable), yaitu Total;


60

Programming in Microsoft Excel 2000 Dua perintah pemberian nilai (Assignment), yaitu Total = 0 dan Total = Total + I; Struktur pengulangan (Looping Structure), yaitu For—Next; dan Perintah VBA, yaitu MsgBox.

Cara Memasukkan Kode VBA Kode VBA, yang terdapat dalam modul VBA, terdiri dari perintah-perintah. Sebagai latihan, langkah yang paling baik adalah memasukkan satu perintah per satu baris. Tetapi, bagaimanapun, ini bukan suatu keharusan; kita bisa menggunakan tanda titik dua (:) untuk memisahkan dua perintah dalam satu baris. Contoh berikut ini menggunakan 4 perintah dalam 1 baris : Sub SatuBaris() x = 1: y = 2: z = 3: MsgBox x+y+z End Sub Sebagian besar programmer lebih memilih untuk memasukkan satu perintah per satu baris seperti di bawah ini : Sub BanyakBaris() x = 1 y = 2 z = 3 MsgBox x+y+z End Sub Masing-masing baris dapat sepanjang yang kita suka; Code Window akan secara otomatis menggulung jika kode yang diketik terlalu panjang. Tetapi jika kita memiliki satu perintah yang panjang sekali, kita bisa menggunakan tanda penghubung perintah dalam VBA yaitu tanda underscore (_) dengan didahului oleh spasi. Contohnya : Sub BarisBersambung() NilaiTotal = _ Worksheets(“Sheet1”).Range(“A1”).Value + _ Worksheets(“Sheet2”).Range(“A1”).Value End Sub Ketika kita merekam macro, terkadang Excel menggunakan underscore untuk memisahkan perintah panjang menjadi beberapa baris. Setelah kita memasukkan suatu kode perintah, VBA secara otomatis akan melakukan langkah-langkah berikut untuk mempermudah pembacaan perintah : VBA akan menyisipkan spasi antara operator. Jika kita mengetik Jawab=1+2 (tanpa spasi), sebagai contoh, VBA akan mengubahnya menjadi : Ans = 1 + 2 VBA akan mengubah besar/kecilnya huruf untuk kata kunci (keywords), properti, dan metode. Jika kita mengetik result=activesheet.range(“a1”).value=12, VBA akan mengubahnya menjadi : Result = ActiveSheet.Range(“a1”).Value = 12 Karena penamaan variabel dalam VBA tidak case-sensitive, maka VBA secara otomatis akan mengubah nama variabel dengan besar kecilnya huruf disamakan dengan besar kecilnya huruf variabel yang terakhir diketik. Sebagai contoh, jika pertama kali kita mengetikkan nama variabel nilaiku (semuanya huruf kecil) lalu pada perintah berikutnya kita mengetikkan NilaiKu, maka VBA secara otomatis akan mengubah seluruh nama variabel nilaiku menjadi NilaiKu. Pengecualian jika kita mendeklarasikan variabel (dengan menggunakan Dim, Private, atau Public); dalam hal ini seluruh besar kecilnya huruf nama variabel tersebut akan disesuaikan dengan besar kecilnya huruf nama variabel yang dideklarasikan. VBA akan memindai setiap perintah akan adanya syntax error. Jika VBA menemukan syntax error dalam suatu perintah, maka perintah tersebut akan diberi warna yang berbeda dan akan menampilkan pesan yang menjelaskan kesalahan tersebut. Sebagai catatan, agar VBA melakukan pemeriksaan syntax error, aktifkan opsi Auto Syntax Check yang terdapat dalam tab Editor di kotak dialog Tools→Options….

Komentar Comment (komentar) adalah teks yang berfungsi sebagai penjelas yang ditaruh ke dalam kode VBA kita. Komentar ini nantinya akan diabaikan oleh VBA. Komentar hanya digunakan sebagai pemerjelas atau sebagai gambaran terhadap perintah yang ada.


61

Programming in Microsoft Excel 2000 Komentar ditandai dengan tanda kutip tunggal (') atau dengan keyword Rem. Kita bisa menggunakan satu baris penuh sebagai komentar, atau kita bisa menyisipkannya di akhir setiap perintah dalam

baris yang sama (khusus untuk penggunaan tanda kutip).

VBA akan

mengabaikan segala teks yang berada setelah komentar—kecuali jika tanda komentar tersebut berada di antara tanda kutip ganda (sebagai jenis string). Contohnya, perintah berikut ini tidak mengandung komentar, walaupun terdapat tanda kutip tunggal : Msg = “Hari Jum’at” Contoh berikut ini menunjukkan prosedur VBA dengan tiga buah komentar : Sub Komentar() Rem Prosedur ini untuk mengisi nilai 1 x = 1 'x diisi nilai 1 'Tampilkan hasilnya MsgBox x End Sub Dalam menyusun komentar, perlu diperhatikan hal-hal berikut ini : Gunakan komentar untuk menggambarkan tujuan setiap prosedur yang kita buat. Gunakan komentar jika terjadi perubahan logika atau kode di dalam prosedur. Gunakan komentar untuk menjelaskan fungsi dan tujuan masing-masing variabel. Jadi kita dan orang lain dapat mudah memahami maksud dari masing-masing variabel. Jangan gunakan komentar pada setiap perintah. Gunakan komentar pada perintah yang memerlukan penjelasan saja. Tulislah komentar sebelum kode ditulis dan jangan sesudah kode ditulis.

62 CATATAN … Terkadang kita ingin mencoba menggunakan perintah yang baru untuk menggantikan perintah yang lama dalam prosedur yang kita buat. Dan kita ingin memastikan apakah perintah yang baru tersebut berhasil dengan baik sehingga perintah yang lama harus dihilangkan. Nah, daripada perintah yang lama dihapus, lebih baik jika perintah yang lama tersebut diberi tanda komentar, sehingga jika terjadi kesalahan kita masih bisa mengambil perintah yang lama tersebut. VBA akan mengabaikan perintah jika diberi tanda komentar. Untuk mengembalikannya lagi, hilangkan saja komentarnya.

Variabel, Jenis Data, dan Konstanta Tujuan utama dari VBA adalah memanipulasi data. Sebagian data terdapat dalam objek, seperti range dalam worksheet. Data lainnya tersimpan dalam variabel yang bisa kita ciptakan. Variables (variabel) adalah tempat penyimpanan data yang memiliki nama tertentu yang terdapat di dalam memori komputer. Variabel dapat menampung jenis data yang berbeda-beda— mulai dari nilai Boolean (True atau False) hingga bilangan desimal dengan presisi ganda. Salah satu hal yang kita harus perhatikan dalam pembuatan nama variabel adalah dengan memberi nama sejelas-jelasnya. VBA memiliki aturan dalam pembuatan nama variabel, yaitu : Kita bisa menggunakan huruf, angka, dan beberapa tanda baca untuk nama variabel, tetapi karakter pertama harus berupa huruf. VBA tidak case-sensitive dalam masalah penamaan variabel. Untuk menjadikan nama variabel lebih mudah dibaca, programmer biasanya menggabungkan antara huruf besar dan huruf kecil (sebagai contoh : StokBarang daripada stokbarang).


Programming in Microsoft Excel 2000 VBA tidak mengizinkan kita untuk menggunakan spasi atau tanda titik dalam penamaan variabel. Untuk menjadikan lebih mudah dibaca, programmer biasanya menggunakan tanda underscore (contoh : Stok_Barang dan bukannya Stok Barang). Karakter tertentu (#, $, %, &, atau !) tidak dapat digunakan dalam penamaan variabel. Panjang nama variabel tidak boleh lebih dari 254 karakter—lagupula belum pernah ada orang yang mau membuat nama variabel sepanjang itu. Berikut adalah contoh pemberian nilai ke variabel tertentu yang menggunakan jenis data yang berbeda-beda. Nama variabel terletak pada sebelah kiri tanda sama dengan, dan nilai yang diberikan terletak pada sebelah kanan tanda sama dengan. x = 1 SafetyStock = 0.75 Daily_Stock = 123450 SudahSelesai = False x = x + 1 NilaiKu = NilaiMu * 1.25 NamaUser = “Aya Brea” TanggalMulai = #3/14/2001# Yang perlu diingat adalah VBA banyak memiliki reserved word yang tidak boleh digunakan sebagai nama variabel. Jika kita mencoba menggunakannya, maka VBA akan mengeluarkan pesan kesalahan. Contohnya adalah jika kita mengetik : Next

=

132, maka VBA akan

mengeluarkan pesan kesalahan karena kata Next merupakan reserved word yang digunakan struktur For—Next. Sayangnya, pesan kesalahan yang diberikan tidak menggam-barkan kesalahan yang sebenarnya. pesan kesalahan yang muncul adalah: Compile Error: Expected: variable. Mungkin akan lebih ‘mengena’ jika pesannya seperti : Reserved word used as a variable. Jadi jika menerima pesan kesalahan yang agak aneh, cobalah periksa sistem Help untuk memastikan bahwa kita tidak menggunakan reserved word. Menentukan Jenis Data Setiap variabel yang kita deklarasikan sebaiknya memiliki jenis tertentu, apakah itu bilangan atau teks. Jika kita tidak mendeklarasikan variabel dengan jenis data tertentu, maka VBA akan menggunakan jenis data Variant yang bisa menampung semua jenis data. Berikut adalah tabel yang memuat jenis-jenis data yang sudah disediakan oleh VBA sebelumnya.

Jenis Data yang sudah disediakan VBA Jenis Data Byte Boolean Integer Long Single

Byte 1 byte 2 byte 2 byte 4 byte 4 byte

Double

8 byte

Currency Decimal

8 byte 14 byte

Date Object String

8 byte 4 byte 10 byte + panjang string Panjang string

(variable-length)

String

(fixed-length)

Batasan Kandungan Isi/Nilai 0 s/d 255 True atau False -32,768 s/d 32,767 -2,147,483,648 s/d 2,147,483,647 -3.402823E38 s/d –1.401298E-45 (untuk bilangan negatif) 1.401298E-45 s/d 3.402823E38 (untuk bilangan positif) -1.79769313486232E308 s/d –4.94065645841247E-324 (untuk bilangan negatif); 4.94065645841247E-324 s/d 1.79769313486232E308 (untuk bilangan positif) -922,337,203,685,477.5808 s/d 922,337,203,685,477.5807 +/- 79,228,162,514,264,337,593,543,950,335 tanpa nilai desimal; +/- 7.9228162514264337593543950335 dengan 28 bilangan desimal January 1, 0100 s/d December 31, 9999 Semua referensi ke segala macam objek 0 hingga rata-rata 2 milyar karakter 1 hingga rata-rata 65,400 karakter


63

Programming in Microsoft Excel 2000 Variant

16 byte

Semua jenis bilangan dengan batasan jenis data Double

Variant

22 byte + panjang string Beragam

0 hingga rata-rata 2 milyar karakter

(with numbers) (with characters)

User defined

tergantung elemen yang terdapat di dalamnya

Perhatikan : tanda , digunakan sebagai pemisah ribuan dan tanda . digunakan sebagai pemisah desimal. Dan tanda En = 10n.

CATATAN … Sebaiknya kita hanya menggunakan jenis data yang membutuhkan byte terkecil tetapi mampu menampung semua nilai yang kita inginkan. Ketika VBA bekerja dengan data, kecepatan proses dipengaruhi oleh jumlah byte yang dibutuhkan, jadi semakin sedikit byte yang dibutuhkan maka proses akan menjadi semakin cepat. Jenis data Decimal adalah jenis data baru yang terdapat dalam Excel 2000. Jenis data ini adalah bukan jenis data biasa karena jenis data ini tidak bisa dideklarasikan. Kita bisa menggunakan fungsi VBA CDec untuk mengubah jenis data Variant menjadi Decimal.

Mendeklarasikan Variabel Jika kita tidak mendeklarasikan suatu variabel yang akan digunakan dalam program, VBA akan menggunakan jenis data default, yaitu Variant. Data yang disimpan dalam jenis Variant bersifat seperti bunglon: ia dapat berubah jenis, tergantung apa yang akan kita lakukan dengannya. Prosedur di bawah ini menunjukkan bagaimana suatu variabel dapat berubah jenisnya. Sub DemoVariant() MyVar = “123” MyVar = MyVar / 2 MyVar = “Nilai = ” & MyVar MsgBox MyVar End Sub Dalam prosedur di atas, MyVar bermula dengan jenis string. Lalu string ini dibagi dua dan menjadi jenis bilangan. Selanjutnya, MyVar digabungkan dengan string, dan menyebabkan MyVar kembali menjadi jenis string. Dan perintah MsgBox akan menampilkan pesan : Nilai = 61.5. Menentukan Jenis Data Kita bisa menggunakan fungsi VBA TypeName untuk mengetahui jenis data suatu variabel. Berikut adalah modifikasi program sebelumnya yang menambahkan fungsi TypeName pada tiap langkah untuk mengetahui jenis data yang dihasilkan per langkah. Sub DemoVariant2() MyVar = “123” MsgBox TypeName(MyVar) MyVar = MyVar / 2 MsgBox TypeName(MyVar) MyVar = “Nilai = ” & MyVar MsgBox TypeName(MyVar) MsgBox MyVar End Sub

Keuntungan Deklarasi Variabel Dengan mendeklarasikan suatu variabel beserta jenis datanya, kita akan mendapatkan dua keuntungan, yaitu : Program kita akan berjalan lebih cepat dan menggunakan memori lebih efisien. Jenis data default VBA, Variant, menyebabkan VBA melakukan pemeriksaan jenis data berulang-ulang dan menggunakan memori lebih dari yang biasanya. Jika VBA mengetahui jenis data suatu variabel, maka pemeriksaan jenis data yang berulang-ulang tersebut tidak akan dilakukan, dan akan menghemat penggunaan memori. Dapat menghindari masalah akibat kesalahan pengetikan nama variabel. Misalnya kita memiliki variabel yang tidak


64

Programming in Microsoft Excel 2000 dideklarasikan bernama CurrentRate. Lalu, pada baris tertentu di bawahnya kita menyisipkan perintah CurentRate = 0.75. Kesalahan seperti inilah yang sulit dilacak, dan akan menyebabkan program kita menghi-tung dan menghasilkan nilai yang salah.

Memaksa Pendeklarasian Semua Variabel Untuk memaksa kita mendeklarasikan semua variabel yang kita gunakan, tambahkan perintah berikut ini dalam modul VBA kita. Option Explicit Perintah di atas akan menyebabkan program untuk berhenti ketika VBA menemukan nama variabel yang belum dideklarasikan sebelumnya. VBA kemudian akan menampilkan pesan kesalahan, dan kita harus mendeklarasikan variabel tersebut.

CATATAN … Agar perintah Option Explicit ditambahkan secara otomatis oleh VBA ketika kita membuat modul VBA baru. Aktifkan opsi yang terletak di Tools→Options…→Editor→Require Variable Declaration.

Lingkup Suatu Variabel Lingkup/jangkauan (scope) suatu variabel menentukan modul dan prosedur mana saja yang bisa menggunakan variabel tersebut. Lingkup suatu variabel dapat salah satu di antara :

Lingkup Variabel Lingkup Satu prosedur Satu modul Seluruh modul

Bagaimana Variabel tersebut harus dideklarasikan Gunakan perintah Dim atau Static (dalam pendeklarasian) di dalam prosedur yang bersangkutan. Gunakan perintah Dim atau Private sebelum prosedur pertama di dalam modul. Gunakan perintah Public sebelum prosedur pertama di dalam modul.

Contoh berikut ini menggambarkan deklarasi beberapa variabel lokal (local variables) yang hanya dikenal di satu prosedur saja. Sub MySub() Dim x As Integer Dim UserName As String * 20 Dim NilaiKu Dim Nilai1 As Single Dim InterestRate As Single End Sub Kita juga bisa mendeklarasikan beberapa variabel dalam satu baris, misalnya : Dim x As Integer, y As Integer, z As Integer Private Nilai1 As Long, Nilai2 As Long

CATATAN … Tidak seperti bahasa pemrograman lain, VBA tidak mengizinkan pendeklarasian beberapa variabel terhadap satu jenis data dengan menggunakan tanda koma sebagai pemisahnya. Sebagai contoh, perintah berikut, walaupun sah, tidak mendeklarasikan seluruh variabel berjenis Integer : Dim i, j, k As Integer Dalam VBA, hanya variabel k saja dideklarasikan sebagai Integer; sisanya dideklarasikan sebagai Variant.


65


Sedangkan untuk mendeklarasikan variabel agar dikenal oleh seluruh prosedur yang terdapat dalam satu modul tertentu, perhatikan contoh deklarasi variabel di bawah ini : Dim CurrentValue As Integer Private RunningValue As Long Sub Sub_1() ... End Sub Sub Sub_2() ... End Sub Untuk mendeklarasikan variabel agar dikenal oleh seluruh modul dan seluruh prosedur yang terdapat di dalamnya, perhatikan contoh deklarasi variabel di bawah ini : Public CurrentRate As Long Sub Sub_1() ... End Sub Sub Sub_2() ... End Sub

Cara Lain Mendeklarasikan Variabel Seperti bahasa BASIC pendahulunya, VBA mengizinkan kita untuk menambahkan karakter tertentu pada nama variabel untuk mengindikasikan jenis datanya. Sebagai contoh, kita bisa mendeklarasikan variabel MyVar sebagai jenis Integer dengan cara : Dim MyVar% Berikut adalah karakter untuk mendeklarasikan jenis data yang didukung VBA. Jenis Data Karakter deklarasi Integer % Long & Single ! Double # Currency @ String $ Sebenarnya metode penggunaan karakter untuk pendeklarasian jenis data ini adalah untuk menjaga kompatibilitas dengan bahasa BASIC yang lama; sebaiknya metode ini dihindari dan gunakan cara pendeklarasian yang biasa saja.

Selain penggunaan perintah Private, Public, dan Dim, terdapat satu perintah deklarasi variabel lain yaitu Static (statis). Jenis variabel ini harus ditaruh di dalam suatu prosedur dan ketika keluar dari prosedur tersebut, nilai yang dikandung tidak akan hilang dan akan disimpan selama program berjalan. Biasanya jenis ini digunakan untuk membuat counter (variabel yang menyimpan nilai perhitungan yang bertambah atau berkurang secara konstan). Konsep ini berbeda dengan deklarasi variabel yang menggunakan Private atau Dim, di mana data yang disimpan akan hilang jika keluar dari prosedur. Untuk lebih jelasnya perhatikan contoh berikut ini : Sub Hitung1() Static Counter As Integer Counter = Counter + 1 MsgBox Counter End Sub

Sub Hitung2() Dim Counter As Integer Counter = Counter + 1 MsgBox Counter End Sub

Sub Utama1() Hitung1 Hitung1 Hitung1 End Sub

Sub Utama2() Hitung2 Hitung2 Hitung2 End Sub


66

Programming in Microsoft Excel 2000 Kedua program di atas terlihat hampir sama. Perbedaannya hanya ada di deklarasi variabel Counter antara prosedur Hitung1 dan Hitung2. Jika kita menjalankan Utama1 maka VBA akan menampilkan pesan dengan MsgBox yang isinya 1, lalu 2, lalu 3. Tetapi jika kita menjalankan Utama2 maka VBA akan menampilkan pesan dengan MsgBox yang isinya 1, lalu 1, lalu 1. Jelas ?

Konstanta Suatu variabel nilainya bisa, bahkan sering dilakukan, berubah-ubah ketika suatu perintah atau prosedur mengaksesnya. Terkadang, kita membutuhkan suatu data yang nilainya tidak bisa diubah-ubah. Jenis data ini disebut konstanta (constant). Kita bisa mendeklarasikan konstanta dengan menggunakan perintah Const seperti berikut : Const JumlahKuartal As Integer = 4 Const Rate = 0.275, Period = 12 Public Const ModName As String = “Budget Macros” Private Const AppName = “Budget Application”

CATATAN … Jika kita mencoba untuk mengubah isi suatu konstanta, maka VBA akan menampilkan pesan kesalahan.

Excel dan VBA memiliki banyak sekali konstanta yang sudah dideklarasikan (predefined constants) yang bisa kita gunakan tanpa harus mendeklarasikan lagi. Konstanta ini biasanya digunakan untuk memudahkan pengguna menggunakan suatu nilai hanya dengan menghapalkan nama harfiahnya. Contohnya : Application.Calculation = xlManual. Sebenarnya konstanta xlManual ini memiliki nilai yaitu –4135—yang mungkin agak sulit untuk dihapalkan.

Jenis Data String Seperti layaknya Excel, VBA juga bisa memanipulasi data berjenis bilangan dan teks (string). Ada dua jenis string di VBA, yaitu : Fixed-length string, string yang dideklarasikan dengan jumlah karakter tertentu. Panjang maksimal string ini adalah 65,535 karakter. Contohnya : Dim MyString As String * 50 Variable-length string, string yang dideklarasikan dengan jumlah karakter bebas. Panjang maksimal string ini adalah, secara teori, 2 milyar karakter. Contohnya : Dim YourString As String

Jenis Data Date Kita sebenarnya bisa saja menaruh data tanggal di dalam variabel jenis string, tetapi kita tidak akan bisa memprosesnya dengan perhitungan-perhitungan khas data tanggal. Nah, untuk itulah VBA menyediakan data berjenis tanggal (date). Dalam VBA kita bisa mengisi variabel jenis date dengan mengapitnya dengan tanda pound (#) seperti contoh berikut : Dim StartDate As Date Const FirstDay As Date = #1/1/2001# Const SiangHari = #12:00:00# StartDate = Now


67


CATATAN … Batasan data tanggal minimal yang mampu ditangani Excel lebih kecil jika dibandingkan dengan VBA. Jika VBA mampu menampung tanggal terkecil hingga January 1, 0100, Excel hanya mampu menampung hingga January 1, 1900. Jadi kita harus berhati-hati jika kita ingin mengisikan tanggal ke worksheet Excel melalui VBA.

Operator Perhitungan, Logika dan Perbandingan Seperti kita tahu, kita bisa mengisikan suatu nilai ke dalam suatu variabel dengan menggunakan tanda sama dengan (=). Nilai yang berada di sisi kanan tanda sama dengan tidak harus nilai tunggal melainkan bisa berupa gabungan beberapa nilai yang menggunakan perantara operator, Berikut diberikan daftar beberapa operator yang sering digunakan dalam proses.

Operator Perhitungan Operator + * / \ ^ & Mod

Maksud Penjumlahan Pengurangan Perkalian Pembagian Pembagian bulat Pemangkatan Penggabungan teks Sisa hasil bagi

Contoh 10 + 2 2 – 3 -4 * 5 10 / 3 10 \ 3 2 ^ 3 “a” & “b” 10 Mod 3

→ → → → → → → →

12 -1 -20 3.33333333 3 8 “ab” 1

68

Operator Perbandingan Operator = > < >= <= <>

Maksud sama dengan lebih besar dari lebih kecil dari lebih besar atau sama dengan dari lebih kecil atau sama dengan dari tidak sama dengan

Contoh 2 = 3 7 > 3 7 < 7 8 >= 7

→ → → →

False True False True

-1 <= -5

→

False

“a” <> “b”

→

True

Operator Logika (0 = False; 1 = True) A 0 0 1 1

B 0 1 0 1

A And B 0 0 0 1

A Or B 0 1 1 1

Not A 1 1 0 0

A Xor B 0 1 1 0

A Eqv B 1 0 0 1

A Imp B 1 1 0 1

CATATAN … Urutan perhitungan dalam VBA sama seperti dalam Excel. Lihat “Urutan Proses Perhitungan” dalam Modul 1 untuk lebih jelasnya.

Array Array (larik) adalah sekumpulan data yang berjenis sama dan memiliki nama yang sama. Masing-masing komponen dalam array diakses melalui nomor indeks. Misalkan kita memiliki array NamaBulan yang berukuran 12, maka untuk mengakses komponen pertama kita bisa mengambil dari NamaBulan(0), komponen kedua NamaBulan(1), dan seterusnya.



Untuk mendeklarasikan array cukup sederhana yaitu dengan menggunakan perintah Dim. Dalam mendeklarasikan array, kita hanya harus menentukan indeks teratasnya, di mana VBA akan mengasumsikan secara otomatis indeks terendahnya adalah 0. Contohnya : ' NamaBulan(0) s/d NamaBulan(12) ' ArrayKu(0) s/d ArrayKu(100) ' ArrayKu(1) s/d ArrayKu(100)

Dim NamaBulan(12) As String Dim ArrayKu(0 To 100) As Integer Dim ArrayKu(1 To 100) As Long

Jika kita ingin VBA secara otomatis mengasumsikan bahwa indeks terendah adalah 1 untuk semua arrray yang hanya mendeklarasikan indeks teratasnya saja, tambahkan perintah berikut ini sebelum prosedur pertama di dalam modul tertentu. Option Base 1 Array yang sebelumnya kita bahas di atas adalah array satu dimensi (one-dimensional arrays). Array juga bisa dideklarasikan secara multidimensi (multidimensional arrays). Array di dalam VBA bisa menampung hingga 60 dimensi, walaupun sangat jarang sekali kita membutuhkan array lebih dari 3 dimensi. Kita bisa menggambarkan array 2 dimensi sebagai bujursangkar yang terdiri dari bujursangkar-bujursangkar kecil berisi komponen, dan array 3 dimensi sebagai kubus yang di dalamnya terdapat kubus-kubus kecil. Contoh berikut ini berhubungan dengan deklarasi array 2 dimensi dan penggunaannya : Dim ArrayKu(1 To 10, 1 To 10) As Integer ArrayKu(3,4) = 125 Array 1 Dimensi

(1)

(2)

' akses komponen baris ke 3 kolom ke 4 (3)

(4)

(5)

……

(n-2)

(n-1)

(n)

Dimensi = (1 To n)

Array 2 Dimensi

(1,1) (2,1) (3,1) (4,1)

(1,2) (2,2) (3,2) (4,2)

(1,3) (2,3) (3,3) (4,3)

(1,4) (2,4) (3,4) (4,4)

(m-1,1) (m,1)

(m-1,2) (m,2)

(m-1,3) (m,3)

(m-1,4) (m,4)

…… …… …… ……

(1,n-1) (2,n-1) (3,n-1) (4,n-1)

(1,n) (2,n) (3,n) (4,n)

…… ……

(m-1,n-1) (m,n-1)

(m-1,n) (m,n)

……

Dimensi = (1 To m, 1 To n)

Array 3 dimensi

1p

1-m 1 - n Dimensi = (1 To m, 1 To n,1 To p)


69

Programming in Microsoft Excel 2000 Kita juga bisa membuat array yang mampu berubah-ubah ukuran dimensinya setiap kali kita inginkan. Array ini disebut dynamic arrays (array dinamis). Deklarasi array dinamis adalah deklarasi array biasa tanpa menggunakan indeks terendah ataupun teratas, seperti : Dim ArrayKu() As Integer Tapi, dengan adanya deklarasi array di atas bukan berarti kita langsung bisa menggunakan array tersebut, melainkan kita harus mendeklarasikan ulang dengan perintah ReDim. Untuk lebih jelasnya perhatikan prosedur di bawah ini : Sub DynArray() Dim ArrayKu() As Integer i = 5 ReDim ArrayKu(1 To i) As Integer ArrayKu(i) = 99 i = i + 5 ReDim ArrayKu(1 To i) As Integer ArrayKu(i) = 999 End Sub Jika ingin agar isi array yang lama tidak hilang ketika kita mendeklarasikan ulang ukuran array tersebut, maka kita harus menambahkan perintah Preserve pada Redim, seperti : Redim Preserve ArrayKu(15) As Integer

Variabel Objek Variabel objek (object variables) adalah variabel yang berisi referensi ke suatu objek, seperti range atau worksheet. Keuntungan yang didapat dari penggunaan variabel objek adalah : dapat menyederhanakan kode.

70

dapat menjalankan program dengan lebih cepat. Variabel objek, sama sepeti variabel lain, bisa dideklarasikan dengan menggunakan perintah Dim, Private, atau Public. Perhatikan kedua contoh di bawah ini dan pelajari perbedaannya : Sub NoObjVar() Worksheets(“Sheet1”).Range(“A1”).Value = 124 Worksheets(“Sheet1”).Range(“A1”).Font.Bold = True Worksheets(“Sheet1”).Range(“A1”).Font.Italic = True End Sub

Sub ObjVar() Dim MyCell As Range Set MyCell = Worksheets(“Sheet1”).Range(“A1”) MyCell.Value = 124 MyCell.Font.Bold = True MyCell.Font.Italic = True End Sub

CATATAN … Setelah suatu objek direferensikan ke suatu variabel, VBA dapat mengaksesnya dengan lebih cepat daripada penggunaan referensi objek yang biasa dan panjang. Jadi jika kita menginginkan kecepatan program, gunakan variabel objek! Satu hal yang berhubungan dengan masalah ini adalah adanya proses di dalam VBA yang disebut “proses titik” (dot processing). Setiap kali VBA menemukan titik dalam referensi objek, seperti dalam Sheets(1).Range(“A1”), ia akan melakukan pemeriksaan terhadap objek yang bersangkutan. Nah, dengan menggunakan variabel objek, penggunaan titik dapat dikurangi. Semakin sedikit titik yang ada, semakin cepat program berjalan. Cara lain untuk mengurangi penggunaan titik yang banyak adalah dengan menggunakan perintah With...End With.

Built-In Function Salah satu keunggulan yang dimiliki VBA adalah beragamnya built-in function (fungsi yang sudah tersedia sebelumnya) yang disediakan untuk mempermudah perhitungan dan operasioperasi tertentu. Bahkan terkadang fungsi-fungsi tersebut mampu melakukan sesuatu yang sulit atau tidak mungkin dilakukan oleh kita. Banyak fungsi VBA yang sejenis dengan fungsi di dalam Excel, misalnya fungsi UPPER dalam Excel sama dengan fungsi UCase dalam VBA.



Seluruh built-in function yang ada sebenarnya tergabung dalam objek VBA, namun kita bisa menggunakan fungsi-fungsi tersebut tanpa harus menyertakan referensi VBA. Contohnya VBA.MsgBox “Test” hasilnya akan sama dengan MsgBox “Test”.

CATATAN … Karena begitu banyaknya built-in function yang terdapat dalam VBA yang tidak mungkin dijelaskan satu persatu di sini, maka cara yang terbaik untuk mempelajarinya adalah dengan menggunakan sistem Help yang ada di dalam VBA. Cukup ketikkan fungsi yang ingin kita pelajari lalu posisikan kursor pada fungsi tersebut, dan tekan F1. Untuk melihat seluruh built-in function yang terdapat dalam VBA, cukup ketikkan VBA diikuti dengan tanda titik (.).

Fungsi MsgBox Fungsi MsgBox digunakan untuk menampilkan pesan tertentu dan merupakan salah satu fungsi yang penting dan sering digunakan di dalam VBA. Banyak contoh di dalam modul ini menggunakan fungsi MsgBox untuk menampilkan isi variabel tertentu. Jika sebelumnya kita menggunakan MsgBox hanya sebagai prosedur (tidak mengembalikan suatu nilai), sekarang kita akan mencoba menggunakan MsgBox sebagai fungsi (mengembalikan suatu nilai). Fungsi MsgBox selain mengembalikan nilai tombol yang ditekan , tetap menampilkan pesan seperti prosedur MsgBox yang sudah kita bahas sebelumnya. Bentuk umum fungsi MsgBox memiliki lima argumen/parameter sebagai berikut (parameter yang diberi tanda kurung siku bersifat opsional) : MsgBox(prompt[, buttons][, title][, helpfile, context]) prompt – pesan yang akan ditampilkan dalam kotak pesan. buttons – nilai yang menyatakan tombol apa saja dan jenis icon, yang akan ditampilkan dalam kotak pesan. Kita bisa menggunakan konstanta yang sudah disediakan oleh VBA—misalnya vbYesNo. title – judul kotak pesan yang akan ditampilkan. Nilai default-nya adalah Microsoft Excel. helpfile dan context – nama file dan identitas bantuan yang akan digunakan dalam kotak pesan. Perhatikan contoh berikut ini untuk lebih jelasnya : Ans = MsgBox(“Lanjutkan proses ?”, vbYesNo + vbQuestion, “Konfirmasi”) If (Ans = vbNo) Then Exit Sub Perintah di atas akan menampilkan kotak pesan yang memiliki tombol Yes dan No dengan icon Question. Lalu hasil dari fungsi MsgBox disimpan dalam variabel Ans. Ans nantinya akan berisi nilai tombol yang ditekan. Pada perintah di bawahnya, terdapat kondisi jika yang ditekan adalah tombol No, maka proses akan berhenti. Selain cara di atas kita juga bisa menggunakan fungsi MsgBox secara langsung tanpa menggunakan variabel sebagai berikut : If (MsgBox(“Lanjutkan proses ?”, vbYesNo + vbQuestion, “Konfirmasi”) = vbNo) Then Exit Sub Hasil untuk kedua contoh di atas adalah sebagai berikut :

LATIHAN Jika cell A1 berisi panjang suatu komponen ban dan cell A2 berisi toleransinya, buatlah satu prosedur VBA untuk mengisi cell D1 dan E1 dengan panjang minimal dan maksimal yang diizinkan ! Buatlah satu prosedur VBA untuk memanipulasi cell yang aktif dengan mengubah lebar kolomnya menjadi 15 dan tinggi barisnya menjadi 25. Dalam matematika terdapat persamaan ax2+bx+c = 0, dan untuk mencari akarnya digunakan rumus : x1 = -b + √((b2-4ac)/2a) dan x2 = -b - √((b2-4ac)/2a). Buatlah prosedur untuk mencari akar x1 dan x2 dari nilai a, b, dan c yang ada di cell tertentu dan menaruh


71

Programming in Microsoft Excel 2000 hasilnya di cell yang lain ! Pastikan juga agar nilai hasil yang berada di dalam tanda √(……) tidak negatif, karena bilangan negatif tidak dapat diakarkan !

Struktur Dasar Pemrograman Dalam ‘dunia’ pemrograman banyak sekali struktur bahasa yang mutlak harus kita kuasai agar kita bisa membuat program yang baik, fleksibel, efektif dan efisien, serta terstruktur. Secara garis besar struktur bahasa pemrograman bisa dikategorikan menjadi 3, yaitu : pengulangan (looping) proses; penyeleksian kondisi (conditioning) proses; dan pemecahan program menjadi beberapa modul (modulling).

With-End With Struktur With-End With mengizinkan kita untuk melakukan beberapa operasi dengan hanya menggunakan satu referensi objek saja. Untuk lebih jelasnya perhatikan contoh berikut ini, yang digunakan untuk mengubah lima properti pem-format-an pada Range yang aktif : Sub ChangeFont1() Selection.Font.Name = “Times New Roman” Selection.Font.FontStyle = “Bold Italic” Selection.Font.Size = 12 Selection.Font.Underline = xlSingle Selection.Font.ColorIndex = 5 End Sub Prosedur di atas bisa diubah dengan menggunakan struktur With-End With sebagai berikut : Sub ChangeFont2() With Selection.Font .Name = “Times New Roman” .FontStyle = “Bold Italic” .Size = 12 .Underline = xlSingle .ColorIndex = 5 End With End Sub Tujuan dari penggunaan struktur With-End With adalah untuk menghemat penulisan program dan agar program dapat berjalan lebih cepat. Itulah sebab mengapa ketika kita melakukan perekaman macro, Excel akan selalu menggunakan struktur With-End With jika memungkinkan.

For-Next Struktur For-Next yang sederhana digunakan untuk mengulang proses dari nilai yang satu hingga mencapai nilai yang lainnya. Syntax-nya adalah : For counter = start To end [Step stepval] [instructions] Next [counter] Dimana proses instructions akan diulang mulai dari nilai start hingga nilai end dengan lompatan nilai stepval dimana nilai yang berulang itu sendiri disimpan di variabel counter. Perhatikan contoh berikut yang digunakan untuk menghitung total hasil akar 1 sampai 100 :


72

Programming in Microsoft Excel 2000 Sub SumAkar() Dim Sum As Double Dim Hitung As Integer Sum = 0 For Hitung = 1 To 100 Sum = Sum + Sqr(Hitung) Next Hitung MsgBox Sum End Sub Pada contoh di atas, Hitung (variabel pengulangan) dimulai dari nilai 1 lalu bertambah 1 setiap kali terjadi pengulangan. Variabel Sum lalu akan mengakumulasikan akar dari masing-masing nilai Hitung. Kita juga bisa menggunakan perintah Step untuk menentukan lompatan nilai pengulangan. Berikut adalah modifikasi program sebelumnya yang digunakan untuk menghitung total hasil akar bilangan yang ganjil saja mulai dari 1 sampai 100 : Sub SumAkarGanjil() Dim Sum As Double Dim Hitung As Integer Sum = 0 For Hitung = 1 To 100 Step 2 Sum = Sum + Sqr(Hitung) Next Hitung MsgBox Sum End Sub Pada contoh di atas Hitung akan dimulai dari 1 lalu 3, 5, 7 dan seterusnya hingga nilai akhir 99. Berikut adalah contoh lain dari penggunaan For-Next, cobalah analisa maksud dan fungsi dari prosedur tersebut : Sub ContohForNext() Dim NilaiAwal As Integer, BanyakCell As Integer, I As Integer NilaiAwal = Val(InputBox(“Masukkan Nilai Awal :”)) BanyakCell = Val(InputBox(“Banyaknya Cell :”)) For I = 0 To BanyakCell – 1 ActiveCell.Offset(I, 0).Value = NilaiAwal + I Next I End Sub Pada contoh-contoh sebelumnya, pengulangan yang dilakukan relatif sederhana karena hanya ada satu pengulangan. Sebenarnya kita bisa menyusun struktur For-Next di dalam struktur ForNext yang lainnya. Perhatikan contoh berikut yang digunakan untuk mengisi nilai –1 ke dalam setiap elemen array berukuran 10 x 10 x 10 : Sub ContohForNext() Dim ArrayKu(1 To 10, 1 To 10, 1 To 10) As Integer Dim I As Integer, J As Integer, K As Integer For I = 1 To 10 For J = 1 To 10 For K = 1 To 10 ArrayKu(I, J, K) = -1 Next K Next J Next I End Sub

CATATAN … Untuk menghentikan proses pengulangan dan keluar dari struktur For-Next, gunakanlah perintah Exit For.

Selain bentuk For-Next sederhana, terdapat juga bentuk yang lain yaitu For Each-Next yang digunakan untuk mengakses semua objek yang terdapat dalam suatu koleksi. Dengan perintah For Each-Next ini kita tidak perlu mengetahui jumlah objek yang terdapat dalam suatu koleksi. Syntaxnya adalah :


73

Programming in Microsoft Excel 2000 For Each element In group [instructions] Next [element] Dimana proses instruction akan dilaksanakan untuk seluruh objek element yang tergabung dalam group. Untuk lebih jelasnya perhatikan contoh berikut ini : Sub Macro1() Dim Nilai(5) As Single, I As Integer Dim N As Variant For I = 0 To 5 Nilai(I) = Rnd Next I For Each N In Nilai Debug.Print N Next N End Sub Program di atas akan mengisi array berukuran 5 x 1 dengan bilangan acak (fungsi Rnd menghasilkan bilangan acak yang yang lebih kecil dari 1 dan lebih besar atau sama dengan 0, atau bisa dikatakan 0 ≤ Rnd < 1) lalu menampilkannya di jendela Immediate.

CATATAN … Variabel yang digunakan oleh struktur For Each-Next yang mengakses komponen dalam array harus berjenis Variant.

Berikut adalah contoh lain penggunaan For Each-Next yang digunakan untuk menampilkan pesan nama semua worksheet yang ada di dalam workbook yang aktif : Sub x() Dim Item As Worksheet For Each Item In ActiveWorkbook.Worksheets MsgBox Item.Name Next Item End Sub

LATIHAN Buatlah prosedur untuk memeriksa apakah di dalam suatu workbook ada worksheet yang tersembunyi. Jika ada, maka tampilkan nama worksheet tersebut dengan perintah MsgBox ! Buatlah prosedur untuk menutup semua workbook yang ada selain workbook yang sedang aktif ! Buatlah prosedur untuk mengubah isi semua cell yang terdapat pada range yang dipilih menjadi huruf besar semua ! Analisalah prosedur di bawah ini dan tentukan maksud dan fungsinya : Sub SoalNomor4() Tertinggi = Application.WorksheetFunction.Max(Range(“A:A”)) For Baris = 1 To 65536 Set CellNya = Range(“A1”).Offset(Baris – 1, 0) If (CellNya.Value = Tertinggi) Then MsgBox “Nilai Tertinggi terdapat dalam baris ” & Baris CellNya.Activate Exit For End If Next Baris End Sub Buatlah prosedur untuk menghitung nilai rata-rata yang terdapat dalam suatu range yang dipilih dengan menggunakan perintah For-Next atau For Each-Next ! Buatlah prosedur untuk menukar isi dua buah array berukuran 5 x 5 lalu jumlahkan masing-


74

Programming in Microsoft Excel 2000 masing elemen array tersebut dan simpan hasilnya dalam array lain !

Do-While Beda dengan pengulangan For-Next yang memiliki batas nilai awal dan nilai akhir, struktur pengulangan

Do-While

digunakan

jika

yang

diketahui

hanya

kondisi

dilaksanakannya

pengulangan. Secara harfiah, Do-While jika diartikan menjadi Lakukan-Selama. Jadi selama kondisi terpenuhi, pengulangan akan terus dilaksanakan. Syntaxnya adalah : Do [While condition] [instructions] Loop atau Do [instructions] Loop [While condition] Seperti bisa dilihat di atas, kita dapat meletakkan kondisi pada awal atau akhir blok. Perbedaan antara kedua syntax di atas adalah pada syntax yang pertama, ada kemungkinan instructions tidak pernah dijalankan, sedangkan pada syntax yang kedua, instructions akan dijalankan sedikitnya 1 kali. Untuk lebih jelasnya perhatikanlah contoh berikut ini yang digunakan untuk mengosongkan isi cell mulai dari cell yang aktif lalu ke bawah hingga ditemui cell yang kosong : Sub DemoDoWhile1() Do While Not IsEmpty(ActiveCell) ActiveCell.Value = 0 ActiveCell.Offset(1, 0).Select Loop End Sub Jika programnya diubah menjadi sebagai berikut maka pengulangan akan dilaksanakan minimal satu kali, walaupun cell yang pertama itu kosong : Sub DemoDoWhile2() Do ActiveCell.Value = 0 ActiveCell.Offset(1, 0).Select Loop While Not IsEmpty(ActiveCell) End Sub Berikut adalah contoh lain yang digunakan untuk membuka dan membaca suatu file teks lalu mengubah setiap baris menjadi huruf besar semua dan ditaruh di worksheet. Proses ini akan terus berulang hingga dicapai akhir file (dengan menggunakan fungsi EOF). Dan perintah terakhir akan menutup file. Sub DemoDoWhile3() Open “c:\data\textfile.txt” For Input As #1 Baris = 0 Do While Not EOF(1) Input #1, HasilBaca Range(“A1”).Offset(Baris, 0) = UCase(HasilBaca) Baris = Baris + 1 Loop Close #1 End Sub

Do-Until


75


Pengulangan Do-Until memiliki fungsi yang hampir sama dengan Do-While. Jika diartikan secara harfiah, Do-Until akan menjadi Lakukan-Sampai. Jadi pengulangan Do-Until ini akan terus

dilaksanakan

selama

kondisinya

tidak terpenuhi,

jika

kondisinya

terpenuhi maka

pengulangan akan berakhir.

CATATAN … Struktur pengulangan Do-While akan terus dilaksanakan selama kondisinya bernilai True, sedangkan struktur pengulangan Do-Until akan terus dilaksanakan selama kondisinya bernilai False.

Syntax yang dimiliki oleh Do-Until ada dua, yaitu : Do [Until condition] [instructions] Loop atau Do [instructions] Loop [Until condition] Seperti bisa dilihat di atas, kita dapat meletakkan kondisi pada awal atau akhir blok. Perbedaan antara kedua syntax di atas adalah pada syntax yang pertama, ada kemungkinan instructions tidak pernah dijalankan, sedangkan pada syntax yang kedua, instructions akan dijalankan sedikitnya 1 kali. Berikut adalah contoh yang digunakan untuk membuka dan membaca suatu file teks lalu mengubah setiap baris menjadi huruf besar semua dan ditaruh di worksheet. Proses ini akan terus berulang hingga dicapai akhir file (dengan menggunakan fungsi EOF). Dan perintah terakhir akan

menutup

file.

Contoh

ini

merupakan

modifikasi

dari

program

sebelumnya

yang

menggunakan Do-While. Coba cari dan analisa perbedaannya ! Sub DemoDoUntil() Open “c:\data\textfile.txt” For Input As #1 Baris = 0 Do Until EOF(1) Input #1, HasilBaca Range(“A1”).Offset(Baris, 0) = UCase(HasilBaca) Baris = Baris + 1 Loop Close #1 End Sub

CATATAN … Untuk menghentikan proses pengulangan dan keluar dari struktur Do-While atau Do-Until, gunakanlah perintah Exit Do.

LATIHAN Buatlah prosedur untuk menghasilkan deret bilangan sebagai berikut (boleh dengan ForNext, Do-While, atau Do-Until) : 1 2 4 8 16 ……… 1024 1 3 5 7 9 ……… 21


76

Programming in Microsoft Excel 2000 1 3 2 4 3 5 4 6 5 7 6 8 7 9 8 10 9 11 10 12 Buatlah program sederhana untuk memeinta masukkan pessword ! Program akan terus berulang hingga dimasukkan password yang benar.

If-Then Mungkin struktur If-Then merupakan struktur yang sangat penting dan yang akan sering digunakan dalam pemrograman. Struktur If-Then memungkinkan program kita menjadi fleksibel dan mampu mengambil keputusan dari masukkan tertentu dan menghasilkan keluaran yang berbeda. Syntax dasar dari If-Then ini adalah : If condition Then true_instruction [Else false_instruction] Struktur di atas akan menguji condition. Jika hasilnya True, maka true_instruction akan dijalankan, sebaliknya jika hasilnya False, maka false_instruction akan dijalankan. Perlu diperhatikan juga bahwa penggunaan Else bersifat opsional. Perhatikan contoh berikut ini untuk lebih jelasnya : Sub Ucapan() If Time < 0.5 Then MsgBox “Selamat Pagi” End Sub Prosedur di atas digunakan untuk menampilkan ucapan berdasarkan waktu yang aktif (fungsi Time akan mengembalikan waktu yang aktif berisikan nilai desimal—sebagai contoh, tengah hari dinyatakan dengan nilai 0.5). Pada prosedur di atas, jika Time menghasilkan nilai lebih besar dari 0.5, maka tidak akan dilaksanakan apa-apa. Jika kita ingin menampilkan ucapan setelah tengah hari, maka tambahkan perintah If-Then lagi seperti berikut : Sub Ucapan() If Time < 0.5 Then MsgBox “Selamat Pagi” If Time >= 0.5 Then MsgBox “Selamat Siang/Sore” End Sub Prosedur di atas menambahkan satu perintah If Time >= 0.5 untuk menangani waktu setelah jam 12:00 siang. Pendekatan lain bisa dilakukan dengan menggunakan Else sebagai berikut : Sub Ucapan() If Time < 0.5 Then MsgBox “Selamat Pagi” Else _ MsgBox “Selamat Siang/Sore” End Sub Jika kita ingin agar prosedur kita bisa menangani 3 ucapan (misalnya pagi, siang/sore, dan malam), maka kita bisa menggunakan 3 perintah If-Then sebagai berikut : Sub Ucapan() If Time < 0.5 Then MsgBox “Selamat Pagi” If Time >= 0.5 And Time < 0.75 Then MsgBox “Selamat Siang/Sore” If Time >= 0.75 Then MsgBox “Selamat Malam” End Sub Nilai 0.75 di atas menandakan jam 06:00 sore. Perhatikan modifikasi program di bawah ini yang menggunakan struktur If-Then-Else bertingkat : Sub Ucapan() If Time < 0.5 Then MsgBox “Selamat Pagi” Else _ If Time >= 0.5 And Time < 0.75 Then MsgBox “Selamat Siang/Sore” Else _ If Time >= 0.75 Then MsgBox “Selamat Malam” End Sub


77

Programming in Microsoft Excel 2000 Prosedur-prosedur di atas bisa kita lihat hanya menggunakan satu perintah sederhana saja pada masing-masing blok If dan Else, sehingga struktur If-Then-Else cukup dituliskan dalam satu baris saja. Namun bagaimana jika perintah yang harus dilaksanakan dalam blok If dan Else lebih dari satu perintah? Syntax If-Then-Else lain untuk program yang lebih kompleks adalah sebagai berikut : If condition Then [true_instructions] [ElseIf condition-n Then [alternate_instructions]] [Else [default_instructions]] End If Berikut adalah modifikasi program terakhir (yang agak sulit dibaca) dengan menggunakan syntax If-Then-Else di atas : Sub Ucapan() If Time < 0.5 Then MsgBox “Selamat Pagi” ElseIf Time >= 0.5 And Time < 0.75 Then MsgBox “Selamat Siang/Sore” ElseIf Time >= 0.75 Then MsgBox “Selamat Malam” End If End Sub

78

LATIHAN Buatlah prosedur-prosedur untuk meminta masukkan dengan InputBox untuk jumlah barang yang akan dibeli. Lalu adakan pengkondisian untuk diskon berdasarkan jumlah barang yang dibeli sebagai berikut : 1 ≤ jumlah < 25 ≤ jumlah < 50 ≤ jumlah < jumlah ≥

25, 50, 75, 75,

maka maka maka maka

diskon diskon diskon diskon

= = = =

10 15 20 25

% % % %

Lalu tampilkan besarnya diskon dengan MsgBox ! Catatan : Buatlah dua program ! Satu dengan syntax pada hal. 76 dan satu dengan syntax pada hal. 77.

Fungsi Iif

VBA menawarkan alternatif lain untuk menggantikan struktur If-Then: fungsi IIf. Fungsi ini memiliki tiga argumen/ parameter dan bekerja sama persis dengan fungsi IF dalam worksheet Excel. Syntaxnya adalah : IIf(condition, true_value, false_value) Jika condition bernilai True, maka nilai true_value akan akan dikembalikan, sebaliknya jika condition bernilai False, maka nilai false_value akan dikembalikan. Perintah berikut mendemonstrasikan penggunaan fungsi IIf untuk menampilkan pesan “Nol” jika cell A1 berisi nilai 0 atau tidak ada isinya, sebaliknya akan menampilkan pesan “Bukan Nol”. MsgBox IIf(Range(“A1”).Value = 0, “Nol”, “Bukan Nol”)

Select-Case Select-Case sangat berguna untuk melakukan penyeleksian terhadap 3 kondisi atau lebih dan merupakan alternatif yang lebih baik daripada penggunaan If-Then-Else untuk kondisi yang banyak. Syntax untuk Select-Case adalah sebagai berikut : Select Case testexpresion


Programming in Microsoft Excel 2000 [Case expressionlist-n [instructions]] [Case Else [default_instructions]] End Select Kita bisa memasukkan nama variabel atau ekspresi lain yang menghasilkan nilai pada testexpression dan nilainya pada expressionlist-n. Bagian Case Else akan dikerjakan jika nilainya tidak ada yang memenuhi expressionlist-n. Berikut adalah modifikasi dari prosedur terakhir yang menggunakan If-Then-Else : Sub Ucapan() Select Case Time Case Is < 0.5 Msg = “Selamat Pagi” Case 0.5 To 0.75 Msg = “Selamat Siang/Sore” Case Else Msg = “Selamat Malam” End Select MsgBox Msg End Sub Berikut adalah contoh dari penggunaan Select-Case bertingkat : Sub AppWindow() Select Case Application.WindowState Case xlMaximized: MsgBox “App Maximized” Case xlMinimized: MsgBox “App Minimized” Case xlNormal: MsgBox “App Normal” Select Case ActiveWindow.WindowState Case xlMaximized: MsgBox “Book Maximized” Case xlMinimized: MsgBox “Book Minimized” Case xlNormal: “Book Normal” End Select End Select End Sub

LATIHAN Ubahlah prosedur pada latihan sebelumnya yang menggunakan If-Then-Else menjadi menggunakan Select-Case !

Bekerja Dengan Prosedur Prosedur adalah sekumpulan kode VBA yang berada di dalam modul VBA, yang bisa diakses melalui Visual Basic Editor. Sebuah modul dapat menampung beberapa prosedur. Fungsi adalah prosedur yang dapat mengembalikan nilai. Prosedur dapat dijalankan melalui menubar atau toolbar melalui fungsi Assign Macro. Sedangkan fungsi dapat digunakan langsung pada worksheet Excel. Misalkan kita memiliki prosedur Proc1 dan fungsi Func1, maka kita bisa membuat menu untuk mengakses Proc1 (akan dibahas kemudian) dan menulis rumus di cell seperti =Func1() (akan dibahas kemudian).

Mendeklarasikan Prosedur


79

Programming in Microsoft Excel 2000 Prosedur dideklarasikan dengan kata Sub yang syntaxnya adalah sebagai berikut : [Private | Public][Static] Sub name [(arglist)] [instructions] End Sub Private

(opsional) menandakan bahwa prosedur yang bersangkutan hanya bisa diakses oleh prosedur yang terletak pada modul yang sama. Jika sebuah prosedur

Public

bersifat Private, ia tidak akan bisa diakses melalui kotak dialog Macro. (opsional) menandakan bahwa prosedur yang bersangkutan bisa diakses oleh semua prosedur di dalam modul yang berada pada workbook yang sama.

Static

Secara default prosedur memiliki lingkup Public. (opsional) menandakan bahwa variabel yang terdapat pada prosedur nilainya

Sub name arglist

tidak akan berubah walaupun proses di prosedur tersebut sudah selesai. menandakan awal prosedur. menandakan nama prosedur. (opsional) daftar variabel argumen/parameter, yang terdapat di dalam tanda

instructions End Sub

kurung. Gunakan koma untuk memisahkan argumen/parameter. (opsional) perintah-perintah VBA. menandakan akhir prosedur.

CATATAN … Untuk memaksa semua prosedur bersifat Private (termasuk prosedur yang menyertakan kata Public), tambahkan perintah Option Private Module pada awal modul sebelum prosedur pertama.

80

Menjalankan Prosedur Terdapat banyak cara untuk menjalankan prosedur, diantaranya adalah : Melalui menu Run→Run Sub/UserForm atau dengan menekan F5 di dalam VB Editor. Excel lalu akan menjalankan prosedur dimana kursor berada. Tetapi metode ini tidak akan bekerja jika prosedur yang bersangkutan memiliki argumen/parameter. Melalui kotak dialog Macro yang dapat dibuka melalui menu Tools→Macro→Macros… atau dengan menekan tombol Alt+F8. Kotak dialog Macro akan menampilkan daftar seluruh prosedur yang bisa dipanggil

dimana

kita

bisa

memilih

salah

satu

macro

lalu

menekan

menjalankannya. Gambar 2.3 Kotak dialog Macro


tombol

Run

untuk

Programming in Microsoft Excel 2000 Dengan menggunakan tombol (shortcut) Ctrl yang dipasang ke prosedur yang bersangkutan. Misalkan kita memiliki prosedur Update yang dipasang dengan tombol shortcut Ctrl+U, maka dengan menekan Ctrl+U prosedur Update akan dijalankan secara otomatis. Ketika kita membuat macro dengan menggunakan macro recorder, kita ditanyakan tombol shortcutnya. Akan tetapi kita bisa setiap saat menentukan tombol shortcutnya. Untuk menentukan tombol shortcut suatu macro langkahnya adalah : Aktifkan Excel Pilih menu Tools→Macro→Macros… atau tekan tombol Alt+F8 Tekan tombol Options… yang akan menampilkan kotak dialog seperti gambar 2.4 di bawah ini : Gambar 2.4 Kotak dialog Macro Options

Masukkan karakter pada teks bertuliskan Ctrl+. Jika kita memasukkan huruf kecil s, maka tombol shortcutnya adalah Ctrl+S, jika kita memasukkan huruf besar S, maka tombol shortcutnya adalah Ctrl+Shift+S. Masukkan keterangannya pada teks bertuliskan Description (opsional). Tekan tombol OK untuk menutup kotak dialog Macro Options, dan tekan tombol Close untuk menutup kotak dialog Macro.

CATATAN … Jika kita memasang tombol shortcut yang sudah didefinisikan Excel sebelumnya, maka tombol shortcut tersebut akan mengambil alih tombol shortcut Excel. Misalnya jika kita memasang suatu prosedur dengan tombol Ctrl+S, maka dengan menekan tombol Ctrl+S yang dilakukan bukannya menyimpan file lagi, melainkan melakukan prosedur yang telah kita tentukan.

Melalui menubar yang sudah dipasang ke prosedur yang bersangkutan. Excel mengizinkan kita untuk menambah menu baru pada menu bar yang ada, yang urut-urutannya adalah sebagai berikut : Klik kanan pada daerah toolbar lalu pilih Customize… atau pilih menu View→Toolbars→Customize…. Excel kemudian akan menampilkan kotak dialog Customize seperti gambar 2.5 di bawah. Klik tab Commands lalu gulung daftar Categories ke bawah dan pilih Macros. Di daftar Commands, geser (drag) item pertama yang bertuliskan Custom Menu Item ke arah menu bar (misalnya bawa ke menu Data paling bawah) untuk menambah sub-menu baru. (catatan : jika ingin menambahkan menu baru pilih New Menu pada daftar Categories lalu geser item New Menu pada daftar Commands ke—misalnya—kanan menu Help) Klik kanan pada sub-menu yang baru tadi untuk menampilkan menu shortcut dan berikan nama baru pada sub-menu tersebut misalnya &Ucapan seperti terlihat pada gambar 2.6 di bawah.


81

Programming in Microsoft Excel 2000 Gambar 2.5 Kotak dialog Customize

Gambar 2.6 Shortcut-menu untuk penambahan menu baru

82

Lalu pilih Assign Macro… pada menu shortcut tersebut dan pilih macro yang kita inginkan dari daftar macro yang ada—misalnya macro Ucapan. Setelah itu klik OK untuk menutup kotak dialog Assign Macro dan klik Close untuk menutup kotak dialog Customize. Sekarang menu baru tersebut sudah tersedia. Jika kita mengakses menu tersebut maka macro Ucapan akan dijalankan. Melalui tombol pada toolbar yang sudah dipasang ke prosedur yang bersangkutan. Excel mengizinkan kita untuk menambah tombol baru pada toolbar yang ada, yang urut-urutannya adalah sebagai berikut :


Programming in Microsoft Excel 2000 Klik kanan pada daerah toolbar lalu pilih Customize… atau pilih menu View→Toolbars→Customize…. Excel kemudian akan menampilkan kotak dialog Customize seperti gambar 2.5 di atas. Klik tab Commands lalu gulung daftar Categories ke bawah dan pilih Macros. Di daftar Commands, geser (drag) item kedua yang bertuliskan Custom Button ke arah toolbar (misalnya bawa ke toolbar Formatting paling kanan) untuk menambah tombol baru. Klik kanan pada sub-menu yang baru tadi untuk menampilkan menu shortcut dan berikan nama baru pada sub-menu tersebut misalnya Ucapan seperti terlihat pada gambar 2.6 di atas. Nama ini nantinya akan tampil sebagai tooltip. Jika kita mengosongkannya, maka tooltip akan menampilkan “Custom”. Lalu pilih Assign Macro… pada menu shortcut tersebut dan pilih macro yang kita inginkan dari daftar macro yang ada—misalnya macro Ucapan. Setelah itu klik OK untuk menutup kotak dialog Assign Macro dan klik Close untuk menutup kotak dialog Customize. Sekarang menu baru tersebut sudah tersedia. Jika kita mengakses menu tersebut maka macro Ucapan akan dijalankan. Memanggil dari prosedur lain. Untuk memanggil suatu prosedur dari prosedur lain terdapat banyak cara, diantaranya adalah : Menggunakan nama prosedur langsung diikuti oleh argumen/parameter yang masing-masing dipisahkan dengan tanda koma. Contoh : Sub MySub() ... UpdateSheet MonthNum ... End Sub Sub UpdateSheet(MonthSeg) ... End Sub Menggunakan perintah Call diikuti dengan nama prosedur lalu argumen/ parameter-nya yang diapit oleh tanda kurung. Contoh Sub MySub() ... Call UpdateSheet(MonthNum) ... End Sub Sub UpdateSheet(MonthSeg) ... End Sub Menggunakan metode Run yang terdapat dalam objek Application. Metode ini sangat berguna jika nama prosedur yang akan kita jalankan terdapat dalam suatu variabel dan bisa diubah-ubah. Contoh : Sub MySub() ... Application.Run “UpdateSheet”, MonthNum ... End Sub Sub UpdateSheet(MonthSeg) ... End Sub Memanggil suatu prosedur dari modul lain. Seperti kita tahu bahwa jika kita memanggil suatu prosedur yang tidak terdapat pada modul yang sama, VBA akan secara otomatis mencari prosedur tersebut (yang bersifat Public) di modul lain dalam workbook yang sama. Jika suatu prosedur—misalnya prosedur B—


83

Programming in Microsoft Excel 2000 memanggil prosedur lain—misalnya prosedur A—dan prosedur A tersebut terdapat di modul yang sama dan terdapat juga di modul lain sebagai prosedur Public, maka kita harus menyertakan nama modulnya dalam pemanggilan prosedur A tersebut. Perhatikan gambaran berikut untuk lebih jelasnya : Module1

Module2

Sub A() ... End Sub Sub B() Call A Call Module1.A Call Module2.A End Sub

Sub A() ... End Sub ‘ambiguous error

Memanggil suatu prosedur dari workbook lain. Kita juga bisa memanggil suatu prosedur dari workbook lain dengan cara menyertakan nama projek VBA-nya diikuti oleh nama modulnya lalu nama prosedurnya dan argumen/parameternya jika ada, misalnya : ProjekKu.ModulKu.ProsedurKu atau bisa juga dengan menggunakan perintah Call, misalnya : Call ProjekKu.ModulKu.ProsedurKu. Cara lain untuk memanggil prosedur dari workbook lain adalah dengan menggunakan metode Run yang terdapat di dalam objek Application dan menyertakan

nama

workbook

dan

nama

prosedurnya,

misalnya

:

Application.Run “‘budget macros.xls’!Consolidate”. Dengan klik objek button atau shape yang ditaruh di dalam worksheet. Button atau shape tersebut harus dipasang ke prosedur tertentu. Excel memiliki banyak objek yang bisa ditaruh ke dalam worksheet dan dipasang ke prosedur tertentu. Objek-objek tersebut terletak di dalam tiga toolbar, yaitu : toolbar Drawing toolbar Forms toolbar Control Toolbox

CATATAN … Objek yang terdapat dalam toolbar Forms berbeda dengan yang terdapat di dalam toolbar Control Toolbox. Objek yang terdapat di toolbar Control Toolbox adalah jenis objek ActiveX yang penggunaannya lebih umum (jumlahnya lebih banyak dan bisa digunakan dimana saja selain Excel), sedangkan objek yang terdapat di toolbar Forms hanya dapat digunakan di dalam Excel sekaligus untuk menjaga kompabilitas dengan Excel 5 dan Excel 95. Sebaiknya kita tidak menggunakan objek yang terdapat di dalam toolbar Control Toolbox jika belum mengerti tentang ActiveX.

Untuk memasang prosedur ke suatu button, lakukan langkah-langkah sebagai berikut : Pastikan toolbar Forms sudah aktif. Klik objek Button (tombol) di toolbar Forms. Geser atau klik di dalam worksheet untuk membuat objek button. Excel lalu akan menampilkan kotak dialog Assign Macro (seperti gambar 2.3) dimana kita bisa memasang prosedur yang kita inginkan. Atau jika kita ingin melewatkan langkah ini, kita tetap bisa memasang prosedur ke objek yang bersangkutan dengan klik kanan lalu pilih Assign Macro…. Klik tombol OK. Sekarang jika kita mengarahkan kursor ke arah tombol tersebut, maka kursor akan berubah menjadi gambar tangan dan jika kita klik tombol tersebut maka prosedur yang kita pasang sebelumnya akan dijalankan. Untuk menambahkan sebuah objek shape, langkahnya kurang lebih sama dengan cara di atas hanya kita harus mengambilnya dari toolbar Drawing. Melaksanakan prosedur ketika suatu event (kejadian) muncul. Yang dimaksud dengan event adalah seluruh tindakan yang kita lakukan yang mengakibatkan adanya interaksi antara kita dengan program


84

Programming in Microsoft Excel 2000 (dalam hal ini Excel). Misalnya ketika kita meng-klik suatu cell, maka event SelectionChange akan muncul. Ketika kita mengubah isi suatu cell, maka event Change akan muncul. Sebenarnya prosedur yang dibangkitkan oleh event tertentu sudah disediakan oleh VBA, kita hanya tinggal menaruh program di dalamnya. Sekarang kita akan masuk ke contoh : “Kita ingin agar setiap ada perubahan data di cell A1, sejarah data-data yang pernah dibuat tercatat di kolom B”. Ikutilah langkah-langkah berikut ini : Aktifkan Visual Basic Editor. Klik dua kali pada worksheet—dimana terdapat cell A1 yang dimaksud—dalam Project Explorer, misalnya Sheet1. Setelah Code Window muncul, pilih Worksheet dari kotak daftar yang awalnya bertuliskan (General). Lalu pilih item Change (karena kita ingin menjalankan program ketika terjadi perubahan cell) dari kotak daftar yang awalnya bertuliskan SelectionChange. Masukkan kode program berikut. Private Sub Worksheet_Change(ByVal Target As Range) Static row As Integer If (Target.Address(False, False) = "A1") Then Range("B1").Offset(row, 0).Value = Range("A1").Value row = row + 1 End If End Sub

CATATAN … Parameter Target pada event di atas diciptakan secara otomatis oleh VBA yang jenisnya adalah objek Range. Untuk mendapatkan alamat Target kita bisa menggunakan properti Address yang syntax sederhananya adalah sebagai berikut : object.Address(rowabsolute,columnabsolute) Jika rowabsolute atau columnabsolute diisi dengan True maka pengalamatan absolut akan diberikan (dengan tanda $) dan jika diisi dengan False maka pengalamatan relatif akan diberikan. Contoh : misalnya cell C5 dipilih : Target.Address(False,False) akan menghasilkan “C5” Target.Address(False, True) akan menghasilkan “$C5” Target.Address( True,False) akan menghasilkan “C$5” Target.Address( True, True) akan menghasilkan “$C$5”

Menjalankan suatu prosedur dari Immediate Window. Untuk menjalankan suatu prosedur, kita juga bisa melakukannya melalui Immediate Window dengan mengetikkan langsung nama prosedurnya diikuti dengan argumen/parameternya lalu menekan Enter.

Bekerja Dengan Fungsi Berbeda dengan prosedur yang berfungsi untuk menjalankan tugas tertentu tanpa perlu mengembalikan nilai, fungsi akan menjalankan tugas tertentu dan juga mengembalikan nilai— sama seperti fungsi worksheet dalam Excel (seperti SUM atau AVERAGE) atau fungsi VBA. Sama seperti prosedur, fungsi juga dapat memiliki argumen/parameter. Tanpa panjang lebar, berikut adalah contoh fungsi yang digunakan untuk membalik suatu teks : Function Balik(Kalimat) As String Dim PanjangKalimat As Integer, I As Integer Balik = “”


85

Programming in Microsoft Excel 2000 PanjangKalimat = Len(Kalimat) For I = PanjangKalimat To 1 Step –1 Balik = Balik & Mid(Kalimat, I, 1) Next I End Function Perhatikan bahwa fungsi dimulai dengan kata Function dan bukannya Sub diikuti oleh nama fungsinya (Balik). Fungsi ini hanya menggunakan satu argumen (Kalimat). As menandakan

bahwa

hasil

keluaran

fungsi

tersebut

adalah

jenis

String.

String

(Excel

akan

menggunakan jenis data Variant sebagai nilai yang dihasilkan jika tidak dispesifikasikan.) Fungsi Len digunakan untuk mendapatkan panjang suatu string. Sedangkan fungsi Mid digunakan untuk mendapatkan bagian tertentu dari suatu teks (fungsinya sama dengan fungsi worksheet Excel).

CATATAN … Jika kita ingin menciptakan suatu fungsi agar bisa digunakan dalam formula di worksheet, pastikan agar mereka terletak di dalam satu modul VBA. Jika kita menaruhnya di modul Sheet atau ThisWorkbook, maka fungsi tersebut tidak akan bisa digunakan.

Jika kita sudah membuat suatu fungsi, maka kita bisa menggunakannya dalam formula di worksheet seperti biasa, misalnya =Balik(A1). Berikut adalah contoh penggunaannya : A B C 1 Excel lecxE 2 Kalimat Terbalik kilabreT tamilaK 3 Malam di Makam makaM id malaM 4 Kasur Rusak kasuR rusaK 5 12345.678 876.54321 6 TRUE eurT 7 Sekarang apa yang terjadi jika kita memasukkan formula =Balik(Balik(A1)) di cell C1 ? Untuk memanggil fungsi dari prosedur atau fungsi VBA lain caranya sama dengan memanggil prosedur hanya bedanya hasil dari fungsi tersebut harus dimasukkan ke variabel tertentu atau diarahkan ke masukkan lain misalnya sebagai argumen/parameter. Perhatikan contoh berikut ini untuk lebih jelasnya : Sub BalikKalimat() Masukkan = InputBox(“Masukkan Sembarang teks :”) MsgBox Balik(Masukkan) Kalimat = Balik(“Squall Leonhart”) MsgBox Kalimat End Sub

Mendeklarasikan Fungsi Fungsi dideklarasikan dengan kata Function yang syntaxnya adalah sebagai berikut : [Private | Public][Static] Function name [(arglist)][As type] [instructions] [name = expression] End Function Private

(opsional) menandakan bahwa fungsi yang bersangkutan hanya bisa diakses oleh prosedur/fungsi yang terletak pada modul yang sama. Fungsi juga tidak


86

Programming in Microsoft Excel 2000 akan tampil di dalam kotak dialog Paste Function ketika kita bekerja dengan worksheet Excel. (opsional) menandakan bahwa fungsi yang bersangkutan bisa diakses oleh

Public

semua prosedur/fungsi di dalam modul yang berada pada workbook yang sama. Static

Secara default fungsi memiliki lingkup Public. (opsional) menandakan bahwa variabel yang terdapat pada fungsi nilainya tidak

Function name arglist

akan berubah walaupun proses di fungsi tersebut sudah selesai. menandakan awal fungsi. menandakan nama fungsi. (opsional) daftar variabel argumen/parameter, yang terdapat di dalam tanda

type instructions End Function

kurung. Gunakan koma untuk memisahkan argumen/parameter. (opsional) jenis data yang akan dikembalikan sebagai hasil fungsi. (opsional) perintah-perintah VBA. menandakan akhir fungsi.

CATATAN … Sama halnya seperti memaksa semua prosedur untuk bersifat Private, untuk memaksa semua fungsi bersifat Private (termasuk fungsi yang menyertakan kata Public), tambahkan perintah Option Private Module pada awal modul sebelum fungsi pertama. Jika kita ingin berhenti secara paksa dari suatu prosedur, maka gunakan perintah Exit Sub. Sedangkan jika kita ingin berhenti secara paksa dari suatu fungsi, maka gunakan perintah Exit Function.

Menjalankan Fungsi Cara untuk menjalankan fungsi sama seperti menjalankan prosedur, diantaranya adalah : Menjalankan dari prosedur atau fungsi lain. Misalnya, kita sudah membuat fungsi SumArray, maka kita bisa memasukkan perintah pada salah satu prosedur/fungsi lain sebagai berikut : Total = SumArray(ArrayKu) Perintah di atas akan menjalankan fungsi SumArray dengan argumen/parameter ArrayKu dan menghasilkan nilai yang dimasukkan ke variabel Total. Memasukkannya ke dalam formula Excel. Sebelum kita menulis formula yang menggu-nakan fungsi yang kita buat, kita harus memastikan bahwa fungsi tersebut bisa diakses oleh worksheet (jangan memberikan perintah Private pada fungsi yang bersangkutan). Jika fungsi tersebut berada dalam satu workbook yang sama, tidak ada masalah dalam penggunaannya. Tetapi jika berada dalam workbook yang berbeda, kita harus ‘memberitahu’ Excel letak fungsi yang bersangkutan dengan cara mendahului nama fungsinya dengan nama workbooknya, misalnya : =DataUmum.xls!HitungRunningStock(A1:A1000) Perintah di atas akan menjalankan fungsi HitungRunningStock yang terdapat di dalam workbook DataUmum.xls. Jika kita perhatikan, fungsi yang kita buat dengan VBA tidak akan tampil di dalam daftar kotak dialog Macro ketika kita mengaktifkan menu Tools→Macro→Macros…. Sebagai tambahan, kita juga tidak bisa menjalankan fungsi dengan cara memposisikan kursor di fungsi tersebut (ketika berada di dalam VB Editor) lalu memilih menu Run→Run Sub/UserForm. Ini menjadikan kita agak sulit untuk menguji fungsi tersebut karena kita tidak bisa menjalankannya secara langsung. Satu cara yang cukup baik untuk menguji suatu fungsi adalah dengan membuat sebuah prosedur dan hanya menaruh perintah untuk menjalankan fungsi tersebut di dalamnya.


87


Argumen/Parameter Prosedur/fungsi (selanjutnya kita sebut prosedur saja), seperti kita tahu, dapat memiliki argumen/parameter (selanjutnya kita sebut argumen saja). Argumen adalah data yang dikirim untuk digunakan pada perintah yang terdapat dalam prosedur. Argumen dapat berupa : Variabel (termasuk array), misalnya : Call TampilkanData(ArrayData); Konstanta, misalnya : j = CariDeterminan(1, 3, 2); atau Objek, misalnya : HapusIsiSheet Sheet1. Hubungan antara prosedur dan argumen dapat berupa sebagai berikut : Prosedur dapat tidak memiliki argumen, misalnya RAND; ‘Contoh fungsi tanpa argumen Function NamaUser() User = Application.UserName End Function Prosedur dapat memiliki argumen dalam jumlah yang tetap, misalnya COUNTIF yang membutuhkan dua argumen; ‘Contoh prosedur dengan argumen Sub Ucapan(Nama As String) MsgBox “Selamat Datang ” & Nama End Sub Prosedur dapat memiliki argumen yang jumlahnya tidak terbatas, misalnya SUM; dan ‘Contoh fungsi dengan argumen tak terbatas Function Total(ParamArray Nilai() As Variant) Dim I As Integer Total = 0 For I = 0 To Ubound(Nilai()) Total = Total + Nilai(I) Next I End Function Prosedur dapat memiliki argumen yang opsional, misalnya PMT yang memiliki dua argumen opsional. ‘Contoh prosedur dengan argumen opsional Sub Alamat(Jalan As String, Optional Kota As Variant, Optional Negara As Variant = "Indonesia") If IsMissing(Kota) And IsMissing(Negara) Then MsgBox Jalan ElseIf IsMissing(Kota) Then MsgBox Jalan & “ ” & Negara Else MsgBox Jalan & “ ” & Kota & “ ” & Negara End If End Sub

CATATAN … Jika kita menguji suatu fungsi melalui formula dalam worksheet dan menghasilkan nilai #VALUE!, maka fungsi kita memiliki kesalahan di dalamnya. Kesalahan ini bisa disebabkan oleh logical error yang terdapat di dalam perintah kita atau mungkin juga karena kita memberikan argumen yang salah. Untuk lebih jelasnya mengenai penanganan kesalahan, lihat bagian “Penanganan Kesalahan”.

Antara Variabel Public dan Argumen Prosedur Pada bagian sebelumnya kita sudah mempelajari bagaimana menggunakan argumen sebagai penghubung antar prosedur. Sebenarnya terdapat cara lain untuk menghubungkan satu prosedur dengan prosedur lain, yaitu dengan menggunakan variabel Public. Perhatikanlah perbedaan kedua contoh berikut untuk lebih jelasnya : ‘Dengan menggunakan argumen ‘Dengan menggunakan variabel Public Sub Prosedur1() Public Bulan As Integer Dim Bulan As Integer Sub Prosedur2() Bulan = 4 Bulan = 4 Call ProsesBulanan(Bulan) ProsesBulanan End Sub End Sub Karena pada blok sebelah kanan variabel Bulan berjenis Public, maka secara otomatis prosedur ProsesBulan akan mengenalnya dan kita bisa menghilangkan argumen yang dibutuhkannya.


88


By Value atau By Reference ? Sebagai informasi tambahan—sebuah argumen dapat dilewatkan ke prosedur dengan dua cara: by value atau by reference. Dengan melewatkan argumen by reference (default), VBA akan memberikan alamat memori dimana variabel tersebut berada—sehingga jika terdapat perintah dalam prosedur yang mengubah isi argumen tersebut, maka perubahannya akan dibawa walaupun setelah keluar dari prosedur yang bersangkutan. Jika melewatkan argumen by value, VBA hanya akan mengirim duplikat variabel tersebut—sehingga jika terjadi perubahan isi, hasilnya tidak akan dibawa keluar prosedur tersebut. Untuk mengetahui perbedaannya lebih jelas, buatlah empat prosedur di bawah ini dan jalankan PanggilByVal dan PanggilByRef : Sub ProcByVal(ByVal NilaiKu) MsgBox “Sebelum Proc =” & NilaiKu NilaiKu = NilaiKu + 10 MsgBox “Sesudah Proc =” & NilaiKu End Sub

Sub ProcByRef(NilaiKu) MsgBox “Sebelum Proc =” & NilaiKu NilaiKu = NilaiKu + 10 MsgBox “Sesudah Proc =” & NilaiKu End Sub

Sub PanggilByVal() Dim Nilai As Integer Nilai = 10 MsgBox “Sebelum Panggil =” & Nilai ProcByVal Nilai MsgBox “Sesudah Panggil =” & Nilai End Sub

Sub PanggilByRef() Dim Nilai As Integer Nilai = 10 MsgBox “Sebelum Panggil =” & Nilai ProcByRef Nilai MsgBox “Sesudah Panggil =” & Nilai End Sub

Contoh Prosedur dan Fungsi Fungsi di bawah ini digunakan untuk menghasilkan nama user yang aktif dengan mengakses properti UserName yang terdapat di dalam objek Application : Function User() User = Application.UserName End Function Untuk menggunakannya dalam worksheet, kita hanya cukup memasukkan formula =User() dalam suatu cell. MsgBox “User yang aktif adalah ” & User() adalah contoh penggunaan di dalam VBA. Fungsi di bawah ini digunakan untuk menghasilkan bilangan acak antara 0 hingga 1000 dengan menggunakan fungsi Rnd : Function Acak() Acak = Int(Rnd * 1000) End Function Perlu diperhatikan bahwa nilai yang dihasilkan oleh fungsi di atas tidak akan berubah jika ditaruh di dalam worksheet walaupun sudah dilakukan proses Recalculate. Berbeda dengan fungsi worksheet yang sudah disediakan oleh Excel, yaitu RAND().

Mengatur Proses Recalculation Suatu Fungsi

Suatu fungsi VBA akan di-recalculate jika terjadi perubahan nilai pada argumen milik fungsi tersebut. Fungsi Acak sebelumnya tidak memiliki argumen, sehingga walaupun kita recalculate secara manual dengan menggunakan tombol F9, hasilnya tidak akan pernah berubah. Untuk memaksa suatu fungsi agar melakukan recalculate kita bisa menambahkan perintah Application.Volatile True. Volatile yang terdapat di dalam objek Application bisa diberi argumen True atau False. Jika argumennya diisi dengan


89

Programming in Microsoft Excel 2000 True maka fungsi akan di-recalculate jika ada perubahan apa pun pada cell mana pun. Jika argumennya diisi dengan False maka fungsi akan di-recalculate hanya jika terjadi perubahan nilai pada argumen fungsi yang bersangkutan. Jadi kita bisa memodifikasi fungsi sebelumnya menjadi seperti berikut jika ingin menghasilkan nilai yang berbeda-beda setiap ditekan tombol F9 : Function Acak() Application.Volatile True Acak = Int(Rnd * 1000) End Function

Dalam kasus ini, seorang manajer departemen Sales menghadapi kesulitan dalam menghitung komisi masing-masing sales terhadap hasil penjualannya per bulan. Perhitungannya didasarkan atas tabel berikut ini : Penjualan per Bulan $0 - $9,999 $10,000 - $19,999 $20,000 - $39,999 $40,000+ Sebenarnya masalah di atas bisa diselesaikan

Komisi 8.0% 10.5% 12.0% 14.0% dengan menggunakan formula sederhana

sebagai berikut : =IF(AND(A1>=0,A1<=9999.99),A1*0.08, IF(AND(A1>=10000,A1<=19999.99),A1*0.105, IF(AND(A1>=20000,A1<=39999.99),A1*0.12, IF(A1>=40000,A1*0.14, 0)))) Hanya masalahnya, formula di atas terlalu sulit untuk dibaca jika sudah dimasukkan ke dalam worksheet karena Excel akan membuat formula di atas menjadi satu baris panjang. Dan satu lagi, formula tersebut agak sulit untuk dimodifikasi karena nilai komisinya tertanam secara konstan. Pendekatan lain yang lebih baik adalah dengan membuat fungsi sebagai berikut : Function KomisiSales(Penjualan) Const Komisi1 = 0.08, Komisi2 = 0.105, Komisi3 = 0.12, Komisi4 = 0.14 Select Case Penjualan Case 0 To 9999.99: KomisiSales = Penjualan * Komisi1 Case 10000 To 19999.99: KomisiSales = Penjualan * Komisi2 Case 20000 To 39999.99: KomisiSales = Penjualan * Komisi3 Case Is >= 40000: KomisiSales = Penjualan * Komisi4 End Select End Function

Kita bisa menggunakan fungsi di atas dalam worksheet seperti =KomisiSales(25000) atau menggunakannya di dalam prosedur VBA sebagai berikut : Sub TampilkanKomisi() Penjualan = Val(InputBox(“Masukkan Nilai Penjualan ($) :”)) Pesan = “Nilai Penjualan:” & vbTab & Format(Penjualan,“$#.##0”) & vbCrLf Pesan = Pesan & “Komisi:” & vbTab & Format(KomisiSales(Penjualan),“$#.##0”) Pesan = Pesan & vbCrLf & vbCrLf & “Mau Hitung Lagi ?” If (MsgBox(Pesan, vbYesNo) = vbYes) Then TampilkanKomisi End Sub

Hasilnya kurang lebih terlihat seperti gambar 2.7 di bawah ini.

Gambar 2.7 Hasil dari contoh prosedur di atas

Fungsi di bawah ini dapat digunakan untuk menggantikan fungsi worksheet SUM() yang akan menghitung seluruh komponen yang terdapat di dalam array : Function SumArray(List) As Double SumArray = 0 For Each Item In List


90

Programming in Microsoft Excel 2000 If Application.IsNumber(Item) Then _ SumArray = SumArray + Item Next Item End Function Fungsi IsNumber di atas akan memeriksa apakah suatu komponen (Item) yang terdapat di dalam List berjenis bilangan atau bukan. Jika bilangan maka akan dijumlah ke SumArray. Selanjutnya kita bisa membuat formula worksheet seperti =SumArray(A1:C10) atau membuat prosedur sebagai berikut : Sub BuatDaftarAcak() Dim Num(1 To 100) As Double For i = 1 To 100 Num(i) = Rnd * 1000 Next i MsgBox SumArray(Num) End Sub Mungkin kita bertanya-tanya bagaimana jika fungsi kita menerima argumen yang salah dan kita ingin mengirim pesan kesalahan ke worksheet misalnya #N/A. Berikut adalah modifikasi dari fungsi terdahulu yang digunakan untuk membalik suatu teks : Function Balik(Kalimat) As Variant Dim PanjangKalimat As Integer, I As Integer If Application.WorksheetFunction.IsText(Kalimat) Then Balik = “” PanjangKalimat = Len(Kalimat) For I = PanjangKalimat To 1 Step –1 Balik = Balik & Mid(Kalimat, I, 1) Next I Else Balik = CVErr(xlErrNA) End If End Function Pertama, akan dilakukan pemeriksaan terhadap argumen yang dimasukkan apakah jenis teks atau bukan (dengan menggunakan fungsi IsText). Jika jenisnya teks maka akan dilakukan proses yang sama dengan fungsi terdahulu. Jika bukan, maka akan dihasilkan nilai kesalahan dengan menggunakan fungsi CVErr dengan argumen xlErrNA. Argumen fungsi CVErr bisa

diisi dengan konstanta nilai

kesalahan xlErrDiv0, xlErrNA,

xlErrName, xlErrNull, xlErrNum, xlErrRef, atau xlErrValue.

Teknik Penanganan Kesalahan Ketika kita menjalankan suatu prosedur, kesalahan (error) dapat muncul setiap saat—yang bahkan tidak kita perkirakan sebelumnya. Kesalahan ini termasuk kesalahan sintaksis (syntax errors) yang terjadi karena kita salah dalam memasukkan perintah VBA, ataupun kesalahan yang terjadi ketika prosedur dijalankan (run-time errors). Pada bagian ini, yang akan dibahas adalah penanganan run-time errors.

CATATAN … Agar penanganan kesalahan ini dapat bekerja, opsi Break on All Errors harus dimatikan. Untuk mengakses opsi ini masuk Tools→Options…→tab General. Jika kita menyalakan opsi Break on All Errors, maka VBA akan mengabaikan kode penanganan kesalahan yang kita buat.


91

Programming in Microsoft Excel 2000 Biasanya programmer selalu mengaktifkan opsi Break on Unhandled Errors.

Ketika error terjadi, VBA biasanya berhenti dan menampilkan kotak dialog yang menampilkan kode kesalahan dan keterangan akan error tersebut. Suatu program yang baik tidak akan membiarkan suatu pesan kesalahan terlihat oleh pengguna, melainkan akan ‘menangkap’ error tersebut

lalu

mengambil

tindakan

tertentu.

Sekurang-kurangnya

program

harus

dapat

menampilkan pesan kesalahan yang lebih dimengerti oleh pengguna daripada pesan yang diberikan oleh VBA.

‘Menangkap’ Error Untuk menangkap error, kita bisa menggunakan perintah On Error yang memiliki dua teknik penggunaan, yaitu : Mengabaikan kesalahan yang ada dan mengizinkan VBA untuk melanjutkan proses. Kemudian pada perintah selanjutnya kita bisa menambahkan proses untuk menganalisa jenis kesalahan yang timbul dengan menggunakan objek Err, lalu mengambil tindakan tertentu jika diperlukan. Teknik ini menggunakan perintah On Error Resume Next. Melompat ke bagian khusus penanganan kesalahan untuk mengambil tindakan. Bagian ini biasanya diletakkan pada akhir suatu prosedur, dan ditandai dengan alamat (label). Teknik ini menggunakan perintah On Error Goto error_label. On Error Resume Next

92

Agar VBA mengabaikan suatu kesalahan dan melanjutkan ke proses berikutnya, tambahkan perintah berikut ini di awal suatu prosedur : On Error Resume Next Beberapa error mungkin dapat kita abaikan tanpa ‘memberitahu’ pengguna akan adanya suatu error. Tetapi mungkin kita ingin mengambil tindakan jika terjadi error-error tertentu. Kita bisa menggunakan objek Err yang memiliki properti Number (merupakan properti default) untuk mendapatkan kode error yang timbul. Lalu kita bisa menggunakan fungsi Error untuk mendapatkan keterangannya (bisa juga diakses melalui properti Description pada objek Err). Sebagai contoh, kedua baris perintah di bawah ini akan menampilkan pesan kesalahan yang kurang lebih sama dengan yang ditampilkan oleh VBA ketika terjadi suatu error : MsgBox “Error” & Err.Number & “: ” & Error(Err.Number) atau MsgBox “Error” & Err & “: ” & Err.Description Berikut adalah contoh prosedur yang memiliki penanganan kesalahan dengan menggunakan On Error Resume Next : Sub ProsesFormula() On Error Resume Next Selection.SpecialCells(xlFormulas, xlNumbers).Select If (Err <> 0) Then MsgBox “Tidak ada formula yang dipilih” Else ‘proses... End If End Sub


Programming in Microsoft Excel 2000 Pada prosedur diatas jika nilai Err tidak sama dengan 0 (nol), maka telah terjadi kesalahan dan akan menampilkan pesan kesalahan. On Error Goto error_label Selain cara di atas, VBA menyediakan satu teknik lagi yaitu dengan melompat ke bagian khusus yang menangani kesalahan. Agar VBA melakukan hal tersebut, perlu ditambahkan perintah berikut ini pada awal prosedur : On Error Goto error_label error_label di atas adalah blok alamat program penanganan kesalahan yang terletak dalam prosedur yang sama. Perhatikan contoh berikut : Sub IsiNilai() On Error Goto tangani_error Selection.Value = 123 Exit Sub tangani_error: MsgBox “Tidak bisa mengisi nilai” End Sub Prosedur di atas mencoba untuk mengisi nilai ke objek yang dipilih (Selection). Jika yang dipilih bukan range atau suatu sheet dalam keadaan diproteksi, maka perintah Selection.Value = 123 akan menimbulkan kesalahan. Perintah On Error sebelumnya sudah menentukan bahwa jika terjadi kesalahan maka proses lompat ke tangani_error. Penggunaan Exit Sub ditujukan untuk proses yang berjalan lancar tanpa menimbulkan suatu kesalahan agar tidak masuk ke dalam blok penanganan kesalahan. Keuntungan dari penggunaan teknik ini dibandingkan dengan teknik sebelumnya adalah teknik ini memungkinkan untuk menulis satu penanganan kesalahan untuk keseluruhan prosedur, sedangkan dengan teknik sebelumnya kita harus menulis satu penanganan kesalahan untuk setiap perintah yang memiliki kemungkinan terjadinya kesalahan. Untuk lebih jelasnya perhatikan contoh berikut : ‘contoh dengan on error resume next Sub ErrorDemo1() Dim Nilai As Integer Dim Kondisi As Boolean On Error Resume Next Nilai = “Salah” If (Err.Number <> 0) Then _ MsgBox “Salah mengisi nilai” Err.Clear Kondisi = “Benar” If (Err.Number <> 0) Then _ MsgBox “Salah mengisi nilai” End Sub

‘contoh dengan on error goto ... Sub ErrorDemo2() Dim Nilai As Integer Dim Kondisi As Boolean On Error Goto periksa Nilai = “Salah” Kondisi = “Benar” Exit Sub periksa: If (Err.Number <> 0) Then _ MsgBox “Salah mengisi nilai” Err.Clear Resume Next End Sub

CATATAN … Sebagian besar programmer cenderung menggunakan teknik On Error Resume Next, walaupun Biasanya programmer selalu mengaktifkan opsi Break on Unhandled Errors.

Manfaat ‘Menangkap’ Kesalahan


93

Programming in Microsoft Excel 2000 Terkadang, kita bisa mendapatkan keuntungan dari ‘menangkap’ kesalahan misalnya untuk mendapatkan informasi. Contoh berikut ini digunakan untuk memeriksa apakah suatu workbook sedang dibuka atau tidak : Sub CheckForFile1() Dim FileName As String, FileExist As Boolean Dim book As Workbook FileName = “BUDGET.XLS” FileExist = False For Each book In Workbooks If UCase(book.Name) = UCase(FileName) Then _ FileExist = True Next book If FileExist Then _ MsgBox FileName & “ sedang dibuka” Else _ MsgBox FileName & “ tidak sedang dibuka” End Sub Pada prosedur di atas perintah For Each-Next akan melakukan looping proses untuk semua workbook yang sedang dibuka. Jika workbook yang dimaksud sedang dibuka (UCase(book.Name) = UCase(FileName)) maka variabel FileExist diisi dengan nilai True. Prosedur di atas bisa disusun ulang dengan menggunakan On Error Resume Next sebagai berikut : Sub CheckForFile2() Dim FileName As String, Temp As String On Error Resume Next FileName = “BUDGET.XLS” Temp = UCase(Workbooks(FileName).Name) If (Err = 0) Then MsgBox FileName & “ sedang dibuka” Else MsgBox FileName & “ tidak sedang dibuka” End If End Sub Prosedur di atas mencoba untuk mengisi variabel Temp dengan nama dari workbook berdasarkan FileName. Jika workbook yang bersangkutan sedang dibuka maka tidak ada error yang muncul (Err = 0). Sebaliknya jika workbook yang bersangkutan tidak sedang dibuka maka error akan muncul (Err <> 0).

Debugging Ketika kita membuat formula workseheet yang menggunakan fungsi yang sudah kita buat sebelumnya, run-time errors tidak akan muncul dalam bentuk kotak dialog, melainkan fungsi tersebut akan mengembalikan nilai kesalahan tersebut ke cell yang bersangkutan (misalnya #VALUE!). Untuk mengatasi hal tersebut, kita harus membetulkan fungsi tersebut. Tapi, bagimana kita tahu letak kesalahannya ? Berikut adalah cara untuk mengetahuinya : Dengan menempatkan perintah MsgBox untuk menampilkan isi suatu variabel pada daerah yang kita ‘curigai’. Lalu kita bisa menganalisa apakah isi variabel tersebut sudah benar atau belum. Jika belum maka pasti terjadi kesalahan pada proses-proses sebelumnya. Akan tetapi, kita sebelumnya harus memastikan terlebih dahulu bahwa hanya satu formula saja yang menggunakan fungsi yang bersangkutan, jika tidak maka MsgBox akan dijalankan sebanyak formula yang ada di worksheet—dan ini akan sangat mengganggu. Menguji fungsi dengan menjalankannya dari prosedur dan bukannya dari formula worksheet. Memasang breakpoint pada perintah yang kita ‘curigai’, lalu melangkah perintah per perintah dan mengamati dimana letak kesalahannya.


94

Programming in Microsoft Excel 2000 Untuk memasang breakpoint, pilih baris perintah yang diinginkan lalu pilih menu Debug → Toggle Breakpoint (F9). Atau bisa juga dengan klik daerah abu-abu pada samping kiri baris perintah. VBA kemudian akan menampilkan baris tersebut dengan warna merah. Lalu jalankan prosedur seperti biasa dan proses akan berhenti pada baris dimana kita memasang breakpoint tersebut (baris perintah tersebut belum dijalankan). Dan kita bisa melangkah per perintah dengan memilih menu Debug → Step Into (F8) atau menjalankan lagi secara normal. Menggunakan perintah Debug.Print di dalam prosedur untuk mencetak nilai suatu variabel dalam Immediate window. Sebagai contoh, jika kita ingin memantau nilai yang berada di dalam suatu loop, kita bisa menambahkan perintah sebagai berikut : Function HitungVokal(Kalimat As String) As Integer Dim TotVokal As Integer, i As Integer, c As String * 1 TotVokal = 0 For i = 1 To Len(Kalimat) c = UCase(Mid(Kalimat, i, 1)) If (c Like “[AEIOU]”) Then TotVokal = TotVokal + 1 Debug.Print c, i End If Next i HitungVokal = TotVokal End Function Jika kita menjalankan fungsi di atas dengan argumen “Gajah Tunggal”, maka Immediate Window kurang lebih akan menjadi seperti gambar 2.8. di bawah.

Gambar 2.8 Immediate Window ketika kode di atas dijalankan

LATIHAN Ketika kita melakukan perekaman macro dengan macro recorder maka hasilnya adalah seperti di bawah ini. Ubahlah prosedur copy di bawah ini menjadi lebih sederhana lagi ! Sub Macro1() Range(“A1”).Select Selection.Copy Range(“B1”).Select ActiveSheet.Paste Application.CutCopyMode = False End Sub Properti CurrentRegion milik objek Range digunakan untuk mendapatkan satu blok range—yang dipisahkan oleh baris dan kolom kosong—dimana objek range yang dimaksud terdapat di dalamnya. Jika dalam Sheet1 kita memiliki satu tabel yang dimulai dari cell A1 dan selalu bertambah barisnya, buatlah prosedur untuk melakukan proses copy dari Sheet1 ke Sheet2! Jika butuh keterangan lebih lanjut, bukalah sistem Help VBA mengenai CurrentRegion !


95

Programming in Microsoft Excel 2000 Jadikanlah prosedur yang sudah dikerjakan pada nomor 2 di atas bisa diakses melalui menubar, toolbar, dan shortcut Ctrl+Shift+* ! Buatlah fungsi yang menyerupai fungsi worksheet SUM() ! Range("B4").End(xlUp).Select digunakan untuk memilih cell paling atas—yang berada pada daerah yang sama dengan cell B4—yang terdapat pada kolom B. Range(ActiveCell, ActiveCell.End(xlDown)).Select digunakan untuk memilih range mulai dari cell yang aktif hingga cell terbawah yang digunakan. Bisa kita simpulkan bahwa properti End bekerja persis seperti tombol End dalam Excel dan konstanta yang dapat digunakan adalah xlUp, xlDown, xlToLeft, dan xlToRight. Buatlah sekumpulan menu beserta programnya seperti terlihat pada gambar 2.9 di bawah ini ! Untuk informasi lebih lanjut, buka sistem Help yang ada di VBA ! Gambar 2.9 Menu yang harus dibuat untuk latihan nomor 5

96

Sebagian besar koleksi objek di dalam VBA memiliki properti Count yang digunakan untuk mendapatkan jumlah objek yang terdapat dalam koleksi tersebut. Misalnya Selection.Cells.Count digunakan untuk menghitung jumlah cell yang sedang dipilih. Sekarang buat sebuah tombol di dalam worksheet lalu buatlah sebuah prosedur untuk menganalisa jumlah cell yang dipilih, jumlah baris dan kolom yang dipilih, dan jumlah area yang dipilih ! Buatlah prosedur untuk menghapus baris yang tidak memiliki satu cell pun yang ada isinya atau semua cellnya kosong ! Buatlah fungsi yang mampu menghasilkan jenis data suatu cell atau range (dengan mengambil cell paling kiri atas) ! Buatlah prosedur atau fungsi untuk menghasilkan deret-deret berikut ini : 1, 2, 3, 4, 5, ……, 10 1, 3, 5, 7, 9, ……, 19 10 suku pertama dari 100, 95, 90, 85, 80, …… 10 suku pertama dari 7, 12, 20, 31, 45, 62, …… 1, 2, 4, 8, 16, ……, 1024 Program di bawah ini memiliki beberapa kesalahan. Perbaikilah program sehingga


Programming in Microsoft Excel 2000 bisa untuk menghasilkan deret bilangan sebagai berikut : 1, 3, 2, 4, 3, 5, 4, 6, 5, 7, 6, 8, 7, 9, 8, 10, 9, 11, 10, 12 Sub DeretSalah() Dim Nilai As Integer Nilai = 1 Do While (Nilai < 10) Debug Print Nilai; Nilai + 1; Nilai = Nilai + 2 Loop End Sub Buatlah suatu prosedur untuk menukar isi dua buah bilangan ! Misalkan A = 7 dan B = 5, lalu dilakukan Call Tukar(A, B), maka menjadi A = 5 dan B = 7. Buatlah satu prosedur untuk melakukan langkah-langkah berikut : menyediakan array berukuran 20; mengisi masing-masing elemen array dengan bilangan acak antara 10 sampai 20; menentukan nilai rata-rata, nilai maksimum, dan nilai minimum dari seluruh elemen array; lalu menampilkan hasilnya di Immediate Window atau dengan MsgBox. Buatlah satu prosedur untuk melakukan langkah-langkah berikut : menyediakan dua buah array—misalnya P dan Q—berukuran 3x4 (array dua dimensi biasa disebut matriks); mengisi masing-masing elemen matriks dengan bilangan acak genap antara 0 sampai 20; jumlahkan matriks P dan Q dan simpan hasilnya di matriks R; lalu tampilkan isi matriks R. Buatlah satu prosedur untuk mengisi dua buah matriks berukuran 2x3 dengan bilangan acak antara 1 sampai 10! Kemudian tukarlah semua elemen antara matriks-matriks tersebut, matriks B ke dalam matriks A, dan sebaliknya! Tampilkan isi matriks sebelum dan sesudah ditukar !


97

Penyusun : Achmad Affandi ( )

Recommend Documents