Tutorial Excel VBA - Cara Menulis Kode di Spreadsheet Menggunakan Visual Basic

pengantar

Ini adalah tutorial tentang menulis kode di spreadsheet Excel menggunakan Visual Basic for Applications (VBA).

Excel adalah salah satu produk Microsoft yang paling populer. Pada tahun 2016, CEO Microsoft berkata "Pikirkan tentang dunia tanpa Excel. Itu tidak mungkin bagi saya.” Mungkin dunia tidak bisa berpikir tanpa Excel.

  • Pada tahun 1996, terdapat lebih dari 30 juta pengguna Microsoft Excel (sumber).
  • Saat ini, diperkirakan ada 750 juta pengguna Microsoft Excel. Itu sedikit lebih banyak daripada populasi Eropa dan 25x lebih banyak pengguna dibandingkan pada tahun 1996.

Kami satu keluarga besar yang bahagia!

Dalam tutorial ini, Anda akan belajar tentang VBA dan cara menulis kode di lembar bentang Excel menggunakan Visual Basic.

Prasyarat

Anda tidak memerlukan pengalaman pemrograman sebelumnya untuk memahami tutorial ini. Namun, Anda membutuhkan:

  • Pemahaman dasar hingga menengah dengan Microsoft Excel
  • Jika Anda ingin mengikuti contoh VBA dalam artikel ini, Anda memerlukan akses ke Microsoft Excel, sebaiknya versi terbaru (2019) tetapi Excel 2016 dan Excel 2013 akan berfungsi dengan baik.
  • Kesediaan untuk mencoba hal baru

Tujuan Pembelajaran

Selama artikel ini, Anda akan mempelajari:

  1. Apa itu VBA
  2. Mengapa Anda menggunakan VBA
  3. Cara menyiapkan di Excel untuk menulis VBA
  4. Bagaimana mengatasi beberapa masalah dunia nyata dengan VBA

Konsep Penting

Berikut adalah beberapa konsep penting yang harus Anda ketahui untuk sepenuhnya memahami tutorial ini.

Objek : Excel berorientasi objek, yang berarti semuanya adalah objek - jendela Excel, buku kerja, lembar, bagan, sel. VBA memungkinkan pengguna untuk memanipulasi dan melakukan tindakan dengan objek di Excel.

Jika Anda tidak memiliki pengalaman dengan pemrograman berorientasi objek dan ini adalah konsep baru, luangkan waktu sejenak untuk memahami!

Prosedur : prosedur adalah potongan kode VBA, yang ditulis di Editor Visual Basic, yang menyelesaikan tugas. Terkadang, ini juga disebut sebagai makro (selengkapnya tentang makro di bawah). Ada dua jenis prosedur:

  • Subrutin: sekelompok pernyataan VBA yang melakukan satu atau beberapa tindakan
  • Fungsi: sekelompok pernyataan VBA yang melakukan satu atau beberapa tindakan dan mengembalikan satu atau beberapa nilai

Catatan: Anda dapat memiliki fungsi yang beroperasi di dalam subrutin. Anda akan lihat nanti.

Makro : Jika Anda telah menghabiskan waktu mempelajari lebih banyak fungsi Excel tingkat lanjut, Anda mungkin pernah menemukan konsep "makro". Pengguna Excel dapat merekam makro, yang terdiri dari perintah pengguna / penekanan tombol / klik, dan memutarnya secepat kilat untuk menyelesaikan tugas yang berulang. Makro yang direkam menghasilkan kode VBA, yang kemudian dapat Anda periksa. Sebenarnya cukup menyenangkan untuk merekam makro sederhana dan kemudian melihat kode VBA.

Harap diingat bahwa terkadang lebih mudah dan lebih cepat untuk merekam makro daripada membuat kode manual prosedur VBA.

Misalnya, mungkin Anda bekerja di manajemen proyek. Sekali seminggu, Anda harus mengubah laporan mentah yang diekspor dari sistem manajemen proyek Anda menjadi laporan yang rapi dan bersih untuk kepemimpinan. Anda perlu memformat nama proyek yang melebihi anggaran dengan teks merah tebal. Anda dapat merekam perubahan pemformatan sebagai makro dan menjalankannya kapan pun Anda perlu membuat perubahan.

Apa itu VBA?

Visual Basic for Applications adalah bahasa pemrograman yang dikembangkan oleh Microsoft. Setiap program perangkat lunak di suite Microsoft Office dibundel dengan bahasa VBA tanpa biaya tambahan. VBA memungkinkan pengguna Microsoft Office untuk membuat program kecil yang beroperasi dalam program perangkat lunak Microsoft Office.

Pikirkan VBA seperti oven pizza di dalam restoran. Excel adalah restorannya. Dapur dilengkapi dengan peralatan komersial standar, seperti lemari es besar, kompor, dan oven ole biasa - semua itu adalah fitur standar Excel.

Tetapi bagaimana jika Anda ingin membuat pizza berbahan bakar kayu? Tidak dapat melakukannya dalam oven pemanggang komersial standar. VBA adalah oven pizza.

Nyam.

Mengapa menggunakan VBA di Excel?

Karena pizza berbahan bakar kayu adalah yang terbaik!

Tapi serius.

Banyak orang menghabiskan banyak waktu di Excel sebagai bagian dari pekerjaan mereka. Waktu di Excel juga bergerak berbeda. Bergantung pada situasinya, 10 menit di Excel bisa terasa seperti selamanya jika Anda tidak dapat melakukan apa yang Anda butuhkan, atau 10 jam dapat berlalu dengan sangat cepat jika semuanya berjalan dengan baik. Kapan Anda harus bertanya pada diri sendiri, mengapa saya menghabiskan 10 jam di Excel?

Terkadang, hari-hari itu tidak bisa dihindari. Tetapi jika Anda menghabiskan 8-10 jam setiap hari di Excel melakukan tugas berulang, mengulangi banyak proses yang sama, mencoba membersihkan setelah pengguna lain file, atau bahkan memperbarui file lain setelah perubahan dilakukan pada file Excel, prosedur VBA mungkin bisa menjadi solusi untuk Anda.

Anda harus mempertimbangkan untuk menggunakan VBA jika Anda perlu:

  • Otomatiskan tugas yang berulang
  • Buat cara mudah bagi pengguna untuk berinteraksi dengan spreadsheet Anda
  • Memanipulasi data dalam jumlah besar

Mempersiapkan untuk Menulis VBA di Excel

Tab Pengembang

Untuk menulis VBA, Anda harus menambahkan tab Pengembang ke pita, sehingga Anda akan melihat pita seperti ini.

Untuk menambahkan tab Pengembang ke pita:

  1. Pada tab File, masuk ke Opsi> Kustomisasi Pita.
  2. Di bawah Kustomisasi Pita dan di bawah Tab Utama, pilih kotak centang Pengembang.

Setelah Anda memperlihatkan tab, tab pengembang tetap terlihat, kecuali Anda mengosongkan kotak centang atau harus menginstal ulang Excel. Untuk informasi lebih lanjut, lihat dokumentasi bantuan Microsoft.

Editor VBA

Arahkan ke Tab Pengembang, dan klik tombol Visual Basic. Sebuah jendela baru akan muncul - ini adalah Editor Visual Basic. Untuk keperluan tutorial ini, Anda hanya perlu terbiasa dengan panel Project Explorer dan panel Properti Properti.

Contoh VBA Excel

Pertama, mari buat file untuk kita mainkan.

  1. Buka file Excel baru
  2. Simpan sebagai buku kerja berkemampuan makro (. Xlsm)
  3. Pilih tab Pengembang
  4. Buka Editor VBA

Mari bergoyang dengan beberapa contoh mudah untuk membuat Anda menulis kode di spreadsheet menggunakan Visual Basic.

Contoh # 1: Menampilkan Pesan saat Pengguna Membuka Buku Kerja Excel

Di VBA Editor, pilih Insert -> New Module

Tulis kode ini di jendela Modul (jangan tempel!):

Sub Auto_Open ()

MsgBox ("Selamat datang di Buku Kerja XYZ.")

Akhiri Sub

Simpan, tutup buku kerja, dan buka kembali buku kerja. Dialog ini akan ditampilkan.

Ta da!

Bagaimana cara melakukannya?

Bergantung pada pengetahuan Anda tentang pemrograman, Anda mungkin memiliki beberapa tebakan. Ini tidak terlalu rumit, tetapi ada banyak hal yang terjadi:

  • Sub (kependekan dari "Subrutin): ingat dari awal," sekelompok pernyataan VBA yang melakukan satu atau beberapa tindakan. "
  • Auto_Open: ini adalah subrutin spesifik. Ini secara otomatis menjalankan kode Anda ketika file Excel terbuka - ini adalah peristiwa yang memicu prosedur. Auto_Open hanya akan berjalan saat workbook dibuka secara manual; itu tidak akan berjalan jika buku kerja dibuka melalui kode dari buku kerja lain (Workbook_Open akan melakukannya, pelajari lebih lanjut tentang perbedaan antara keduanya).
  • Secara default, akses subrutin adalah publik. Ini berarti modul lain dapat menggunakan subrutin ini. Semua contoh dalam tutorial ini akan menjadi subrutin publik. Jika perlu, Anda bisa mendeklarasikan subrutin sebagai privat. Ini mungkin diperlukan dalam beberapa situasi. Pelajari lebih lanjut tentang pengubah akses subrutin.
  • msgBox: ini adalah fungsi - sekelompok pernyataan VBA yang melakukan satu atau lebih tindakan dan mengembalikan nilai. Nilai yang dikembalikan adalah pesan "Selamat datang di Buku Kerja XYZ".

Singkatnya, ini adalah subrutin sederhana yang berisi sebuah fungsi.

Kapan saya bisa menggunakan ini?

Mungkin Anda memiliki file yang sangat penting yang jarang diakses (katakanlah, sekali seperempat), tetapi secara otomatis diperbarui setiap hari oleh prosedur VBA lain. Saat diakses, ini dilakukan oleh banyak orang di berbagai departemen, di seluruh perusahaan.

  • Masalah: Sebagian besar waktu ketika pengguna mengakses file, mereka bingung tentang tujuan file ini (mengapa ada), bagaimana file itu sering diperbarui, siapa yang memeliharanya, dan bagaimana mereka harus berinteraksi dengannya. Karyawan baru selalu memiliki banyak pertanyaan, dan Anda harus menjawab pertanyaan ini berulang kali.
  • Solusi: buat pesan pengguna yang berisi jawaban singkat untuk setiap pertanyaan yang sering dijawab ini.

Contoh Dunia Nyata

  • Gunakan fungsi MsgBox untuk menampilkan pesan ketika ada acara apa pun: pengguna menutup buku kerja Excel, pengguna mencetak, lembar baru ditambahkan ke buku kerja, dll.
  • Gunakan fungsi MsgBox untuk menampilkan pesan saat pengguna harus memenuhi kondisi sebelum menutup buku kerja Excel
  • Gunakan fungsi InputBox untuk mendapatkan informasi dari pengguna

Contoh # 2: Izinkan Pengguna untuk Menjalankan Prosedur lain

Di VBA Editor, pilih Insert -> New Module

Tulis kode ini di jendela Modul (jangan tempel!):

Sub UserReportQuery ()

Redupkan UserInput Selama

Redupkan Jawaban Sebagai Integer

UserInput = vbYesNo

Answer = MsgBox ("Proses Laporan XYZ?", UserInput)

Jika Answer = vbYes Kemudian ProcessReport

Akhiri Sub

Sub ProcessReport ()

MsgBox ("Terima kasih telah memproses Laporan XYZ.")

Akhiri Sub

Simpan dan arahkan kembali ke tab Pengembang di Excel dan pilih opsi "Tombol". Klik sel dan tetapkan makro UserReportQuery ke tombol.

Sekarang klik tombolnya. Pesan ini harus menampilkan:

Klik "ya" atau tekan Enter.

Sekali lagi, tada!

Harap dicatat bahwa subrutin sekunder, ProcessReport, bisa apa saja . Saya akan mendemonstrasikan lebih banyak kemungkinan dalam contoh # 3. Tapi pertama-tama...

Bagaimana cara melakukannya?

Contoh ini dibuat berdasarkan contoh sebelumnya dan memiliki beberapa elemen baru. Mari kita bahas hal-hal baru:

  • Redupkan UserInput As Long: Dim adalah kependekan dari "dimensi" dan memungkinkan Anda untuk mendeklarasikan nama variabel. Dalam hal ini, UserInput adalah nama variabel dan Long adalah tipe datanya. Dalam bahasa Inggris sederhana, baris ini berarti "Berikut adalah variabel yang disebut" UserInput ", dan itu adalah jenis variabel Panjang."
  • Dim Answer As Integer: mendeklarasikan variabel lain yang disebut "Answer," dengan tipe data Integer. Pelajari lebih lanjut tentang tipe data di sini.
  • UserInput = vbYesNo: memberikan nilai ke variabel. Dalam hal ini, vbYesNo, yang menampilkan tombol Yes dan No. Ada banyak jenis tombol, pelajari lebih lanjut di sini.
  • Answer = MsgBox (“Memproses Laporan XYZ?”, UserInput): menetapkan nilai variabel Answer menjadi fungsi MsgBox dan variabel UserInput. Ya, variabel dalam variabel.
  • If Answer = vbYes Then ProcessReport: ini adalah “pernyataan If,” pernyataan bersyarat, yang memungkinkan kita untuk mengatakan jika x benar, maka lakukan y. Dalam kasus ini, jika pengguna telah memilih "Ya", maka jalankan subrutin ProcessReport.

Kapan saya bisa menggunakan ini?

Ini dapat digunakan dengan banyak cara. Nilai dan keserbagunaan fungsi ini lebih ditentukan oleh apa yang dilakukan subrutin sekunder.

Misalnya, mungkin Anda memiliki file yang digunakan untuk menghasilkan 3 laporan mingguan yang berbeda. Laporan ini diformat dengan cara yang sangat berbeda.

  • Masalah: Setiap kali salah satu dari laporan ini perlu dibuat, pengguna membuka file dan mengubah format dan bagan; dst dan lain sebagainya. File ini sedang diedit secara ekstensif setidaknya 3 kali seminggu, dan membutuhkan setidaknya 30 menit setiap kali diedit.
  • Solusi: buat 1 tombol per jenis laporan, yang secara otomatis memformat ulang komponen laporan yang diperlukan dan menghasilkan bagan yang diperlukan.

Contoh Dunia Nyata

  • Buat kotak dialog bagi pengguna untuk secara otomatis mengisi informasi tertentu di beberapa lembar
  • Gunakan fungsi InputBox untuk mendapatkan informasi dari pengguna, yang kemudian diisi di beberapa lembar

Contoh # 3: Menambahkan Angka ke Rentang dengan Loop Untuk Berikutnya

For loop sangat berguna jika Anda perlu melakukan tugas berulang pada rentang nilai tertentu - array atau rentang sel. Dalam bahasa Inggris sederhana, sebuah lingkaran mengatakan "untuk setiap x, lakukan y."

Di VBA Editor, pilih Insert -> New Module

Tulis kode ini di jendela Modul (jangan tempel!):

Sub LoopExample ()

Dim X Sebagai Integer

Untuk X = 1 Sampai 100

Rentang ("A" & X). Nilai = X

X berikutnya

Akhiri Sub

Simpan dan navigasikan kembali ke tab Pengembang Excel dan pilih tombol Macro. Jalankan makro LoopExample.

Ini harus terjadi:

Dll, sampai baris ke-100.

Bagaimana cara melakukannya?

  • Dim X As Integer: mendeklarasikan variabel X sebagai tipe data Integer.
  • Untuk X = 1 Sampai 100: ini adalah awal dari loop For. Sederhananya, ini memberi tahu loop untuk terus berulang sampai X = 100. X adalah penghitung . Perulangan akan terus berjalan hingga X = 100, dieksekusi untuk terakhir kalinya, dan kemudian berhenti.
  • Range ("A" & X) .Value = X: ini menyatakan kisaran loop dan apa yang harus dimasukkan ke dalam kisaran itu. Karena X = 1 awalnya, sel pertama akan menjadi A1, pada titik mana loop akan menempatkan X ke dalam sel tersebut.
  • X Berikutnya: ini memberi tahu loop untuk berjalan lagi

Kapan saya bisa menggunakan ini?

Loop For-Next adalah salah satu fungsi VBA yang paling kuat; ada banyak kasus penggunaan potensial. Ini adalah contoh yang lebih kompleks yang akan membutuhkan banyak lapisan logika, tetapi ini mengkomunikasikan dunia kemungkinan dalam loop For-Next.

Mungkin Anda memiliki daftar semua produk yang dijual di toko roti Anda di Kolom A, jenis produk di Kolom B (kue, donat, atau muffin), biaya bahan di Kolom C, dan harga rata-rata pasar dari setiap jenis produk di lembar lain.

Anda perlu mencari tahu berapa harga eceran setiap produk. Anda mengira itu harus menjadi biaya bahan ditambah 20%, tetapi juga 1,2% di bawah rata-rata pasar jika memungkinkan. Perulangan For-Next akan memungkinkan Anda melakukan jenis penghitungan ini.

Contoh Dunia Nyata

  • Gunakan loop dengan pernyataan if bersarang untuk menambahkan nilai tertentu ke array terpisah hanya jika memenuhi kondisi tertentu
  • Lakukan kalkulasi matematis pada setiap nilai dalam suatu rentang, misalnya menghitung biaya tambahan dan menambahkannya ke nilai tersebut
  • Ulangi setiap karakter dalam string dan ekstrak semua angka
  • Pilih secara acak sejumlah nilai dari larik

Kesimpulan

Sekarang kita telah berbicara tentang pizza dan muffin dan oh-ya, bagaimana menulis kode VBA di spreadsheet Excel, mari kita lakukan pemeriksaan pembelajaran. Lihat apakah Anda bisa menjawab pertanyaan-pertanyaan ini.

  • Apa itu VBA?
  • Bagaimana cara menyiapkan untuk mulai menggunakan VBA di Excel?
  • Mengapa dan kapan Anda akan menggunakan VBA?
  • Apa saja masalah yang bisa saya selesaikan dengan VBA?

Jika Anda memiliki gagasan yang bagus tentang bagaimana Anda dapat menjawab pertanyaan-pertanyaan ini, maka ini berhasil.

Apakah Anda pengguna sesekali atau pengguna yang mahir, saya harap tutorial ini memberikan informasi yang berguna tentang apa yang dapat dicapai hanya dengan sedikit kode di spreadsheet Excel Anda.

Selamat membuat kode!

Sumber Belajar

  • Pemrograman VBA Excel untuk Dummies, John Walkenbach
  • Mulailah dengan VBA, Dokumentasi Microsoft
  • Mempelajari VBA di Excel, Lynda

Sedikit tentang saya

Saya Chloe Tucker, seorang seniman dan pengembang di Portland, Oregon. Sebagai mantan pendidik, saya terus mencari titik temu antara pembelajaran dan pengajaran, atau teknologi dan seni. Hubungi saya di Twitter @_chloetucker dan lihat situs web saya di chloe.dev.