Nota Kursus
BASIC EXCEL MACRO DAN VBA PROGRAMMING
Disediakan Oleh:
MOHD SAFFUAN BIN SUNTONG
1
Modul Kursus Basic Excel Macro dan VBA Programming Hari 1
Modul Pengenalan Kepada Excel Macro dan VBA Mempelajari asas kepada macro programming: a) Pengenalan kepada Macro dan VBA b) Merekod aktiviti Excel sebagai Macro dan mencipta Macro sendiri c) Pembangunan form ringkas di Excel d) Manipulasi komponen Excel menggunakan Macro Asas Macro Programming menggunakan VBA Mempelajari asas macro programming menggunakan bahasa VBA (Visual Basic for Application): a) Pengenalan kepada Visual Basic Editor b) Mentakrif VBA Variable c) VBA Array d) VBA Operators dan Built-In Function e) VBA If-Then-Else Statement f) VBA Select-Case Statement g) VBA Do-While / Do-Until Loop h) VBA For-Next Loop i) VBA For-Each Loop j) Membina VBA Sub-Procedure dan Function sendiri
Muka Surat
Tempoh (Jam) 2.5 jam
1 2 7 10
4 jam 13 14 15 17 20 22 24 26 28 29
2
MODUL 1: PENGENALAN KEPADA EXCEL MACRO dan VBA 1. Pengenalan kepada Macro dan VBA Microsoft Excel adalah sebuah program aplikasi spreadsheet yang dibangunkan oleh Microsoft. Ia juga adalah salah satu perisian yang dimasukkan ke dalam Microsoft Office yang standard selain MS Word dan MS Powerpoint. Se belum kewujudan MS Excel, aplikasi spreadsheet yang paling terkenal dan banyak digunakan adalah Lotus 1-2-3. Namun sejak munculnya MS Excel, ia telah mengambil alih perisian Lotus 1-2-3 sebagai aplikasi spreadsheet yang paling terkenal. Aplikasi MS Excel versi pertama yang dikeluarkan adalah MS Excel 2.0, kemudian diikuti dengan MS Excel 3.0, MS Excel 4.0, MS Excel 5.0, MS Excel 95, MS Excel 97, MS Excel 2000, MS Excel 2002, MS Excel 2003, MS Excel 2007 dan yang paling terkini iaitu MS Excel 2010.
MS Excel mempunyai fungsi yang dipanggil macro yang boleh digunakan untuk merekod segala aktiviti dan arahan yang boleh digunakan di dalam Excel dan kemudian akan disimpan dalam bentuk program macro. Dengan membangunkan mem bangunkan macro, pengguna boleh menjalankan segala fungsi dan arahan yang telah diprogramkan dalam macro dengan hanya menjalankan sahaja macro tersebut. Macro juga dikenali sebagai VBA procedure. VBA bermaksud Visual Basic for Applications di mana penggunakan boleh m embangunkan sendiri aplikasi ringkas dengan hanya menggunakan MS Excel dengan me nggunakan bahasa VBA yang boleh difahami oleh MS Excel dan ia a kan menjalankan segala yang telah diprogramkan. Untuk membangunkan program VBA, pengguna akan m embuat programming di dalam Visual Basic Editor. 1
2. Merekod aktiviti Excel sebagai Macro dan mencipta Macro sendiri Bagi menggunakan Macro dan VBA, bagi MS Excel 2010, keluarkan dahulu menu Developers dengan pergi ke File > Options. Kemudian pilih Customize Ribbon, dan pada “Choose commands from”, pilih All Tabs. Highlight Highlight pada Developer dan tekan butang butang Add >> untuk memasukkan menu Developer ke c olumn sebelah kanan. Pastikan Developer telah ditandakan dan tekan butang OK. Perhatikan menu Developer telah wujud di bahagian Menu di bahagian atas. Klik pada menu Developer untuk melihat kandungan submenu bagi Developer.
Bagi MS Excel 2007, klik pada dan klik pada butang Excel Options. Kemudian tandakan “Show Developer tab in the Ribbon” dan klik OK.
2
Main Menu:
Bagi mencuba untuk merekod segala aktiviti yang kita lalukan di dalam Excel, klik pada submenu Record Macro. Tetingkap berikut akan dipaparkan:
Anda boleh namakan apa-apa sahaja untuk nama Macro yang ak an direkod, seperti contoh Macro1. Shortcut key digunakan untuk kita ta krifkan sendiri shortcut key yang hendak digunakan untuk menjalankan Macro yang akan direkod. Secar a default simpan sahaja macro di dalam This Workbook. Tekan OK untuk mula merekod. Taip apa-apa sahaja di dalam worksheet Excel seperti contoh di bawah:
Pada bahagian Jumlah, jumlahkan kesemua harga barangan dengan menggunakan fungsi AutoSum pada menu Home.
3
Setelah tamat, pada menu Developer, D eveloper, klik pada Stop Recording. Dengan it u segala aktiviti yang baru sahaja dilakukan telah direkod ke dalam macro Macro1.
Sekarang padam semua perkara yang telah kita lakukan di dalam worksheet Excel sebentar tadi. Klik pada submenu Macros di menu Developer. Pilih Macro1 dan tekan butang Run.
Didapati segala isi kandungan worksheet yang baru dipadam akan muncul semula. I ni kerana segala aktiviti sebelum ini telah pun direkod dan apabila dijalankan, ia akan melaksanakan semula aktiviti yang telah direkod tersebut.
4
Aktiviti yang direkod ini disimpan di dalam satu procedure Macro yang diprogramkan dalam bahasa VBA. Untuk melihat program tersebut, klik semula pada submenu Macros di menu Developer, pilih Macro1 dan tekan butang Edit. Sebuah aplikasi Visual Basic Editor akan dibuka dan di dalamnya terkandung program Macro1 yang t elah direkod sebentar tadi.
Di dalam program Macro1 tersebut, ters ebut, segala aktiviti menaip, pilihan cell dan lain-lain akan direkod. Kini kita akan cuba untuk mengubah dan membangunkan sendiri kod aturcara untuk mendapatkan hasil yang sama dengan kaedah yang berlainan. Untuk menghantar nilai kepada sel-sel tertentu di dalam worksheet Excel, format sintaks adalah seperti berikut: Range("[No. Sel]").Value = "[Nilai]” Contohnya penggunaan adalah seperti berikut di mana pada sel A1 akan diisi dengan teks “Senarai Harga Barang” apabila dijalankan: Range("A1").Value = "Senarai Harga Barang"
5
Untuk memasukkan formula di dalam sebuah sel, gunakan sintaks seperti contoh berikut: Range("A6").Formula = "=SUM(A2:A5)" Untuk membina macro sendiri, padam semua aturc ara program bagi Macro1 dan cuba gantikan dengan aturcara berikut: Sub Macro1() Range("A1").Value = "Senarai Harga Barang" Range("A3").Value = "Komputer" Range("B3").Value = "2000" Range("A4").Value = "Monitor" Range("B4").Value = "500" Range("A5").Value = "Printer" Range("B5").Value = "400" Range("A6").Value = "Scanner" Range("B6").Value = "150" Range("A7").Value = "Jumlah" Range("B7").Formula = "=SUM(B3:B6)" End Sub Padam juga semula semua isi kandungan di dalam Excel worksheet itu tadi. Kemudian pergi semula ke Visual Basic Editor dan tekan butang Run Sub/UserForm atau tekan F5 untuk menjalankan Macro1 yang baru dicipta tadi.
Lihat semula pada worksheet dan didapati isi kandungan yang t elah dipadam wujud semula. Tanpa menggunakan fungsi Record Macro, kita juga boleh membangunkan program macro kita sendiri dengan menggunakan teknik pengaturcaraan VBA.
6
3. Pembangunan form ringkas di Excel Untuk membangunkan form di dalam Excel dengan menggunakan be berapa komponen seperti Textbox, Combobox, List box, Button dan sebagainya, pada menu Developer, klik pada submenu Design Mode untuk mula merekabentuk form.
Untuk memasukkan komponen form, klik pada submenu Insert dan pilih komponen form yang hendak digunakan. Di dalam tutorial ini, kita akan menggunakan komponen form daripada ActiveX Controls.
Bina satu form seperti gambar yang berikut yang mengandungi Textbox, Combobox dan Command Button:
7
Setiap komponen mempunyai nama yang unik yang digunakan untuk mengenalpasti komponenkomponen tersebut. Untuk mengetahui nama komponen, klik kanan pada komponen dan klik Properties.
Properties bagi komponen tersebut akan dipaparkan seperti berikut:
Seperti contoh, nama bagi komponen te rsebut adalah ComboBox1. Anda juga boleh menamakan komponen tersebut dengan nama yang lain dengan me nukarnya pada bahagian (Name).
8
Bagi komponen ComboBox atau ListBox, untuk memasukkan data k e dalam senarai komponen, buat satu senarai di dalam mana-mana bahagian dalam worksheet seperti berikut:
Kemudian pada properties bagi ComboBox1 itu t adi, cari parameter ListFillRange dan masukkan range bagi senarai yang telah ditulis di dalam worksheet seperti contoh, ComboBox1 digunakan untuk memaparkan senarai komputer, oleh itu m asukkan range E5:E7 (mengikut no. lajur dan baris yang di komputer anda).
Tutup tetingkap properties dan klik pada submenu Design Mode untuk disablekannya. Pergi pada ComboBox itu tadi dan klik pada tanda V untuk memaparkan senarai seperti berikut:
Buat untuk kesemua ComboBox yang ada de ngan memasukkan senarai isi kandungannya di dalam Design Mode. Kemudian semak semula dan pastikan semua senarai telah dimasukkan ke dalam ComboBox. Bagi Command Button pula, secara default, perkataan yang dipaparkan di atas Command Button tersebut adalah CommandButton1. Anda boleh menukar perkataan ter sebut dengan membuka tetingkap properties bagi Command Button dan t ukar pada parameter Caption.
9
4. Manipulasi komponen Excel menggunakan Macro Setelah form ringkas siap dibangunkan, kita perlu memasukkan a turcara macro agar form kita dapat menjalankan fungsi yang kita kehendaki. Seperti contoh dengan menggunakan form yang kita bangunkan, pengguna dikehendaki memilih jenis jenama bagi setiap barang, masukkan harga dan klik butang Kira Jumlah untuk me ngetahui jumlahnya. Untuk tujuan itu, kita perlu menulis aturcara macro bagi insiden butang Kira Jumlah diklik. Bagi menulis aturcara, klik kanan pada butang Kira Juml ah dan klik View Code.
Apabila View Code diklik, Visual Basic Editor akan dibuka dan sebuah sub procedure akan dicipta secara automatik iaitu CommandButton1_Click(). Sub procedure ini bermaksud, apabila butang CommandButton1 diklik (dipanggil Event atau Insiden), maka segala aturcara di dalam sub procedure ini akan dijalankan.
10
Bagi setiap komponen, terdapat insiden yang boleh digunakan bersama komponen te rsebut. Untuk melihat senarai insiden bagi komponen, klik pada Combo Box di bahagian atas kanan seperti berikut:
Masukkan contoh aturcara berikut di dalam sub procedure CommandButton1_Click() (Ubah nama TextBox sekiranya anda ada me mbuat perubahan pada namanya): Private Sub CommandButton1_Click() TextBox5.Value = Int(TextBox1.Value) + Int(TextBox2.Value) + Int(TextBox3.Value) + Int(TextBox4.Value) End Sub Tekan butang Save dan tutup tetingkap Visual Basic Editor. Pada worksheet form kita tadi, disable Design Mode dan cuba isi form yang dibangunkan itu tadi,
Klik pada butang Kira Jumlah dan kita akan dapat lihat jumlah bagi keseluruhan harga akan dijumlahkan dan dipaparkan di TextBox yang dibawah sekali (TextBox5).
11
Kali ini kita akan cuba membuat ma nipulasi insiden yang lain iaitu pada ComboBox. Pada bahagian senarai komponen, masukkan harga bagi setiap barangan dan je nama di sebelah setiap jenama seperti berikut:
Enablekan Design Mode, klik kanan pada ComboBox1 (Pilihan jenama Komputer) dan k lik View Code. Visual Basic Editor akan dibuka dan pengguna dikehendaki memasukkan aturcara bagi insiden ComboBox1_Change() di mana aturcara akan dijalankan setiap kali pengguna membuat pemilihan pada ComboBox tersebut.
Masukkan kod seperti berikut: Private Sub ComboBox1_Change() pilihan = ComboBox1.ListIndex pilihan = pilihan + 5 TextBox1.Text = Range("F" & pilihan) End Sub Simpan dan tutup tetingkap Visual Basic Editor. Disable Design Mode dan cuba klik pada ComboBox jemana Komputer dan pilih mana-mana jenama. Sekiranya tiada masalah, se tiap kali jenama komputer dipilih, harganya juga akan ditukar sec ara automatik mengikut pada senarai yang ditetapkan tadi.
Buat untuk kesemua komponen ComboBox yang lain agar ia dapat memilih harga bagi setiap jenama barangan lain secara automatik. 12
MODUL 2: ASAS MACRO PROGRAMMING MENGGUNAKAN MENGGUNAKAN VBA 1. Pengenalan kepada Visual Basic Editor Sebelum memulakan pembelajaran asas macro programming menggunakan VBA, kita perlu mengetahui sedikit sebanyak mengenai Visual Basic Editor.
A
C B
Gambar di atas menunjukkan Visual Basic Editor y ang akan digunakan untuk menulis aturcara VBA. Berikut adalah keterangan bagi setiap bahagian di Visual Basic Editor: A. Bahagian menu dan toolbar B. Project Window yang menunjukkan aturcara VBA atau Macro akan ditulis di object at au modules yang mana C. Bahagian untuk menulis aturcara VBA
13
2. Mentakrif VBA Variable Membuat takrifan variable VBA boleh dilakukan secara global atau di dalam Sub Procedure. Untuk membuat menakrif variable, gunakan format seperti berikut: Dim [nama variable] as [format variable] Sebagai contoh: Dim name As String Dim age As Integer Dim dateofbirth As Date Dim height As Double Untuk memberi nilai pada variable, gunakan tanda = seperti berikut: Sub Macro2() name = "Ali bin Abu" age = 25 dateofbirth = #1/1/1984# height = 170.5 MsgBox "Nama saya " & name & ". Berumur " & age & " tahun dan dilahirkan pada " & dateofbirth & ". Mempunyai ketinggian " & height & " cm." End Sub Cuba jalankan Macro2 dan lihat kotak mesej seperti berikut akan dipaparkan dan semua variable yang mempunyai nilai akan ditunjukkan.
14
3. VBA Array Selain membuat variable declaration dengan menggunakan kaedah biasa, kita juga boleh melakukannya dengan menggunakan Array. Array merupakan variable yang boleh me nyimpan sekumpulan data yang dipanggil elemen. Ia dikenal melalui nama array dan no. indeks. Array yang asas berbentuk one-dimensional array. Berikut merupakan contoh untuk membuat declaration pada arr ay dan menetapkan data: Dim Films(4) As String Films(0) = "Lord of the Rings" Films(1) = "Speed" Films(2) = "Star Wars" Films(3) = "The Godfather" Films(4) = "Pulp Fiction" Untuk memanggil array Films elemen i ndeks ke-2, cuma gunakan sahaja nama array dan no. indeks seperti berikut: MsgBox Films(2) Dengan itu maklumat di dalam array Films(3) iaitu Sta r Wars akan dipaparkan:
Kebiasaannya jumlah elemen di dalam sebuah array sudah ditetapkan sewaktu m embuat variable declaration di mana ia dipanggil Static Ar ray. Seperti contoh di atas, dengan declaration Dim Films(4) As String, ia hanya menyokong sehingga elemen ke-4 sahaja. Sekiranya ktia cuba menetapkan nilai untuk indeks yang ke-5 seperti Films(5) = "Avatar", ralat “Subscript out of range” akan dipaparkan. Bilangan elemen Static A rray tidak dapat diubah.
15
Kita juga boleh declare Dynamic Array bagi array yang kita tidak tetapkan bilangan elemennya dengan cara seperti berikut: Dim Films() As Variant Dengan menggunakan Dynamic Array, kita boleh me ngubah bilangan elemen yang disokong di dalam array dengan menggunakan sintaks ReDim seperti berikut: ReDim Films(4) As Variant Apabila menggunakan Dynamic Array, kita juga boleh menetapkan data ke dalam elemen array tersebut dengan kaedah berikut di mana no. indeks bagi array tersebut mengikut turutan data bermula dengan indeks 0 dari sebelah kiri ke kanan: Films = Array("Lord of the Rings", R ings", "Speed", "Star Wars", "The G odfather", "Pulp Fiction") Array juga boleh dibuat dalam bentuk 2- dimensional atau lebih seperti contoh berikut di mana terdapat 2 no. indeks yang digunakan: Dim Films(2, 4) As String Films(0, 0) = "Lord of the Rings" Films(0, 1) = "Speed" Films(0, 2) = "Star Wars" Films(0, 3) = "The Godfather" Films(0, 4) = "Pulp Fiction" Films(1, 0) = "Karak" Films(1, 1) = "KL Gangster" Films(1, 2) = "Hikayat Merong Mahawangsa" Films(1, 3) = "Kongsi" Films(1, 4) = "Seru" Dengan contoh di atas, untuk memanggil array Films bagi elemen indeks pertama iaitu 1 dan indeks kedua iaitu 2 adalah sepeti berikut: MsgBox Films(1,2) Dengan itu maklumat di dalam array Films(1,2) iaitu Hikayat Mer ong Mahawangsa akan dipaparkan:
16
4. VBA Operators dan Built-In Function Terdapat beberapa built-in VBA operator operator yang boleh digunakan dalam dalam kod VBA Excel. Antaranya adalah operator Mathematical, operator Stri ng, operator Perbandingan dan operator Logikal. a.
Operator Mathematical
Operator ^
Fungsi Kuasa
*
Darab
/
Bahagi
\
Bahagi (dengan jumlah hanya no. Integer)
Mod
Modulus (baki bagi operasi bahagi)
+
Penambahan
-
Penolakan
Contoh penggunaan Total = 9 ^ 2 (Jumlah = 81) Total = 3 * 2 (Jumlah = 6) Total = 10 / 2 (Jumlah = 5) Total = 7 / 4 (Jumlah = 1) Total = 10 Mod 3 (Jumlah = 1) Total = 3 + 2 (Jumlah = 5) Total = 5 - 3 (Jumlah = 2)
b. Operator String
Operator &
c.
Fungsi Menyambungkan string (boleh juga digunakan dengan variable)
Contoh penggunaan Nama = “Ali ” & “ bin “ & “ Abu” (nama = Ali bin Abu)
Operator Perbandingan
Operator = <> < > <= >=
Fungsi Sama dengan Tidak sama dengan Lebih kecil daripada Lebih besar daripada Lebih kecil atau sama dengan Lebih besar atau sama dengan
Keputusan bagi operator perbandingan adalah berbentuk Boolean (TRUE atau FAL SE)
17
d. Operator Logikal
Operator And Or Not
Fungsi Jika A dan B kedua-duanya TRUE, maka keputusan adalah TRUE. Jika keduaduanya FALSE atau salah satu adalah FALSE, keputusannya adalah FALSE. Jika kedua atau salah satu A atau B TRUE, maka keputusan adalah TRUE. Jika kedua-duanya FALSE maka keputusan adalah FALSE. Menterbalikkan keputusan penilaian, contohnya “Not A”, jika A adalah FALSE maka keputusan adalah TRUE.
e. Built-in Function Excel VBA menyediakan beberapa built-in function yang boleh digunakan untuk membantu dan memudahkan pengguna membangunkan suatu kod aturcara. Antara build-in function yang biasa digunakan:
Function Abs
Chr
Date DateAdd
DateDiff
Penggunaan Untuk mendapatkan nilai numeric mutlak, sekiranya nilai negatif maka nilai positif akan diambil. Untuk mendapatkan karakter ANSI yang berkaitan dengan no. ANSI seperti contoh Chr(10) untuk Line Break atau ENTER. Mendapatkan tarikh semasa sistem. Menambah tempoh masa pada suatu tarikh. Contoh di sebelah akan mendapatkan tarikh 32 hari selepas 1/1/2009. Berikut adalah simbol yang digunakan untuk DateAdd. Simbol Mewakili yyyy Tahun q Quarter m Bulan y Hari bagi tahun d Hari w Weekday ww Minggu h Jam n Minit s Saat Mengira tempoh di antara dua tarikh. Simbol yang boleh digunakan sama seperti DateAdd. Contoh di sebelah akan mengira bilangan hari di antara 1/1/2009 dan 2/2/2009.
Contoh penggunaan Total = abs(2 – 5)
Msgbox “SILA” & Chr(10) & “TUNGGU” MsgBox Date Tambahdate = DateAdd("d", 32, "01/01/2009")
Bilanganhari = DateDiff("d", "01/01/2009", "02/02/2009")
18
Format
InStr Int IsDate
LCase Left
Len
Mid
Now Right
Sqr Time Ubound
UCase
Digunakan untuk membuat format tertentu pada string, integer atau date. Seperti contoh sintaks di sebelah akan memaparkan format tarikh terkini dalam bentuk YYYYMMDD (20110706). Digunakan untuk mencari perkataan di dalam satu ayat/string. Menukar format string ke bentuk Integer. Menyemak samada format string adalah Date atau tidak. Akan memulangkan nilai Boolean iaitu TRUE atau FALSE. Menukar semua string kepada Lowercase (Huruf kecil). Mendapatkan beberapa karakter yang diperlukan daripada sebuah string bermula daripada karakter paling kiri. Contoh di sebelah akan mendapatkan karakter “abcd” Jika digunakan dengan string, ia akan mendapat panjang string atau jumlah karakter. Contoh sebelah akan menghasilkan 7. Sama seperti Left tetapi ia digunakan untuk mendapatkan karakter bermula di tengah. Contoh di sebelah akan mendapatkan karakter "defgh". Digunakan untuk mendapatkan tarikh dan masa sekarang. Sama seperti Left, tetapi untuk mendapatkan karakter bermula dari karakter paling kanan. Contoh di sebelah akan mendapatkan karakter "jklm" Digunakan untuk mendapatkan nilai punca kuasa dua bagi nombor yang dimasukkan. Untuk mendapatkan masa sekarang. Untuk mendapatkan index elemen tertinggi suatu array. Contoh disebelah akan mendapatkan nilai 4. Menukar semua string kepada Uppercase (Huruf besar).
MsgBox Format(Now, "YYYYMMDD")
carian = InStr(1, "Here is the searchword", "searchword") Strtoint = Int(5.79) Strtoint2 = Int(“10”) Semakdate = IsDate("01/01/2009") Hurufkecil = LCase(“Nama saya Ali”) Kiri = Left("abcdefghijklm", 4)
Bilkarakter = Len("abcdefg")
Tengah = Mid("abcdefghijklm", 4, 5)
MsgBox Now Kanan = Right("abcdefghijklm", 4)
Puncakuasa = Sqr(4) MsgBox Time Dim Films(4) As String Indextertinggi = UBound(Films) Hurufbesar = UCase(“Nama saya Ali”)
19
5. VBA If-Then-Else Statement Seperti bahasa pengaturcaraan yang lain, VBA juga menyokong pelbagai pernyataan aturcara yang standard seperti IF-THEN-ELSE, SELECT, DO-WHILE dan sebagainya. Statement If-Then-Else digunakan untuk membolehkan keputusan dibuat berdasarkan syarat-syarat yang telah ditetapkan. Format If-Then-Else statement di dalam kod VBA: If condition_1 Then result_1 ElseIf condition_2 Then result_2 ... ElseIf condition_n Then result_n Else result_else End If Berdasarkan format di atas, ia menunjukkan sekiranya syarat condition_1 ditepati, maka jalankan kod result_1, sekiranya tidak ditepati pergi ke syarat condition_2. Sekiranya ditepati, jalankan kod result_2 dan seterusnya. Sekiranya semua syarat tidak ditepati, maka jalankan kod result_else. Kebiasaannya syarat condition digunakan bersama Operator Perbandingan atau Logikal seperti contoh berikut: If LRegion ="N" Then LRegionName = "North" End If Contoh di atas adalah penggunaan IF-THEN yang paling asas. Sekiranya LR egion adalah N maka arahan LRegionName = "North" akan dilaksanakan. Jika LRegion tidak sama dengan N, maka tiada sebarang kod dijalankan. Berikut adalah contoh penggunaan IF-THEN-ELSEIF yang lebih panjang: If LRegion ="N" Then LRegionName = "North" ElseIf LRegion = "S" Then LRegionName = "South" ElseIf LRegion = "E" Then LRegionName = "East" End If 20
Dengan statement di atas, sekiranya seki ranya LRegion adalah N, S atau E, ia akan melaksanakan kod arahan mengikut syarat mana yang ditepati. Sekir anya LRegion adalah selain N, S atau E, ia tidak akan melaksanakan sebarang arahan. Berikut adalah contoh penggunaan IF-THEN-ELSEIF-ELSE yang lengkap: lengkap: If LRegion ="N" Then LRegionName = "North" ElseIf LRegion = "S" Then LRegionName = "South" ElseIf LRegion = "E" Then LRegionName = "East" Else LRegionName = "West" End If Statement di atas sama seperti statement yang sebelum ini, namun terdapat penambahan syarat iaitu sekiranya region LRegion adalah selain N, S atau E, maka ar ahan LRegionName = "West" akan dijalankan. Anda juga boleh melakukan Nested-If atau I f-Bersarang di mana terdapat statement stateme nt If-Then-Else di dalam statement If-Then-Else. Dengan menggunakan If-Bersarang, statement If-Then-Else yang di luar akan dilaksanakan terlebih dahulu diikuti dengan If-Then-Else yang di dalam. IfBersarang boleh dilakukan dengan seberapa banyak sarang tanpa had. If condition_1 Then If condition_A Then If condition_i Then result_i Else result_else End If ElseIf condition_B Then result_B Else result_else End If ElseIf condition_2 Then result_2 Else result_else End If
21
6. VBA Select-Case Statement Statement Select-Case mempunyai fungsi yang sama dengan If-Then-Else di mana ia membantu dalam membuat keputusan berdasarkan syarat yang ditetapkan. Bahasa pengaturcaraan lain ada yang menggelarkan sebagai Switch-Case. Format statement di dalam kod VBA: Select Case test_expression Case condition_1 result_1 Case condition_2 result_2 ... Case condition_n result_n Case Else result_else End Select Sama seperti If-Then-Else, sekiranya condition_1 ditepati maka r esult_1 akan dijalankan dan begitu juga seterusnya. Sekiranya tiada syarat yang ditepati maka result_else akan dijalankan. Di bawah merupakan contoh statement Sele ct-Case yang lengkap. Case Else adalah optional. Select Case LRegion Case "N" LRegionName = "North" Case "S" LRegionName = "South" Case "E" LRegionName = "East" Case "W" LRegionName = "West" End Select
22
Anda juga boleh menggunakan katakunci To untuk menetapkan syarat yang berbentuk julat nilai seperti berikut: Select Case LNumber Case 1 To 10 LRegionName = "North" Case 11 To 20 LRegionName = "South" Case 21 To 30 LRegionName = "East" Case Else LRegionName = "West" End Select Syarat bagi Select-Case juga boleh digunakan secara berbilang dengan menggunakan tanda koma (,) seperti berikut: Select Case LNumber Case 1, 2 LRegionName = "North" Case 3, 4, 5 LRegionName = "South" Case 6 LRegionName = "East" Case 7, 11 LRegionName = "West" End Select Akhir sekali, ia juga boleh digunakan dengan katakunci I s untuk membandingkan nilai seperti berikut: Select Case LNumber Case Is < 100 LRegionName = "North" Case Is < 200 LRegionName = "South" Case Is < 300 LRegionName = "East" Case Else LRegionName = "West" End Select
23
7. VBA Do-While / Do-Until Loop Apakah Loop? Loop merupakan satu kaedah untuk membolehkan suatu kod at urcara dijalankan secara berulangkali sehinggalah satu masa di mana syarat untuk berhenti ditepati. Salah satu kaedah melaksanakan loop adalah dengan statement Do-While atau Do-Until. Perbezaan antara While dan Until adalah While - ia akan berada dalam loop selagi mana syarat ditepati, manakala Until - ia akan berada dalam loop sehinggalah syarat tidak ditepati. Format bagi Do-While adalah seperti berikut: Do [{While | Until} condition] [statements] [Exit Do] [statements] Loop atau: Do [statements] [Exit Do] [statements] Loop [{While | Until} condition] Dengan menggunakan format yang pertama, jalankan aturcara berikut: lNum = 1 Do While lNum < 5 lNum = lNum + 1 MsgBox lNum Loop Berdasarkan contoh di atas, aturcara di dalam Do-While loop ini akan dijalankan sebanyak 4 kali. Ini kerana sebelum memasuki loop, nilai lNum bersamaan dengan 1. Syarat untuk memasuki dan berada di dalam loop adalah selagi nilai lNum di bawah 5. Se tiap kali di dalam loop, nilai lNum akan ditambah dengan 1. Oleh itu ia akan terus ditambah sehinggalah lNum menjadi 5, syarat untuk terus berada di dalam loop tidak lagi ditepati kerana syaratnya adalah nilai lNum mesti di bawah 5, oleh itu loop tersebut akan berhenti. Dengan menggunakan konsep aturcara yang sama tetapi dengan menggunakan kaedah DoUntil, ia juga boleh ditulis seperti berikut: lNum = 1 Do Until lNum > 4 24
lNum = lNum + 1 MsgBox lNum Loop Contoh di atas akan menghasilkan keputusan yang sama de ngan contoh Do-While sebelum ini, di mana nilai akhir lNum adalah 5 dan pengulangan dilakukan sebanyak 4 kali. Seperti contoh Do-While, ia akan berada dalam loop selagi lNum le bih kecil daripada 5, tetapi bagi contoh DoUntil ini, ia akan berada dalam loop sehingga lNum lebih besar daripada 4. Bagi kedua-dua Do-While dan Do-Until, dengan menggunakan contoh yang sama, ia juga boleh ditulis seperti berikut: lNum = 1 Do lNum = lNum + 1 MsgBox lNum Loop While lNum < 5 atau: lNum = 1 Do lNum = lNum + 1 MsgBox lNum Loop Until lNum > 4 Anda juga boleh menggunakan sintaks Exit Do untuk memak sa proses looping berhenti berdasarkan syarat yang diberikan seperti c ontoh berikut: lNum = 1 Do While lNum < 5 lNum = lNum + 1 MsgBox lNum If lNum = 3 Then Exit Do Loop Kadangkala ketika menulis aturcara, terdapat ke silapan berlaku di mana berlakunya endless loop iaitu loop yang tidak dapat berhenti kerana syarat langsung tidak dapat dipenuhi. Untuk memberhentikan endless loop ini, tekan ESC atau CTRL+BREAK.
25
8. VBA For-Next Loop Selain Do-While / Do-Until, salah satu kaedah yang boleh digunakan untuk membuat loop adalah For-Next. Penggunaannya juga lebih popular dalam membuat loop berbanding Do-While / Do-Until. Ia biasanya digunakan dengan penggunaan counter di mana kita sendiri menentukan bila loop akan bermula dan berakhir. Format bagi sintaks For-Next loop adalah seperti berikut: For counter = Start To end [Step step] [statements] [Exit For] [statements] Next [counter] Seperti contoh di bawah, proses loop akan berlaku sebanyak 10 kali sahaja kerana kita telah menetapkan counter daripada 1 sehingga 10. lNum = 0 For counter = 1 To 10 lNum = lNum + 1 MsgBox lNum Next counter Kita juga boleh memaksa proses loop ini berhenti dengan meletakkan sintaks Exit For beserta syarat seperti berikut di mana looping akan berhenti sehingga loop yang ke-5 sahaja: lNum = 0 For counter = 1 To 10 lNum = lNum + 1 MsgBox lNum If lNum = 5 Then Exit For Next counter
26
Secara default, counter akan bergerak dan meningkat dengan pertambahan 1 nilai. Akan tetapi kita juga boleh memaksa counter m eningkat dengan pertambahan 2 atau lebih nilai ataupun counter menurun dengan nilai -1 at au kurang dengan penggunaan sintaks Step seperti berikut: lNum = 0 For counter = 1 To 10 Step 2 lNum = lNum + 1 MsgBox lNum Next counter Dengan menambah sintaks Step 2, pe ningkatan counter adalah secara 2 kali ganda dengan pergerakan berbentuk 2, 4, 6, 8, 10. Oleh itu loop akan berulang sebanyak 5 kali sahaja bukan 10 kali. Contoh berikut pula adalah untuk membuat counter secara menurun dengan penggunaan sintaks Step -1: For counter = 10 To 1 Step -1 MsgBox counter Next counter
27
9. VBA For-Each Loop Kebiasaannya For-Each loop digunakan untuk membuat loop menerusi setiap eleme n di dalam Collection ataupun Array. Berikut adalah format bagi For-Each loop: For Each element In group [statements] [Exit For] [statements] Next [element] Berikut adalah contoh For-Each loop beserta A rray yang digunakan: Dim lArray() As Variant Dim lArr Dim lCount As Long lArray = Array("A", "B", "C", "D", "E") lCount = 0 'Show each value in Array For Each lArr In lArray MsgBox "The number " & lCount & " element in lArray is " & lArr lA rr lCount = lCount + 1 Next lArr Berdasarkan contoh di atas, jumlah elemen y ang terdapat di dalam lArray adalah sebanyak 5 elemen di mana lArray(0) = A, lArray(1) = B dan berikutnya sehingga lArray(4) = E. Oleh itu dengan menggunakan sintaks For Each lArr I n lArray, looping hanya akan berlaku sebanyak 5 k ali iaitu jumlah elemen di dalam array lArray tersebut.
28
10. Membina VBA Sub-Procedure dan Function sendiri Semua kod atucara biasanya ditulis dalam sebuah Sub-Procedure yang telah ditentukan oleh Excel VBA seperti contoh CommandButtong_Click(), Macro1() dan lain-lain. Namun kita boleh juga membina sub-procedure kita sendiri. Setiap sub-procedure bermula dan berakhir mengikut format berikut: Sub [nama sub-procedure] () …. [Kod aturcara] …. End Sub Dengan menggunakan sub-procedure yang dibina sendiri, kita boleh me nghantar parameter kepada sub-procedure berikut dari sub-procedure yang lain dan memproses parameter tersebut. Seperti contoh, bangunkan sebuah sub-procedure yang bernama KiraDanMaklum seperti di bawah yang akan menerima me nerima 3 parameter iaitu nama, nilai_a dan nilai_b. Setelah itu ia akan menjumlahkan nilai_a dan nilai_b dan memaklumkan kepada pengguna dengan kotak mesej. Sub KiraDanMaklum(nama As String, nilai_a As Integer, nilai_b As Integer) Dim jumlah As Integer jumlah = nilai_a + nilai_b MsgBox "Selamat Datang " & nama & ". Jumlah keseluruhan adalah " & jumlah End Sub Di sub-procedure yang lain, panggil sub-procedure KiraDanMaklum dan hantar parameter kepadanya untuk diproses seperti berikut: Sub Macro2() Call KiraDanMaklum("Ali bin Abu", 10, 5) End Sub Apabila sub-procedure Macro() dijalankan, ia akan memanggil dan menghantar parameter ke sub-procedure KiraDanMaklum untuk diproses dan mendapatkan keputusan seperti berikut:
29
Dengan membina sub-procedure sendiri, kita tidak perlu m enulis kod aturcara yang sama setiap kali hendak membuat operasi yang sama. Kita hanya perlu memanggil sub-procedure untuk menjalankan operasi yang sama. Sama j uga dengan function. Excel VBA telah menyediakan beberapa built-in function, namun kita juga boleh membangunkan function kita sendiri. Fungsinya hampir sama dengan sub-procedure namun ia membolehkan function tersebut memulangkan semula result atau keputusan function kepada pemanggil function. Format function adalah seperti berikut: Function [nama function] ([parameter] As [jenisparameter]) As [jenisparameter] …. [Kod aturcara] …. End Function Berikut merupakan contoh function yang dibina sendiri: Function KiraUntung(hargabeli As Integer, hargajual As Integer, diskaun As Integer) as Integer KiraUntung = (hargajual * ((100 - diskaun) / 100)) - hargabeli End Function Function di atas perlu menerima 3 parameter iaitu hargabeli, hargajual dan diskaun untuk membolehkan ia memproses kod aturcara di dalamnya. Setelah diproses, ia akan memulangkan hasil keputusan kepada pemanggil seperti kod berikut di mana keputusannya akan diumpukkan kepada variable keuntungan: Sub Macro3() Dim hargabeli As Integer, hargajual As Integer, diskaun As Integer, keuntungan As Integer hargabeli = 100 hargajual = 150 diskaun = 15 keuntungan = KiraUntung(hargabeli, hargajual, diskaun) MsgBox "Untung = RM" & keuntungan End Sub
30
SEKIAN, TERIMA KASIH
31
