Bisa jadi, salah satu alasan Tuhan memberi pilihan kebaikan dan keburukan adalah untuk membuat manusia berpikir dengan akalnya menjadi tahu bahwa hanya Tuhan yang Maha Pengampun dan hanya Tuhan yang bisa mengampuni segala dosa dan kesalahan sebesar apapun, sehingga manusia mau bersegera memohon ampunan Tuhan. Apakah kita termasuk orang yang berpikir ?
Setelah pembahasan yang lalu diakhiri dengan mendapatkan rujukan ke seluruh cell, maka mulai pembahasan kali ini adalah tentang mendapatkan suatu rujukan range yang lebih spesifik. Pembahasan kali ini akan menggunakan sebuah data di-sheet bernama 'Data15' yang ada difile bernama BelajarVBA011_03.xlsm.
Gambar berikut ini adalah bentuk data dalam sheet bernama 'Data15' tersebut.
Mendapatkan sebuah area range yang berisi seluruh data
Pembahasan terakhir yang lalu adalah mendapatkan seluruh range dalam worksheet. Sangat jarang terjadi data akan tersebar dari cell pertama (A1) sampai dengan baris terkahir di kolom terkanan suatu worksheet. Pada umumnya, data dalam sebuah worksheet akan membentuk kumpulan-kumpulan dataset atau bahkan hanya berisi sebuah tabel atau dataset kecil. Tentu saja merujuk ke seluruh range dalam worksheet akan menjadi terasa berlebihan karena sebaran area data tidak sampai seluas worksheet.Object worksheet memiliki properti bernama UsedRange yang dapat digunakan untuk mendapatkan sebuah area yang melingkupi seluruh sebaran data. Pada gambar diatas, data tersebar mulai dari range B2 sampai L18. Baris 1 ataupun kolom A tidak berisi data. Begitu juga dengan kolom K sampai kolom terkanan dan baris 19 sampai baris terakhir range dalam worksheet. Prosedur berikut ini adalah contoh penggunaan properti UsedRange milik object Worksheet untuk mendapatkan sebuah area yang melingkupi seluruh sebaran data di sheet bernama 'Data15'.
Public Sub TentangUsedRange()
Dim rng As Range
Set rng = Sheets("Data15").UsedRange
rng.Copy
MsgBox "UsedRange : " & rng.Address & vbCrLf & _
"Jumlah area : " & rng.Areas.Count
End Sub
Proses yang terjadi dalam prosedur tersebut adalah :
- Deklarasi variabel ber-type object Range sebagai penyimpan range yang dirujuk pada baris kode :
- Mengisi variabel range dengan rujukan berupa range terpakai (UsedRange) yang ada di sheet bernama 'Data15' pada baris kode :
- Contoh proses yang dilakukan terhadap area range yang sudah didapatkan dan disimpan pada variabel bernama rng. Kali ini contohnya adalah meng-Copy ke Clipboard, pada baris kode :
- Menampilkan pesan kepada user dengan sebuah kotak pesan (MsgBox) yang diisi teks informasi tentang alamat range terpakai (UsedRange) melalui bagian berbunyi rng.Address. Pesan juga berisi (digabung) dengan jumlah area range yang tersimpan dalam variabel bernama rng melalui bagian berbunyi rng.Areas.Count. Teks pesan tentang jumlah area diletakkan pada baris baru, dengan adanya bagian berbunyi vbCrLF. Seluruh teks informasi dirangkai menjadi sebuah teks panjang dengan cara menggabungkannya, yaitu dengan memanfaatkan operator penggabungan teks berupa karakter ampersand (&).
Beberapa hal yang sebaiknya dicoba adalah ketika :
- minimal sebuah cell di baris 1 mulai kolom B diisi data, sedangkan seluruh kolom A adalah kosong, maka UsedRange akan berubah menjadi '$B$1:$L$18'
- minimal sebuah cell di kolom A mulai baris 2 diisi data, sedangkan seluruh baris 1 adalah kosong, maka UsedRange akan berubah menjadi '$A$2:$L$18'
- cell A1 diisi data, sedangkan di baris 1 mulai kolom B adalah kosong dan di kolom A mulai baris 2 adalah kosong, maka UsedRange akan berubah menjadi '$A$1:$L$18'
- sebuah cell di baris 1 mulai kolom B diisi data dan sebuah cell di kolom A mulai baris 2 diisi data serta cell A1 adalah kosong, maka UsedRange akan berubah menjadi '$A$1:$L$18'
Perpotongan range
Antara 2 (dua) area range yang saling berpotongan bisa didapatkan suatu area range perpotongan. Salah satu kegunaan dari memperoleh perpotongan range adalah sebagai alat cek keberadaan range yang diubah user dengan suatu area range data yang boleh diubah user. Jika ada range perpotongannya, maka user melakukan pengubahan pada tempatnya dan proses pengubahan boleh dilakukan. Jika tidak ada hasil perpotongannya, maka proses pengubahan tidak boleh terjadi.Perpotongan range bisa didapatkan dengan menggunakan methods milik Application yang bernama Intersect. Bagian ini akan menjabarkan konsep perpotongan range dengan methods bernama Intersect tersdebut. Contohnya, pada data yang ada dalam gambar diatas, dibutuhkan data milik header data h2 dan h3 dari kedua dataset cabang. Range data (yang di-border) bisa didapatkan dari perpotongan antara kolom D:E dan I:K dengan baris 6:16. Prosedur untuk proses ini bisa berbentuk seperti prosedur berikut ini :
Public Sub TentangIntersection()
Dim rngCabang As Range, rngBarisData As Range
Dim rngPotongan As Range
Set rngCabang = Range("D:E,I:J")
Set rngBarisData = Range("6:16")
Set rngPotongan = Intersect(rngCabang, rngBarisData)
rngPotongan.Copy
MsgBox "Data Kolom : " & rngCabang.Address & vbCrLf & _
"Data Baris : " & rngBarisData.Address & vbCrLf & _
"Potongan : " & rngPotongan.Address & vbCrLf & _
"Jumlah area : " & rngPotongan.Areas.Count
End Sub
Proses yang terjadi pada prosedur diatas adalah :
- Deklarasi variabel rngVabang ber-type object Range sebagai wadah range yang merujuk ke kolom-kolom yang berisi header tabel h2 dan h3 dikedua dataset. Juga dideklarasikan variabel rngBarisData yang ber-type object Range sebagai wadah range yang merujuk ke baris-baris data dari kedua dataset cabang.
- Deklarasi variabel rngPotongan ber-type object Range sebagai wadah range hasil perpotongan antara rngCabang dengan rngBarisData.
- Mendefinisikan isi variabel rngCabang dengan range dikolom-kolom dengan header h2 dan h3 di kedua dataset cabang pada baris kode :
- Mendefinisikan isi variabel rngBarisData dengan range dibaris-baris kedua dataset cabang pada baris kode :
- Mendefinisikan isi variabel rngPotongan dengan hasil pemanfaatan methods Intersect, dengan range yang diperpotongkan adalah rngCabang dengan rngBarisData. Proses ini ada dibaris kode :
- Memanfaatkan hasil perpotongan range. Pada prosedur diatas, contoh pemanfaatannya adalah meng-Copy hasil perpotongan range ke Clipboard pada baris kode :
- Menampilkan pesan kepada user menggunakan sebuah kotak pesan (MsgBox) yang berisi alamat dari rngCabang, alamat rngBarisData dan alamat hasil perpotongan (rngPotongan) yang memanfaatkan properti Address milik object Range. Juga diinformasikan jumlah area range dalam hasil perpotongan (rngPotongan) menggunakan properti Count milik object collection Areas, dimana object collection Areas adalah salah satu properti milik object Range. Seluruh informasi disusun menggunakan operator penggabungan teks berupa karakter ampersand (&) dan diatur letak barisnya dengan vbCrLf.
Menyimpan banyak area range kedalam sebuah variabel object Range
Kadangkala ditemukan suatu kondisi bahwa data yang akan diolah membentuk banyak area dan tidak didapatkan secara sekaligus. Sedangkan pengolahan lanjutan akan mengolahnya secara berkesinambungan. Oleh sebab itu, area-area range yang telah didapatkan tersebut harus disimpan dalam sebuah variabel object Range.Menyimpan berbagai area range ke dalam sebuah variabel object Range bisa dilakukan dengan memanfaatkan methods Union milik Application. Contohnya, pada gambar diatas, telah didapatkan 3 (tiga) area range yang berupa data-data di header h2 dan h3 dikedua dataset cabang yang memiliki nilai di header h2-nya. Artinya, ada area range D6:E13, I6:J10 dan I12:J15. Ketiga area range tersebut akan disimpan disebuah variabel object range agar bisa diolah lebih lanjut sebagai satu kesatuan data yang dibutuhkan. Prosedur untuk contoh ini dapat berbunyi :
Public Sub TentangUnion()
Dim rngCabang1 As Range
Dim rngCabang2A As Range, rngCabang2B As Range
Dim rngGabung As Range
Set rngCabang1 = Range("D6:D13")
Set rngCabang2A = Range("i6:i10")
Set rngCabang2B = Range("i12:i15")
Set rngGabung = Union(rngCabang1, rngCabang2A, rngCabang2B)
MsgBox "Range 1 : " & rngCabang1.Address & vbCrLf & _
"Range 2 : " & rngCabang2A.Address & vbCrLf & _
"Range 3 : " & rngCabang2B.Address & vbCrLf & _
"Gabungan : " & rngGabung.Address & vbCrLf & _
"Jumlah area : " & rngGabung.Areas.Count
End Sub
Proses yang terjadi pada prosedur diatas adalah :
- Deklarasi variabel rngCabang1 ber-type object Range sebagai wadah data header h2 dan h3 di dataset cabang1.
- Deklarasi variabel rngCabang2A ber-type object Range sebagai wadah data header h2 dan h3 di dataset cabang2 pada area atas sebelum baris cell kosong di range I11:J11. Sedang variabel rngCabang2B adalah untuk setelah baris kosong tersebut.
- Deklarasi variabel rngGabung ber-type object Range sebagai wadah hasil penggabungan seluruh area tersebut kedalam sebuah variabel object range.
- Mendefinisikan range untuk rngCabang1 dengan range D6:D13
- Mendefinisikan range untuk rngCabang2A dengan range I6:J10
- Mendefinisikan range untuk rngCabang2B dengan range I12:J15
- Mendefinisikan range untuk rngGabung dengan hasil penggabungan seluruh range yang diketahui dengan menggunakan methods Union. Proses ini adalah pada baris kode :
- Menampilkan pesan kepada user menggunakan sebuah kotak pesan (MsgBox) berisi alamat masing-masing range yang digabungkan dan alamat range hasil penggabungan. Juga disertai informasi jumlah area hasil penggabungan.
Simpulan
Pengolahan data membutuhkan rujukan range yang spesifik pada yang dibutuhkan saja. Proses otomasi atau aplikasi berbasis Excel juga dituntut untuk bisa mendapatkan rujukan range yang spesifik tersebut.Proses perujukan range yang spesifik pada bahasan kali ini baru pada area range yang berisi data saja dan beberapa cara mendapatkan range yang dibutuhkan dari suatu range yang luas melalui perpotongan (Intersect). Selain itu juga dibahas tentang menyatukan tempat penyimpanan range dalam memori komputer (dalam variabel) menggunakan Union.
Pembelajaran lebih lanjut untuk bisa mendapatkan area data yang akan di-Intersect-kan atau yang akan di-Union-kan sangat diperlukan, sehingga proses otomasi atau aplikasi berbasis Excel yang sedang dibangun bisa menjadi lebih dinamis terhadap perubahan jumlah record data yang akan diolah.
:)
Insya Allah bahasan selanjutnya tentang Offset dan Resize. Kedua hal ini termasuk tulang punggung untuk mendinamiskan perujukan range yang dibutuhkan dalam suatu proses otomasi atau aplikasi berbasis Excel.
Tidak ada komentar:
Posting Komentar