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)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 kami akan menjawab pertanyaan itu, dan menunjukkan kepada Anda cara menggunakannya di skrip VBA Anda sendiri^{(your own VBA script)} .

Apa Itu Array VBA?

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

Pikirkan^(Think) 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. ^(VBA)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

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.

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 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^{(rows and columns)} 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 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 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 Sel^(Cells) (i, j).Nilai.

Tempat Memasukkan Skrip Array VBA Anda^{(Your VBA Array Script)}

Untuk menempatkan program skrip VBA di ^(VBA)Excel , Anda perlu menggunakan editor VBA . Anda dapat mengakses ini dengan memilih menu Pengembang^(Developer) dan memilih Lihat Kode^{(View Code)} di bagian Kontrol^(Controls) pada pita.

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

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

Saat jendela Editor Kode^{(Code Editor)} terbuka, pastikan lembar tempat data Anda dipilih di panel kiri. Pilih Lembar Kerja^(Worksheet) di dropdown kiri dan Aktifkan^(Activate) 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 For 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 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.

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 "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 ^(VBA)Excel sangat berguna dan serbaguna seperti trik Excel lainnya^{(any other Excel trick)} . 

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^{(containing a range of cells)} dari dua lembar berbeda, dan melakukan penghitungan di antara elemen setiap larik.

Aplikasi hanya dibatasi oleh imajinasi Anda.

What Is a VBA Array in Excel and How to Program One

VBA has been part of the Microsoft Office suite fоr many years. While it doesn’t have the full functionality and power of a full VΒ application, VBA prоνіdes Office users with the flexіbility to integrate Office produсts and automate the work уou do in them.

One of the most powerful tools available in VBA is the ability to load an entire range of data into a single variable called an array. By loading data in this way, you can manipulate or perform calculations on that range of data in a variety of ways.

So what is a VBA array? In this article we’ll answer that question, and show you how to use one in your own VBA script.

What Is a VBA Array?

Using a VBA array in Excel is very simple, but understanding the concept of arrays can be a little bit complex if you’ve never used one.

Think of an array like a box with sections inside it. A one-dimensional array is a box with one line of sections. A two-dimensional array is a box with two lines of sections.

You can put data into each section of this “box” in any order you like.

At the beginning of your VBA script, you need to define this “box” by defining your VBA array. So to create an array that can hold one set of data (a one-dimension array), you would write the following line.

Dim arrMyArray(1 To 6) As String

Later in your program, you can place data into any section of this array by referencing the section inside of parentheses.

arrMyArray(1) = "Ryan Dube"

You can create a two-dimensional array using the following line.

Dim arrMyArray(1 To 6,1 to 2) As Integer

The first number represents the row, and the second is the column. So the above array could hold a range with 6 rows and 2 columns.

You can load any element of this array with data as follows.

arrMyArray(1,2) = 3

This will load a 3 into the cell B1.

An array can hold any type of data as a regular variable, such as strings, boolean, integers, floats and more.

The number inside the parenthesis can also be a variable. Programmers commonly use a For Loop to count through all sections of an array and load data cells in the spreadsheet into the array. You’ll see how to do this later in this article.

How to Program a VBA Array in Excel

Let’s take a look at a simple program where you may want to load information from a spreadsheet into a multidimensional array.

As an example, let’s look at a product sales spreadsheet, where you want to pull the sales rep’s name, item, and total sale out of the spreadsheet.

Note that in VBA when you reference rows or columns, you count rows and columns starting from the top left at 1. So the rep column is 3, the item column is 4, and the total column is 7.

To load these three columns over all 11 rows, you’ll need to write the following script.

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

You’ll notice that in order to skip the header row, the row number in the first For look needs to start at 2 rather than 1. This means you need to subtract 1 for the array row value when you’re loading the cell value into the array using Cells(i, j).Value.

Where to Insert Your VBA Array Script

To place program a VBA script in Excel, you need to use the VBA editor. You can access this by selecting the Developer menu and selecting View Code in the Controls section of the ribbon.

If you don’t see the Developer in the menu, you’ll need to add it. To do this, select File and Options to open the Excel Options window.

Change the choose commands from dropdown to All Commands. Select Developer from the left menu and select the Add button to move it to the pane on the right. Select the checkbox to enable it and select OK to finish.

When the Code Editor window opens, make sure the sheet your data is in is selected in the left pane. Select Worksheet in the left dropdown and Activate in the right. This will create a new subroutine called Worksheet_Activate().

This function will run whenever the spreadsheet file is opened. Paste the code into the script pane inside this subroutine.

This script will work through the 12 rows and will load the rep name from column 3, the item from column 4, and the total sale from column 7.

Once both For loops are finished, the two dimensional array arrMyArray contains all of the data you specified from the original sheet.

Manipulating Arrays in Excel VBA

Let’s say you want to apply a 5% sales tax to all of the final sale prices, and then write all of the data to a new sheet.

You can do this by adding another For loop after the first, with a command to write the results to a new sheet.

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

This will “unload” the entire array into Sheet2, with an additional row containing the total multiplied by 5% for the tax amount.

The resulting sheet will look like this.

As you can see, VBA arrays in Excel are extremely useful and as versatile as any other Excel trick. 

The example above is a very simple use for an array. You can create much larger arrays and perform sorting, averaging, or many other functions against the data you store in them.

If you want to get real creative, you could even create two arrays containing a range of cells from two different sheets, and perform calculations between elements of each array.

The applications are limited only by your imagination.

Related posts

• Cara Menghapus Baris Kosong di Excel

• Cara Menggunakan Fitur Sel Bicara Excel

• Cara Memasukkan Lembar Kerja Excel ke dalam Word Doc

• Cara Menggunakan Analisis Bagaimana-Jika Excel

• Cara Memperbaiki Baris Di Excel

• Cara Cepat Menyisipkan Beberapa Baris di Excel

• 2 Cara Menggunakan Fungsi Transpose Excel

• 3 Cara Memisahkan Sel Di Excel

• 10 Tip dan Trik Excel untuk 2019

• Cara Membuka Word dan Excel dalam Safe Mode

• 4 Cara Mengonversi Excel ke Google Spreadsheet

• Mengeja Angka dengan Cepat di Word dan Excel

• Cara Mengurutkan Berdasarkan Tanggal di Excel

• Pusatkan Data Lembar Kerja Anda di Excel untuk Pencetakan

• 4 Cara Menggunakan Tanda Centang di Excel

• Gunakan Keyboard untuk Mengubah Tinggi Baris dan Lebar Kolom di Excel

• Google Spreadsheet vs Microsoft Excel – Apa Perbedaannya?

• Cara Menghitung Z-Score di Excel

• Panduan VBA Tingkat Lanjut Untuk MS Excel

• Kelompokkan Baris dan Kolom dalam Lembar Kerja Excel