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 SubStep 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 / FalsePernyataan 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/user2Itu 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 FunctionKeluaran
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 FunctionKeluaran
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 SubOutput
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 SubKeluaran
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 aboveFungsi 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 SubSaat 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 PMArray 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 SubSaat Anda menjalankan fungsi di atas, ini menghasilkan output berikut.
Value stored in Array index : 0 , 1 : Orange
Value stored in Array index : 2 , 2 : coffeePernyataan 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 SubSaat Anda menjalankan fungsi di atas, ini menghasilkan output berikut.
XYZ
41.25
22
3
4
5
6
7Metode 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 FunctionContoh
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 FunctionMemanggil 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 SubProsedur 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 FunctionSekarang 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 SubKeluaran
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 SubKeluaran
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 FunctionKesalahan 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 FunctionKesalahan 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 SubSaat 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 = TrueObjek 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").ActivateObjek 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:=TrueObjek 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 = 5Anda 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 SubKeluaran
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 SubKeluaran
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 SubStep 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 SubStep 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 SubStep 9 - Tambahkan metode untuk menutup formulir ketika pengguna mengklik tombol Batal.
Private Sub btncancel_Click()
Unload Me
End SubStep 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.
