Belajar VBA - Loop Range & Array (Performa)

#Pendahuluan

Di episode 9 kita mengolah data dengan method bawaan Excel yang sudah efisien. Episode ini membahas kasus yang berbeda: saat data harus diproses baris per baris dengan logika khusus — dan di situlah macro bisa menjadi sangat lambat jika ditulis naif.

Mengapa ini penting? Perbedaan antara macro yang membutuhkan 3 detik dan yang butuh 3 menit sering kali bukan soal mesin, melainkan soal cara kalian mengakses Excel. Kuncinya: akses ke Excel itu mahal. Semakin sedikit perjalanan kode → Excel, semakin cepat macro. Teknik array di episode ini adalah jawaban paling berdampak untuk masalah tersebut.

#Biaya Tersembunyi Per-Cell Loop

Bayangkan 50.000 baris data yang perlu diproses. Pola naifnya:

Pola lambat - per-cell loop

Dim r As Long
For r = 2 To 50001
    Cells(r, 3).Value = Cells(r, 1).Value * Cells(r, 2).Value
Next r

Setiap iterasi melakukan dua perjalanan ke Excel (baca A, baca B) dan satu perjalanan pulang (tulis C). Total 150.000 akses. Setiap akses melintasi batas antar-proses yang tidak gratis — inilah mengapa 50 ribu baris terasa seperti selamanya.

Note

Aneka teknik makro (mematikan ScreenUpdating, Calculation, EnableEvents) mempercepat dengan mengurangi pekerjaan latar — tetapi teknik array mempercepat dengan mengurangi jumlah akses itu sendiri. Keduanya bisa digabung; detail tuning lengkap di episode 21.

#Solusi: Baca ke Array, Proses di Memori

Array adalah blok data di memori VBA. Kita baca seluruh range sekali, proses dalam array, lalu tuliskan kembali sekali:

Pola cepat - baca array, proses, tulis kembali

Sub ProsesArray()
    Dim data As Variant
    Dim r As Long
 
    data = Range("A1:C50001").Value2
 
    For r = 2 To UBound(data, 1)
        data(r, 3) = data(r, 1) * data(r, 2)
    Next r
 
    Range("A1:C50001").Value2 = data
End Sub

Cara kerjanya:

data = Range(...).Value2 — seluruh range disalin ke array satu akses.
Loop bekerja sepenuhnya di memori — nol akses Excel.
Range(...).Value2 = data — hasil dituliskan kembali satu akses.

Hasilnya: 150.000 akses Excel berubah menjadi 2. Pada 50.000 baris, kecepatannya bisa melonjak 10-100 kali lipat. Ini pola paling penting dalam VBA untuk data besar.

#Memahami Array VBA

#Basis Indeks

Array yang dihasilkan Value2 selalu 1-based (berawal dari 1), berbeda dari kebanyakan bahasa yang 0-based. data(1, 1) adalah sel kiri-atas range.

#Dimensi

UBound(data, 1) = jumlah baris, UBound(data, 2) = jumlah kolom. Untuk array 2 kolom dan 50.000 baris:

Ukuran	Nilai
`UBound(data, 1)`	50001
`UBound(data, 2)`	3
`data(2, 1)`	nilai baris 2 kolom A

#Array Satu Kolom / Satu Baris

Range yang satu kolom menghasilkan array 2 dimensi berukuran N×1, bukan array 1 dimensi — selalu akses data(r, 1), bukan data(r).

#Praktik: Kasus Nyata

#Contoh 1: Membersihkan Teks dengan Trim

Trim semua sel kolom A

Sub TrimKolom()
    Dim data As Variant
    Dim r As Long
 
    data = Range("A1:A50000").Value2
    For r = 1 To UBound(data, 1)
        data(r, 1) = Trim(data(r, 1))
    Next r
    Range("A1:A50000").Value2 = data
End Sub

#Contoh 2: Kategorisasi dengan Kondisi

Kategorisasi berdasarkan nilai

Sub Kategorisasi()
    Dim data As Variant
    Dim r As Long
 
    data = Range("A1:C50000").Value2
    For r = 2 To UBound(data, 1)
        If data(r, 2) >= 1000000 Then
            data(r, 3) = "VIP"
        ElseIf data(r, 2) >= 100000 Then
            data(r, 3) = "Reguler"
        Else
            data(r, 3) = "Basic"
        End If
    Next r
    Range("A1:C50000").Value2 = data
End Sub

Perhatikan: logika If...ElseIf yang sama persis seperti episode 6, tetapi sekarang berjalan di memori.

#Kapan Array, Kapan Tidak?

Situasi	Teknik
Memproses nilai secara logika	Array (baca-tulis sekali)
Mengubah format, filter, sortir	Method bawaan (episode 9) — sudah dioptimalkan Excel
Data kecil (beberapa ratus baris)	Keduanya sama; pilih yang mudah dibaca
Perlu format sel berbeda per baris	Array untuk nilai + method untuk format

Tip

Jangan memaksakan array untuk segala hal. RemoveDuplicates, Sort, dan AutoFilter berjalan di mesin Excel dan sudah cepat — memproses ulang di VBA justru memperlambat. Gunakan array untuk logika per-baris yang tidak punya method bawaan.

#Menghindari Select dan Aktivasi Berlebihan

Pola Select/Activate adalah peninggalan Macro Recorder yang mahal:

Lambat - meniru gerakan manusia

Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Tujuan").Select
ActiveSheet.Range("A1").Select
ActiveSheet.Paste

Cepat - akses objek langsung

Range("A1").CurrentRegion.Copy
Sheets("Tujuan").Range("A1").PasteSpecial

Versi cepat tidak membuat workbook "melompat" antar sheet, tidak mengganti sel aktif, dan lebih pendek. Aturan emas: jangan pernah Select hanya untuk mengoper ke baris berikutnya — setiap Select adalah sinyal bahwa kode bisa ditulis lebih langsung.

#Mematikan Layar untuk Data Besar

Meski array sudah memotong akses, menyertakan ScreenUpdating = False untuk data besar masih membantu — Excel tidak repot menggambar layar saat macro berjalan:

Bungkus dengan ScreenUpdating

Sub ProsesCepat()
    Application.ScreenUpdating = False
    On Error GoTo Selesai
 
    ' ... logika array ...
 
Selesai:
    Application.ScreenUpdating = True
End Sub

Pastikan ScreenUpdating selalu dikembalikan ke True — termasuk saat error (makanya ada label Selesai). Pola ini dibahas penuh di episode 21.

#Common Pitfalls

Kesalahan	Dampak
Mengakses `data(r)` pada array 2D	Error Subscript out of range — pakai `data(r, 1)`
Mencampur array 0-based hasil `Split` dengan `Value2`	Indeks meleset
Menulis array ke range yang ukurannya beda	Error atau data terpotong
Array dengan sel kosong / teks, lalu dihitung	Error Type mismatch; pastikan tipe konsisten
`Select` berantai dari hasil recorder	Lambat dan rapuh

#Penutup

Pada episode 10 ini, kalian telah menguasai teknik yang paling berdampak pada performa VBA.

Inti yang harus dibawa pulang:

Akses Excel itu mahal — per-cell loop pada 50.000 baris = 150.000 perjalanan.
Baca range sekali ke array (Value2), proses di memori, tulis kembali sekali.
Array hasil Value2 adalah 1-based dan selalu 2 dimensi.
Select/Activate adalah pemborosan — akses objek langsung.
Kombinasikan dengan ScreenUpdating = False untuk data besar.

Di episode 11 selanjutnya kita keluar dari Excel menuju Word VBA: objek Documents, Selection, Range, dan Find/Replace, plus studi kasus membuat laporan otomatis dari data Excel ke Word. Sampai jumpa di episode 11!