Apakah array VBA di Excel dan bagaimana memprogramnya

VBA telah menjadi sebahagian daripada suite Microsoft Office selama bertahun -tahun. Walaupun ia tidak mempunyai fungsi penuh dan kuasa aplikasi VB penuh, VBA menyediakan pengguna pejabat dengan fleksibiliti untuk mengintegrasikan produk pejabat dan mengautomasikan kerja yang anda lakukan di dalamnya.

Salah satu alat yang paling berkuasa yang terdapat di VBA adalah keupayaan untuk memuatkan pelbagai data ke dalam satu pemboleh ubah yang disebut array. Dengan memuatkan data dengan cara ini, anda boleh memanipulasi atau melakukan pengiraan pada pelbagai data dalam pelbagai cara.

Anda boleh memasukkan data ke dalam setiap bahagian "kotak" ini dalam apa jua pesanan yang anda suka.

Pada permulaan skrip VBA anda, anda perlu menentukan "kotak" ini dengan menentukan array VBA anda. Jadi untuk membuat array yang boleh memegang satu set data (satu dimensi pelbagai), anda akan menulis baris berikut.

Red arrmyarray (1 hingga 6) sebagai rentetan

Kemudian dalam program anda, anda boleh meletakkan data ke mana -mana bahagian array ini dengan merujuk bahagian di dalam kurungan.

ArrmyArray (1) = "Ryan Dube"

Anda boleh membuat array dua dimensi menggunakan baris berikut.

Red arrmyarray (1 hingga 6,1 hingga 2) sebagai integer

Nombor pertama mewakili baris, dan yang kedua adalah lajur. Jadi array di atas boleh memegang julat dengan 6 baris dan 2 lajur.

Anda boleh memuatkan sebarang elemen array ini dengan data seperti berikut.

ArrmyArray (1,2) = 3

Ini akan memuatkan 3 ke dalam sel b1.

Arahan boleh memegang sebarang jenis data sebagai pemboleh ubah biasa, seperti rentetan, boolean, bilangan bulat, terapung dan banyak lagi.

Nombor di dalam kurungan juga boleh menjadi pemboleh ubah. Pengaturcara biasanya menggunakan gelung untuk dikira melalui semua bahagian pelbagai dan memuatkan sel data dalam spreadsheet ke dalam array. Anda akan melihat bagaimana untuk melakukan ini kemudian dalam artikel ini.

Cara memprogram array VBA di Excel

Mari kita lihat program mudah di mana anda mungkin ingin memuatkan maklumat dari spreadsheet ke dalam pelbagai multidimensi.

Sebagai contoh, mari kita lihat spreadsheet jualan produk, di mana anda ingin menarik nama, item, dan jumlah jualan jualan dari spreadsheet.

Perhatikan bahawa dalam VBA apabila anda merujuk baris atau lajur, anda mengira baris dan lajur bermula dari kiri atas pada 1. Jadi lajur rep adalah 3, lajur item adalah 4, dan jumlah lajur adalah 7.

Untuk memuatkan tiga lajur ini sepanjang 11 baris, anda perlu menulis skrip berikut.

Red arrmyarray (1 hingga 11, 1 hingga 3) sebagai rentetan
Dim I sebagai Integer, J sebagai Integer

Untuk i = 2 hingga 12
Untuk j = 1 hingga 3
arrmyarray (i-1, j) = sel (i, j).Nilai
Seterusnya j
Seterusnya saya

Anda akan melihat bahawa untuk melangkau baris tajuk, nombor baris pada yang pertama untuk melihat perlu bermula pada 2 daripada 1. Ini bermakna anda perlu menolak 1 untuk nilai baris array apabila anda memuatkan nilai sel ke dalam array menggunakan sel (i, j).Nilai.

Di mana untuk memasukkan skrip array VBA anda

Untuk meletakkan program skrip VBA di Excel, anda perlu menggunakan editor VBA. Anda boleh mengaksesnya dengan memilih Pemaju menu dan memilih Lihat kod di bahagian kawalan reben.

Sekiranya anda tidak melihat pemaju dalam menu, anda perlu menambahkannya. Untuk melakukan ini, pilih Fail dan Pilihan Untuk membuka tetingkap Pilihan Excel.

Tukar arahan pilih dari lungsur turun ke Semua arahan. Pilih Pemaju dari menu kiri dan pilih Tambah butang untuk memindahkannya ke panel di sebelah kanan. Pilih kotak semak untuk membolehkannya dan pilih okey untuk menamatkan.

Apabila tetingkap editor kod dibuka, pastikan lembaran data anda dipilih di panel kiri. Pilih Lembaran kerja di jatuh turun kiri dan Aktifkan di sebelah kanan. Ini akan mewujudkan subrutin baru yang disebut worksheet_activate ().

Fungsi ini akan dijalankan apabila fail spreadsheet dibuka. Tampal kod ke dalam anak tetingkap skrip di dalam subrutin ini.

Skrip ini akan berfungsi melalui 12 baris dan akan memuatkan nama rep dari lajur 3, item dari lajur 4, dan jumlah jualan dari lajur 7.

Sekali untuk kedua -dua gelung selesai, arrmyarray array dua dimensi mengandungi semua data yang anda tentukan dari lembaran asal.

Memanipulasi susunan di Excel VBA

Katakan anda ingin memohon cukai jualan 5% untuk semua harga jualan akhir, dan kemudian tulis semua data ke lembaran baru.

Anda boleh melakukan ini dengan menambahkan lagi untuk gelung selepas yang pertama, dengan arahan untuk menulis hasilnya ke lembaran baru.

Untuk k = 2 hingga 12
Lembaran ("Sheet2").Sel (k, 1).Nilai = arrmyarray (k - 1, 1)
Lembaran ("Sheet2").Sel (k, 2).Nilai = arrmyarray (k - 1, 2)
Lembaran ("Sheet2").Sel (k, 3).Nilai = arrmyarray (k - 1, 3)
Lembaran ("Sheet2").Sel (k, 4).Nilai = arrmyarray (k - 1, 3) * 0.05
Seterusnya k

Ini akan "memunggah" keseluruhan array ke Sheet2, dengan baris tambahan yang mengandungi jumlah yang didarabkan sebanyak 5% untuk jumlah cukai.

Lembaran yang dihasilkan akan kelihatan seperti ini.

Seperti yang anda lihat, susunan VBA di Excel sangat berguna dan serba boleh seperti mana -mana helah Excel lain. 

Contoh di atas adalah penggunaan yang sangat mudah untuk array. Anda boleh membuat array yang jauh lebih besar dan melakukan penyortiran, purata, atau banyak fungsi lain terhadap data yang anda simpan di dalamnya.

Jika anda ingin mendapatkan kreatif sebenar, anda juga boleh membuat dua tatasusunan yang mengandungi pelbagai sel dari dua helaian yang berbeza, dan melakukan pengiraan antara unsur -unsur setiap array.

Aplikasi hanya terhad oleh imaginasi anda.