Apa itu array VBA di Excel dan bagaimana memprogramnya

VBA telah menjadi bagian dari Microsoft Office Suite selama bertahun -tahun. Meskipun tidak memiliki fungsionalitas penuh dan kekuatan aplikasi VB penuh, VBA memberikan fleksibilitas kepada pengguna kantor untuk mengintegrasikan produk kantor 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 variabel tunggal yang disebut array. Dengan memuat data dengan cara ini, Anda dapat memanipulasi atau melakukan perhitungan pada rentang data tersebut dengan berbagai cara.

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.

Dim arrmyarray (1 hingga 6) sebagai string

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

arrmYarray (1) = "Ryan Dube"

Anda dapat membuat array dua dimensi menggunakan baris berikut.

Redup 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 elemen 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, bilangan bulat, mengapung dan banyak lagi.

Angka di dalam tanda kurung juga bisa menjadi variabel. Pemrogram biasanya menggunakan loop untuk dihitung melalui semua bagian array dan memuat sel data dalam spreadsheet ke dalam array. Anda akan melihat bagaimana melakukan ini 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, barang, dan total penjualan tenaga penjualan dari spreadsheet.

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

Untuk memuat ketiga kolom ini di semua 11 baris, Anda harus menulis skrip berikut.

Dim arrmyarray (1 hingga 11, 1 hingga 3) sebagai string
Redup saya sebagai bilangan bulat, j sebagai bilangan bulat

Untuk i = 2 hingga 12
Untuk j = 1 hingga 3
ArrmyArray (I-1, J) = sel (i, j).Nilai
Berikutnya j
Selanjutnya saya

Anda akan melihat bahwa untuk melewatkan baris header, nomor baris di yang pertama untuk tampilan harus dimulai dari 2 daripada 1. Ini berarti Anda perlu mengurangi 1 untuk nilai baris array saat Anda memuat nilai sel ke dalam array menggunakan sel (i, j).Nilai.

Tempat untuk memasukkan skrip array VBA Anda

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

Jika Anda tidak melihat pengembang di menu, Anda harus menambahkannya. Untuk melakukan ini, pilih Mengajukan Dan Pilihan Untuk membuka jendela Opsi Excel.

Ubah perintah pilih dari dropdown ke Semua perintah. Pilih Pengembang dari menu kiri dan pilih Menambahkan tombol untuk memindahkannya ke panel di sebelah kanan. Pilih kotak centang untuk mengaktifkannya dan pilih OKE menyelesaikan.

Saat jendela editor kode terbuka, pastikan lembar data Anda dipilih di panel kiri. Pilih Lembar Kerja di dropdown kiri dan Mengaktifkan di kanan. Ini akan membuat subrutin baru yang disebut worksheet_activate ().

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

Skrip 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 keduanya untuk loop selesai, array array dua dimensi 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 tulis semua data ke lembar baru.

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

Untuk k = 2 hingga 12
Lembar ("sheet2").Sel (k, 1).Nilai = arrmYarray (k - 1, 1)
Lembar ("sheet2").Sel (k, 2).Nilai = arrmYarray (k - 1, 2)
Lembar ("sheet2").Sel (k, 3).Nilai = arrmYarray (k - 1, 3)
Lembar ("sheet2").Sel (k, 4).Nilai = arrmYarray (k - 1, 3) * 0.05
Berikutnya k

Ini akan "membongkar" seluruh array ke dalam sheet2, dengan baris tambahan yang berisi total dikalikan 5% untuk jumlah pajak.

Lembar yang dihasilkan akan terlihat seperti ini.

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

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

Jika Anda ingin menjadi sangat kreatif, Anda bahkan dapat membuat dua array yang berisi berbagai sel dari dua lembar yang berbeda, dan melakukan perhitungan antara elemen masing -masing array.

Aplikasi hanya dibatasi oleh imajinasi Anda.