Apa itu Array VBA di Excel dan Bagaimana Memprogram Satu

VBA telah menjadi bagian dari suite Microsoft Office selama bertahun-tahun. Meskipun tidak memiliki fungsionalitas penuh dan kekuatan aplikasi VB lengkap, VBA memberi pengguna Office fleksibilitas untuk mengintegrasikan produk Office dan mengotomatiskan pekerjaan yang Anda lakukan di dalamnya.

Salah satu alat paling kuat yang tersedia di VBA adalah kemampuan untuk memuat seluruh rentang data ke dalam satu variabel yang disebut array. Dengan memuat data dengan cara ini, Anda dapat memanipulasi atau melakukan perhitungan pada rentang data tersebut dalam berbagai cara.

Jadi apa itu array VBA? Dalam artikel ini kita akan menjawab pertanyaan itu, dan menunjukkan kepada Anda cara menggunakannya di skrip VBA Anda sendiri.

Apa Itu Array VBA?

Menggunakan array VBA di Excel sangat sederhana, tetapi memahami konsep array bisa menjadi sedikit rumit jika Anda belum pernah menggunakannya.

Pikirkan array seperti kotak dengan bagian di dalamnya. Array satu dimensi adalah kotak dengan satu baris bagian. Array dua dimensi adalah kotak dengan dua baris bagian.

Anda dapat memasukkan data ke setiap bagian dari “kotak” ini dalam urutan apa pun yang Anda suka.

Di awal skrip VBA Anda, Anda perlu mendefinisikan “kotak” ini dengan mendefinisikan array VBA Anda. Jadi untuk membuat array yang dapat menampung satu set data (array satu dimensi), Anda akan menulis baris berikut.

Redupkan arrMyArray(1 Sampai 6) Sebagai String

Nanti di program Anda, Anda dapat menempatkan data ke bagian mana pun dari larik ini dengan merujuk bagian di dalam tanda kurung.

arrMyArray(1) = “Ryan Dube”

Anda dapat membuat array dua dimensi menggunakan baris berikut.

Redupkan arrMyArray(1 Hingga 6,1 hingga 2) Sebagai Integer

Angka pertama mewakili baris, dan yang kedua adalah kolom. Jadi array di atas dapat menampung rentang dengan 6 baris dan 2 kolom.

Anda dapat memuat unsur apa pun dari array ini dengan data sebagai berikut.

arrMyArray(1,2) = 3

Ini akan memuat 3 ke dalam sel B1.

Array dapat menampung semua jenis data sebagai variabel reguler, seperti string, boolean, integer, float, dan lainnya.

Angka di dalam kurung juga bisa berupa variabel. Pemrogram biasanya menggunakan For Loop untuk menghitung semua bagian larik dan memuat sel data dalam spreadsheet ke dalam larik. Anda akan melihat bagaimana melakukannya nanti di artikel ini.

Cara Memprogram Array VBA di Excel

Mari kita lihat program sederhana di mana Anda mungkin ingin memuat informasi dari spreadsheet ke dalam array multidimensi.

Sebagai contoh, mari kita lihat spreadsheet penjualan produk, di mana Anda ingin menarik nama perwakilan penjualan, item, dan total penjualan dari spreadsheet.

Perhatikan bahwa di VBA ketika Anda mereferensikan baris atau kolom, Anda menghitung baris dan kolom mulai dari kiri atas pada 1. Jadi kolom rep adalah 3, kolom item adalah 4, dan total kolom adalah 7.

Untuk memuat tiga kolom ini pada 11 baris, Anda harus menulis skrip berikut.

Dim arrMyArray(1 Hingga 11, 1 Hingga 3) Sebagai String Dim i Sebagai Integer, j Sebagai Integer

For i = 2 To 12 For j = 1 To 3 arrMyArray(i-1, j) = Cells(i, j).Nilai Next j Next i

Anda akan melihat bahwa untuk melewati baris header, nomor baris pada tampilan For pertama harus dimulai dari 2, bukan 1. Ini berarti Anda harus mengurangi 1 untuk nilai baris array saat Anda memuat nilai sel ke dalam array menggunakan Cells(i, j).Value.

Tempat Memasukkan Skrip Array VBA Anda

Untuk menempatkan program skrip VBA di Excel, Anda perlu menggunakan editor VBA. Anda dapat mengakses ini dengan memilih menu Pengembang dan memilih Lihat Kode di bagian Kontrol pada pita.

Jika Anda tidak melihat Pengembang di menu, Anda harus menambahkannya. Untuk melakukannya, pilih File dan Opsi untuk membuka jendela Opsi Excel.

Ubah perintah pilih dari dropdown ke All Commands. Pilih Pengembang dari menu kiri dan pilih tombol Tambah untuk memindahkannya ke panel di sebelah kanan. Pilih kotak centang untuk mengaktifkannya dan pilih OK untuk menyelesaikan.

Saat jendela Editor Kode terbuka, pastikan lembar tempat data Anda dipilih di panel kiri. Pilih Lembar Kerja di dropdown kiri dan Aktifkan di kanan. Ini akan membuat subrutin baru bernama Worksheet_Activate().

Fungsi ini akan berjalan setiap kali file spreadsheet dibuka. Tempelkan kode ke panel skrip di dalam subrutin ini.

Script ini akan bekerja melalui 12 baris dan akan memuat nama perwakilan dari kolom 3, item dari kolom 4, dan total penjualan dari kolom 7.

Setelah kedua loop For selesai, array dua dimensi arrMyArray berisi semua data yang Anda tentukan dari lembar asli.

Memanipulasi Array di Excel VBA

Katakanlah Anda ingin menerapkan pajak penjualan 5% untuk semua harga jual akhir, dan kemudian menulis semua data ke lembar baru.

Anda dapat melakukan ini dengan menambahkan loop For lainnya setelah yang pertama, dengan perintah untuk menulis hasilnya ke lembar baru.

Untuk k = 2 Sampai 12 Sheets(“Sheet2”).Sel(k, 1).Nilai = arrMyArray(k – 1, 1) Sheets(“Sheet2”).Sel(k, 2).Nilai = arrMyArray(k – 1, 2) Sheets(“Sheet2”).Sel(k, 3).Nilai = arrMyArray(k – 1, 3) Sheets(“Sheet2”).Sel(k, 4).Nilai = arrMyArray(k – 1, 3) * 0,05 Selanjutnya k

Ini akan “membongkar” seluruh array ke Sheet2, dengan baris tambahan yang berisi total dikalikan dengan 5% untuk jumlah pajak.

Lembar yang dihasilkan akan terlihat seperti ini.

Seperti yang Anda lihat, array VBA di Excel sangat berguna dan serbaguna seperti trik Excel lainnya.

Contoh di atas adalah penggunaan array yang sangat sederhana. Anda dapat membuat array yang jauh lebih besar dan melakukan pengurutan, rata-rata, atau banyak fungsi lainnya terhadap data yang Anda simpan di dalamnya.

Jika Anda ingin benar-benar kreatif, Anda bahkan dapat membuat dua larik yang berisi rentang sel dari dua lembar berbeda, dan melakukan penghitungan di antara unsur setiap larik.

Aplikasi hanya dibatasi oleh imajinasi Anda.

Related Posts