4 Kesalahan yang Dapat Anda Hindari saat Memprogram Excel Macro dengan VBA

Microsoft Excel adalah alat analisis data yang sangat mampu, tetapi dengan kemampuan untuk mengotomatisasi tugas berulang dengan makro dengan menulis kode sederhana dalam (VBA), itu jauh lebih kuat. Namun, digunakan secara tidak benar, VBA dapat menyebabkan banyak masalah.

Bahkan jika Anda bukan seorang programmer, VBA menawarkan fungsi-fungsi sederhana yang memungkinkan Anda untuk menambahkan beberapa fungsi yang sangat mengesankan ke spreadsheet Anda, jadi jangan pergi dulu!

Apakah Anda seorang guru VBA,  yang membuat dasbor di Excel , atau pemula, yang hanya tahu cara menulis skrip sederhana yang melakukan penghitungan sel dasar, Anda dapat mengikuti teknik pemrograman yang mudah, yang akan membantu Anda meningkatkan peluang penulisan bersih dan kode bebas bug.

Memulai dengan VBA

Jika Anda belum pernah diprogram dalam VBA di Excel sebelumnya, mengaktifkan alat Pengembang untuk melakukannya sebenarnya cukup mudah. Buka saja File  >  Optionsdan kemudian Customize Ribbon . Pindahkan saja grup perintah Pengembang dari panel kiri ke kanan.

Pastikan kotak centang diaktifkan, dan sekarang tab Pengembang akan muncul di menu Excel Anda.

Cara termudah untuk masuk ke jendela editor kode pada titik ini adalah hanya dengan mengklik tombol Lihat Kode di bawah Kontrol di menu Pengembang.

1. Nama-nama Variabel yang Mengerikan

Sekarang Anda berada di jendela kode, saatnya untuk mulai menulis kode VBA . Langkah penting pertama dalam sebagian besar program, apakah itu dalam VBA atau bahasa lainnya, adalah mendefinisikan variabel Anda.

Sepanjang beberapa dekade saya menulis kode, saya menemukan banyak aliran pemikiran ketika datang ke konvensi penamaan variabel dan belajar beberapa hal dengan cara yang sulit. Berikut adalah kiat cepat untuk membuat nama variabel:

• Buat sesingkat mungkin.


• Buatlah seeksriptif mungkin.


• Pendahuluan mereka dengan tipe variabel (boolean, integer, dll …).


• Ingat untuk menggunakan ruang lingkup yang tepat (lihat di bawah).

Berikut contoh tangkapan layar dari program yang sering saya gunakan untuk membuat panggilan WMIC Windows dari Excel untuk mengumpulkan informasi PC .

Ketika Anda ingin menggunakan variabel di dalam setiap fungsi di dalam modul atau objek (saya akan menjelaskan ini di bawah), maka Anda perlu mendeklarasikannya sebagai variabel “publik” dengan menampilkan deklarasi dengan Publik . Jika tidak, variabel dideklarasikan oleh prefacing mereka dengan kata Dim .

Seperti yang Anda lihat, jika variabelnya adalah bilangan bulat, itu diawali dengan int . Jika itu sebuah string, maka str . Ini membantu nantinya saat Anda memprogram karena Anda akan selalu tahu jenis data apa yang disimpan variabel, hanya dengan melihat nama. Anda juga akan melihat bahwa jika ada sesuatu seperti string yang memegang nama komputer, maka variabel tersebut disebut strComputerName .

Hindari membuat nama variabel yang sangat membingungkan atau membingungkan yang hanya Anda pahami. Buat lebih mudah bagi programmer lain untuk datang di belakang Anda dan memahami apa artinya semua itu!

Kesalahan lain yang dilakukan orang adalah meninggalkan nama sheet sebagai default “Sheet1”, “Sheet2”, dll … Ini menambah kebingungan lebih lanjut untuk sebuah program. Sebaliknya, beri nama seprai sehingga mereka masuk akal.

Dengan cara ini, ketika Anda merujuk ke nama sheet dalam kode VBA Excel Anda, Anda mengacu pada nama yang masuk akal. Dalam contoh di atas, saya memiliki sheet tempat saya menarik informasi Jaringan, jadi saya sebut lembar “Jaringan”. Sekarang dalam kode, kapan saja saya ingin mereferensikan lembar Jaringan, saya dapat melakukannya dengan cepat tanpa mencari nomor lembar itu.

2. Melanggar Bukan Looping

Salah satu masalah paling umum yang dialami programmer baru ketika mereka mulai menulis kode adalah menangani loop dengan benar. Dan karena begitu banyak orang yang menggunakan Excel VBA sangat banyak pemula dalam hal kode, pengulangan yang buruk adalah epidemi.

Looping sangat umum di Excel karena sering kali Anda memproses nilai data di seluruh baris atau kolom, jadi Anda perlu mengulang untuk memproses semuanya. Programer baru sering ingin hanya keluar dari loop (baik untuk For loop atau a While look) langsung ketika kondisi tertentu benar.

Anda dapat mengabaikan kompleksitas kode di atas, cukup perhatikan bahwa di dalam pernyataan IF bagian dalam, ada opsi untuk keluar dari For loop jika kondisinya benar. Berikut contoh yang lebih sederhana:

For x = 1 To 20
    If x = 6 Then Exit For
    y = x + intRoomTemp
Next i

Programer baru menggunakan pendekatan ini karena mudah. Ketika suatu kondisi terjadi yang Anda tunggu untuk berhenti dari satu lingkaran, godaan untuk melompat keluar begitu saja adalah kuat, tetapi jangan lakukan.

Lebih sering daripada tidak, kode yang datang setelah “istirahat” itu penting untuk diproses, bahkan terakhir kali melalui loop sebelum keluar. Cara yang lebih bersih dan lebih profesional untuk menangani kondisi di mana Anda ingin meninggalkan lingkaran setengah jalan, hanya untuk memasukkan kondisi keluar itu dalam sesuatu seperti pernyataan Sementara.

While (x>=1 AND x<=20 AND x<>6)
    For x = 1 To 20
        y = x + intRoomTemp
    Next i
Wend

Hal ini memungkinkan untuk aliran logis dari kode Anda, dengan run terakhir melalui ketika x adalah 5, dan kemudian dengan lancar keluar setelah loop For menghitung hingga 6. Tidak perlu menyertakan perintah canggung EXIT atau BREAK mid-loop.

3. Tidak Menggunakan Array

Kesalahan lain yang menarik yang dibuat oleh programmer VBA baru adalah mencoba memproses semuanya di dalam banyak loop bersarang yang menyaring melalui baris dan kolom selama proses perhitungan.

Meskipun ini dapat bekerja, itu juga dapat menyebabkan masalah kinerja utama, jika Anda terus-menerus harus melakukan perhitungan yang sama pada angka yang sama di kolom yang sama. Mengulang-ulang kolom itu dan mengekstraksi nilai setiap saat tidak hanya membosankan untuk program, tetapi juga pembunuh pada prosesor Anda. Cara yang lebih efisien untuk menangani daftar panjang angka adalah dengan memanfaatkan array.

Jika Anda belum pernah menggunakan array sebelumnya, jangan takut. Bayangkan sebuah array sebagai nampan es batu dengan sejumlah “kubus” tertentu yang dapat Anda masukkan ke dalamnya. Kubus diberi nomor 1 hingga 12, dan begitulah cara Anda “memasukkan” data ke dalamnya.

Anda dapat dengan mudah mendefinisikan suatu array hanya dengan mengetik Dim arrMyArray (12) sebagai Integer .

Ini menciptakan “baki” dengan 12 slot yang tersedia untuk Anda isi.

Inilah baris kode perulangan tanpa larik mungkin terlihat seperti:

Sub Test1()
      Dim x As Integer
      intNumRows = Range("A2", Range("A2").End(xldown)).Rows.Count
      Range("A2").Select
      For x = 1 To intNumRows
         If Range("A" & str(x)).value < 100 then
            intTemp = (Range("A" & str(x)).value) * 32 - 100
         End If
         ActiveCell.Offset(1, 0).Select
      Next
End Sub

Dalam contoh ini, kode memproses melalui setiap sel dalam rentang dan melakukan perhitungan suhu.

Kemudian dalam program, jika Anda ingin melakukan beberapa perhitungan lain pada nilai-nilai yang sama ini, Anda harus menduplikasi kode ini, memproses turun melalui semua sel-sel ini, dan melakukan perhitungan baru Anda.

Sekarang, jika Anda memilih, gunakan array, maka Anda dapat menyimpan 12 nilai pada baris ke dalam susunan penyimpanan Anda yang nyaman. Kemudian, kapan pun Anda ingin menjalankan perhitungan terhadap angka-angka itu, mereka sudah ada di memori dan siap digunakan.

Sub Test1()
      Dim x As Integer
      intNumRows = Range("A2", Range("A2").End(xldown)).Rows.Count
      Range("A2").Select
      For x = 1 To intNumRows
         arrMyArray(x-1) = Range("A" & str(x)).value)        
         ActiveCell.Offset(1, 0).Select
      Next
   End Sub

The “x-1” untuk menunjuk ke elemen array hanya diperlukan karena loop For mulai pada 1. Elemen array perlu mulai dari 0.

Tetapi setelah Anda memiliki semua larik yang terisi dengan nilai dari baris, selanjutnya dalam program itu Anda dapat mencabut semua perhitungan yang Anda inginkan dengan menggunakan larik.

Sub TempCalc()
      For x = 0 To UBound(arrMyArray)
         arrMyTemps(y) = arrMyArray(x) * 32 - 100   
      Next
End Sub

Contoh ini melewati seluruh baris array (UBound memberi Anda jumlah nilai data dalam array), melakukan perhitungan suhu, dan kemudian memasukkannya ke array lain yang disebut arrMyTemps.

Anda dapat melihat betapa lebih sederhana kode perhitungan kedua. Dan sejak saat ini, kapan pun Anda ingin melakukan lebih banyak perhitungan pada rangkaian angka yang sama, larik Anda sudah dimuat sebelumnya dan siap dijalankan.

4. Menggunakan Terlalu Banyak Referensi

Apakah Anda memprogram dalam Visual Basic lengkap, atau VBA, Anda harus menyertakan “referensi” untuk mengakses fitur-fitur tertentu, seperti mengakses database Access, atau menulis output ke file teks.

Referensi adalah semacam “pustaka” yang diisi dengan fungsi yang dapat Anda manfaatkan, jika Anda mengaktifkan file itu. Anda dapat menemukan Referensi di tampilan Pengembang dengan mengklik Alat di menu dan kemudian mengklik Referensi .

Apa yang akan Anda temukan di jendela ini adalah semua referensi yang saat ini dipilih untuk proyek VBA Anda saat ini.

Menurut pengalaman saya, pilihan di sini dapat berubah dari satu instalasi Excel ke yang lain, dan dari satu PC ke PC lainnya. Banyak yang tergantung pada apa yang telah dilakukan orang lain dengan Excel pada PC ini, apakah add-on atau fitur tertentu ditambahkan atau diaktifkan, atau apakah beberapa programmer lain mungkin telah menggunakan referensi tertentu dalam proyek-proyek sebelumnya.

Alasannya baik untuk memeriksa daftar ini karena referensi yang tidak perlu membuang sumber daya sistem. Jika Anda tidak menggunakan manipulasi file XML, lalu mengapa tetap memilih Microsoft XML? jika Anda tidak berkomunikasi dengan database, kemudian hapus Microsoft DAO. Jika Anda tidak menulis output ke file teks, kemudian hapus Microsoft Scripting Runtime .

Jika Anda tidak yakin apa referensi yang dipilih ini, tekan F2 dan Anda akan melihat Object Explorer. Di bagian atas jendela ini, Anda dapat memilih pustaka referensi untuk dijelajahi.

Setelah dipilih, Anda akan melihat semua objek dan fungsi yang tersedia, yang dapat Anda klik untuk mempelajari lebih lanjut.

Sebagai contoh, ketika saya mengklik perpustakaan DAO, dengan cepat menjadi jelas bahwa ini semua tentang menghubungkan dan berkomunikasi dengan database.

Mengurangi jumlah referensi yang Anda gunakan dalam proyek pemrograman Anda hanyalah akal sehat, dan akan membantu membuat keseluruhan aplikasi Anda berjalan lebih efisien.

Pemrograman di Excel VBA

Seluruh ide untuk benar-benar menulis kode di Excel membuat takut banyak orang, tetapi ketakutan ini sebenarnya tidak perlu. Visual Basic for Applications adalah bahasa yang sangat sederhana untuk dipelajari, dan jika Anda mengikuti praktik umum dasar yang disebutkan di atas, Anda akan memastikan bahwa kode Anda bersih, efisien, dan mudah dimengerti.

Apakah Anda membuat kode di VBA? Pelajaran apa yang telah Anda pelajari selama bertahun-tahun yang dapat Anda bagikan dengan pembaca lain yang mempelajari VBA untuk pertama kalinya? Bagikan tips Anda di bagian komentar di bawah ini!

Sumber https://indoint.com/