Bekerja dengan Array di VBA Excel

Mengapa Makro Bisa Berjalan Lambat?

Banyak pemula menulis makro dengan cara membaca atau menulis data langsung ke worksheet secara berulang.

Sebagai contoh:

For i = 2 To 10000
    Cells(i, 1).Value = Cells(i, 2).Value * 2
Next i

Kode tersebut memang menghasilkan output yang benar. Namun, pada data yang jumlahnya ribuan bahkan puluhan ribu baris, prosesnya bisa terasa lambat.

Penyebabnya bukan karena perulangannya, melainkan karena VBA terus berkomunikasi dengan worksheet pada setiap iterasi. Semakin sering proses baca dan tulis dilakukan, semakin besar pula waktu yang dibutuhkan.

Salah satu cara paling efektif untuk meningkatkan performa makro adalah menggunakan Array.

Apa Itu Array?

Array adalah struktur data yang digunakan untuk menyimpan sekumpulan nilai dalam satu variabel.

Dengan array, data dapat dipindahkan dari worksheet ke memori komputer, diproses di sana, lalu dikembalikan ke worksheet dalam satu kali operasi.

Karena sebagian besar proses berlangsung di memori, eksekusi makro menjadi jauh lebih cepat dibandingkan mengakses worksheet secara terus-menerus.

Deklarasi Array

Array dapat dideklarasikan menggunakan kata kunci Dim.

Contoh:

Dim Nilai(1 To 5) As Integer

Array tersebut memiliki lima elemen yang dapat diakses melalui indeks 1 hingga 5.

Mengisi nilainya:

Nilai(1) = 80
Nilai(2) = 85
Nilai(3) = 90
Nilai(4) = 75
Nilai(5) = 88

Membaca Nilai Array

Nilai pada array dapat diakses berdasarkan indeksnya.

MsgBox Nilai(3)

Hasilnya:

Menggunakan Perulangan

Array biasanya diproses menggunakan For...Next.

Dim i As Integer

For i = 1 To 5

    Debug.Print Nilai(i)

Next i

Dengan cara ini, seluruh isi array dapat diproses tanpa menulis kode untuk setiap elemen.

Array Dua Dimensi

Saat membaca data dari worksheet, VBA umumnya menghasilkan array dua dimensi.

Dim Data As Variant

Data = Range("A2:C6").Value

Struktur array:

Baris	Kolom
Data(1,1)	Data(1,2)
Data(2,1)	Data(2,2)
...	...

Contohnya:

MsgBox Data(2, 3)

Artinya, membaca data pada baris ke-2 dan kolom ke-3 dari array.

Membaca Worksheet ke Dalam Array

Salah satu penggunaan array yang paling sering adalah memindahkan seluruh data worksheet ke memori.

Dim Data As Variant

Data = Range("A2:D1000").Value

Setelah itu, seluruh data dapat diproses tanpa perlu membaca worksheet lagi.

Mengubah Isi Array

Misalkan Anda ingin menambahkan teks "Lunas" pada seluruh baris.

Dim i As Long

For i = 1 To UBound(Data)

    Data(i, 4) = "Lunas"

Next i

Seluruh perubahan dilakukan di dalam memori sehingga proses berlangsung lebih cepat.

Menulis Kembali Array ke Worksheet

Setelah selesai diproses, tuliskan kembali seluruh isi array ke worksheet.

Range("A2:D1000").Value = Data

Berbeda dengan penulisan per sel, seluruh data dikirim ke worksheet hanya dalam satu operasi.

Menggunakan LBound dan UBound

Ukuran array sebaiknya tidak ditentukan secara manual.

Gunakan:

LBound(Data)

untuk memperoleh indeks pertama.

Dan:

UBound(Data)

untuk memperoleh indeks terakhir.

Contoh:

Dim i As Long

For i = LBound(Data) To UBound(Data)

    Debug.Print Data(i, 1)

Next i

Dengan cara ini, kode tetap dapat digunakan meskipun jumlah data berubah.

Studi Kasus

Misalkan Anda ingin menaikkan harga seluruh produk sebesar 10%.

Dim Data As Variant
Dim i As Long

Data = Range("A2:C1000").Value

For i = LBound(Data) To UBound(Data)

    Data(i, 3) = Data(i, 3) * 1.1

Next i

Range("A2:C1000").Value = Data

Makro hanya membaca worksheet satu kali dan menulis kembali satu kali, sehingga performanya jauh lebih baik dibandingkan memproses setiap sel secara langsung.

Kapan Sebaiknya Menggunakan Array?

Array sangat cocok digunakan ketika:

mengolah ribuan baris data;
melakukan perhitungan berulang;
membersihkan atau memodifikasi data dalam jumlah besar;
membuat laporan otomatis;
mengurangi akses langsung ke worksheet.

Untuk data yang hanya terdiri dari beberapa baris, penggunaan array mungkin tidak memberikan perbedaan performa yang signifikan.

Kesalahan yang Sering Dilakukan

Menganggap Array Sama dengan Range

Array berada di memori, sedangkan Range merupakan objek yang mewakili sel pada worksheet.

Karena itu, array tidak memiliki properti seperti .Font, .Interior, atau .Select.

Menentukan Ukuran Array Secara Tetap

Kurang fleksibel:

Dim Data(1 To 1000)

Lebih baik gunakan data dari worksheet atau manfaatkan ReDim ketika ukuran array belum diketahui.

Lupa Menulis Kembali ke Worksheet

Perubahan yang dilakukan pada array tidak akan terlihat di worksheet sampai Anda menuliskannya kembali.

Range("A2:D1000").Value = Data

Tips Praktis

💡 Gunakan array saat bekerja dengan data dalam jumlah besar.

💡 Baca data dari worksheet satu kali, proses di memori, lalu tulis kembali satu kali.

💡 Gunakan LBound dan UBound agar perulangan menyesuaikan ukuran array.

💡 Hindari mengakses Cells atau Range di dalam perulangan jika tidak diperlukan.

💡 Simpan array dalam variabel bertipe Variant saat membaca data dari worksheet.

Ringkasan

Konsep	Fungsi
`Array`	Menyimpan banyak nilai dalam satu variabel
`Variant`	Menampung array hasil pembacaan dari worksheet
`LBound()`	Mengambil indeks pertama array
`UBound()`	Mengambil indeks terakhir array
`Range.Value`	Membaca atau menulis data sekaligus

Kesimpulan

Array merupakan salah satu teknik paling penting untuk meningkatkan performa makro di VBA Excel. Dengan memproses data di dalam memori, Anda dapat mengurangi akses langsung ke worksheet yang sering menjadi penyebab utama lambatnya eksekusi. Selain membuat makro lebih cepat, penggunaan array juga menghasilkan kode yang lebih efisien, mudah dipelihara, dan siap menangani dataset dalam jumlah besar.