VBA - Panduan Cepat

VBA adalah singkatan dari Visual Basic untuk Applications bahasa pemrograman berbasis peristiwa dari Microsoft yang sekarang sebagian besar digunakan dengan aplikasi kantor Microsoft seperti MSExcel, MS-Word, dan MS-Access.

Ini membantu teknisi untuk membangun aplikasi dan solusi yang disesuaikan untuk meningkatkan kemampuan aplikasi tersebut. Keuntungan dari fasilitas ini adalah Anda TIDAK PERLU menginstal visual basic di PC kami, namun, menginstal Office secara implisit akan membantu dalam mencapai tujuan.

Anda dapat menggunakan VBA di semua versi kantor, langsung dari MS-Office 97 hingga MS-Office 2013 dan juga dengan versi terbaru apa pun yang tersedia. Di antara VBA, Excel VBA adalah yang paling populer. Keuntungan menggunakan VBA adalah Anda dapat membuat alat yang sangat kuat di MS Excel menggunakan pemrograman linier.

Penerapan VBA

Anda mungkin bertanya-tanya mengapa menggunakan VBA di Excel karena MS-Excel sendiri menyediakan banyak fungsi bawaan. MS-Excel hanya menyediakan fungsi bawaan dasar yang mungkin tidak cukup untuk melakukan kalkulasi kompleks. Dalam keadaan seperti itu, VBA menjadi solusi paling jelas.

Misalnya, sangat sulit untuk menghitung pembayaran bulanan pinjaman menggunakan rumus bawaan Excel. Sebaliknya, VBA mudah diprogram untuk penghitungan seperti itu.

Mengakses Editor VBA

Di jendela Excel, tekan "ALT + F11". Jendela VBA terbuka seperti yang ditunjukkan pada gambar berikut.

Di bab ini, Anda akan belajar cara menulis makro sederhana secara langkah demi langkah.

Step 1- Pertama, aktifkan menu 'Pengembang' di Excel 20XX. Untuk melakukan hal yang sama, klik File → Options.

Step 2- Klik tab 'Sesuaikan Pita' dan centang 'Pengembang'. Klik 'OK'.

Step 3 - Pita 'Pengembang' muncul di bilah menu.

Step 4 - Klik tombol 'Visual Basic' untuk membuka Editor VBA.

Step 5- Mulai membuat skrip dengan menambahkan tombol. Klik Sisipkan → Pilih tombol.

Step 6 - Lakukan klik kanan dan pilih 'properti'.

Step 7 - Edit nama dan keterangan seperti yang ditunjukkan pada tangkapan layar berikut.

Step 8 - Sekarang klik dua kali tombol dan garis sub-prosedur akan ditampilkan seperti yang ditunjukkan pada gambar berikut.

Step 9 - Mulai membuat kode hanya dengan menambahkan pesan.

Private Sub say_helloworld_Click()
   MsgBox "Hi"
End Sub

Step 10- Klik tombol untuk menjalankan sub-prosedur. Output dari sub-prosedur ditunjukkan pada gambar layar berikut. Pastikan Anda telah mengaktifkan mode desain. Cukup klik untuk menyalakannya jika tidak menyala.

Note - Pada bab selanjutnya, kami akan mendemonstrasikan menggunakan tombol sederhana, seperti yang dijelaskan dari langkah # 1 hingga 10. Oleh karena itu, penting untuk memahami bab ini secara menyeluruh.

Di bab ini, Anda akan mempelajari terminologi VBA excel yang umum digunakan. Istilah-istilah ini akan digunakan dalam modul selanjutnya, oleh karena itu memahami masing-masing dari ini adalah penting.

Modul

Modul adalah area tempat kode ditulis. Ini adalah Buku Kerja baru, oleh karena itu tidak ada Modul.

Untuk menyisipkan Module, buka Insert → Module. Setelah modul dimasukkan, 'modul1' dibuat.

Di dalam modul, kita dapat menulis kode VBA dan kode tersebut ditulis dalam Prosedur. Prosedur / Sub Prosedur adalah serangkaian pernyataan VBA yang menginstruksikan apa yang harus dilakukan.

Prosedur

Prosedur adalah sekumpulan pernyataan yang dieksekusi secara keseluruhan, yang menginstruksikan Excel bagaimana melakukan tugas tertentu. Tugas yang dilakukan bisa menjadi tugas yang sangat sederhana atau sangat rumit. Namun, merupakan praktik yang baik untuk memecah prosedur yang rumit menjadi yang lebih kecil.

Dua jenis utama dari Prosedur adalah Sub dan Fungsi.

Fungsi

Fungsi adalah sekelompok kode yang dapat digunakan kembali, yang dapat dipanggil di mana saja dalam program Anda. Ini menghilangkan kebutuhan untuk menulis kode yang sama berulang kali. Ini membantu pemrogram untuk membagi program besar menjadi sejumlah fungsi kecil dan dapat dikelola.

Selain Fungsi bawaan, VBA memungkinkan untuk menulis fungsi yang ditentukan pengguna juga dan pernyataan ditulis di antaranya Function dan End Function.

Sub-Prosedur

Pekerjaan sub-prosedur mirip dengan fungsi. Sementara sub prosedur JANGAN Mengembalikan nilai, fungsi mungkin atau mungkin tidak mengembalikan nilai. Sub prosedur BISA dipanggil tanpa kata kunci panggilan. Prosedur sub selalu tertutup di dalamSub dan End Sub pernyataan.

Komentar digunakan untuk mendokumentasikan logika program dan informasi pengguna yang dengannya pemrogram lain dapat bekerja dengan lancar pada kode yang sama di masa mendatang.

Ini mencakup informasi seperti yang dikembangkan oleh, dimodifikasi oleh, dan dapat juga mencakup logika yang digabungkan. Komentar diabaikan oleh penerjemah saat eksekusi.

Komentar di VBA dilambangkan dengan dua metode.

  • Setiap pernyataan yang dimulai dengan Kutipan Tunggal (') dianggap sebagai komentar. Berikut ini contohnya.

' This Script is invoked after successful login 
' Written by : TutorialsPoint 
' Return Value : True / False
  • Pernyataan apa pun yang diawali dengan kata kunci "REM". Berikut ini contohnya.

REM This Script is written to Validate the Entered Input 
REM Modified by  : Tutorials point/user2

Itu MsgBox function menampilkan kotak pesan dan menunggu pengguna untuk mengklik tombol dan kemudian tindakan dilakukan berdasarkan tombol yang diklik oleh pengguna.

Sintaksis

MsgBox(prompt[,buttons][,title][,helpfile,context])

Deskripsi Parameter

  • Prompt- Parameter yang Diperlukan. String yang ditampilkan sebagai pesan di kotak dialog. Panjang maksimum prompt adalah sekitar 1024 karakter. Jika pesan meluas ke lebih dari satu baris, maka baris tersebut dapat dipisahkan menggunakan karakter carriage return (Chr (13)) atau karakter linefeed (Chr (10)) di antara setiap baris.

  • Buttons- Parameter Opsional. Ekspresi numerik yang menentukan tipe tombol yang akan ditampilkan, gaya ikon yang digunakan, identitas tombol default, dan modalitas kotak pesan. Jika dibiarkan kosong, nilai default untuk tombol adalah 0.

  • Title- Parameter Opsional. Ekspresi String ditampilkan di bilah judul kotak dialog. Jika judul dibiarkan kosong, nama aplikasi ditempatkan di bilah judul.

  • Helpfile- Parameter Opsional. Ekspresi String yang mengidentifikasi file Bantuan yang akan digunakan untuk menyediakan bantuan peka konteks untuk kotak dialog.

  • Context- Parameter Opsional. Ekspresi numerik yang mengidentifikasi nomor konteks bantuan yang ditetapkan oleh penulis bantuan untuk topik bantuan yang sesuai. Jika konteks disediakan, helpfile juga harus disediakan.

Itu Buttons parameter dapat mengambil salah satu dari nilai berikut -

  • 0 vbOKOnly - Hanya menampilkan tombol OK.

  • 1 vbOKCancel - Menampilkan tombol OK dan Batal.

  • 2 vbAbortRetryIgnore - Menampilkan tombol Batalkan, Coba Lagi, dan Abaikan.

  • 3 vbYesNoCancel - Menampilkan tombol Ya, Tidak, dan Batal.

  • 4 vbYesNo - Menampilkan tombol Ya dan Tidak.

  • 5 vbRetryCancel - Menampilkan tombol Coba Lagi dan Batal.

  • 16 vbCritical - Menampilkan ikon Pesan Kritis.

  • 32 vbQuestion - Menampilkan ikon Warning Query.

  • 48 vbExclamation - Menampilkan ikon Pesan Peringatan.

  • 64 vbInformation - Menampilkan ikon Pesan Informasi.

  • 0 vbDefaultButton1 - Tombol pertama adalah default.

  • 256 vbDefaultButton2 - Tombol kedua adalah default.

  • 512 vbDefaultButton3 - Tombol ketiga adalah default.

  • 768 vbDefaultButton4 - Tombol keempat adalah default.

  • 0 vbApplicationModal Application modal - Aplikasi saat ini tidak akan berfungsi sampai pengguna menanggapi kotak pesan.

  • 4096 vbSystemModal System modal - Semua aplikasi tidak akan bekerja sampai pengguna menanggapi kotak pesan.

Nilai di atas secara logis dibagi menjadi empat kelompok: The first group(0 hingga 5) menunjukkan tombol yang akan ditampilkan di kotak pesan. Itusecond group (16, 32, 48, 64) menjelaskan gaya ikon yang akan ditampilkan, file third group (0, 256, 512, 768) menunjukkan tombol mana yang harus menjadi default, dan fourth group (0, 4096) menentukan modalitas kotak pesan.

Kembalikan Nilai

Fungsi MsgBox dapat mengembalikan salah satu dari nilai berikut yang dapat digunakan untuk mengidentifikasi tombol yang diklik pengguna di kotak pesan.

  • 1 - vbOK - OK diklik
  • 2 - vbCancel - Batal diklik
  • 3 - vbAbort - Batalkan diklik
  • 4 - vbRetry - Coba lagi diklik
  • 5 - vbIgnore - Abaikan diklik
  • 6 - vbYa - Ya telah diklik
  • 7 - vbNo - Tidak ada yang diklik

Contoh

Function MessageBox_Demo() 
   'Message Box with just prompt message 
   MsgBox("Welcome")     
   
   'Message Box with title, yes no and cancel Butttons  
   int a = MsgBox("Do you like blue color?",3,"Choose options") 
   ' Assume that you press No Button  
   msgbox ("The Value of a is " & a) 
End Function

Keluaran

Step 1 - Fungsi di atas dapat dijalankan baik dengan mengklik tombol "Jalankan" pada Jendela VBA atau dengan memanggil fungsi dari Lembar Kerja Excel seperti yang ditunjukkan pada gambar layar berikut.

Step 2 - Kotak Pesan Sederhana ditampilkan dengan pesan "Selamat Datang" dan Tombol "OK"

Step 3 - Setelah Mengklik OK, kotak dialog lain ditampilkan dengan pesan bersama dengan tombol "ya, tidak, dan batal".

Step 4- Setelah mengklik tombol 'Tidak', nilai tombol itu (7) disimpan sebagai bilangan bulat dan ditampilkan sebagai kotak pesan kepada pengguna seperti yang ditunjukkan pada tangkapan layar berikut. Dengan menggunakan nilai ini, dapat dipahami tombol mana yang diklik pengguna.

Itu InputBox functionmeminta pengguna untuk memasukkan nilai. Setelah memasukkan nilai, jika pengguna mengklik tombol OK atau menekan ENTER pada keyboard, fungsi InputBox akan mengembalikan teks di dalam kotak teks. Jika pengguna mengklik tombol Batal, fungsi akan mengembalikan string kosong ("").

Sintaksis

InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])

Deskripsi Parameter

  • Prompt- Parameter yang dibutuhkan. String yang ditampilkan sebagai pesan di kotak dialog. Panjang maksimum prompt adalah sekitar 1024 karakter. Jika pesan meluas ke lebih dari satu baris, maka baris tersebut dapat dipisahkan menggunakan karakter carriage return (Chr (13)) atau karakter linefeed (Chr (10)) di antara setiap baris.

  • Title- Parameter opsional. Ekspresi String ditampilkan di bilah judul kotak dialog. Jika judul dibiarkan kosong, nama aplikasi ditempatkan di bilah judul.

  • Default- Parameter opsional. Teks default di kotak teks yang ingin ditampilkan pengguna.

  • XPos- Parameter opsional. Posisi dariXsumbu mewakili jarak prompt dari sisi kiri layar secara horizontal. Jika dibiarkan kosong, kotak masukan diletakkan di tengah secara horizontal.

  • YPos- Parameter opsional. Posisi dariYsumbu mewakili jarak prompt dari sisi kiri layar secara vertikal. Jika dibiarkan kosong, kotak masukan secara vertikal di tengah.

  • Helpfile- Parameter opsional. Ekspresi String yang mengidentifikasi file bantuan yang akan digunakan untuk menyediakan Bantuan peka konteks untuk kotak dialog.

  • context- Parameter opsional. Ekspresi numerik yang mengidentifikasi nomor konteks bantuan yang ditetapkan oleh penulis bantuan untuk topik bantuan yang sesuai. Jika konteks disediakan, helpfile juga harus disediakan.

Contoh

Mari kita hitung luas persegi panjang dengan mendapatkan nilai dari pengguna pada waktu berjalan dengan bantuan dua kotak masukan (satu untuk panjang dan satu untuk lebar).

Function findArea() 
   Dim Length As Double 
   Dim Width As Double 
   
   Length = InputBox("Enter Length ", "Enter a Number") 
   Width = InputBox("Enter Width", "Enter a Number") 
   findArea = Length * Width 
End Function

Keluaran

Step 1 - Untuk melakukan hal yang sama, panggil menggunakan nama fungsi dan tekan Enter seperti yang ditunjukkan pada gambar berikut.

Step 2- Setelah eksekusi, kotak input pertama (panjang) ditampilkan. Masukkan nilai ke dalam kotak masukan.

Step 3 - Setelah memasukkan nilai pertama, kotak input kedua (lebar) ditampilkan.

Step 4- Saat memasukkan nomor kedua, klik tombol OK. Area tersebut ditampilkan seperti yang ditunjukkan pada tangkapan layar berikut.

Variableadalah lokasi memori bernama yang digunakan untuk menyimpan nilai yang dapat diubah selama eksekusi skrip. Berikut adalah aturan dasar penamaan variabel.

  • Anda harus menggunakan huruf sebagai karakter pertama.

  • Anda tidak dapat menggunakan spasi, titik (.), Tanda seru (!), Atau karakter @, &, $, # dalam nama.

  • Nama tidak boleh lebih dari 255 karakter.

  • Anda tidak dapat menggunakan kata kunci khusus Visual Basic sebagai nama variabel.

Syntax

Di VBA, Anda perlu mendeklarasikan variabel sebelum menggunakannya.

Dim <<variable_name>> As <<variable_type>>

Jenis Data

Ada banyak tipe data VBA, yang dapat dibagi menjadi dua kategori utama, yaitu tipe data numerik dan nonnumerik.

Tipe Data Numerik

Tabel berikut menampilkan tipe data numerik dan rentang nilai yang diperbolehkan.

Tipe Jarak nilai
Byte 0 sampai 255
Bilangan bulat -32.768 hingga 32.767
Panjang -2.147.483.648 hingga 2.147.483.648
Tunggal

-3.402823E + 38 hingga -1.401298E-45 untuk nilai negatif

1.401298E-45 hingga 3.402823E + 38 untuk nilai positif.

Dua kali lipat

-1.79769313486232e + 308 hingga -4.94065645841247E-324 untuk nilai negatif

4.94065645841247E-324 hingga 1.79769313486232e + 308 untuk nilai positif.

Mata uang -922,337,203,685,477.5808 hingga 922,337,203,685,477.5807
Desimal

+/- 79.228.162.514.264.337.593.543.950.335 jika tidak ada desimal yang digunakan

+/- 7.9228162514264337593543950335 (28 tempat desimal).

Tipe Data Non-Numerik

Tabel berikut menampilkan tipe data non-numerik dan rentang nilai yang diperbolehkan.

Tipe Jarak nilai
String (panjang tetap) 1 hingga 65.400 karakter
String (panjang variabel) 0 hingga 2 miliar karakter
Tanggal 1 Januari 100 hingga 31 Desember 9999
Boolean Benar atau salah
Obyek Objek apa pun yang disematkan
Varian (numerik) Nilai apa pun sebesar ganda
Varian (teks) Sama seperti string dengan panjang variabel

Example

Mari kita buat tombol dan beri nama 'Variables_demo' untuk mendemonstrasikan penggunaan variabel.

Private Sub say_helloworld_Click()
   Dim password As String
   password = "Admin#1"

   Dim num As Integer
   num = 1234

   Dim BirthDay As Date
   BirthDay = DateValue("30 / 10 / 2020")

   MsgBox "Passowrd is " & password & Chr(10) & "Value of num is " &
      num & Chr(10) & "Value of Birthday is " & BirthDay
End Sub

Output

Setelah menjalankan skrip, hasilnya akan seperti yang ditunjukkan pada tangkapan layar berikut.

Constant adalah lokasi memori bernama yang digunakan untuk menyimpan nilai yang TIDAK DAPAT diubah selama eksekusi skrip. Jika pengguna mencoba untuk mengubah nilai Konstanta, eksekusi skrip berakhir dengan kesalahan. Konstanta dideklarasikan dengan cara yang sama dengan variabel dideklarasikan.

Berikut adalah aturan penamaan konstanta.

  • Anda harus menggunakan huruf sebagai karakter pertama.

  • Anda tidak dapat menggunakan spasi, titik (.), Tanda seru (!), Atau karakter @, &, $, # dalam nama.

  • Nama tidak boleh lebih dari 255 karakter.

  • Anda tidak dapat menggunakan kata kunci khusus Visual Basic sebagai nama variabel.

Sintaksis

Di VBA, kita perlu menetapkan nilai ke Konstanta yang dideklarasikan. Kesalahan terjadi, jika kita mencoba mengubah nilai konstanta.

Const <<constant_name>> As <<constant_type>> = <<constant_value>>

Contoh

Mari kita buat tombol "Constant_demo" untuk mendemonstrasikan cara bekerja dengan konstanta.

Private Sub Constant_demo_Click() 
   Const MyInteger As Integer = 42 
   Const myDate As Date = #2/2/2020# 
   Const myDay As String = "Sunday" 
   
   MsgBox "Integer is " & MyInteger & Chr(10) & "myDate is " 
      & myDate & Chr(10) & "myDay is " & myDay  
End Sub

Keluaran

Setelah menjalankan skrip, hasilnya akan ditampilkan seperti yang ditunjukkan pada tangkapan layar berikut.

Sebuah Operator dapat didefinisikan menggunakan ekspresi sederhana - 4 + 5 sama dengan 9. Di sini, 4 dan 5 dipanggil operands dan + dipanggil operator. VBA mendukung jenis operator berikut -

  • Operator Aritmatika
  • Operator Perbandingan
  • Operator Logis (atau Relasional)
  • Operator Penggabungan

Operator Aritmatik

Operator aritmatika berikut didukung oleh VBA.

Asumsikan variabel A memiliki 5 dan variabel B memiliki 10, maka -

Tunjukkan Contoh

Operator Deskripsi Contoh
+ Menambahkan dua operan A + B akan menghasilkan 15
- Mengurangi operan kedua dari yang pertama A - B akan memberikan -5
* Mengalikan kedua operan A * B akan memberi 50
/ Membagi pembilang dengan penyebut B / A akan memberi 2
% Operator modulus dan sisanya setelah pembagian integer B% A akan memberi 0
^ Operator eksponen B ^ A akan memberikan 100000

Operator Pembanding

Ada operator perbandingan berikut yang didukung oleh VBA.

Asumsikan variabel A memiliki 10 dan variabel B memiliki 20, maka -

Tunjukkan Contoh

Operator Deskripsi Contoh
= Memeriksa apakah nilai kedua operan sama atau tidak. Jika ya, maka kondisinya benar. (A = B) adalah False.
<> Memeriksa apakah nilai kedua operan sama atau tidak. Jika nilainya tidak sama, maka kondisinya benar. (A <> B) adalah Benar.
> Memeriksa apakah nilai operan kiri lebih besar dari nilai operan kanan. Jika ya, maka kondisinya benar. (A> B) adalah False.
< Memeriksa apakah nilai operan kiri kurang dari nilai operan kanan. Jika ya, maka kondisinya benar. (A <B) Benar.
> = Memeriksa apakah nilai operan kiri lebih besar dari atau sama dengan nilai operan kanan. Jika ya, maka kondisinya benar. (A> = B) adalah False.
<= Memeriksa apakah nilai operan kiri kurang dari atau sama dengan nilai operan kanan. Jika ya, maka kondisinya benar. (A <= B) Benar.

Operator Logis

Operator logika berikut didukung oleh VBA.

Asumsikan variabel A memiliki 10 dan variabel B memiliki 0, maka -

Tunjukkan Contoh

Operator Deskripsi Contoh
DAN Disebut Logical AND operator. Jika kedua kondisinya Benar, maka Ekspresi benar. a <> 0 DAN b <> 0 adalah False.
ATAU Disebut Logical OR Operator. Jika salah satu dari dua kondisi ini Benar, maka kondisinya benar. a <> 0 ATAU b <> 0 benar.
TIDAK Disebut Logical NOT Operator. Digunakan untuk membalikkan keadaan logis operannya. Jika kondisi benar, maka operator NOT akan membuat salah. TIDAK (a <> 0 ATAU b <> 0) salah.
XOR Disebut Pengecualian Logis. Ini adalah kombinasi dari Operator NOT dan OR. Jika satu, dan hanya satu, ekspresi bernilai True, hasilnya adalah True. (a <> 0 XOR b <> 0) benar.

Operator Concatenation

Operator Concatenation berikut didukung oleh VBA.

Asumsikan variabel A memiliki 5 dan variabel B memiliki 10 maka -

Tunjukkan Contoh

Operator Deskripsi Contoh
+ Menambahkan dua Nilai sebagai Variabel. Nilainya adalah Numerik A + B akan menghasilkan 15
& Menggabungkan dua Nilai A & B akan memberikan 510

Asumsikan variabel A = "Microsoft" dan variabel B = "VBScript", lalu -

Operator Deskripsi Contoh
+ Menggabungkan dua Nilai A + B akan memberikan MicrosoftVBScript
& Menggabungkan dua Nilai A & B akan memberikan MicrosoftVBScript

Note- Operator Penggabungan dapat digunakan untuk angka dan string. Outputnya tergantung pada konteksnya, jika variabel tersebut memiliki nilai numerik atau nilai string.

Pengambilan keputusan memungkinkan pemrogram untuk mengontrol aliran eksekusi skrip atau salah satu bagiannya. Eksekusi diatur oleh satu atau lebih pernyataan bersyarat.

Berikut ini adalah bentuk umum dari struktur pengambilan keputusan yang ditemukan di sebagian besar bahasa pemrograman.

VBA menyediakan jenis pernyataan pengambilan keputusan berikut. Klik tautan berikut untuk memeriksa detailnya.

Sr.No. Pernyataan & Deskripsi
1 jika pernyataan

Sebuah if pernyataan terdiri dari ekspresi Boolean diikuti oleh satu atau lebih pernyataan.

2 if..else pernyataan

Sebuah if elsepernyataan terdiri dari ekspresi Boolean diikuti oleh satu atau lebih pernyataan. Jika kondisinya Benar, pernyataan di bawahIfpernyataan dieksekusi. Jika kondisinya salah, fileElse bagian dari skrip dijalankan.

3 if ... elseif..else statement

Sebuah if pernyataan diikuti oleh satu atau lebih ElseIf pernyataan, yang terdiri dari ekspresi Boolean dan kemudian diikuti dengan opsional else statement, yang dijalankan ketika semua kondisi menjadi salah.

4 pernyataan if bersarang

Sebuah if atau elseif pernyataan di dalam yang lain if atau elseif pernyataan.

5 pernyataan switch

SEBUAH switch pernyataan memungkinkan variabel untuk diuji kesetaraan terhadap daftar nilai.

Mungkin ada situasi ketika Anda perlu mengeksekusi blok kode beberapa kali. Secara umum, pernyataan dieksekusi secara berurutan: Pernyataan pertama dalam suatu fungsi dijalankan pertama, diikuti oleh yang kedua, dan seterusnya.

Bahasa pemrograman menyediakan berbagai struktur kontrol yang memungkinkan jalur eksekusi yang lebih rumit.

Pernyataan loop memungkinkan kita untuk mengeksekusi pernyataan atau sekelompok pernyataan beberapa kali. Berikut ini adalah bentuk umum dari pernyataan loop di VBA.

VBA menyediakan jenis loop berikut untuk menangani persyaratan perulangan. Klik tautan berikut untuk memeriksa detailnya.

Sr.No. Jenis & Deskripsi Loop
1 untuk loop

Menjalankan urutan pernyataan beberapa kali dan menyingkat kode yang mengelola variabel loop.

2 untuk ..setiap putaran

Ini dijalankan jika setidaknya ada satu elemen dalam grup dan diulangi untuk setiap elemen dalam grup.

3 while..wend loop

Ini menguji kondisi sebelum menjalankan badan perulangan.

4 lakukan .. sementara loop

Pernyataan do..While akan dieksekusi selama kondisinya True. (Mis.,) Loop harus diulang sampai kondisinya False.

5 lakukan..sampai loop

Pernyataan do..Until akan dieksekusi selama kondisinya False. (Mis.,) Loop harus diulang sampai kondisinya True.

Pernyataan Kontrol Loop

Pernyataan kontrol loop mengubah eksekusi dari urutan normalnya. Ketika eksekusi meninggalkan ruang lingkup, semua pernyataan yang tersisa di loop TIDAK dieksekusi.

VBA mendukung pernyataan kontrol berikut. Klik tautan berikut untuk memeriksa detailnya.

S.No. Pernyataan & Deskripsi Kontrol
1 Keluar Untuk pernyataan

Menghentikan For loop pernyataan dan transfer eksekusi ke pernyataan segera setelah loop

2 Keluar dari pernyataan Do

Menghentikan Do While pernyataan dan transfer eksekusi ke pernyataan segera setelah loop

String adalah urutan karakter, yang dapat terdiri dari huruf, angka, karakter khusus, atau semuanya. Variabel dikatakan string jika diapit oleh tanda kutip ganda "".

Sintaksis

variablename = "string"

Contoh

str1 = "string"   ' Only Alphabets
str2 = "132.45"   ' Only Numbers
str3 = "!@#$;*"  ' Only Special Characters
Str4 = "Asc23@#"  ' Has all the above

Fungsi String

Ada fungsi String VBA yang telah ditentukan, yang membantu pengembang untuk bekerja dengan string dengan sangat efektif. Berikut adalah metode String yang didukung di VBA. Silakan klik masing-masing metode untuk mengetahui secara detail.

Sr.No. Nama & Deskripsi Fungsi
1 InStr

Mengembalikan kemunculan pertama dari substring yang ditentukan. Pencarian terjadi dari kiri ke kanan.

2 InstrRev

Mengembalikan kemunculan pertama dari substring yang ditentukan. Pencarian terjadi dari kanan ke kiri.

3 Lcase

Mengembalikan huruf kecil dari string yang ditentukan.

4 Ucase

Mengembalikan huruf besar dari string yang ditentukan.

5 Kiri

Mengembalikan sejumlah karakter tertentu dari sisi kiri string.

6 Baik

Mengembalikan sejumlah karakter tertentu dari sisi kanan string.

7 Pertengahan

Mengembalikan sejumlah karakter tertentu dari string berdasarkan parameter yang ditentukan.

8 Ltrim

Mengembalikan string setelah menghapus spasi di sisi kiri string yang ditentukan.

9 Rtrim

Mengembalikan string setelah menghapus spasi di sisi kanan string yang ditentukan.

10 Memangkas

Mengembalikan nilai string setelah menghapus spasi kosong di depan dan di belakangnya.

11 Len

Mengembalikan panjang string yang diberikan.

12 Menggantikan

Mengembalikan string setelah mengganti string dengan string lain.

13 Ruang

Mengisi string dengan jumlah spasi yang ditentukan.

14 StrComp

Mengembalikan nilai integer setelah membandingkan dua string yang ditentukan.

15 Tali

Mengembalikan string dengan karakter tertentu untuk jumlah waktu tertentu.

16 StrReverse

Mengembalikan string setelah membalik urutan karakter dari string yang diberikan.

VBScript Date and Time Functions membantu pengembang untuk mengubah tanggal dan waktu dari satu format ke format lain atau untuk mengekspresikan nilai tanggal atau waktu dalam format yang sesuai dengan kondisi tertentu.

Fungsi Tanggal

Sr.No. Deskripsi fungsi
1 Tanggal

Sebuah Fungsi, yang mengembalikan tanggal sistem saat ini.

2 CDate

Sebuah Fungsi, yang mengubah input yang diberikan menjadi tanggal.

3 DateAdd

Sebuah Fungsi, yang mengembalikan tanggal di mana interval waktu tertentu telah ditambahkan.

4 DateDiff

Fungsi, yang mengembalikan perbedaan antara dua periode waktu.

5 DatePart

Sebuah Fungsi, yang mengembalikan bagian tertentu dari nilai tanggal masukan yang diberikan.

6 DateSerial

Sebuah Fungsi, yang mengembalikan tanggal yang valid untuk tahun, bulan, dan tanggal tertentu.

7 FormatDateTime

A Function, yang memformat tanggal berdasarkan parameter yang disediakan.

8 IsDate

Fungsi, yang mengembalikan Nilai Boolean terlepas dari apakah parameter yang diberikan adalah tanggal atau bukan.

9 Hari

A Function, yang mengembalikan integer antara 1 dan 31 yang mewakili hari dari tanggal yang ditentukan.

10 Bulan

Fungsi, yang mengembalikan bilangan bulat antara 1 dan 12 yang mewakili bulan dari tanggal yang ditentukan.

11 Tahun

A Function, yang mengembalikan integer yang mewakili tahun dari tanggal yang ditentukan.

12 MonthName

Fungsi, yang mengembalikan nama bulan tertentu untuk tanggal yang ditentukan.

13 Hari kerja

Sebuah Fungsi, yang mengembalikan integer (1 sampai 7) yang mewakili hari dalam seminggu untuk hari yang ditentukan.

14 WeekDayName

A Function, yang mengembalikan nama hari kerja untuk hari yang ditentukan.

Fungsi Waktu

Sr.No. Deskripsi fungsi
1 Sekarang

A Function, yang mengembalikan tanggal dan waktu sistem saat ini.

2 Jam

Fungsi, yang mengembalikan bilangan bulat antara 0 dan 23 yang mewakili bagian jam dari waktu tertentu.

3 Menit

Fungsi, yang mengembalikan bilangan bulat antara 0 dan 59 yang mewakili bagian menit dari waktu tertentu.

4 Kedua

Fungsi, yang mengembalikan bilangan bulat antara 0 dan 59 yang mewakili bagian detik dari waktu tertentu.

5 Waktu

Sebuah Fungsi, yang mengembalikan waktu sistem saat ini.

6 Timer

Sebuah Fungsi, yang mengembalikan jumlah detik dan milidetik sejak 12:00 AM.

7 TimeSerial

Sebuah Fungsi, yang mengembalikan waktu untuk input jam, menit dan detik tertentu.

8 Nilai waktu

Sebuah Fungsi, yang mengubah string input menjadi format waktu.

Kita tahu betul bahwa variabel adalah wadah untuk menyimpan nilai. Terkadang, pengembang berada dalam posisi untuk menyimpan lebih dari satu nilai dalam satu variabel dalam satu waktu. Ketika serangkaian nilai disimpan dalam satu variabel, maka itu dikenal sebagaiarray variable.

Deklarasi Array

Array dideklarasikan dengan cara yang sama ketika variabel dideklarasikan kecuali deklarasi variabel array menggunakan tanda kurung. Dalam contoh berikut, ukuran array disebutkan dalam tanda kurung.

'Method 1 : Using Dim
Dim arr1()	'Without Size

'Method 2 : Mentioning the Size
Dim arr2(5)  'Declared with size of 5

'Method 3 : using 'Array' Parameter
Dim arr3
arr3 = Array("apple","Orange","Grapes")
  • Meskipun, ukuran array diindikasikan sebagai 5, ukuran array dapat menampung 6 nilai karena indeks array dimulai dari NOL.

  • Indeks Array tidak boleh negatif.

  • VBScript Array dapat menyimpan semua jenis variabel dalam array. Karenanya, sebuah array dapat menyimpan integer, string, atau karakter dalam variabel array tunggal.

Menetapkan Nilai ke Array

Nilai ditetapkan ke larik dengan menetapkan nilai indeks larik terhadap masing-masing nilai yang akan ditetapkan. Ini bisa menjadi string.

Contoh

Tambahkan tombol dan tambahkan fungsi berikut.

Private Sub Constant_demo_Click()
   Dim arr(5)
   arr(0) = "1"           'Number as String
   arr(1) = "VBScript"    'String
   arr(2) = 100 		     'Number
   arr(3) = 2.45 		     'Decimal Number
   arr(4) = #10/07/2013#  'Date
   arr(5) = #12.45 PM#    'Time
  
   msgbox("Value stored in Array index 0 : " & arr(0))
   msgbox("Value stored in Array index 1 : " & arr(1))
   msgbox("Value stored in Array index 2 : " & arr(2))
   msgbox("Value stored in Array index 3 : " & arr(3))
   msgbox("Value stored in Array index 4 : " & arr(4))
   msgbox("Value stored in Array index 5 : " & arr(5))
End Sub

Saat Anda menjalankan fungsi di atas, ini menghasilkan output berikut.

Value stored in Array index 0 : 1
Value stored in Array index 1 : VBScript
Value stored in Array index 2 : 100
Value stored in Array index 3 : 2.45
Value stored in Array index 4 : 7/10/2013
Value stored in Array index 5 : 12:45:00 PM

Array Multi-Dimensi

Array tidak hanya terbatas pada satu dimensi, namun juga dapat memiliki maksimal 60 dimensi. Array dua dimensi adalah yang paling umum digunakan.

Contoh

Dalam contoh berikut, array multi-dimensi dideklarasikan dengan 3 baris dan 4 kolom.

Private Sub Constant_demo_Click()
   Dim arr(2,3) as Variant	' Which has 3 rows and 4 columns
   arr(0,0) = "Apple" 
   arr(0,1) = "Orange"
   arr(0,2) = "Grapes"           
   arr(0,3) = "pineapple" 
   arr(1,0) = "cucumber"           
   arr(1,1) = "beans"           
   arr(1,2) = "carrot"           
   arr(1,3) = "tomato"           
   arr(2,0) = "potato"             
   arr(2,1) = "sandwitch"            
   arr(2,2) = "coffee"             
   arr(2,3) = "nuts"            
           
   msgbox("Value in Array index 0,1 : " &  arr(0,1))
   msgbox("Value in Array index 2,2 : " &  arr(2,2))
End Sub

Saat Anda menjalankan fungsi di atas, ini menghasilkan output berikut.

Value stored in Array index : 0 , 1 : Orange
Value stored in Array index : 2 , 2 : coffee

Pernyataan ReDim

Pernyataan ReDim digunakan untuk mendeklarasikan variabel array dinamis dan mengalokasikan atau mengalokasikan kembali ruang penyimpanan.

Sintaksis

ReDim [Preserve] varname(subscripts) [, varname(subscripts)]

Deskripsi Parameter

  • Preserve - Parameter opsional yang digunakan untuk mempertahankan data dalam larik yang ada saat Anda mengubah ukuran dimensi terakhir.

  • Varname - Parameter yang diperlukan, yang menunjukkan nama variabel, yang harus mengikuti konvensi penamaan variabel standar.

  • Subscripts - Parameter yang diperlukan, yang menunjukkan ukuran array.

Contoh

Dalam contoh berikut, sebuah array telah didefinisikan ulang dan kemudian nilainya dipertahankan ketika ukuran array yang ada diubah.

Note - Setelah mengubah ukuran larik yang lebih kecil dari aslinya, data dalam elemen yang dihilangkan akan hilang.

Private Sub Constant_demo_Click()
   Dim a() as variant
   i = 0
   redim a(5)
   a(0) = "XYZ"
   a(1) = 41.25
   a(2) = 22
  
   REDIM PRESERVE a(7)
   For i = 3 to 7
   a(i) = i
   Next
  
   'to Fetch the output
   For i = 0 to ubound(a)
      Msgbox a(i)
   Next
End Sub

Saat Anda menjalankan fungsi di atas, ini menghasilkan output berikut.

XYZ
41.25
22
3
4
5
6
7

Metode Array

Ada berbagai fungsi bawaan dalam VBScript yang membantu pengembang menangani array secara efektif. Semua metode yang digunakan bersama dengan array tercantum di bawah ini. Silakan klik pada nama metode untuk mengetahuinya secara detail.

Sr.No. Deskripsi fungsi
1 LBound

Fungsi, yang mengembalikan integer yang sesuai dengan subskrip terkecil dari array yang diberikan.

2 UBound

Fungsi, yang mengembalikan integer yang sesuai dengan subskrip terbesar dari array yang diberikan.

3 Membagi

Sebuah Fungsi, yang mengembalikan larik yang berisi sejumlah nilai tertentu. Pisahkan berdasarkan pembatas.

4 Ikuti

Sebuah Fungsi, yang mengembalikan string yang berisi substring dalam jumlah tertentu dalam larik. Ini adalah fungsi kebalikan dari Metode Split.

5 Saring

Sebuah Fungsi, yang mengembalikan array berbasis nol yang berisi subset dari array string berdasarkan kriteria filter tertentu.

6 IsArray

A Function, yang mengembalikan nilai boolean yang menunjukkan apakah variabel input berupa array atau bukan.

7 Menghapus

Sebuah Fungsi, yang memulihkan memori yang dialokasikan untuk variabel array.

SEBUAH functionadalah sekelompok kode yang dapat digunakan kembali yang dapat dipanggil di mana saja dalam program Anda. Ini menghilangkan kebutuhan untuk menulis kode yang sama berulang kali. Hal ini memungkinkan pemrogram untuk membagi program besar menjadi sejumlah fungsi kecil dan dapat diatur.

Selain fungsi bawaan, VBA memungkinkan untuk menulis fungsi yang ditentukan pengguna juga. Di bab ini, Anda akan mempelajari cara menulis fungsi Anda sendiri di VBA.

Definisi Fungsi

Fungsi VBA dapat memiliki pernyataan pengembalian opsional. Ini diperlukan jika Anda ingin mengembalikan nilai dari suatu fungsi.

Misalnya, Anda dapat meneruskan dua angka dalam suatu fungsi dan kemudian Anda dapat mengharapkan dari fungsi tersebut untuk mengembalikan perkaliannya dalam program panggilan Anda.

Note - Suatu fungsi dapat mengembalikan beberapa nilai yang dipisahkan oleh koma sebagai larik yang ditetapkan ke nama fungsi itu sendiri.

Sebelum kita menggunakan suatu fungsi, kita perlu mendefinisikan fungsi tersebut. Cara paling umum untuk menentukan fungsi di VBA adalah dengan menggunakanFunction kata kunci, diikuti dengan nama fungsi yang unik dan mungkin atau mungkin tidak membawa daftar parameter dan pernyataan dengan End Functionkata kunci, yang menunjukkan akhir dari fungsi tersebut. Berikut ini adalah sintaks dasarnya.

Sintaksis

Tambahkan tombol dan tambahkan fungsi berikut.

Function Functionname(parameter-list)
   statement 1
   statement 2
   statement 3
   .......
   statement n
End Function

Contoh

Tambahkan fungsi berikut yang mengembalikan area. Perhatikan bahwa nilai / nilai bisa dikembalikan dengan nama fungsi itu sendiri.

Function findArea(Length As Double, Optional Width As Variant)
   If IsMissing(Width) Then
      findArea = Length * Length
   Else
      findArea = Length * Width
   End If
End Function

Memanggil Fungsi

Untuk memanggil suatu fungsi, panggil fungsi tersebut menggunakan nama fungsi seperti yang ditunjukkan pada gambar layar berikut.

Output dari area seperti yang ditunjukkan di bawah ini akan ditampilkan kepada pengguna.

Sub Procedures mirip dengan fungsi, namun ada beberapa perbedaan.

  • Prosedur sub JANGAN Mengembalikan nilai sementara fungsi mungkin atau mungkin tidak mengembalikan nilai.

  • Prosedur sub BISA dipanggil tanpa kata kunci panggilan.

  • Prosedur sub selalu tertutup dalam pernyataan Sub dan End Sub.

Contoh

Sub Area(x As Double, y As Double)
   MsgBox x * y
End Sub

Prosedur Panggilan

Untuk memanggil sebuah Prosedur di suatu tempat dalam skrip, Anda dapat melakukan panggilan dari suatu fungsi. Kita tidak akan dapat menggunakan cara yang sama seperti fungsi karena sub prosedur TIDAK AKAN mengembalikan nilai.

Function findArea(Length As Double, Width As Variant)
   area Length, Width    ' To Calculate Area 'area' sub proc is called
End Function

Sekarang Anda akan dapat memanggil fungsi saja tetapi tidak sub prosedur seperti yang ditunjukkan pada gambar berikut.

Area dihitung dan hanya ditampilkan di kotak pesan.

Sel hasil menampilkan NOL karena nilai area TIDAK dikembalikan dari fungsi. Singkatnya, Anda tidak dapat melakukan panggilan langsung ke sub prosedur dari lembar kerja excel.

VBA, pemrograman berbasis peristiwa bisa dipicu saat Anda mengubah sel atau rentang nilai sel secara manual. Ubah acara mungkin membuat segalanya lebih mudah, tetapi Anda dapat dengan cepat mengakhiri halaman yang penuh dengan format. Ada dua macam acara.

  • Acara Lembar Kerja
  • Acara Buku Kerja

Acara Lembar Kerja

Peristiwa Lembar Kerja dipicu saat ada perubahan di lembar kerja. Itu dibuat dengan melakukan klik kanan pada tab lembar dan memilih 'kode tampilan', dan kemudian menempelkan kode.

Pengguna dapat memilih setiap lembar kerja tersebut dan memilih "Lembar Kerja" dari menu drop-down untuk mendapatkan daftar semua acara Lembar Kerja yang didukung.

Berikut adalah acara lembar kerja yang didukung yang dapat ditambahkan oleh pengguna.

Private Sub Worksheet_Activate() 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) 
Private Sub Worksheet_Calculate() 
Private Sub Worksheet_Change(ByVal Target As Range) 
Private Sub Worksheet_Deactivate() 
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Contoh

Katakanlah, kita hanya perlu menampilkan pesan sebelum mengklik dua kali.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   MsgBox "Before Double Click"
End Sub

Keluaran

Setelah mengklik dua kali pada sel mana pun, kotak pesan ditampilkan kepada pengguna seperti yang ditunjukkan pada tangkapan layar berikut.

Acara Buku Kerja

Peristiwa buku kerja dipicu saat ada perubahan di buku kerja secara keseluruhan. Kami dapat menambahkan kode untuk acara buku kerja dengan memilih 'Buku Kerja Ini' dan memilih 'buku kerja' dari dropdown seperti yang ditunjukkan pada gambar layar berikut. Segera sub prosedur Workbook_open ditampilkan kepada pengguna seperti yang terlihat pada gambar layar berikut.

Berikut adalah acara Buku Kerja yang didukung yang bisa ditambahkan oleh pengguna.

Private Sub Workbook_AddinUninstall() 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Private Sub Workbook_BeforePrint(Cancel As Boolean) 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
Private Sub Workbook_Deactivate() 
Private Sub Workbook_NewSheet(ByVal Sh As Object) 
Private Sub Workbook_Open() 
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) 
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) 
Private Sub Workbook_SheetCalculate(ByVal Sh As Object) 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) 
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) 
Private Sub Workbook_WindowActivate(ByVal Wn As Window) 
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window) 
Private Sub Workbook_WindowResize(ByVal Wn As Window)

Contoh

Katakanlah, kita hanya perlu menampilkan pesan kepada pengguna bahwa lembar baru berhasil dibuat, setiap kali lembar baru dibuat.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
   MsgBox "New Sheet Created Successfully"
End Sub

Keluaran

Saat membuat lembar excel baru, sebuah pesan ditampilkan kepada pengguna seperti yang ditunjukkan pada gambar layar berikut.

Ada tiga jenis kesalahan dalam pemrograman: (a) Kesalahan Sintaks, (b) Kesalahan Runtime, dan (c) Kesalahan Logis.

Kesalahan sintaks

Kesalahan sintaks, juga disebut sebagai kesalahan penguraian, terjadi pada waktu interpretasi untuk VBScript. Misalnya, baris berikut ini menyebabkan kesalahan sintaks karena tidak ada tanda kurung penutup.

Function ErrorHanlding_Demo()
   dim x,y
   x = "Tutorialspoint"
   y = Ucase(x
End Function

Kesalahan runtime

Error runtime, juga disebut pengecualian, terjadi selama eksekusi, setelah interpretasi.

Misalnya, baris berikut menyebabkan kesalahan waktu proses karena di sini sintaksnya benar tetapi pada waktu proses mencoba memanggil fnmultiply, yang merupakan fungsi yang tidak ada.

Function ErrorHanlding_Demo1()
   Dim x,y
   x = 10
   y = 20
   z = fnadd(x,y)
   a = fnmultiply(x,y)
End Function

Function fnadd(x,y)
   fnadd = x + y
End Function

Kesalahan Logis

Kesalahan logika bisa menjadi jenis kesalahan yang paling sulit untuk dilacak. Kesalahan ini bukan hasil dari kesalahan sintaks atau runtime. Sebaliknya, ini terjadi saat Anda membuat kesalahan dalam logika yang menjalankan skrip Anda dan Anda tidak mendapatkan hasil yang diharapkan.

Anda tidak dapat menangkap kesalahan tersebut, karena tergantung pada kebutuhan bisnis Anda jenis logika apa yang ingin Anda masukkan ke dalam program Anda.

Misalnya, membagi angka dengan nol atau skrip yang ditulis yang masuk ke loop tak terbatas.

Objek Err

Asumsikan jika kita mengalami error runtime, maka eksekusi berhenti dengan menampilkan pesan error. Sebagai pengembang, jika kita ingin menangkap kesalahan, makaError Objek digunakan.

Contoh

Dalam contoh berikut, Err.Number memberikan nomor kesalahan dan Err.Description memberikan deskripsi kesalahan.

Err.Raise 6   ' Raise an overflow error.
MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description
Err.Clear   ' Clear the error.

Penanganan Error

VBA mengaktifkan rutinitas penanganan kesalahan dan juga dapat digunakan untuk menonaktifkan rutinitas penanganan kesalahan. Tanpa pernyataan On Error, semua kesalahan run-time yang terjadi berakibat fatal: pesan kesalahan ditampilkan, dan eksekusi berhenti tiba-tiba.

On Error { GoTo [ line | 0 | -1 ] | Resume Next }

Sr.No. Kata Kunci & Deskripsi
1

GoTo line

Mengaktifkan rutinitas penanganan kesalahan yang dimulai pada baris yang ditentukan dalam argumen baris yang diperlukan. Baris yang ditentukan harus dalam prosedur yang sama dengan pernyataan On Error, atau kesalahan waktu kompilasi akan terjadi.

2

GoTo 0

Menonaktifkan penangan kesalahan yang diaktifkan dalam prosedur saat ini dan menyetel ulang ke Tidak Ada.

3

GoTo -1

Menonaktifkan pengecualian yang diaktifkan dalam prosedur saat ini dan menyetel ulang ke Tidak Ada.

4

Resume Next

Menentukan bahwa ketika kesalahan run-time terjadi, kontrol pergi ke pernyataan segera setelah pernyataan di mana kesalahan terjadi, dan eksekusi berlanjut dari titik itu.

Contoh

Public Sub OnErrorDemo()
   On Error GoTo ErrorHandler   ' Enable error-handling routine.
   Dim x, y, z As Integer
   x = 50
   y = 0
   z = x / y   ' Divide by ZERO Error Raises
  
   ErrorHandler:    ' Error-handling routine.
   Select Case Err.Number   ' Evaluate error number.
      Case 10   ' Divide by zero error
         MsgBox ("You attempted to divide by zero!")
      Case Else
         MsgBox "UNKNOWN ERROR  - Error# " & Err.Number & " : " & Err.Description
   End Select
   Resume Next
End Sub

Saat memprogram menggunakan VBA, ada beberapa objek penting yang akan dihadapi pengguna.

  • Objek Aplikasi
  • Objek Buku Kerja
  • Objek Lembar Kerja
  • Objek Jarak Jauh

Objek Aplikasi

Objek Aplikasi terdiri dari:

  • Pengaturan dan opsi di seluruh aplikasi.
  • Metode yang mengembalikan objek level atas, seperti ActiveCell, ActiveSheet, dan seterusnya.

Contoh

'Example 1 :
Set xlapp = CreateObject("Excel.Sheet") 
xlapp.Application.Workbooks.Open "C:\test.xls"

'Example 2 :
Application.Windows("test.xls").Activate

'Example 3:
Application.ActiveCell.Font.Bold = True

Objek Buku Kerja

Objek Buku Kerja adalah anggota kumpulan Buku Kerja dan berisi semua objek Buku Kerja yang saat ini terbuka di Microsoft Excel.

Contoh

'Ex 1 : To close Workbooks
Workbooks.Close

'Ex 2 : To Add an Empty Work Book
Workbooks.Add

'Ex 3: To Open a Workbook
Workbooks.Open FileName:="Test.xls", ReadOnly:=True

'Ex : 4 - To Activate WorkBooks
Workbooks("Test.xls").Worksheets("Sheet1").Activate

Objek Lembar Kerja

Objek lembar kerja adalah anggota dari kumpulan lembar kerja dan berisi semua objek lembar kerja dalam buku kerja.

Contoh

'Ex 1 : To make it Invisible
Worksheets(1).Visible = False

'Ex 2 : To protect an WorkSheet
Worksheets("Sheet1").Protect password:=strPassword, scenarios:=True

Objek Jarak Jauh

Objek Rentang mewakili sel, baris, kolom, atau pilihan sel yang berisi satu atau beberapa blok sel berkelanjutan.

'Ex 1 : To Put a value in the cell A5
Worksheets("Sheet1").Range("A5").Value = "5235"

'Ex 2 : To put a value in range of Cells
Worksheets("Sheet1").Range("A1:A4").Value = 5

Anda juga dapat membaca File Excel dan menulis konten sel ke dalam File Teks menggunakan VBA. VBA memungkinkan pengguna untuk bekerja dengan file teks menggunakan dua metode -

  • Objek Sistem File
  • menggunakan Perintah Tulis

Objek Sistem File (FSO)

Seperti namanya, FSO membantu pengembang untuk bekerja dengan drive, folder, dan file. Pada bagian ini, kita akan membahas bagaimana menggunakan FSO.

Sr.No. Jenis & Deskripsi Objek
1

Drive

Drive adalah Objek. Berisi metode dan properti yang memungkinkan Anda mengumpulkan informasi tentang drive yang terpasang ke sistem.

2

Drives

Drive adalah Koleksi. Ini memberikan daftar drive yang terpasang ke sistem, baik secara fisik atau logis.

3

File

File adalah Objek. Ini berisi metode dan properti yang memungkinkan pengembang untuk membuat, menghapus, atau memindahkan file.

4

Files

File adalah Koleksi. Ini memberikan daftar semua file yang ada di dalam folder.

5

Folder

Folder adalah Objek. Ini menyediakan metode dan properti yang memungkinkan pengembang untuk membuat, menghapus, atau memindahkan folder.

6

Folders

Folder adalah Koleksi. Ini memberikan daftar semua folder di dalam folder.

7

TextStream

TextStream adalah Objek. Ini memungkinkan pengembang untuk membaca dan menulis file teks.

Mendorong

Driveadalah objek, yang menyediakan akses ke properti dari drive disk atau jaringan bersama tertentu. Properti berikut ini didukung olehDrive objek -

  • AvailableSpace
  • DriveLetter
  • DriveType
  • FileSystem
  • FreeSpace
  • IsReady
  • Path
  • RootFolder
  • SerialNumber
  • ShareName
  • TotalSize
  • VolumeName

Contoh

Step 1- Sebelum melanjutkan ke pembuatan skrip menggunakan FSO, kita harus mengaktifkan Microsoft Scripting Runtime. Untuk melakukan hal yang sama, navigasikan ke Alat → Referensi seperti yang ditunjukkan pada tangkapan layar berikut.

Step 2 - Tambahkan "Microsoft Scripting RunTime" dan Klik OK.

Step 3 - Tambahkan Data yang ingin Anda tulis dalam File Teks dan tambahkan Tombol Perintah.

Step 4 - Sekarang waktunya untuk Script.

Private Sub fn_write_to_text_Click()
   Dim FilePath As String
   Dim CellData As String
   Dim LastCol As Long
   Dim LastRow As Long
  
   Dim fso As FileSystemObject
   Set fso = New FileSystemObject
   Dim stream As TextStream
  
   LastCol = ActiveSheet.UsedRange.Columns.Count
   LastRow = ActiveSheet.UsedRange.Rows.Count
    
   ' Create a TextStream.
   Set stream = fso.OpenTextFile("D:\Try\Support.log", ForWriting, True)
  
   CellData = ""
  
   For i = 1 To LastRow
      For j = 1 To LastCol
         CellData = Trim(ActiveCell(i, j).Value)
         stream.WriteLine "The Value at location (" & i & "," & j & ")" & CellData
      Next j
   Next i
  
   stream.Close
   MsgBox ("Job Done")
End Sub

Keluaran

Saat menjalankan skrip, pastikan Anda menempatkan kursor di sel pertama lembar kerja. File Support.log dibuat seperti yang ditunjukkan pada gambar berikut di bawah "D: \ Coba".

Isi file ditunjukkan pada tangkapan layar berikut.

Tulis Perintah

Tidak seperti FSO, kami TIDAK perlu menambahkan referensi apa pun, namun, kami TIDAK akan dapat bekerja dengan drive, file, dan folder. Kami hanya dapat menambahkan aliran ke file teks.

Contoh

Private Sub fn_write_to_text_Click()
   Dim FilePath As String
   Dim CellData As String
   Dim LastCol As Long
   Dim LastRow As Long
  
   LastCol = ActiveSheet.UsedRange.Columns.Count
   LastRow = ActiveSheet.UsedRange.Rows.Count
    
   FilePath = "D:\Try\write.txt"
   Open FilePath For Output As #2
  
   CellData = ""
   For i = 1 To LastRow
      For j = 1 To LastCol
         CellData = "The Value at location (" & i & "," & j & ")" & Trim(ActiveCell(i, j).Value)
         Write #2, CellData
      Next j
   Next i
  
   Close #2
   MsgBox ("Job Done")
End Sub

Keluaran

Setelah menjalankan skrip, file "write.txt" dibuat di lokasi "D: \ Try" seperti yang ditunjukkan pada gambar layar berikut.

Isi file ditunjukkan pada gambar layar berikut.

Menggunakan VBA, Anda dapat membuat grafik berdasarkan kriteria tertentu. Mari kita lihat dengan menggunakan sebuah contoh.

Step 1 - Masukkan data untuk membuat grafik.

Step 2 - Buat 3 tombol - satu untuk membuat grafik batang, satu lagi untuk membuat diagram lingkaran, dan satu lagi untuk membuat diagram kolom.

Step 3 - Kembangkan Makro untuk menghasilkan masing-masing jenis bagan ini.

' Procedure to Generate Pie Chart
Private Sub fn_generate_pie_graph_Click()
   Dim cht As ChartObject
   For Each cht In Worksheets(1).ChartObjects
      cht.Chart.Type = xlPie
   Next cht
End Sub

' Procedure to Generate Bar Graph
Private Sub fn_Generate_Bar_Graph_Click()
   Dim cht As ChartObject
   For Each cht In Worksheets(1).ChartObjects
      cht.Chart.Type = xlBar
   Next cht
End Sub

' Procedure to Generate Column Graph
Private Sub fn_generate_column_graph_Click()
   Dim cht As ChartObject
   For Each cht In Worksheets(1).ChartObjects
      cht.Chart.Type = xlColumn
   Next cht
End Sub

Step 4- Setelah mengklik tombol yang sesuai, grafik dibuat. Pada keluaran berikut, klik tombol buat Bagan Pie.

SEBUAH User Formadalah kotak dialog yang dibuat khusus yang membuat entri data pengguna lebih terkontrol dan lebih mudah digunakan bagi pengguna. Pada bab ini, Anda akan belajar mendesain formulir sederhana dan menambahkan data ke dalam excel.

Step 1- Arahkan ke Jendela VBA dengan menekan Alt + F11 dan Arahkan ke "Sisipkan" Menu dan pilih "Formulir Pengguna". Setelah memilih, formulir pengguna ditampilkan seperti yang ditunjukkan pada tangkapan layar berikut.

Step 2 - Desain formulir menggunakan kontrol yang diberikan.

Step 3- Setelah menambahkan setiap kontrol, kontrol tersebut harus diberi nama. Judul sesuai dengan apa yang muncul di formulir dan nama sesuai dengan nama logis yang akan muncul saat Anda menulis kode VBA untuk elemen itu.

Step 4 - Berikut adalah nama untuk setiap kontrol yang ditambahkan.

Kontrol Nama Logis Caption
Dari frmempform Formulir Karyawan
Kotak Label ID Karyawan kosong identitas pegawai
Kotak Label nama depan nama depan Nama depan
Kotak Label nama belakang nama keluarga Nama keluarga
Kotak Label dob dob Tanggal lahir
Kotak Label mailid mailid ID Email
Kotak Label Pemegang Paspor Pemegang paspor Pemegang paspor
Kotak Teks Emp ID txtempid Tak dapat diterapkan
Kotak Teks Nama Depan txtfirstname Tak dapat diterapkan
Kotak Teks Nama Belakang txtlastname Tak dapat diterapkan
Kotak Teks ID Email txtemailid Tak dapat diterapkan
Kotak Kombo Tanggal cmbdate.dll Tak dapat diterapkan
Kotak Kombo Bulan cmbmonth Tak dapat diterapkan
Tahun Combo Box cmbyear Tak dapat diterapkan
Ya Tombol Radio radioyes Iya
Tidak Ada Tombol Radio radiono Tidak
Tombol Kirim btnsubmit Kirimkan
Tombol Batal btncancel Membatalkan

Step 5 - Tambahkan kode untuk acara pemuatan formulir dengan melakukan klik kanan pada formulir dan memilih 'Lihat Kode'.

Step 6 - Pilih 'Userform' dari objek drop-down dan pilih metode 'Initialize' seperti yang ditunjukkan pada gambar berikut.

Step 7 - Setelah Memuat formulir, pastikan bahwa kotak teks dikosongkan, kotak drop-down terisi dan tombol Radio disetel ulang.

Private Sub UserForm_Initialize()
   'Empty Emp ID Text box and Set the Cursor 
   txtempid.Value = ""
   txtempid.SetFocus
   
   'Empty all other text box fields
   txtfirstname.Value = ""
   txtlastname.Value = ""
   txtemailid.Value = ""
   
   'Clear All Date of Birth Related Fields
   cmbdate.Clear
   cmbmonth.Clear
   cmbyear.Clear
   
   'Fill Date Drop Down box - Takes 1 to 31
   With cmbdate
      .AddItem "1"
      .AddItem "2"
      .AddItem "3"
      .AddItem "4"
      .AddItem "5"
      .AddItem "6"
      .AddItem "7"
      .AddItem "8"
      .AddItem "9"
      .AddItem "10"
      .AddItem "11"
      .AddItem "12"
      .AddItem "13"
      .AddItem "14"
      .AddItem "15"
      .AddItem "16"
      .AddItem "17"
      .AddItem "18"
      .AddItem "19"
      .AddItem "20"
      .AddItem "21"
      .AddItem "22"
      .AddItem "23"
      .AddItem "24"
      .AddItem "25"
      .AddItem "26"
      .AddItem "27"
      .AddItem "28"
      .AddItem "29"
      .AddItem "30"
      .AddItem "31"
   End With
   
   'Fill Month Drop Down box - Takes Jan to Dec
   With cmbmonth
      .AddItem "JAN"
      .AddItem "FEB"
      .AddItem "MAR"
      .AddItem "APR"
      .AddItem "MAY"
      .AddItem "JUN"
      .AddItem "JUL"
      .AddItem "AUG"
      .AddItem "SEP"
      .AddItem "OCT"
      .AddItem "NOV"
      .AddItem "DEC"
   End With
   
   'Fill Year Drop Down box - Takes 1980 to 2014
   With cmbyear
      .AddItem "1980"
      .AddItem "1981"
      .AddItem "1982"
      .AddItem "1983"
      .AddItem "1984"
      .AddItem "1985"
      .AddItem "1986"
      .AddItem "1987"
      .AddItem "1988"
      .AddItem "1989"
      .AddItem "1990"
      .AddItem "1991"
      .AddItem "1992"
      .AddItem "1993"
      .AddItem "1994"
      .AddItem "1995"
      .AddItem "1996"
      .AddItem "1997"
      .AddItem "1998"
      .AddItem "1999"
      .AddItem "2000"
      .AddItem "2001"
      .AddItem "2002"
      .AddItem "2003"
      .AddItem "2004"
      .AddItem "2005"
      .AddItem "2006"
      .AddItem "2007"
      .AddItem "2008"
      .AddItem "2009"
      .AddItem "2010"
      .AddItem "2011"
      .AddItem "2012"
      .AddItem "2013"
      .AddItem "2014"
   End With
   
   'Reset Radio Button. Set it to False when form loads.
   radioyes.Value = False
   radiono.Value = False

End Sub

Step 8- Sekarang tambahkan kode ke tombol Kirim. Setelah mengklik tombol kirim, pengguna harus dapat menambahkan nilai ke dalam lembar kerja.

Private Sub btnsubmit_Click()
   Dim emptyRow As Long
  
   'Make Sheet1 active
   Sheet1.Activate
  
   'Determine emptyRow
   emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
  
   'Transfer information
   Cells(emptyRow, 1).Value = txtempid.Value
   Cells(emptyRow, 2).Value = txtfirstname.Value
   Cells(emptyRow, 3).Value = txtlastname.Value
   Cells(emptyRow, 4).Value = cmbdate.Value & "/" & cmbmonth.Value & "/" & cmbyear.Value
   Cells(emptyRow, 5).Value = txtemailid.Value
  
   If radioyes.Value = True Then
      Cells(emptyRow, 6).Value = "Yes"
   Else
      Cells(emptyRow, 6).Value = "No"
   End If
End Sub

Step 9 - Tambahkan metode untuk menutup formulir ketika pengguna mengklik tombol Batal.

Private Sub btncancel_Click()
   Unload Me
End Sub

Step 10- Jalankan formulir dengan mengklik tombol "Jalankan". Masukkan nilai ke dalam formulir dan klik tombol 'Kirim'. Secara otomatis nilai akan mengalir ke lembar kerja seperti yang ditunjukkan pada gambar layar berikut.