Tuhan tidak akan membebani mahluk dengan cobaan yang melebihi batas kemampuan si mahluk. Kenapa harus menghabiskan energi untuk berkeluh kesah ketika ada sesuatu yang terasa tidak nyaman menimpa diri kita ? Bukankah lebih bermanfaat jika kita mengevaluasi diri dan berpikir untuk memperbaikinya ?
Merujuk ke suatu range dengan VBA bisa beranjak dari suatu range yang sudah pasti diketahui. Bisa dikatan jika ada suatu range A dan suatu range B, maka masing-masing range, yaitu range A dan range B, memiliki dimensi jumlah baris dan kolom tertentu dan memiliki range awal (range pojok kiri atas) tertentu. Antara range B dengan range A memiliki jarak tertentu sebanyak y baris dan x kolom. Jadi, dari dimensi suatu range bisa diketahui batas-batas range tersebut di keempat sisinya. Dari sebuah range dapat diketahui jumlah baris dan kolomnya. Sedangkan dari dua buah area range dapat diketahui jarak antar keduanya berdasar range pojok kiri atas masing-masing, baik dari jarak dalam dimensi baris dan jarak dalam dimensi kolomnya.
Hal-hal tersebut diataslah yang menjadi salah satu modal untuk membuat suatu rujukan range yang dinamis. Cukup dengan mengetahui sebuah area range, maka bisa merujuk area range lain yang dibutuhkan selama tahu dimensi maupun jarak antara range yang dirujuk dengan range yang pasti diketahui. Artinya, minimal mengetahui sebuah range dalam data dan memahami pola susunan data, maka bisa dilakukan penjelajahan terhadap seluruh data dalam worksheet. Properti range yang dapat digunakan untuk keperluan diatas adalah Offset dan Resize.
Pembahasan kali ini adalah tentang range pada sisi properti bernama Offset dan Resize. Kedua properti ini termasuk tulang punggung dalam merujuk suatu range secara dinamis dalam Excel VBA. Untuk keperluan pembelajaran kali ini, data yang akan digunakan adalah data pada sheet bernama 'Data16' yang ada pada file BelajarVBA011_04.xlsm. Gambar berikut ini adalah bentuk susunan data yang ada di sheet bernama 'Data16'.
Memanfaatkan OFFSET
Properti Offset adalah sebuah properti milik object Range. Offset digunakan untuk melompat menuju suatu range tertentu dengan mengetahui jarak antar baris atau jarak antar kolom relatif terhadap range asalnya. Offset tidak mengubah dimensi suatu area range.Syntaxnya :
object_range.OFFSET( [jarak_antar_baris] , [jarak_antar_kolom] )
dengan :
- object_range adalah range asal
- jarak_antar_baris :
- adalah bilangan bulat jumlah baris dari baris range asal sampai baris range tujuan
- yang bernilai positif akan memiliki arah menuju ke bawah atau ke baris setelah baris range asal
- yang bernilai negatif akan memiliki arah menuju ke atas atau ke baris sebelum baris range asal
- yang bernilai nol atau dikosongkan akan berarti tidak akan menuju kemanapun alias tetap pada baris range asal
- jarak_antar_kolom :
- adalah bilangan bulat jumlah kolom dari kolom range asal sampai kolom range tujuan
- yang bernilai positif akan memiliki arah menuju ke kanan atau ke kolom setelah kolom range asal
- yang bernilai negatif akan memiliki arah menuju ke kiri atau ke kolom sebelum kolom range asal
- yang bernilai nol atau dikosongkan akan berarti tidak akan menuju kemanapun alias tetap pada kolom range asal
Public Sub TentangOffset()
Dim sAlamatAsal As String, rngAsal As Range
Dim rngOffset1 As Range, rngOffset2 As Range
Dim rngOffset3 As Range, rngOffset4 As Range
sAlamatAsal = "A1"
Set rngAsal = Range(sAlamatAsal)
'baris kode contoh akan diletakkan setelah baris ini
End Sub
Berikut ini adalah beberapa contoh penggunaan setelah rngAsal didefinisikan sebagai range dengan alamat yang disimpan pada sAlamatAsal :
Contoh 1 : rngAsal ke 5 kolom dikanan rngAsal
'----------------------------------
'rngAsal ke 5 kolom dikanan rngAsal
'----------------------------------
'disimpan di rngOffset1
Set rngOffset1 = rngAsal.Offset(0, 5)
rngOffset1.Copy
MsgBox "rngAsal : " & rngAsal.Address & vbCrLf & _
"ke 5 kolom di-KANAN rngAsal : " & rngOffset1.Address & _
vbCrLf & "yaitu : rngAsal.Offset(0, 5)"
Baris-baris kode diatas akan diproses sebagai berikut :
- Proses pertama adalah dari rngAsal, dilakukan Offset (lompat) sebanyak 0 baris kebawah dan 5 kolom kekanan oleh bagian :
- kemudian disimpan ke variabel rngOffset1 pada bagian :
- di baris berbunyi :
- Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngOffset1 ke Clipboard oleh baris kode :
- Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngAsal dan rngOffset1
Contoh 2 : rngAsal ke 9 baris dibawah rngAsal
'----------------------------------
'rngAsal ke 9 baris dibawah rngAsal
'----------------------------------
'disimpan di rngOffset2
Set rngOffset2 = rngAsal.Offset(9, 0)
rngOffset2.Copy
MsgBox "rngAsal : " & rngAsal.Address & vbCrLf & _
"ke 9 baris di-BAWAH rngAsal : " & rngOffset2.Address & _
vbCrLf & "yaitu : rngAsal.Offset(9, 0)"
Baris-baris kode diatas akan diproses sebagai berikut :
- Proses pertama adalah dari rngAsal, dilakukan Offset (lompat) sebanyak 9 baris kebawah dan 0 kolom kekanan oleh bagian :
- kemudian disimpan ke variabel rngOffset2 pada bagian :
- di baris berbunyi :
- Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngOffset2 ke Clipboard oleh baris kode :
- Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngAsal dan rngOffset2
Contoh 3 : rngAsal ke 3 baris dibawah rngAsal dan 4 kolom dikanan rngAsal
'--------------------------------------------------------------
'rngAsal ke 3 baris dibawah rngAsal dan 4 kolom dikanan rngAsal
'--------------------------------------------------------------
'disimpan di rngOffset3
Set rngOffset3 = rngAsal.Offset(3, 4)
rngOffset3.Copy
MsgBox "rngAsal : " & rngAsal.Address & vbCrLf & _
"ke 3 baris di-BAWAH rngAsal dan " & _
"4 kolom diKANAN rngAsal : " & _
rngOffset3.Address & vbCrLf & _
"yaitu : rngAsal.Offset(3, 4)"
Baris-baris kode diatas akan diproses sebagai berikut :
- Proses pertama adalah dari rngAsal, dilakukan Offset (lompat) sebanyak 3 baris kebawah dan 4 kolom kekanan oleh bagian :
- kemudian disimpan ke variabel rngOffset3 pada bagian :
- di baris berbunyi :
- Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngOffset3 ke Clipboard oleh baris kode :
- Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngAsal dan rngOffset3
Contoh 4 : Range hasil offset (rngOffset3) ke 2 baris diatas dan 1 kolom dikirinya
'----------------------------------------------------------
'Range hasil offset ke 2 baris diatas dan 1 kolom dikirinya
'----------------------------------------------------------
'disimpan di rngOffset4
Set rngOffset4 = rngOffset3.Offset(-2, -1)
rngOffset4.Copy
MsgBox "rngOffset3 : " & rngOffset3.Address & vbCrLf & _
"ke 2 baris di-ATAS dan " & _
"1 kolom di-KIRI-nya : " & _
rngOffset3.Address & vbCrLf & _
"yaitu : rngOffset3.Offset(-2, -1)"
Baris-baris kode diatas akan diproses sebagai berikut :
- Proses pertama adalah dari rngOffset3, hasil contoh 3 yang berupa range di E4, dilakukan Offset (lompat) sebanyak 2 baris keatas (berarti negatif pada sisi baris senilai -2) dan 1 kolom kekiri (berarti negatif pada sisi kolom senilai -1) oleh bagian :
- kemudian disimpan ke variabel rngOffset4 pada bagian :
- di baris berbunyi :
- Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngOffset4 ke Clipboard oleh baris kode :
- Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngAsal dan rngOffset4
Bagaimana jika dicoba untuk alamat range asal yang disimpan pada variabel sAlamatAsal yang berupa teks berbunyi A1 di baris kode :
sAlamatAsal = "A1"
diganti menjadi B2:E6
Memanfaatkan RESIZE
Properti Resize adalah sebuah properti milik object Range. Resize digunakan untuk mendapatkan suatu range yang mengalami pengubahan luasan area range dari range asalnya, dengan mengetahui jumlah baris luasan baru atau jumlah kolom luasan baru dari range pojok kiri range asalnya. Resize tidak mengubah alamat range pojok kiri atas range asalnya.Syntaxnya :
object_range.RESIZE( [jumlah_baris_baru] , [jumlah_kolom_baru] )
dengan :
- object_range adalah range asal
- jumlah_baris_baru :
- adalah bilangan bulat jumlah baris dari luasan yang baru dimulai dari range pojok kiri atas range asalnya
- selalu bernilai lebih dari nol (>0) alias minimal 1
- yang dikosongkan akan berarti memiliki jumlah baris yang tetap alias sama dengan jumlah baris range asalnya
- jumlah_kolom_baru :
- adalah bilangan bulat jumlah kolom dari luasan yang baru dimulai dari range pojok kiri atas range asalnya
- selalu bernilai lebih dari nol (>0) alias minimal 1
- yang dikosongkan akan berarti memiliki jumlah kolom yang tetap alias sama dengan jumlah kolom range asalnya
Public Sub TentangResize()
Dim sAlamatAnchor As String, rngAnchor As Range
Dim rngResize1 As Range, rngResize2 As Range
Dim rngResize3 As Range
sAlamatAnchor = "A1"
Set rngAnchor = Range(sAlamatAnchor)
'baris kode contoh akan diletakkan setelah baris ini
End Sub
Berikut ini adalah beberapa contoh penggunaan setelah rngAnchor (sebagai range asalnya) didefinisikan sebagai range dengan alamat yang disimpan pada sAlamatAnchor :
Contoh 1 : Perluas rngAnchor menjadi 1 baris x 15 kolom
'--------------------------------------------
'Perluas rngAnchor menjadi 1 baris x 15 kolom
'--------------------------------------------
'dari rngAnchor diperluas menjadi 1 x 15
'disimpan dalam variabel rngResize1
Set rngResize1 = rngAnchor.Resize(1, 15)
rngResize1.Copy
MsgBox "rngAnchor : " & rngAnchor.Address & _
" ( " & rngAnchor.Rows.Count & " x " & _
rngAnchor.Columns.Count & " )" & vbCrLf & _
"menjadi" & vbCrLf & _
"rngResize1 : " & rngResize1.Address & _
" ( " & rngResize1.Rows.Count & " x " & _
rngResize1.Columns.Count & " )" & vbCrLf & _
"yaitu : rngAnchor.Resize(1, 15)"
Baris-baris kode diatas akan diproses sebagai berikut :
- Proses pertama adalah dari rngAnchor, dilakukan Resize (pengubahan luasan) menjadi berdimensi 1 baris x 15 kolom oleh bagian :
- kemudian disimpan ke variabel rngResize1 pada bagian :
- di baris berbunyi :
- Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngResize1 ke Clipboard oleh baris kode :
- Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngAnchor sebagai range asalnya dan rngResize1 sebagai range penyimpan hasil pengubahan luasan, disertai dimensi masing-masing
Contoh 2 : Perluas rngResize1 (hasil contoh 1) menjadi 17 baris x 3 kolom
'---------------------------------------------
'Perluas rngResize1 menjadi 17 baris x 3 kolom
'---------------------------------------------
'dari rngResize1 diperluas menjadi 17 x 3
'disimpan dalam variabel rngResize2
Set rngResize2 = rngResize1.Resize(17, 3)
rngResize2.Copy
MsgBox "rngResize1 : " & rngResize1.Address & _
" ( " & rngResize1.Rows.Count & " x " & _
rngResize1.Columns.Count & " )" & vbCrLf & _
"menjadi" & vbCrLf & _
"rngResize2 : " & rngResize2.Address & _
" ( " & rngResize2.Rows.Count & " x " & _
rngResize2.Columns.Count & " )" & vbCrLf & _
"yaitu : rngResize1.Resize(17, 3)"
Baris-baris kode diatas akan diproses sebagai berikut :
- Proses pertama adalah dari rngResize1, hasil contoh 1, dilakukan Resize (pengubahan luasan) menjadi berdimensi 17 baris x 3 kolom oleh bagian :
- kemudian disimpan ke variabel rngResize2 pada bagian :
- di baris berbunyi :
- Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngResize2 ke Clipboard oleh baris kode :
- Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngResize1 sebagai range asalnya dan rngResize2 sebagai penyimpan hasil pengubahan luasan, disertai dimensi masing-masing
Contoh 3 : Perluas rngResize2 (hasil contoh 2) menjadi baris yang berjumlah tetap x 9 kolom
'---------------------------------------------------------------
'Perluas rngResize2 menjadi baris yang berjumlah tetap x 9 kolom
'---------------------------------------------------------------
'dari rngResize2 diperluas menjadi berjumlah baris tetap x 9 kolom
'disimpan dalam variabel rngResize3
Set rngResize3 = rngResize2.Resize(, 9)
rngResize3.Copy
MsgBox "rngResize2 : " & rngResize2.Address & _
" ( " & rngResize2.Rows.Count & " x " & _
rngResize2.Columns.Count & " )" & vbCrLf & _
"menjadi" & vbCrLf & _
"rngResize3 : " & rngResize3.Address & _
" ( " & rngResize3.Rows.Count & " x " & _
rngResize3.Columns.Count & " )" & vbCrLf & _
"yaitu : rngResize2.Resize(, 9)"
Baris-baris kode diatas akan diproses sebagai berikut :
- Proses pertama adalah dari rngResize2, hasil contoh 2, dilakukan Resize (pengubahan luasan) menjadi berdimensi tetap seperti jumlah baris range asalnya (rngResize2) x 9 kolom. Karena jumlah baris yang baru adalah tetap seperti jumlah baris range asalnya, maka bagian barisnya bisa dikosongkan. Proses ini dilakukan oleh bagian :
- kemudian disimpan ke variabel rngResize2 pada bagian :
- di baris berbunyi :
- Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngResize3 ke Clipboard oleh baris kode :
- Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngResize2 sebagai range asalnya dan rngResize3 sebagai penyimpan hasil pengubahan luasan, disertai dimensi masing-masing
Contoh 4 : Mengubah luasan diri sendiri si rngAnchor menjadi 3 baris x 4 kolom
'---------------------------------------------------
'Mengubah luasan rngAnchor menjadi 3 baris x 4 kolom
'---------------------------------------------------
'dari rngAnchor diperluas menjadi 3 x 4
'disimpan pada dirinya sendiri (rngAnchor juga)
Set rngAnchor = rngAnchor.Resize(3, 4)
rngAnchor.Copy
MsgBox "rngAnchor (awal) : " & Range(sAlamatAnchor).Address & _
" ( " & Range(sAlamatAnchor).Rows.Count & " x " & _
Range(sAlamatAnchor).Columns.Count & " )" & vbCrLf & _
"menjadi" & vbCrLf & _
"rngAnchor (baru) : " & rngAnchor.Address & _
" ( " & rngAnchor.Rows.Count & " x " & _
rngAnchor.Columns.Count & " )" & vbCrLf & _
"yaitu : rngAnchor.Resize(3, 4)"
Baris-baris kode diatas akan diproses sebagai berikut :
- Proses pertama adalah dari rngAnchor, dilakukan Resize (pengubahan luasan) menjadi berdimensi 3 baris x 4 kolom oleh bagian :
- kemudian disimpan ke dirinya sendiri, yaitu variabel rngAnchor pada bagian :
- di baris berbunyi :
- Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngAnchor yang sudah memiliki luasan baru ke Clipboard oleh baris kode :
- Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngAnchor yang masih berisi range dengan luasan yang lama (sebagai range asalnya) dan rngAnchor yang sudah berisi range dengan luasan yang baru (sebagai penyimpan hasil pengubahan luasan), disertai dimensi masing-masing
Bagaimana jika dicoba untuk alamat range asal yang disimpan pada variabel sAlamatAnchor yang berupa teks berbunyi A1 di baris kode :
sAlamatAnchor = "A1"
diganti menjadi B2:E6
Memanfaatkan OFFSET dan RESIZE secara berurutan
Properti OFFSET dan RESIZE dapat digunakan secara berurutan. Proses OFFSET bisa diletakkan sebelum proses RESIZE ataupun sebaliknya. Syntax :object_range _
.OFFSET( [jarak_baris] , [jarak_kolom] ) _
.RESIZE( [jumlah_baris] , [jumlah_kolom] )
atau
object_range _
.RESIZE( [jumlah_baris] , [jumlah_kolom] ) _
.OFFSET( [jarak_baris] , [jarak_kolom] )
Pada syntax berbunyi :
object_range _
.OFFSET( [jarak_baris] , [jarak_kolom] ) _
.RESIZE( [jumlah_baris] , [jumlah_kolom] )
sebenarnya berisi 2 buah proses, yaitu object_range di-OFFSET dan kemudian hasilnya di-RESIZE. Jika proses pertama disimpan dalam variabel range bernama rngHasil1 dan hasil akhir disimpan dalam variabel range bernama rngHasilAkhir, maka prosesnya akan setara dengan :
Set rngHasil1 = object_range.OFFSET( [jarak_baris] , [jarak_kolom] )
Set rngHasilAkhir = rngHasil1.RESIZE( [jumlah_baris] , [jumlah_kolom] )
begitu juga pada syntax berbunyi :
object_range _
.RESIZE( [jumlah_baris] , [jumlah_kolom] ) _
.OFFSET( [jarak_baris] , [jarak_kolom] )
sebenarnya berisi 2 buah proses, yaitu object_range di-RESIZE dan kemudian hasilnya di-OFFSET. Jika proses pertama disimpan dalam variabel range bernama rngHasil1 dan hasil akhir disimpan dalam variabel range bernama rngHasilAkhir, maka prosesnya akan setara dengan :
set rngHasil1 = object_range.RESIZE( [jumlah_baris] , [jumlah_kolom] )
set rngHasilAkhir = rngHasil1.OFFSET( [jarak_baris] , [jarak_kolom] )
Contohnya adalah untuk mendapatkan area range (lihat gambar data diatas sana) record untuk kolom negara dan port, yang beralamat di D2:E12, dari range A1. Prosedur untuk proses ini bisa berbunyi :
Public Sub TentangOffsetDanResizeBerurutan()
Dim rng As Range
'dari A1,
'proses 1 -> lompat (OFFSET) ke D2 dari A1 yang berarti
'lompat 1 baris kebawah dan 3 kolom kekanan dari A1
'proses 2 -> perluas (RESIZE) area di D2
'menjadi 11 baris x 2 kolom
Set rng = Range("A1").Offset(1, 3).Resize(11, 2)
rng.Copy
MsgBox "Range hasil : " & rng.Address & " ( " & _
rng.Rows.Count & " x " & rng.Columns.Count & ") " & _
vbCrLf & "yaitu : Range(""A1"").Offset(3, 1).Resize(11, 2)"
End Sub
Proses yang terjadi pada prosedur diatas adalah :
- Dari range asal di range A1,yaitu pada bagian Range("A1")
- dilakukan OFFSET untuk mencapai D2, yaitu sebanyak jarak 1 baris keBAWAH dan jarak 3 kolom keKANAN, pada bagian .Offset(1, 3)
- yang hasil dari OFFSET tersebut kemudian di-RESIZE dari berdimensi 1 baris x 1 kolom menjadi berdimensi baru 11 baris x 2 kolom, pada bagian .Resize(11, 2)
- dilanjutkan dengan menyimpan hasil RESIZE terhadap hasil OFFSET dari range asal tersebut ke variabel range bernama rng, pada bagian Set rng =
- dengan bentuk akhir baris kode semua proses tersebut berbunyi :
- Proses dilanjutkan dengan meng-Copy range dalam variabel rng ke Clipboard oleh baris kode :
- Proses diakhiri dengan menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi alamat hasil akhir proses disertai dimensinya
Simpulan
Beberapa hal yang penting dari contoh-contoh diatas adalah :- Properti Offset :
- adalah untuk mendapatkan suatu range dengan luasan yang sama dengan range asalnya dijarak tertentu dari sisi baris dan kolomnya.
- tidak pernah mengubah luasan. Artinya, luasan hasil Offset selalu sama dengan luasan range asalnya.
- bisa diberi nilai negatif, nol, atau positif. Jika dikosongkan, maka akan dianggap bernilai 0.
- sisi baris negatif berarti keATAS, positif berarti keBAWAH, dan nol adalah TETAP pada baris range asal
- sisi kolom negatif berarti keKIRI, positif berarti keKANAN, dan nol adalah TETAP pada kolom range asal
- jarak baris maksimum adalah sebanyak baris tersisa setelah baris range asal dikurangi jumlah baris range asal ditambah 1, ketika diberi nilai positif
- jarak baris minimum adalah sebanyak baris yang ada sebelum baris range asal ketika diberi nilai negatif
- jarak kolom maksimum adalah sebanyak kolom tersisa setelah kolom range asal dikurangi jumlah kolom range asal ditambah 1, ketika diberi nilai positif
- jarak kolom minimum adalah sebanyak kolom yang ada sebelum kolom range asal ketika diberi nilai negatif
- Properti Resize :
- adalah untuk mendapatkan suatu range dengan luasan yang baru dengan mengubah nilai-nilai dimensi baris dan kolom dari range asalnya.
- tidak pernah berpindah alamat pojok kiri atasnya.
- hanya bisa menerima nilai positif atau selalu lebih dari nol (>0) alias minimal 1 (satu).
- jika nilai jumlah baris baru atau nilai jumlah kolom baru dikosongkan, maka akan dianggap sama dengan dimensi sisi tersebut milik range asalnya.
- arah perluasan selalu keKANAN atau keBAWAH karena hanya bisa menerima nilai positif saja.
- jumlah baris baru maksimum adalah sebanyak baris tersisa setelahnya dari baris range asal
- jumlah kolom baru maksimum adalah sebanyak kolom tersisa setelahnya mulai dari kolom range asal
- Properti Offset dan Resize :
- bekerja pada range utama adalah range pojok kiri atas dari range asalnya atau range yang diproses.
bisa digunakan berurutan yang harus memperhatikan batas-batas maksimal atau minimal setiap nilai yang di-input-kan ke bagian [jarak_baris], [jarak_kolom], [jumlah_baris], [jumlah_kolom] relatif terhadap range asal atau hasil OFFSET maupun RESIZE sebelumnya. - penggunaan Offset dan Resize pada range asal yang berupa 1 (satu) buah range secara berurutan mirip dengan built-in function Offset dalam worksheet.
:)
Insya Allah pembahasan selanjutnya adalah tentang properti END
Tidak ada komentar:
Posting Komentar