Tutorial VBA Excel - Cara Menulis Kod dalam Spreadsheet Menggunakan Visual Basic

Pengaturcaraan

Pengenalan

Ini adalah tutorial mengenai menulis kod dalam hamparan Excel menggunakan Visual Basic for Applications (VBA).

Excel adalah salah satu produk Microsoft yang paling popular. Pada tahun 2016, Ketua Pegawai Eksekutif Microsoft berkata, "Fikirkan dunia tanpa Excel. Itu mustahil bagi saya." Mungkin dunia tidak boleh berfikir tanpa Excel.

  • Pada tahun 1996, terdapat lebih daripada 30 juta pengguna Microsoft Excel (sumber).
  • Hari ini, terdapat kira-kira 750 juta pengguna Microsoft Excel. Itu sedikit lebih banyak daripada penduduk Eropah dan 25x lebih banyak pengguna daripada pada tahun 1996.

Kami satu keluarga bahagia yang besar!

Dalam tutorial ini, anda akan belajar mengenai VBA dan cara menulis kod dalam spreadsheet Excel menggunakan Visual Basic.

Prasyarat

Anda tidak memerlukan pengalaman pengaturcaraan sebelumnya untuk memahami tutorial ini. Walau bagaimanapun, anda memerlukan:

  • Pengenalan asas hingga menengah dengan Microsoft Excel
  • Sekiranya anda ingin mengikuti contoh VBA dalam artikel ini, anda memerlukan akses ke Microsoft Excel, lebih baik versi terbaru (2019) tetapi Excel 2016 dan Excel 2013 akan berfungsi dengan baik.
  • Kesediaan untuk mencuba perkara baru

Objektif Pembelajaran

Sepanjang artikel ini, anda akan belajar:

  1. Apa itu VBA
  2. Mengapa anda menggunakan VBA
  3. Cara mendapatkan persediaan di Excel untuk menulis VBA
  4. Bagaimana menyelesaikan beberapa masalah dunia nyata dengan VBA

Konsep Penting

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

Objek : Excel berorientasikan objek, yang bermaksud semuanya adalah objek - tetingkap Excel, buku kerja, helaian, carta, sel. VBA membolehkan pengguna memanipulasi dan melakukan tindakan dengan objek di Excel.

Sekiranya anda tidak mempunyai pengalaman dengan pengaturcaraan berorientasikan objek dan ini adalah konsep baru, luangkan masa untuk membiarkannya masuk!

Prosedur : prosedur adalah sebilangan besar kod VBA, yang ditulis dalam Visual Basic Editor, yang menyelesaikan tugas. Kadang-kadang, ini juga disebut sebagai makro (lebih lanjut mengenai makro di bawah). Terdapat dua jenis prosedur:

  • Subrutin: sekumpulan pernyataan VBA yang melakukan satu atau lebih tindakan
  • Fungsi: sekumpulan pernyataan VBA yang melakukan satu atau lebih tindakan dan mengembalikan satu atau lebih nilai

Catatan: anda boleh mempunyai fungsi yang beroperasi di dalam subrutin. Anda akan berjumpa kemudian.

Makro : Sekiranya anda meluangkan masa untuk mempelajari fungsi Excel yang lebih maju, anda mungkin menemui konsep "makro". Pengguna Excel dapat merakam makro, yang terdiri daripada perintah pengguna / penekanan kekunci / klik, dan memainkannya kembali dengan sepantas kilat untuk menyelesaikan tugas berulang. Makro yang dirakam menghasilkan kod VBA, yang kemudian anda boleh periksa. Sebenarnya sangat menyenangkan untuk merakam makro sederhana dan kemudian melihat kod VBA.

Perlu diingat bahawa kadangkala lebih mudah dan cepat untuk merakam makro daripada menggunakan prosedur VBA dengan kod tangan.

Contohnya, mungkin anda bekerja dalam pengurusan projek. Seminggu sekali, anda mesti mengubah laporan yang dieksport mentah dari sistem pengurusan projek anda menjadi laporan bersih yang diformat indah untuk kepimpinan. Anda perlu memformat nama projek yang terlalu anggaran dengan teks merah tebal. Anda boleh mencatat perubahan pemformatan sebagai makro dan menjalankannya setiap kali anda perlu membuat perubahan.

Apa itu VBA?

Visual Basic for Applications adalah bahasa pengaturcaraan yang dikembangkan oleh Microsoft. Setiap program perisian dalam suite Microsoft Office digabungkan dengan bahasa VBA tanpa biaya tambahan. VBA membolehkan pengguna Microsoft Office membuat program kecil yang beroperasi dalam program perisian Microsoft Office.

Fikirkan VBA seperti ketuhar pizza di restoran. Excel adalah restoran. Dapur dilengkapi dengan peralatan komersial standard, seperti peti sejuk besar, kompor, dan oven ole 'biasa - semuanya adalah ciri standard Excel.

Tetapi bagaimana jika anda ingin membuat pizza bakar kayu? Tidak boleh melakukannya dalam oven pembakar komersil standard. VBA adalah ketuhar pizza.

Yum.

Mengapa menggunakan VBA dalam Excel?

Kerana pizza bakar kayu adalah yang terbaik!

Tetapi serius.

Sebilangan besar orang menghabiskan banyak masa di Excel sebagai sebahagian daripada pekerjaan mereka. Masa di Excel juga berbeza. Bergantung pada keadaan, 10 minit di Excel dapat terasa seperti selama-lamanya jika anda tidak dapat melakukan apa yang anda perlukan, atau 10 jam dapat berlalu dengan cepat jika semuanya berjalan lancar. Di mana anda harus bertanya kepada diri sendiri, mengapa saya menghabiskan 10 jam di Excel?

Kadang-kadang, hari-hari itu tidak dapat dielakkan. Tetapi jika anda menghabiskan 8-10 jam setiap hari di Excel melakukan tugas berulang, mengulangi banyak proses yang sama, cuba membersihkan setelah pengguna fail lain, atau bahkan mengemas kini fail lain setelah perubahan dibuat pada fail Excel, prosedur VBA mungkin merupakan penyelesaian untuk anda.

Anda harus mempertimbangkan untuk menggunakan VBA jika anda perlu:

  • Automatikkan tugas berulang
  • Buat cara mudah bagi pengguna untuk berinteraksi dengan hamparan anda
  • Memanipulasi sejumlah besar data

Mendapatkan Persediaan untuk Menulis VBA di Excel

Tab Pembangun

Untuk menulis VBA, anda perlu menambahkan tab Pembangun ke pita, jadi anda akan melihat pita seperti ini.

Untuk menambahkan tab Pembangun ke pita:

  1. Pada tab Fail, pergi ke Pilihan> Sesuaikan Pita.
  2. Di bawah Sesuaikan Pita dan di bawah Tab Utama, pilih kotak semak Pembangun.

Selepas anda menunjukkan tab, tab Pembangun akan tetap kelihatan, kecuali anda mengosongkan kotak centang atau perlu memasang semula Excel. Untuk maklumat lebih lanjut, lihat dokumentasi bantuan Microsoft.

Penyunting VBA

Navigasi ke Tab Pembangun, dan klik butang Visual Basic. Tetingkap baru akan muncul - ini adalah Visual Basic Editor. Untuk tujuan tutorial ini, anda hanya perlu mengetahui panel Project Explorer dan properti Properties.

Contoh VBA Excel

Pertama, mari buat fail untuk kita main-main.

  1. Buka fail Excel baru
  2. Simpan sebagai buku kerja yang dibolehkan makro (. Xlsm)
  3. Pilih tab Pembangun
  4. Buka Editor VBA

Mari kita bergambar dengan beberapa contoh mudah untuk membuat anda menulis kod dalam hamparan menggunakan Visual Basic.

Contoh # 1: Memaparkan Mesej ketika Pengguna Membuka Buku Kerja Excel

Dalam Editor VBA, pilih Masukkan -> Modul Baru

Tuliskan kod ini di tetingkap Modul (jangan tampal!):

Sub Auto_Buka ()

MsgBox ("Selamat Datang ke Buku Kerja XYZ.")

Sub Akhir

Simpan, tutup buku kerja, dan buka semula buku kerja. Dialog ini mesti dipaparkan.

Ta da!

Bagaimana ia melakukannya?

Bergantung pada keakraban anda dengan pengaturcaraan, anda mungkin mempunyai beberapa tekaan. Ia tidak terlalu rumit, tetapi banyak yang berlaku:

  • Sub (kependekan dari "Subroutine): ingat dari awal," sekumpulan pernyataan VBA yang melakukan satu atau lebih tindakan. "
  • Auto_Open: ini adalah subrutin tertentu. Secara automatik menjalankan kod anda apabila fail Excel dibuka - ini adalah peristiwa yang mencetuskan prosedur. Auto_Open hanya akan dijalankan apabila buku kerja dibuka secara manual; ia tidak akan berjalan jika buku kerja dibuka melalui kod dari buku kerja lain (Workbook_Open akan melakukannya, ketahui lebih lanjut mengenai perbezaan antara keduanya).
  • Secara lalai, akses subrutin adalah umum. Ini bermaksud modul lain boleh menggunakan subrutin ini. Semua contoh dalam tutorial ini adalah subrutin awam. Sekiranya diperlukan, anda boleh menyatakan subrutin sebagai peribadi. Ini mungkin diperlukan dalam beberapa keadaan. Ketahui lebih lanjut mengenai pengubah akses subrutin.
  • msgBox: ini adalah fungsi - sekumpulan pernyataan VBA yang melakukan satu atau lebih tindakan dan mengembalikan nilai. Nilai yang dikembalikan adalah mesej "Selamat Datang ke Buku Kerja XYZ."

Ringkasnya, ini adalah subrutin sederhana yang mengandungi fungsi.

Bilakah saya boleh menggunakan ini?

Mungkin anda mempunyai fail yang sangat penting yang jarang diakses (katakanlah, satu perempat sekali), tetapi dikemas kini secara automatik setiap hari dengan prosedur VBA yang lain. Apabila diakses, oleh banyak orang di pelbagai jabatan, di seluruh syarikat.

  • Masalah: Selalunya ketika pengguna mengakses fail, mereka keliru tentang tujuan fail ini (mengapa ia wujud), bagaimana ia sering dikemas kini, siapa yang menyimpannya dan bagaimana mereka harus berinteraksi dengannya. Pekerja baru selalu mempunyai banyak soalan, dan anda harus mengemukakan soalan ini berulang-ulang kali.
  • Penyelesaian: buat mesej pengguna yang mengandungi jawapan ringkas untuk setiap soalan yang sering dijawab ini.

Contoh Dunia Sebenar

  • Gunakan fungsi MsgBox untuk memaparkan mesej apabila ada peristiwa: pengguna menutup buku kerja Excel, cetakan pengguna, lembaran baru ditambahkan ke buku kerja, dll.
  • Gunakan fungsi MsgBox untuk memaparkan mesej apabila pengguna perlu memenuhi syarat sebelum menutup buku kerja Excel
  • Gunakan fungsi InputBox untuk mendapatkan maklumat daripada pengguna

Contoh # 2: Benarkan Pengguna Melaksanakan Prosedur lain

Dalam Editor VBA, pilih Masukkan -> Modul Baru

Tuliskan kod ini di tetingkap Modul (jangan tampal!):

Sub UserReportQuery ()

Dimasukkan Masukan Pengguna Selama

Jawapan Dim sebagai Integer

UserInput = vbYaNo

Jawapan = MsgBox ("Memproses Laporan XYZ?", UserInput)

Sekiranya Jawapan = vbYa Kemudian ProcessReport

Sub Akhir

Sub ProsesLaporan ()

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

Sub Akhir

Simpan dan navigasi kembali ke tab Pembangun Excel dan pilih pilihan "Butang". Klik pada sel dan tetapkan makro UserReportQuery ke butang.

Sekarang klik butang. Mesej ini harus dipaparkan:

Klik "ya" atau tekan Enter.

Sekali lagi, tada!

Harap maklum bahawa subrutin sekunder, ProcessReport, boleh jadi apa sahaja . Saya akan menunjukkan lebih banyak kemungkinan dalam contoh # 3. Tetapi pertama ...

Bagaimana ia melakukannya?

Contoh ini dibina berdasarkan contoh sebelumnya dan mempunyai beberapa elemen baru. Mari kita lihat perkara baru:

  • Dim UserInput Selama: Dim adalah kependekan dari "dimensi" dan membolehkan anda menyatakan nama pemboleh ubah. Dalam kes ini, UserInput adalah nama pemboleh ubah dan Long adalah jenis data. Dalam bahasa Inggeris biasa, baris ini bermaksud "Inilah pemboleh ubah yang disebut" UserInput ", dan ini adalah jenis pemboleh ubah Panjang."
  • Dim Answer As Integer: menyatakan pemboleh ubah lain yang disebut "Jawab," dengan jenis data Integer. Ketahui lebih lanjut mengenai jenis data di sini.
  • UserInput = vbYesNo: memberikan nilai kepada pemboleh ubah. Dalam kes ini, vbYesNo, yang memaparkan butang Ya dan Tidak. Terdapat banyak jenis butang, ketahui lebih lanjut di sini.
  • Jawapan = MsgBox ("Memproses Laporan XYZ?", UserInput): memberikan nilai pemboleh ubah Jawapan menjadi fungsi MsgBox dan pemboleh ubah UserInput. Ya, pemboleh ubah dalam pemboleh ubah.
  • Jika Jawapan = vbYa Kemudian ProcessReport: ini adalah "Pernyataan Jika," pernyataan bersyarat, yang membolehkan kita mengatakan jika x benar, maka lakukan y. Dalam kes ini, jika pengguna telah memilih "Ya," maka jalankan subrutin ProcessReport.

Bilakah saya boleh menggunakan ini?

Ini boleh digunakan dalam banyak cara. Nilai dan fleksibiliti fungsi ini lebih ditentukan oleh apa yang dilakukan oleh subrutin sekunder.

Sebagai contoh, mungkin anda mempunyai fail yang digunakan untuk menghasilkan 3 laporan mingguan yang berbeza. Laporan ini diformat dengan cara yang berbeza secara dramatik.

  • Masalah: Setiap kali salah satu laporan ini dibuat, pengguna membuka fail dan mengubah format dan carta; sebagainya dan sebagainya. Fail ini diedit secara meluas sekurang-kurangnya 3 kali seminggu, dan memerlukan sekurang-kurangnya 30 minit setiap kali diedit.
  • Penyelesaian: buat 1 butang bagi setiap jenis laporan, yang secara automatik memformat semula komponen laporan yang diperlukan dan menghasilkan carta yang diperlukan.

Contoh Dunia Sebenar

  • Buat kotak dialog untuk pengguna mengisi maklumat tertentu secara automatik di beberapa helaian
  • Gunakan fungsi InputBox untuk mendapatkan maklumat dari pengguna, yang kemudian diisi di beberapa helaian

Contoh # 3: Tambahkan Nombor ke Julat dengan Gelung For-Next

Untuk gelung sangat berguna jika anda perlu melakukan tugas berulang pada julat nilai tertentu - tatasusunan atau julat sel. Dalam bahasa Inggeris biasa, gelung bertuliskan "untuk setiap x, lakukan y."

Dalam Editor VBA, pilih Masukkan -> Modul Baru

Tuliskan kod ini di tetingkap Modul (jangan tampal!):

Sub GelungContoh ()

Dim X Sebagai Integer

Untuk X = 1 hingga 100

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

X seterusnya

Sub Akhir

Simpan dan navigasi kembali ke tab Pembangun Excel dan pilih butang Makro. Jalankan makro LoopExample.

Perkara ini mesti berlaku:

Dll, sehingga baris ke-100.

Bagaimana ia melakukannya?

  • Dim X Sebagai Integer: menyatakan pemboleh ubah X sebagai jenis data Integer.
  • Untuk X = 1 hingga 100: ini adalah permulaan gelung For. Ringkasnya, ia memberitahu gelung untuk terus berulang hingga X = 100. X adalah pembilang . Gelung akan terus dijalankan hingga X = 100, jalankan terakhir kali, dan kemudian berhenti.
  • Julat ("A" & X). Nilai = X: ini menyatakan julat gelung dan apa yang harus dimasukkan dalam julat itu. Oleh kerana X = 1 pada mulanya, sel pertama akan menjadi A1, pada ketika itu gelung akan memasukkan X ke dalam sel itu.
  • X seterusnya: ini memberitahu gelung untuk berjalan semula

Bilakah saya boleh menggunakan ini?

Gelung For-Next adalah salah satu fungsi VBA yang paling kuat; terdapat banyak kes penggunaan yang berpotensi. Ini adalah contoh yang lebih kompleks yang memerlukan logik berlapis-lapis, tetapi menyampaikan kemungkinan dunia dalam gelung For-Next.

Mungkin anda mempunyai senarai semua produk yang dijual di kedai roti anda di Lajur A, jenis produk di Lajur B (kek, donat, atau muffin), kos ramuan di Lajur C, dan kos purata pasaran setiap jenis produk di helaian lain.

Anda perlu mengetahui berapa harga runcit setiap produk. Anda fikir ia adalah kos bahan ditambah 20%, tetapi juga 1.2% di bawah purata pasaran jika mungkin. Gelung For-Next akan membolehkan anda melakukan pengiraan jenis ini.

Contoh Dunia Sebenar

  • Gunakan gelung dengan penyataan jika bersarang untuk menambahkan nilai tertentu ke tatasusunan yang berasingan hanya jika memenuhi syarat tertentu
  • Lakukan pengiraan matematik pada setiap nilai dalam julat, mis. Hitung caj tambahan dan tambahkan nilai tersebut
  • Teliti setiap watak dalam rentetan dan ekstrak semua nombor
  • Pilih sebilangan nilai dari array secara rawak

Kesimpulannya

Sekarang kita telah membincangkan mengenai pizza dan muffin dan oh-yeah, bagaimana menulis kod VBA dalam hamparan Excel, mari kita buat pemeriksaan pembelajaran. Lihat sama ada anda dapat menjawab soalan-soalan ini.

  • Apa itu VBA?
  • Bagaimana saya boleh membuat persediaan untuk mula menggunakan VBA di Excel?
  • Mengapa dan kapan anda menggunakan VBA?
  • Apa masalah yang dapat saya selesaikan dengan VBA?

Sekiranya anda mempunyai idea yang baik tentang bagaimana anda dapat menjawab soalan-soalan ini, maka ini berjaya.

Sama ada anda pengguna sesekali atau pengguna kuasa, saya harap tutorial ini memberikan maklumat berguna mengenai apa yang dapat dicapai dengan sedikit kod dalam hamparan Excel anda.

Selamat pengekodan!

Sumber Pembelajaran

  • Pengaturcaraan Excel VBA untuk Dummies, John Walkenbach
  • Mulakan dengan VBA, Dokumentasi Microsoft
  • Belajar VBA dalam Excel, Lynda

Sedikit mengenai saya

Saya Chloe Tucker, seorang artis dan pemaju di Portland, Oregon. Sebagai bekas pendidik, saya terus mencari persimpangan pembelajaran dan pengajaran, atau teknologi dan seni. Hubungi saya di Twitter @_chloetucker dan lihat laman web saya di chloe.dev.