Excel Macros dapat menghemat banyak waktu dengan mengotomatiskan proses Excel yang sering Anda gunakan. Tetapi makro sebenarnya sangat terbatas. Mudah untuk membuat kesalahan dengan alat rekaman, dan proses perekamannya canggung.
Menggunakan VBA untuk membuat makro memberi Anda lebih banyak kekuatan. Anda bisa memberi tahu Excel apa yang harus dilakukan dan bagaimana melakukannya. Anda juga mendapatkan akses ke lebih banyak fungsi dan kemampuan. Jika Anda menggunakan Excel secara teratur, ada baiknya belajar cara membuat makro VBA.
Kami akan mulai dengan dasar-dasarnya.
Apa itu VBA
Menggunakan VBA untuk membuat makro memberi Anda lebih banyak kekuatan. Anda bisa memberi tahu Excel apa yang harus dilakukan dan bagaimana melakukannya. Anda juga mendapatkan akses ke lebih banyak fungsi dan kemampuan. Jika Anda menggunakan Excel secara teratur, ada baiknya belajar cara membuat makro VBA.
Kami akan mulai dengan dasar-dasarnya.
Apa itu VBA
VBA adalah Visual Basic for Applications, bahasa pemrograman yang dapat Anda gunakan di banyak aplikasi Microsoft. Visual Basic adalah bahasa pemrograman, dan VBA adalah versi khusus aplikasi untuk itu. (Microsoft menghentikan Visual Basic kembali pada 2008, tetapi VBA masih kuat).
Untungnya untuk yang bukan programmer, VBA sangat sederhana, dan antarmuka yang Anda gunakan untuk mengeditnya menawarkan banyak bantuan. Banyak perintah yang akan Anda gunakan saran pop up dan penyelesaian otomatis, membantu skrip Anda bekerja dengan cepat.
Namun, VBA membutuhkan waktu untuk membiasakan diri.
Keuntungan dari VBA Macro di Excel
Untungnya untuk yang bukan programmer, VBA sangat sederhana, dan antarmuka yang Anda gunakan untuk mengeditnya menawarkan banyak bantuan. Banyak perintah yang akan Anda gunakan saran pop up dan penyelesaian otomatis, membantu skrip Anda bekerja dengan cepat.
Namun, VBA membutuhkan waktu untuk membiasakan diri.
Keuntungan dari VBA Macro di Excel
Jika VBA lebih sulit daripada merekam makro, mengapa Anda menggunakannya? Jawaban singkatnya adalah Anda mendapatkan lebih banyak kekuatan dari makro VBA.
Alih-alih mengklik di sekitar spreadsheet Anda dan merekam klik itu, Anda dapat mengakses berbagai fungsi dan kemampuan Excel. Anda hanya perlu tahu cara menggunakannya.
Dan begitu Anda merasa lebih nyaman dengan VBA, Anda dapat melakukan semua hal yang mungkin Anda lakukan di makro biasa dalam waktu yang jauh lebih singkat. Hasilnya juga akan lebih mudah diprediksi, karena Anda memberi tahu Excel apa yang harus dilakukan. Tidak ada ambiguitas sama sekali.
Setelah Anda membuat makro VBA Anda, mudah untuk menyimpan dan membagikannya sehingga orang lain dapat memanfaatkannya. Ini sangat berguna ketika Anda bekerja dengan banyak orang yang perlu melakukan hal yang sama di Excel.
Mari kita lihat makro VBA sederhana untuk melihat cara kerjanya.
Alih-alih mengklik di sekitar spreadsheet Anda dan merekam klik itu, Anda dapat mengakses berbagai fungsi dan kemampuan Excel. Anda hanya perlu tahu cara menggunakannya.
Dan begitu Anda merasa lebih nyaman dengan VBA, Anda dapat melakukan semua hal yang mungkin Anda lakukan di makro biasa dalam waktu yang jauh lebih singkat. Hasilnya juga akan lebih mudah diprediksi, karena Anda memberi tahu Excel apa yang harus dilakukan. Tidak ada ambiguitas sama sekali.
Setelah Anda membuat makro VBA Anda, mudah untuk menyimpan dan membagikannya sehingga orang lain dapat memanfaatkannya. Ini sangat berguna ketika Anda bekerja dengan banyak orang yang perlu melakukan hal yang sama di Excel.
Mari kita lihat makro VBA sederhana untuk melihat cara kerjanya.
Mari kita lihat makro sederhana. Spreadsheet kami berisi nama karyawan, nomor toko tempat karyawan bekerja, dan penjualan triwulanan mereka.
Makro ini akan menambahkan penjualan triwulanan dari setiap toko dan menulis totalnya ke sel di spreadsheet (jika Anda tidak yakin cara mengakses dialog VBA, lihat panduan VBA kami di sini):
Mendeklarasikan Sub
Sub StoreSales()Ini mungkin terlihat panjang dan rumit, tetapi kami akan memecahnya sehingga Anda dapat melihat elemen individu dan belajar sedikit tentang dasar-dasar VBA.
Dim Sum1 As Currency
Dim Sum2 As Currency
Dim Sum3 As Currency
Dim Sum4 As Currency
For Each Cell In Range("C2:C51")
Cell.Activate
If IsEmpty(Cell) Then Exit For
If ActiveCell.Offset(0, -1) = 1 Then
Sum1 = Sum1 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 2 Then
Sum2 = Sum2 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 3 Then
Sum3 = Sum3 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 4 Then
Sum4 = Sum4 + Cell.Value
End If
Next Cell
Range("F2").Value = Sum1
Range("F3").Value = Sum2
Range("F4").Value = Sum3
Range("F5").Value = Sum4
End Sub
Mendeklarasikan Sub
Di awal modul, kita memiliki "Sub StoreSales ()". Ini mendefinisikan sub baru yang disebut StoreSales.
Anda juga dapat mendefinisikan fungsi — perbedaannya adalah fungsi dapat mengembalikan nilai, dan subs tidak bisa (jika Anda terbiasa dengan bahasa pemrograman lain, subs adalah metode yang setara). Dalam hal ini, kami tidak perlu mengembalikan nilai, jadi kami menggunakan sub.
Baca Juga
Di akhir modul, kami memiliki "End Sub," yang memberi tahu Excel bahwa kami sudah selesai dengan makro VBA ini.
Mendeklarasikan Variabel
Anda juga dapat mendefinisikan fungsi — perbedaannya adalah fungsi dapat mengembalikan nilai, dan subs tidak bisa (jika Anda terbiasa dengan bahasa pemrograman lain, subs adalah metode yang setara). Dalam hal ini, kami tidak perlu mengembalikan nilai, jadi kami menggunakan sub.
Baca Juga
Di akhir modul, kami memiliki "End Sub," yang memberi tahu Excel bahwa kami sudah selesai dengan makro VBA ini.
Mendeklarasikan Variabel
Baris kode pertama dalam skrip kami semuanya dimulai dengan “Dim.” Dim adalah perintah VBA untuk mendeklarasikan variabel.
Jadi "Dim Sum1" membuat variabel baru yang disebut "Sum1." Namun, kita perlu memberi tahu Excel apa jenis variabel ini. Kita perlu memilih tipe data. Ada banyak tipe data berbeda di VBA — Anda dapat menemukan daftar lengkap di dokumen bantuan Microsoft.
Karena makro VBA kami akan berhadapan dengan mata uang, kami menggunakan tipe data Mata Uang.
Pernyataan “Dim Sum1 As Currency” memberitahu Excel untuk membuat variabel Currency baru yang disebut Sum1. Setiap variabel yang Anda deklarasikan harus memiliki pernyataan "As" untuk memberi tahu Excel tipenya.
Memulai For Loop
Jadi "Dim Sum1" membuat variabel baru yang disebut "Sum1." Namun, kita perlu memberi tahu Excel apa jenis variabel ini. Kita perlu memilih tipe data. Ada banyak tipe data berbeda di VBA — Anda dapat menemukan daftar lengkap di dokumen bantuan Microsoft.
Karena makro VBA kami akan berhadapan dengan mata uang, kami menggunakan tipe data Mata Uang.
Pernyataan “Dim Sum1 As Currency” memberitahu Excel untuk membuat variabel Currency baru yang disebut Sum1. Setiap variabel yang Anda deklarasikan harus memiliki pernyataan "As" untuk memberi tahu Excel tipenya.
Memulai For Loop
Loop adalah beberapa hal paling kuat yang dapat Anda buat dalam bahasa pemrograman apa pun. Jika Anda tidak terbiasa dengan loop, lihat penjelasan tentang loop Do-While ini. Dalam contoh ini, kami menggunakan loop Untuk, yang juga tercakup dalam artikel.
Seperti apa bentuk lingkarannya:
Ini memberitahu Excel untuk beralih melalui sel dalam rentang yang kami tentukan. Kami telah menggunakan objek Rentang, yang merupakan jenis objek tertentu di VBA. Saat kami menggunakannya dengan cara ini — Rentang (“C2: C51”) - ia memberi tahu Excel bahwa kami tertarik pada 50 sel itu.
"Untuk Setiap" memberi tahu Excel bahwa kami akan melakukan sesuatu dengan setiap sel dalam rentang. "Next Cell" datang setelah semua yang ingin kita lakukan, dan memberitahu Excel untuk memulai loop dari awal (dimulai dengan sel berikutnya).
Kami juga memiliki pernyataan ini: "Jika IsEmpty (Sel) Lalu Keluar
Bisakah Anda menebak apa fungsinya?
Catatan: Sebenarnya, menggunakan loop Sementara mungkin merupakan pilihan yang lebih baik. Namun, demi mengajar, saya memutuskan untuk menggunakan loop For dengan Exit.
Pernyataan If-Then-Else
Inti dari makro khusus ini adalah dalam pernyataan If-Then-Else. Berikut urutan pernyataan kondisional kami:
Sebagian besar, Anda mungkin bisa menebak apa yang dilakukan pernyataan ini. Anda mungkin tidak terbiasa dengan ActiveCell.Offset, meskipun. "ActiveCell.Offset (0, -1)" memberitahu Excel untuk melihat sel yang satu kolom di sebelah kiri sel yang aktif.
Dalam kasus kami, itu memberitahu Excel untuk berkonsultasi dengan kolom nomor toko. Jika Excel menemukan 1 di kolom ini, maka ia mengambil konten sel aktif dan menambahkannya ke Sum1. Jika menemukan 2, itu menambah konten sel aktif ke Sum2. Dan seterusnya.
Excel memeriksa semua pernyataan ini secara berurutan. Jika pernyataan bersyarat puas, itu melengkapi pernyataan Lalu. Jika tidak, ia pindah ke ElseIf berikutnya. Jika sampai pada akhir dan tidak ada kondisi yang terpenuhi, itu tidak akan mengambil tindakan apa pun.
Kombinasi loop dan kondisi adalah yang mendorong makro ini. Pengulangan memberitahu Excel untuk melalui setiap sel dalam seleksi, dan kondisional mengatakannya apa yang harus dilakukan dengan sel itu.
Menulis Nilai Sel
Akhirnya, kita bisa menulis hasil perhitungan kita ke sel. Inilah baris yang kami gunakan untuk melakukan itu:
Dengan ".Nilai" dan tanda sama dengan, kami mengatur masing-masing sel tersebut ke nilai salah satu variabel kami.
Dan itu dia! Kami memberi tahu Excel bahwa kami telah selesai menulis Sub ini dengan "End Sub," dan makro VBA selesai.
Saat kami menjalankan makro dengan tombol Makro di tab Pengembang, kami mendapatkan jumlah kami:
Menyusun Blok Bangunan VBA di Excel
Ketika Anda pertama kali melihat makro VBA di atas, itu terlihat cukup kompleks. Tetapi setelah memecahnya menjadi bagian-bagian penyusunnya, logika menjadi jelas. Seperti bahasa skrip lainnya, perlu waktu untuk terbiasa dengan sintaks VBA.
Tetapi dengan latihan, Anda akan membangun kosa kata VBA Anda dan dapat menulis makro lebih cepat, lebih akurat, dan dengan kekuatan yang jauh lebih besar daripada yang dapat Anda rekam.
Seperti apa bentuk lingkarannya:
For Each Cell in Range("C2:C51")
[a bunch of stuff]
Next Cell
Ini memberitahu Excel untuk beralih melalui sel dalam rentang yang kami tentukan. Kami telah menggunakan objek Rentang, yang merupakan jenis objek tertentu di VBA. Saat kami menggunakannya dengan cara ini — Rentang (“C2: C51”) - ia memberi tahu Excel bahwa kami tertarik pada 50 sel itu.
"Untuk Setiap" memberi tahu Excel bahwa kami akan melakukan sesuatu dengan setiap sel dalam rentang. "Next Cell" datang setelah semua yang ingin kita lakukan, dan memberitahu Excel untuk memulai loop dari awal (dimulai dengan sel berikutnya).
Kami juga memiliki pernyataan ini: "Jika IsEmpty (Sel) Lalu Keluar
Bisakah Anda menebak apa fungsinya?
Catatan: Sebenarnya, menggunakan loop Sementara mungkin merupakan pilihan yang lebih baik. Namun, demi mengajar, saya memutuskan untuk menggunakan loop For dengan Exit.
Pernyataan If-Then-Else
Inti dari makro khusus ini adalah dalam pernyataan If-Then-Else. Berikut urutan pernyataan kondisional kami:
If ActiveCell.Offset(0, -1) = 1 Then
Sum1 = Sum1 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 2 Then
Sum2 = Sum2 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 3 Then
Sum3 = Sum3 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 4 Then
Sum4 = Sum4 + Cell.Value
End If
Sebagian besar, Anda mungkin bisa menebak apa yang dilakukan pernyataan ini. Anda mungkin tidak terbiasa dengan ActiveCell.Offset, meskipun. "ActiveCell.Offset (0, -1)" memberitahu Excel untuk melihat sel yang satu kolom di sebelah kiri sel yang aktif.
Dalam kasus kami, itu memberitahu Excel untuk berkonsultasi dengan kolom nomor toko. Jika Excel menemukan 1 di kolom ini, maka ia mengambil konten sel aktif dan menambahkannya ke Sum1. Jika menemukan 2, itu menambah konten sel aktif ke Sum2. Dan seterusnya.
Excel memeriksa semua pernyataan ini secara berurutan. Jika pernyataan bersyarat puas, itu melengkapi pernyataan Lalu. Jika tidak, ia pindah ke ElseIf berikutnya. Jika sampai pada akhir dan tidak ada kondisi yang terpenuhi, itu tidak akan mengambil tindakan apa pun.
Kombinasi loop dan kondisi adalah yang mendorong makro ini. Pengulangan memberitahu Excel untuk melalui setiap sel dalam seleksi, dan kondisional mengatakannya apa yang harus dilakukan dengan sel itu.
Menulis Nilai Sel
Akhirnya, kita bisa menulis hasil perhitungan kita ke sel. Inilah baris yang kami gunakan untuk melakukan itu:
Range("F2").Value = Sum1
Range("F3").Value = Sum2
Range("F4").Value = Sum3
Range("F5").Value = Sum4
Dengan ".Nilai" dan tanda sama dengan, kami mengatur masing-masing sel tersebut ke nilai salah satu variabel kami.
Dan itu dia! Kami memberi tahu Excel bahwa kami telah selesai menulis Sub ini dengan "End Sub," dan makro VBA selesai.
Saat kami menjalankan makro dengan tombol Makro di tab Pengembang, kami mendapatkan jumlah kami:
Menyusun Blok Bangunan VBA di Excel
Ketika Anda pertama kali melihat makro VBA di atas, itu terlihat cukup kompleks. Tetapi setelah memecahnya menjadi bagian-bagian penyusunnya, logika menjadi jelas. Seperti bahasa skrip lainnya, perlu waktu untuk terbiasa dengan sintaks VBA.
Tetapi dengan latihan, Anda akan membangun kosa kata VBA Anda dan dapat menulis makro lebih cepat, lebih akurat, dan dengan kekuatan yang jauh lebih besar daripada yang dapat Anda rekam.