VBA telah menjadi bagian dari paket 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 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 itu dalam berbagai cara.
Jadi apa itu array VBA? Pada artikel ini kami akan menjawab pertanyaan itu, dan menunjukkan kepada Anda bagaimana cara menggunakannya di skrip VBA Anda sendiri.
Apa itu VBA Array?
Menggunakan VBA array di Excel sangat sederhana, tetapi memahami konsep array bisa menjadi sedikit rumit jika Anda belum pernah menggunakannya.
Pikirkan array seperti kotak dengan bagian-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.
In_content_1 all: [300x250] / dfp: [640x360]->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 To 6) As String
Kemudian dalam program Anda, Anda dapat menempatkan data ke bagian mana pun dari array ini dengan mereferensikan bagian di dalam tanda kurung.
arrMyArray(1) = "Ryan Dube"
Anda dapat membuat array dua dimensi menggunakan baris berikut.
Dim arrMyArray(1 To 6,1 to 2) As 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 biasa, seperti string, boolean, integer, float, dan lainnya.
The angka di dalam tanda kurung juga bisa menjadi variabel. Pemrogram biasanya menggunakan For Loop untuk menghitung seluruh bagian array dan memuat sel data dalam spreadsheet ke dalam array. 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 array multidimensi.
Sebagai contoh, mari kita lihat spreadsheet penjualan produk, tempat Anda ingin menarik nama, item, dan total penjualan staf penjualan dari spreadsheet.
Perhatikan bahwa dalam VBA saat Anda mereferensikan baris atau kolom, Anda menghitung baris dan kolom mulai dari kiri atas di 1. Jadi kolom rep adalah 3, kolom item adalah 4, dan kolom total adalah 7.
Untuk memuat tiga kolom ini di seluruh 11 baris, Anda harus menulis skrip berikut.
Dim arrMyArray(1 To 11, 1 To 3) As String
Dim i As Integer, j As Integer
For i = 2 To 12
For j = 1 To 3
arrMyArray(i-1, j) = Cells(i, j).Value
Next j
Next i
Anda akan melihat bahwa untuk melewati baris tajuk, nomor baris dalam tampilan pertama harus dimulai dari 2 daripada 1. Ini berarti Anda harus mengurangi 1 untuk nilai baris array saat Anda memuat nilai sel ke dalam array menggunakan Cells (i, j) .Value.
Di mana Menyisipkan Skrip Array VBA Anda
Untuk menempatkan program skrip VBA di Excel, Anda harus menggunakan Editor VBA. Anda dapat mengakses ini dengan memilih menu Pengembangdan memilih Lihat Kodedi bagian Kontrol pada pita.
Jika Anda tidak melihat Pengembang di menu, Anda harus tambahkan itu. Untuk melakukan ini, pilih Filedan Opsiuntuk membuka jendela Opsi Excel.
Ubah perintah pilih dari dropdown menjadi Semua Perintah. Pilih Pengembangdari menu kiri dan pilih tombol Tambahuntuk memindahkannya ke panel di sebelah kanan. Pilih kotak centang untuk mengaktifkannya dan pilih OKuntuk menyelesaikannya.
Saat jendela Code Editor terbuka, pastikan sheet data Anda masuk dipilih di panel kiri. Pilih Lembar Kerjadi tarik turun kiri dan Aktifkandi kanan. Ini akan membuat subrutin baru bernama Worksheet_Activate ().
Fungsi ini akan berjalan setiap kali file spreadsheet dibuka. Rekatkan kode ke panel skrip di dalam subrutin ini.
Skrip ini akan bekerja melalui 12 baris dan akan memuat nama rep dari kolom 3, item dari kolom 4, dan total penjualan dari kolom 7.
Setelah keduanya Untuk loop 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 tulis semua data ke sheet baru.
Anda dapat melakukan ini dengan menambahkan loop For setelah yang pertama, dengan perintah untuk menulis hasilnya ke sheet baru.
For k = 2 To 12
Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next k
Ini akan "menurunkan" seluruh array ke Sheet2, dengan baris tambahan berisi total dikalikan 5% untuk jumlah pajak.
The 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 lebih besar dan melakukan fungsi sortir, rata-rata, atau banyak lainnya terhadap data yang Anda simpan di dalamnya.
Jika Anda ingin menjadi kreatif, Anda bahkan bisa membuat dua array mengandung rentang sel dari dua lembar berbeda, dan lakukan kalkulasi antar elemen setiap array.
Aplikasi hanya dibatasi oleh imajinasi Anda.