BelajarVBA 011 - Range 06 (Range ke Range, CurrentRegion)

Coretan Mr. Kid

Rajin pangkal pandai

Pembahasan kali ini adalah tentang merujuk suatu area range dari 2 (dua) object range dan properti CurrentRegion milik Range. File berisi data yang bisa digunakan untuk uji coba pada pembahasan kali ini adalah BelajarVBA011_06.xlsm.

Area range berasal dari 2 (dua) range

Sebuah area range dibentuk dengan diketahui 2 (dua) range, yaitu 2 (dua) range pojok yang berseberangan diagonal. Misalnya, area range A1:C5 bisa dibentuk dengan mengetahui range A1 dan C5 atau range C1 dan A5.

Syntax : Range( object_range1 , object_range2 )

Prosedur berikut ini adalah contohnya.
Public Sub TentangRangeToRange()
    Dim rng1 As Range, rng2 As Range, rngHasil As Range
   
    '>> Contoh 1 : Area A1:C5 diketahui A1 dan C5
    Set rng1 = Range("A1")
    Set rng2 = Range("C5")
    Set rngHasil = Range( rng1 , rng2 )
    rngHasil.Copy
    MsgBox "rng1 : " & rng1.Address & vbCrLf & _
            "rng2 : " & rng2.Address & vbCrLf & _
            "rngHasil dibentuk dengan Range( rng1 , rng2 ) : " _
            & rngHasil.Address
           
    '>> Contoh 2 : Area A1:C5 diketahui C1 dan A5
    Set rng1 = Range("C1")
    Set rng2 = Range("A5")
    Set rngHasil = Range( rng1 , rng2 )
    rngHasil.Copy
    MsgBox "rng1 : " & rng1.Address & vbCrLf & _
            "rng2 : " & rng2.Address & vbCrLf & _
            "rngHasil dibentuk dengan Range( rng1 , rng2 ) : " _
            & rngHasil.Address
End Sub

Pada prosedur diatas, contoh 1 yang akan membentuk area range A1:C5 dengan diketahui range A1 dan C5. Proses yang terjadi pada bagian tersebut adalah :

• Deklarasi variabel object range untuk rng1, rng2, dan rngHasil
• Definisi isi rng1 yang diisi dengan range A1, sedangkan rng2 diisi dengan range C5
• Kemudian rngHasil didefinisikan sebagai hasil penyusunan area range dari rng1 sampai rng2 oleh baris kode :

     Set rngHasil = Range( rng1 , rng2 )

• Proses Copy adalah contoh penggunaan rngHasil dan pesan kepada user ditampilkan menggunakan MsgBox

Pada bagian contoh 2 juga mirip dengan contoh 1. Perbedaannya adalah range yang menjadi penyusun area. Pada contoh 1 diketahui range pojok kiri atas dan pojok kanan bawah, sedangkan pada contoh 2 adalah range pojok kanan atas dan range pojok kiri bawah. Penggunaan object range seperti ini bisa digunakan untuk membentuk area range sebaris, sekolom, ataupun n baris x m kolom. Setiap object range yang menjadi penyusunnya (misal rng1 dan rng2 pada contoh diatas) dapat didefinisikan dengan banyak cara, seperti menggunakan properti End atau alamat range secara langsung dan sebagainya.

Properti CurrentRegion

Properti ini digunakan untuk mendapatkan 1 (satu) area range dari sebuah range yang menjadi titik awalnya. Penggunaan properti ini adalah seperti ketika di worksheet melakukan Goto Special dan memilih CurrentRegion (tekan CTRL G -> tekan Special -> pilih CurrentRegion -> OK).

Syntax : object_range.CurrentRegion

Contoh prosedur berikut ini bisa memberi sedikit gambaran tentang penggunaan properti CurrentRegion. Contoh ini menggunakan data yang berada di sheet bernama 'Data16'. Contoh ini berupa penggunaan CurrentRegion dengan range titik awalnya di range A1 untuk mendapatkan seluruh area tabel data di sheet tersebut.
Public Sub TentangCurrentRegion()
    Dim rngAwal As Range, rngHasil As Range
   
    Sheets("Data16").Activate
    Set rngAwal = Sheets("Data16").Range("A1")
    Set rngHasil = rngAwal.CurrentRegion
    rngHasil.Copy
    MsgBox "Range titik awal : " & rngAwal.Address & vbCrLf & _
       "Range(""" & rngAwal.Address & """).CurrentRegion : " & _
       rngHasil.Address
End Sub
Pada prosedur diatas, proses yang terjadi adalah :

• Deklarasi variabel object range bernama rngAwal dan rngHasil
• Mengaktifkan sheet bernama 'Data16' pada baris kode :
• Sheets("Data16").Activate
• Mendefinisikan variabel rngAwal dengan range A1
• Memanfaatkan properti CurrentRegion milik variabel rngAwal dan hasilnya disimpan divariabel rngHasil
• Range yang ada dalam rngHasil di-Copy ke Clipboard, kemudian menampilkan pesan kepada user melalui kotak pesan

Cara Excel membentuk sebuah area range

Properti CurrentRegion dalam membentuk area range mengikuti cara Excel dalam membentuk area range. Berikut ini adalah gambar untuk menjelaskan hal ini. Warna kuning adalah cell aktif dan warna merah adalah cells yang ada disekitar cell aktif (kuning) yang berdampingan secara kontinyu.

• Ketika properti CurrentRegion dari cell aktif (kuning) yang berisi data dimanfaatkan, maka area range hasil akan tergantung cells merah yang berisi data. Seluruh cell merah yang berisi data akan diblok. Dirinya (cell aktif) yang berisi data juga akan terblok. Kemudian dibuatlah kotak area dari seluruh cell pojok terluar yang terblok. Hasilnya, pasti cell aktif akan berada dalam area range hasil.
• Ketika cell aktif (kuning) tidak berisi data, maka yang terblok adalah seluruh cell merah yang berisi data saja, sedangkan dirinya (cell aktif) tidak ikut terblok. Kemudian dibuatlah kotak area dari seluruh cell pojok terluar yang terblok. Akibatnya, cell aktif belum tentu berada dalam area range hasil. Misalnya saja, ketika yang terblok hanyalah cell merah di pojok kiri atas dan kiri bawah saja, maka area range hasil adalah range satu kolom dikiri cell aktif dan cell aktif tidak masuk dalam area range hasil tersebut.

Cell berisi data adalah cells yang berisi suatu nilai tertentu atau suatu formula. Beberapa karakter tidak akan tampak dalam sebuah cell seperti karakter kosong (NULLSTRING), karakter ENTER, karakter TAB, dan sebagainya. Bisa jadi area range yang dibentuk oleh Excel akan tampak tidak seperti yang dijabarkan di atas, yaitu beberapa cell yang tampak tidak ada isinya turut menjadi bagian dari area range yang terbentuk. Hal seperti ini umumnya terjadi karena ada cell yang berisi data :

• karakter-karakter yang tidak tampak
• berisi suatu formula yang menghasilkan karakter tidak tampak
• di-format number custom tertentu (termasuk melalui Conditional Formatting)

Beberapa bentuk dataset dan pengaruhnya pada hasil CurrentRegion

Susunan data dalam dataset akan mempengaruhi hasil CurrentRegion dari suatu range titik awal. Seringkali ditemui susunan dataset memiliki blank cells. Susunan blank cells yang membuat sebuah baris kosong atau kolom kosong bisa membuat hasil CurrentRegion menjadi tidak sesuai dengan yang dibutuhkan ketika tidak tepat menentukan range titik awalnya. Bahkan bisa membuat dataset menjadi tidak memungkinkan untuk bisa didapatkan area range-nya melalui properti CurrentRegion akibat kehadiran blank cells sebaris atau sekolom tersebut.

Berikut ini adalah beberapa contoh pengaruh blank cells yang ada dalam dataset yang sering ditemui. Hasil CurrentRegion berupa area yang dibatasi garis batas (border) warna hitam yang tebal. Warna kuning didalam area hasil CurrentRegion adalah range yang bisa dijadikan titik awal untuk mendapatkan area range tersebut. Jadi range titik awal bisa dipilih salah satu dari cell yang berwarna kuning tersebut.

Dataset dengan record dan kolom yang rapat

Sebuah dataset yang memiliki kaidah penyusunan tabel data yang baik. Seluruh record data adalah rapat atau tidak ada baris kosong diantara 2 (dua) buah record yang berisi data. Kolom data juga rapat atau tidak ada kolom kosong antara 2 (dua) buah kolom yang berisi data. Setiap kolom memiliki nama kolom (header). Header diletakkan cukup pada 1 (satu) baris Excel saja. Record pertama dimulai setelah baris header atau tidak ada baris kosong antara baris header dengan record pertama.

Pada dataset seperti digambar, dimanapun letak range titik awal, selama didalam dataset, akan menghasilkan area range hasil CurrentRegion berupa seluruh dataset. Dataset 1.1 adalah sebuah tabel data yang tersusun secara rapat. Blank cells pada dataset 1.1 ini umumnya karena memang field tersebut tidak memiliki nilai. Jadi, arti setiap blank cells pada dataset 1.1 adalah 'tidak ada nilainya'. Dataset 1.2 adalah sebuah crosstab data yang tersusun secara rapat seperti dataset 1.1. Arti blank cells pada dataset 1.2 berarti 'memiliki nilai yang sama dengan baris data sebelumnya'.

Blank cells pada dataset 1.1 maupun dataset 1.2 tidak akan mempengaruhi hasil penggunaan properti CurrentRegion. karena minimal ada satu kolom yang pasti penuh terisi data. Kolom utama pada dataset 1.1 pasti berisi data diseluruh record-nya. Jadi selalu ada setidaknya 1 (satu) kolom yang berisi data penuh diseluruh record-nya. Pada dataset 1.2 tidak ada baris kosong dan data yang tidak memiliki nilai akan diisi dengan karakter kosong atau teks '(blank)' karena arti blank cells pada dataset 1.2 adalah 'memiliki nilai yang sama dengan baris data sebelumnya'.

Satu baris kosong antara header dan record data

Dataset 2 (yang ditampilkan menjadi dataset 2.1 dan dataset 2.2) memiliki sebuah baris kosong antara header dan record data. Satu baris kosong ini akan membuat dataset terpisah menjadi 2 (dua) area, yaitu area baris header (area diatas baris kosong) dan area record data (area dibawah baris kosong). Kehadiran satu baris kosong ini tidak sepenuhnya memberi masalah pada penggunaan CurrentRegion. Penggunaan properti CurrentRegion akan bermasalah ketika ada lebih dari satu baris kosong. Meskipun demikian, pemilihan range titik awal pada dataset seperti ini harus lebih diperhatikan, yaitu :

• Ketika range titik awal terletak disalah satu field dalam record data (kuning), maka area range hasil CurrentRegion adalah pada area yang dibatasi oleh garis batas (border) warna hitam tebal (lihat Dataset 2.1).
• Ketika range titik awal diletakkan pada salah satu cell di baris header, maka area range hasil CurrentRegion adalah baris header (bayangkan di Dataset 2.1).
• Ketika range titik awal diletakkan pada salah satu cell dibaris kosong, maka area range hasil CurrentRegion adalah seluruh dataset (lihat Dataset 2.2).

Andaikan yang ada adalah satu kolom kosong, maka kehadiran satu kolom kosong tersebut adalah sebagai pembentuk dua area data, yaitu area data disisi kiri kolom kosong dan area data disisi kanan kolom kosong. Jika range titik awal adalah disalah satu cell disisi kiri kolom kosong, maka area range hasil CurrentRegion adalah area data disisi kiri kolom kosong. Begitu juga jika range titik awal dipilih disalah satu cell disisi kanan kolom kosong, akan menghasilkan area range hasil CurrentRegion berupa area data disisi kanan kolom kosong. Ketika range titik awal dipilih pada salah satu cell di kolom kosongnya, maka akan diperoleh area range hasil CurrentRegion adalah seluruh dataset. Kehadiran dua atau lebih kolom kosong akan menyulitkan penggunaan properti CurrentRegion.

Lebih dari satu baris kosong

Dataset yang berisi banyak baris kosong, apalagi yang tersebar (tidak terkumpul menjadi satu area), akan menyulitkan penggunaan properti CurrentRegion. Pada dataset seperti ini sebaiknya menghindari penggunaan properti CurrentRegion. Seperti pada gambar diatas, penggunaan properti CurrentRegion tidak dapat menghasilkan area range hasil berisi seluruh dataset. Jika ingin mendapatkan area range diseluruh dataset, dibutuhkan kolom bantu (kolom buatan yang ditambahkan) yang berisi data diseluruh record dataset.

Dampak karakter tidak tampak atau formula bernilai karakter kosong

Kadangkala ditemui suatu dataset dengan banyak baris kosong maupun kolom kosong yang tersebar. Ketika properti CurrentRegion digunakan pada dataset tersebut, area range yang dihasilkan adalah diseluruh area dataset. Padahal tidak ada penambahan kolom bantu berisi data diseluruh record. Contohnya seperti gambar berikut ini :

Dataset 4 (gambar diatas) mirip dengan dataset 3, tetapi pada dataset 4 ini, dimanapun range titik awal pilihan dalam dataset, akan menghasilkan area range diseluruh dataset yang dibatasi oleh garis batas (border) hitam tebal. Padahal pada daset 4 ini tampak adanya baris kosong antar record data, yang semestinya menjadi pemisah antar area dalam dataset. Ketika ditemukan kejadian seperti ini, maka dugaan pertama adalah adanya karakter kosong hasil formula atau adanya karakter tidak tampak pada baris-baris kosong tersebut. Cara memeriksanya adalah dengan mewarnai seluruh range yang blank. Dengan demikian akan ditemukan keberadaan range yang tampak kosong tetapi tidak turut terwarnai seperti blank cell.

Cara untuk memilih blank cell pada suatu area range adalah sebagai berikut : (bisa dicoba pada file BelajarVBA011_06.xlsm disheet bernama 'AreaRange')

• Blok seluruh range dataset
• Aktifkan fitur Goto (tekan CTRL G)
• Aktifkan opsi Special (tekan tombol Special)
• Pilih opsi Blank
• Tekan OK

Blank cells yang terpilih bisa diberi warna tertentu, misalnya merah. Seluruh cell yang tampak kosong tapi bukan berwarna merah adalah cell yang berisi formula yang menghasilkan karakter kosong atau berisi karakter tidak tampak.

Simpulan

Penggunaan properti CurrentRegion bisa mendapatkan seluruh area range dataset berdasar sebuah range yang ada dalam dataset. Properti CurrentRegion memang rentan terhadap kehadiran baris kosong maupun kolom kosong. Karena data dasar (data yang disimpan dan dipelihara, seperti tabel database) tidak pernah berisi baris kosong atau kolom kolom kosong, maka properti CurrentRegion sangat bermanfaat dalam mendapatkan seluruh area range tabel data.

:)

Insya Allah pembahasan berikutnya adalah tentang memasang formula melalui VBA.

<previous | next>

BelajarVBA 011 - Range 05 (Properti End)

Coretan Mr. Kid

Merasa diri kita memiliki kemampuan yang lebih tinggi dari orang lain adalah awal kerusakan karakter budi pekerti yang baik.

Pembahasan kali ini adalah tentang mendapatkan sebuah range dibatas terluar tertentu dengan memanfatkan properti End milik object Range. Pembahasan kali ini akan menggunakan data di sheet bernama 'Data16' dan 'Data15' pada file BelajarVBA011_05.xlsm, yang juga dapat dijadikan sebagai media untuk mempraktekkan langsung script yang ada.

Kebutuhan untuk segera menuju ke kolom terakhir di baris terakhir suatu dataset dari suatu range tertentu dapat dilakukan dengan memanfaatkan properti End milik object Range. Properti End akan mendapatkan 1 (satu) buah range yang terakhir pada batas area arah pergerakannya. Penggunaan properti END sama halnya dengan penekanan tombol CTRL yang diikuti penekanan arah panah pada keyboard saat berada di Workbook window (berada di worksheet).


Syntax :
object_range.END(arah_pergerakan)

Properti End memiliki 4 (empat) arah pergerakan, yaitu :
(Lihat gambar berikut. Range merah adalah range pojok kiri atas dari area range yang menjadi range asal)

• xlDown
  bergerak kearah bawah (baris selanjutnya) dari range pojok kiri suatu area range dengan area pergerakan sebagai sebuah range N baris x 1 kolom.
• xlUp
  bergerak kearah atas (baris sebelumnya) dari range pojok kiri suatu area range dengan area pergerakan sebagai sebuah range N baris x 1 kolom.
• xlToRight
  bergerak kearah kanan (kolom selanjutnya) dari range pojok kiri suatu area range dengan area pergerakan sebagai sebuah range 1 baris x N kolom.
• xlToLeft
  bergerak kearah kiri (kolom sebelumnya) dari range pojok kiri suatu area range dengan area pergerakan sebagai sebuah range 1 baris x N kolom.

Properti End akan bergerak dalam arah range sebaris atau range sekolom pada worksheet tersebut. Batas maksimum pergerakan End adalah sampai batas worksheet, yaitu :

• baris terakhir Excel sebagai batas terbawah
• baris 1 Excel sebagai batas teratas
• kolom terakhir Excel sebagai batas terkanan
• kolom 1 Excel sebagai batas terkiri

Karakteristik pergerakan properti End adalah sebagai berikut :
(berlaku untuk seluruh arah pergerakan, contoh pada gambar adalah arah dengan xlToLeft)

1. Melintasi blank cells sampai batas worksheet (tanpa ada satupun cell berisi data setelah range asal)
   
   
   • Terjadi ketika digunakan mulai dari sebuah range asal, baik berisi data (Case 1.1) maupun blank (Case 1.2), kemudian range selanjutnya adalah blank cells dan tidak pernah bertemu satupun range berisi data sampai batas worksheet.
   • Range asal bisa berisi data seperti Case 1.1 ataupun berupa blank cell seperti Case 1.2
   • Hasil pergerakan End adalah pada range yang berada dibatas worksheet. Seperti pada gambar yang menggunakan xlToLeft, maka hasil pergerakan End adalah pada range yang berada dikolom pertama Excel (kolom A) dibaris range asalnya.
   • Jika area lintasan yang berupa blank cells tersebut dirapatkan hingga tidak ada lintasannya lagi alias range asal berada di kolom A, maka pergerakan End tidak mengalami pergeseran karena lokasi range hasil adalah pada range batas worksheet yang dalam hal ini juga di kolom A.


2. Melintasi blank cells sampai suatu range yang ada isinya
   
   
   • Terjadi ketika digunakan mulai dari sebuah range asal, baik berisi data (Case 1.1) maupun blank (Case 1.2), kemudian range selanjutnya adalah blank cells sampai bertemu 1 (satu) range yang berisi data
   • Range asal bisa berisi data seperti Case 2.1 ataupun berupa blank cell seperti Case 2.2
   • Hasil pergerakan End adalah pada range yang berisi data setelah range asal. Seperti pada gambar yang menggunakan xlToLeft, maka hasil pergerakan End adalah pada 1 (satu) range yang berisi data berupa teks 'Kid' sesuai baris range asal masing-masing Case
   • Jika area lintasan yang berupa blank cells tersebut dirapatkan hingga tidak ada lintasannya lagi alias range asal berdampingan dengan range berisi data setelah range asal diarah pergerakannya, maka pergerakan End pada :
     1. range asal yang berupa blank cell (Case 2.2) akan menghasilkan range hasil di range berisi data diarah pergerakan setelah range asal alias sesuai kaidah Case 2.2 tersebut
     2. range asal yang berisi data (Case 2.1) akan mengikuti kaidah Case 3 yang dijelaskan berikutnya


3. Bergerak disuatu range yang padat berisi data
   
   
   • Terjadi ketika digunakan mulai dari sebuah range asal yang berisi data dengan range selanjutnya juga berisi data
   • Seluruh range berisi data, yaitu mulai dari range asal sampai range tertentu yang bersinggungan (berdampingan) searah pergerakan
   • Hasil pergerakan adalah pada range terakhir diarah pergerakan yang berisi data. Seperti pada gambar yang menggunakan xlToLeft, maka hasil pergerakan End adalah pada range terakhir dari area range berisi data yang ada, yaitu pada cell yang berisi data teks berbunyi 'Kid'

Karakteristik yang dijabarkan diatas adalah hal-hal yang perlu diwaspadai dalam penggunaan properti End. Dengan demikian resiko dari pergerakan End berdasar struktur data atau pola data yang akan diolah sudah dapat diketahui.

Contoh-contoh berikut ini akan menggunakan data di sheet bernama 'Data16'. Pada sheet tersebut ada sebuah tabel berkaidah database pada area range A1:H12. Header tabel berada di baris 1 Excel dan hanya 1 (satu) baris saja. Record data dimulai dari baris 2 Excel. Kolom-kolom kunci milik tabel adalah pada kolom A sampai kolom E, yang seluruhnya harus berisi data. Jadi, sheet 'Data16' berisi tabel yang padat berisi. Sheet 'Data16' tersebut adalah seperti gambar berikut ini :

Contoh 1 : Bergerak dalam area data

Penggunaan properti End pada sheet 'Data16' dengan range asal adalah range E1 akan diletakkan pada prosedur berikut ini :
Public Sub TentangEnd_Contoh1()
    Dim sht As Worksheet
    Dim rngAsal As Range, rngHasil As Range
   
    'pendefinisian sheet kerja,
    'disimpan dalam variabel sht
    'dan sheet diaktifkan (activate)
    Set sht = Sheets("Data16")
    sht.Activate
   
    'baris-baris kode akan diletakkan setelah baris ini

End Sub
Variabel object Worksheet bernama sht telah didefinisikan berisi sheets 'Data16' pada baris berbunyi :
Set sht = Sheets("Data16")

• Penggunaan xlDown (ke BAWAH)
     Set rngAsal = sht.Range("E1")
     Set rngHasil = rngAsal.End(xlDown)
     rngHasil.Copy
     MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
        "Arah : xlDown" & vbCrLf & _
        "Hasil : " & rngHasil.Address
  Proses pada potongan script diatas adalah :
  • Mendefinisikan variabel rngAsal agar berisi range E1 yang ada di sheet yang tersimpan dalam variabel object worksheet bernama sht.
  • Mendefinisikan variabel rngHasil yang berupa hasil suatu proses penggunaan properti End dengan arah xlDown yang dimulai dari rngAsal. Variabel rngAsal berisi range E1. Jadi properti End digunakan dari range E1 [rngAsal] ke BAWAH [.End(xlDown)]. Range hasil penggunaan properti End akan berisi 1 (satu) cell beralamat di range E12 dan disimpan ke dalam variabel bernama rngHasil. Proses ini adalah pada baris kode :
    Set rngHasil = rngAsal.End(xlDown)
  • Contoh pemanfaatan hasil penggunaan properti End, yang dalam hal ini berupa proses meng-Copy ke Clipboard.
  • Menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi alamat range asal, arah pergerakan End, dan alamat range hasil.


• Penggunaan xlToLeft (ke KIRI)
  Setelah proses menggunakan xlDown diatas, proses dilanjutkan dengan menggunakan xlToLeft. Jadi, range asal adalah range hasil proses sebelumnya yang bernama rngHasil yang berisi range E12.
     Set rngAsal = rngHasil
     Set rngHasil = rngAsal.End(xlToLeft)
     rngHasil.Copy
     MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
        "Arah : xlToLeft" & vbCrLf & _
        "Hasil : " & rngHasil.Address
  Proses pada potongan script diatas adalah :
  • Mendefinisikan variabel rngAsal agar berisi range yang disimpan oleh variabel rngHasil. Pada contoh ini, rngHasil berisi range hasil proses pada penggunaan xlDown yang berupa range E12. Jadi, rngAsal akan berisi range E12.
  • Mendefinisikan variabel rngHasil yang berupa hasil suatu proses penggunaan properti End dengan arah xlToLeft yang dimulai dari rngAsal. Variabel rngAsal berisi range E12. Jadi properti End digunakan dari range E12 [rngAsal] ke KIRI [.End(xlToLeft)]. Range hasil penggunaan properti End akan berisi 1 (satu) cell beralamat di range A12 dan disimpan ke dalam variabel bernama rngHasil. Proses ini adalah pada baris kode :
    Set rngHasil = rngAsal.End(xlToLeft)
  • Contoh pemanfaatan hasil penggunaan properti End, yang dalam hal ini berupa proses meng-Copy ke Clipboard.
  • Menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi alamat range asal, arah pergerakan End, dan alamat range hasil.


• Penggunaan xlUp (ke ATAS)
  Setelah proses menggunakan xlToLeft diatas, proses dilanjutkan dengan menggunakan xlUp. Jadi, range asal adalah range hasil proses sebelumnya yang bernama rngHasil yang berisi range A12.
     Set rngAsal = rngHasil
     Set rngHasil = rngAsal.End(xlUp)
     rngHasil.Copy
     MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
        "Arah : xlUp" & vbCrLf & _
        "Hasil : " & rngHasil.Address
  Proses pada potongan script diatas adalah :
  • Mendefinisikan variabel rngAsal agar berisi range yang disimpan oleh variabel rngHasil. Pada contoh ini, rngHasil berisi range hasil proses pada penggunaan xlToLeft yang berupa range A12. Jadi, rngAsal akan berisi range A12.
  • Mendefinisikan variabel rngHasil yang berupa hasil suatu proses penggunaan properti End dengan arah xlUp yang dimulai dari rngAsal. Variabel rngAsal berisi range A12. Jadi properti End digunakan dari range A12 [rngAsal] ke ATAS [.End(xlUp)]. Range hasil penggunaan properti End akan berisi 1 (satu) cell beralamat di range A1 dan disimpan ke dalam variabel bernama rngHasil. Proses ini adalah pada baris kode :
    Set rngHasil = rngAsal.End(xlUp)
  • Contoh pemanfaatan hasil penggunaan properti End, yang dalam hal ini berupa proses meng-Copy ke Clipboard.
  • Menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi alamat range asal, arah pergerakan End, dan alamat range hasil.


• Penggunaan xlToRight (ke KANAN)
  Setelah proses menggunakan xlUp diatas, proses dilanjutkan dengan menggunakan xlToRight. Jadi, range asal adalah range hasil proses sebelumnya yang bernama rngHasil yang berisi range A1.
     Set rngAsal = rngHasil
     Set rngHasil = rngAsal.End(xlToRight)
     rngHasil.Copy
     MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
         "Arah : xlToRight" & vbCrLf & _
         "Hasil : " & rngHasil.Address
  Proses pada potongan script diatas adalah :
  • Mendefinisikan variabel rngAsal agar berisi range yang disimpan oleh variabel rngHasil. Pada contoh ini, rngHasil berisi range hasil proses pada penggunaan xlUp yang berupa range A1. Jadi, rngAsal akan berisi range A1.
  • Mendefinisikan variabel rngHasil yang berupa hasil suatu proses penggunaan properti End dengan arah xlToRight yang dimulai dari rngAsal. Variabel rngAsal berisi range A1. Jadi properti End digunakan dari range A1 [rngAsal] ke KANAN [.End(xlToRight)]. Range hasil penggunaan properti End akan berisi 1 (satu) cell beralamat di range H1 dan disimpan ke dalam variabel bernama rngHasil. Proses ini adalah pada baris kode :
    Set rngHasil = rngAsal.End(xlToRight)
  • Contoh pemanfaatan hasil penggunaan properti End, yang dalam hal ini berupa proses meng-Copy ke Clipboard.
  • Menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi alamat range asal, arah pergerakan End, dan alamat range hasil.

Pengaruh susunan data
Selain sheet 'Data16', pada workbook tersebut juga ada sheet 'Data15' yang digunakan pada pembahasan lalu tentang UsedRange, Intersection, dan Union seperti gambar berikut ini :

Proses dalam prosedur bernama TentangEnd_Contoh1 diatas diterapkan pada dataset 'cabang2'. Range asal saat penggunaan properti xlDown adalah pada range J5. Proses dalam prosedur akan berjalan sebagai berikut :

• dari range J5
• ke range J10 (hasil xlDown)
• ke range I10 (hasil xlToLeft)
• ke range I5 (hasil xlUp)
• ke range J5 (hasil xlToRight)

Pada proses yang terjadi, area range I12:J15 tidak pernah terlintasi atau menjadi hasil dari proses penggunaan properti End diatas. Artinya, tidak semua record pada dataset 'cabang2' terlingkupi oleh proses jika setiap proses dibuat garis maya pembentuk batas. Hal ini disebabkan karena kehadiran blank cells di range I11:J11. Karakteristik pergerakan properti END yang telah dijelaskan diatas, terutama Case 3, tampak jelas pada contoh proses ini.

Contoh 2 : Mendapatkan batas terluar area data

Properti End sering digunakan untuk mendapatkan batas terluar area data. Batas terluar area data bisa berarti :

• Baris terakhir area data
• Kolom terkanan area data
• Baris pertama area data ketika data diletakkan bukan pada baris pertama Excel atau ada baris kosong antara header data dengan record pertama data
• Kolom pertama area data ketika data diletakkan bukan pada kolom pertama Excel

Penggunaan properti End pada sheet 'Data16' untuk mendapatkan batas-batas terluar area data akan diletakkan pada prosedur berikut ini :
Public Sub TentangEnd_Contoh2()
    Dim sht As Worksheet
    Dim rngAsal As Range, rngHasil As Range
   
    'pendefinisian sheet kerja,
    'disimpan dalam variabel sht
    'dan sheet diaktifkan (activate)
    Set sht = Sheets("Data16")
    sht.Activate
   
    'baris-baris kode akan diletakkan setelah baris ini

End Sub
Variabel object Worksheet bernama sht telah didefinisikan berisi sheets 'Data16' pada baris berbunyi :
Set sht = Sheets("Data16")

• Baris terakhir data dengan xlDown
     '>> Menggunakan xlDown
     '- Khusus untuk tabel yang pasti memiliki record data
     '  dan rapat berisi data
     '  (tidak ada blank cell diantara record)
     '- Range asal pada baris header di kolom yang penuh datanya
     '- Header di baris 1 Excel
     '- Kolom yang penuh datanya adalah kolom A
     '- Range asal adalah A1
     Set rngAsal = sht.Range("A1")
     Set rngHasil = rngAsal.End(xlDown)
     rngHasil.Copy
     MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
        "Arah : xlDown" & vbCrLf & _
        "Hasil : " & rngHasil.Address & vbCrLf & _
        "Baris terakhir data (baris Excel) : " & rngHasil.Row
  Pada penggunaan xlDown, range asal harus berada disalah satu record data yang bukan record terakhir pada kolom yang pasti penuh isinya, seperti pada kolom-kolom kunci yang harus ada isinya. Proses pada potongan script yang menggunakan xlDown diatas adalah :
  • Mendefinisikan range asal di range A1
  • Menyimpan range hasil ke rngHasil yang merupakan hasil proses penggunaan xlDown terhadap rngAsal
  • Meng-Copy rngHasil ke Clipboard sebagai salah satu contoh proses yang bisa dilakukan terhadap range hasil
  • Menampilkan pesan kepada user dengan kotak pesan (MsgBox) yang berisi informasi tentang alamat range asal, arah pergerakan End, alamat range hasil dan indeks baris Excel dari range hasil yang merupakan baris terakhir data
  
  Bahan latihan dan uji coba
  Apa yang terjadi jika syarat :
  • 'pasti memiliki record data' tidak terpenuhi alias script diatas dijalankan pada data yang tidak memiliki record ?
  • 'rapat berisi data' tidak terpenuhi alias script diatas dijalankan pada data yang ada blank cell-nya ?


• Baris terakhir data dengan xlUp
     '>> Menggunakan xlUp
     '- Khusus untuk tabel yang tidak mungkin
     '  berisi record diseluruh baris Excelnya
     '- Range asal dari baris terakhir Excel
     '- Jumlah baris Excel didapat dengan Rows.Count
     '- Kolom yang penuh datanya adalah kolom A (kolom ke-1 Excel)
     '- Range asal adalah Cells(Rows.Count, 1)
     '  yang berarti cell pada
     '  baris Excel terakhir (hasil Rows.Count)
     '  dikolom ke-1 Excel
     Set rngAsal = sht.Cells(Rows.Count, 1)
     Set rngHasil = rngAsal.End(xlUp)
     rngHasil.Copy
     MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
         "Arah : xlUp" & vbCrLf & _
         "Hasil : " & rngHasil.Address & vbCrLf & _
         "Baris terakhir data (baris Excel) : " & rngHasil.Row
  Pada penggunaan xlUp, range asal harus berada dibaris diluar area data. Umumnya range asal adalah baris terakhir Excel pada kolom data yang baris terakhirnya pasti berisi data, seperti pada kolom-kolom kunci yang harus ada isinya. Proses pada potongan script yang menggunakan xlUp diatas adalah :
  • Mendefinisikan range asal di baris terakhir Excel pada kolom A. Kolom A adalah kolom ke-1 Excel. Baris terakhir Excel bisa didapat dengan Rows.Count dan range di baris terakhir Excel pada kolom A bisa didapat dengan menggunakan properti Cells yang membutuhkan informasi nomor indeks baris Excel dan nomor indeks kolom Excel. Proses ini adalah pada baris kode :
    Set rngAsal = sht.Cells(Rows.Count, 1)
  • Menyimpan range hasil ke rngHasil yang merupakan hasil proses penggunaan xlUp terhadap rngAsal
  • Meng-Copy rngHasil ke Clipboard sebagai salah satu contoh proses yang bisa dilakukan terhadap range hasil
  • Menampilkan pesan kepada user dengan kotak pesan (MsgBox) yang berisi informasi tentang alamat range asal, arah pergerakan End, alamat range hasil dan indeks baris Excel dari range hasil yang merupakan baris terakhir data
  
  Bahan latihan dan uji coba
  • Apa yang terjadi ketika potongan script diatas dijalankan pada seluruh baris Excel yang terisi data ?
  Tidak ada hal yang mustahil meskipun menggunakan Excel 2007 ke atas yang berisi sampai satu juta lebih baris Excel. Dulu pada saat Excel 2003 kebawah adalah versi terakhir Excel, dianggap bahwa baris sebanyak 65,535 yang dimiliki worksheet Excel 2003 tidak mungkin penuh terisi. Nyatanya, saat ini jumlah baris sebanyak 65,535 menjadi terlalu sedikit karena data yang diolah selalu melebihi nilai tersebut. Bahkan jumlah baris sebanyak satu juta lebih yang disediakan worksheet Excel 2007 ke atas sebenarnya masih relatif sedikit.


• Kolom terakhir data dengan xlToRight
     '>> Menggunakan xlToRight
     '- Khusus tabel yang pasti memiliki lebih dari 1 kolom
     '  dan rapat berisi data
     '  (tidak ada blank cell diantara nama kolom)
     '- Range asal pada baris header di kolom pertama data
     '- Header di baris 1 Excel
     '- Kolom pertama data adalah kolom A
     '- Range asal adalah A1
     Set rngAsal = sht.Range("A1")
     Set rngHasil = rngAsal.End(xlToRight)
     rngHasil.Copy
     MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
        "Arah : xlToRight" & vbCrLf & _
        "Hasil : " & rngHasil.Address & vbCrLf & _
        "Kolom terakhir data (kolom Excel ke-) : " & _
        rngHasil.Column _
        & vbCrLf & "yaitu pada kolom : " & _
        Replace(rngHasil.Address(False, False), _
        rngHasil.Row, vbNullString)
  Pada penggunaan xlToRight, range asal harus berada disalah satu header yang bukan header terakhir, seperti pada kolom pertama data. Proses pada potongan script yang menggunakan xlToRight diatas adalah :
  • Mendefinisikan range asal di range A1
  • Menyimpan range hasil ke rngHasil yang merupakan hasil proses penggunaan xlToRight terhadap rngAsal
  • Meng-Copy rngHasil ke Clipboard sebagai salah satu contoh proses yang bisa dilakukan terhadap range hasil
  • Menampilkan pesan kepada user dengan kotak pesan (MsgBox) yang berisi informasi tentang alamat range asal, arah pergerakan End, alamat range hasil dan indeks kolom Excel dari range hasil yang merupakan kolom terakhir data beserta nama kolom Excel-nya
  
  Bahan latihan dan uji coba
  Apa yang terjadi jika syarat :
  • 'pasti memiliki lebih dari 1 kolom' tidak terpenuhi alias script diatas dijalankan pada data yang berisi 1 (satu) kolom saja ?
  • 'rapat berisi data' tidak terpenuhi alias script diatas dijalankan pada data yang kolomnya tidak ada namanya (blank) ?


• Kolom terakhir data dengan xlToLeft
     '>> Menggunakan xlToLeft
     '- Khusus untuk tabel yang tidak mungkin
     '  menggunakan seluruh kolom Excel
     '- Range asal dari kolom terakhir Excel dibaris header
     '- Baris header di baris 1 Excel
     '- Jumlah kolom Excel didapat dengan Columns.Count
     '- Range asal adalah Cells(1, Columns.Count)
     '  yang berarti cell pada baris 1 Excel
     '  dikolom terakhir Excel (hasil Columns.Count)
     Set rngAsal = sht.Cells(1, Columns.Count)
     Set rngHasil = rngAsal.End(xlToLeft)
     rngHasil.Copy
     MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
        "Arah : xlToLeft" & vbCrLf & _
        "Hasil : " & rngHasil.Address & vbCrLf & _
        "Kolom terakhir data (kolom Excel ke-) : " & _
        rngHasil.Column _
        & vbCrLf & "yaitu pada kolom : " & _
        Replace(rngHasil.Address(False, False), _
        rngHasil.Row, vbNullString)
  Pada penggunaan xlToLeft, range asal harus berada dibaris diluar area data. Umumnya range asal adalah kolom terakhir Excel pada baris data yang kolom terakhirnya pasti berisi data, seperti pada baris header data yang seluruh kolomnya pasti diberi nama. Proses pada potongan script yang menggunakan xlToLeft diatas adalah :
  • Mendefinisikan range asal di kolom terakhir Excel pada baris 1 Excel karena header di baris 1 Excel. Kolom terakhir Excel bisa didapat dengan Columns.Count dan range di kolom terakhir Excel pada baris 1 Excel bisa didapat dengan menggunakan properti Cells yang membutuhkan informasi nomor indeks baris Excel dan nomor indeks kolom Excel. Proses ini adalah pada baris kode :
    Set rngAsal = sht.Cells(1, Columns.Count)
  • Menyimpan range hasil ke rngHasil yang merupakan hasil proses penggunaan xlToLeft terhadap rngAsal
  • Meng-Copy rngHasil ke Clipboard sebagai salah satu contoh proses yang bisa dilakukan terhadap range hasil
  • Menampilkan pesan kepada user dengan kotak pesan (MsgBox) yang berisi informasi tentang alamat range asal, arah pergerakan End, alamat range hasil dan indeks kolom Excel dari range hasil yang merupakan kolom terakhir data beserta nama kolom Excel-nya
  
  Bahan latihan dan uji coba
  • Apa yang terjadi ketika potongan script diatas dijalankan pada seluruh kolom Excel yang digunakan sebagai kolom data ?
  Hal ini sangat kecil kemungkinannya, dan andaikan terjadi pun, maka biasanya harus segera ditata ulang penyusunan dataset-nya. Meski demikian, tidak ada salahnya untuk menganggap hal tersebut terjadi.

Pengaruh susunan data
Bagaimana jika prosedur bernama TentangEnd_Contoh2 yang berisi potongan-potongan proses untuk mendapatkan batas terkanan dan terbawah data bisa diterapkan pada worksheet bernama 'Data15' ? Berikut ini adalah salah satu dari sekian banyak langkah yang bisa digunakan :

• Kolom pertama data pada dataset 'cabang1' didapat dengan range asal di range A5 kemudian xlToRight
• Kolom terakhir data pada dataset 'cabang1' didapat dengan range asal berupa hasil proses untuk mendapatkan kolom pertama data pada dataset 'cabang1' yang kemudian memanfaatkan xlToRight
• Kolom terakhir data pada dataset 'cabang2' didapat dengan range asal adalah kolom terakhir Excel dibaris 5 Excel kemudian xlToLeft
• Kolom pertama data pada dataset 'cabang2' didapat dengan range asal berupa hasil proses untuk mendapatkan kolom terakhir data pada dataset 'cabang2' yang kemudian memanfaatkan xlToLeft

Permasalahan utama dalam penggunaan properti End pada contoh data seperti di sheet 'Data15' ini adalah dalam penentuan baris terakhir data setiap dataset. Dengan pola susunan data yang setiap kolomnya disetiap baris terakhir data tidak ada yang pasti terisi data, maka akan relatif rumit karena harus menghitung jumlah baris terbanyak dari setiap kolom disetiap dataset. Sebaiknya proses untuk mendapatkan baris terakhir data pada dataset yang polanya demikian tidak menggunakan properti End. Pembahasan berikutnya tentang properti CurrentRegion akan lebih mudah digunakan daripada penggunaan properti End.

Simpulan

Pola susunan data sangat mempengaruhi proses pendinamisan rujukan dalam Excel VBA. Semakin berkaidah database suatu dataset, setidaknya dengan tidak adanya baris kosong seluruhnya maupun kolom kosong seluruhnya, maka akan semakin mudah proses perujukan secara dinamis dalam Excel VBA.

:)

Insya Allah pembahasan berikutnya adalah tentang CurrentRegion. Properti CurrentRegion milik range termasuk salah satu tulang punggung pendinamisan rujukan range dalam Excel VBA.

<previous | next>

BelajarVBA 011 - Range 04 (Offset dan Resize)

Coretan Mr. Kid

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 :

1. adalah bilangan bulat jumlah baris dari baris range asal sampai baris range tujuan
2. yang bernilai positif akan memiliki arah menuju ke bawah atau ke baris setelah baris range asal
3. yang bernilai negatif akan memiliki arah menuju ke atas atau ke baris sebelum baris range asal
4. yang bernilai nol atau dikosongkan akan berarti tidak akan menuju kemanapun alias tetap pada baris range asal

• jarak_antar_kolom :

1. adalah bilangan bulat jumlah kolom dari kolom range asal sampai kolom range tujuan
2. yang bernilai positif akan memiliki arah menuju ke kanan atau ke kolom setelah kolom range asal
3. yang bernilai negatif akan memiliki arah menuju ke kiri atau ke kolom sebelum kolom range asal
4. yang bernilai nol atau dikosongkan akan berarti tidak akan menuju kemanapun alias tetap pada kolom range asal

Contoh penerapannya akan diletakkan pada prosedur berikut ini :
   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 :

     rngAsal.Offset(0, 5)

• kemudian disimpan ke variabel rngOffset1 pada bagian :

     Set rngOffset1 =

• di baris berbunyi :

     Set rngOffset1 = rngAsal.Offset(0, 5)

• Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngOffset1 ke Clipboard oleh baris kode :

     rngOffset1.Copy

• 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 :

     rngAsal.Offset(9, 0)

• kemudian disimpan ke variabel rngOffset2 pada bagian :

     Set rngOffset2 =

• di baris berbunyi :

     Set rngOffset2 = rngAsal.Offset(9, 0)

• Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngOffset2 ke Clipboard oleh baris kode :

     rngOffset2.Copy

• 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 :

     rngAsal.Offset(3, 4)

• kemudian disimpan ke variabel rngOffset3 pada bagian :

     Set rngOffset3 =

• di baris berbunyi :

     Set rngOffset3 = rngAsal.Offset(3, 4)

• Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngOffset3 ke Clipboard oleh baris kode :

     rngOffset3.Copy

• 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 :

     rngOffset3.Offset(-2, -1)

• kemudian disimpan ke variabel rngOffset4 pada bagian :

     Set rngOffset4 =

• di baris berbunyi :

     Set rngOffset4 = rngOffset3.Offset(-2, -1)

• Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngOffset4 ke Clipboard oleh baris kode :

     rngOffset4.Copy

• 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 :

1. adalah bilangan bulat jumlah baris dari luasan yang baru dimulai dari range pojok kiri atas range asalnya
2. selalu bernilai lebih dari nol (>0) alias minimal 1
3. yang dikosongkan akan berarti memiliki jumlah baris yang tetap alias sama dengan jumlah baris range asalnya

• jumlah_kolom_baru :

1. adalah bilangan bulat jumlah kolom dari luasan yang baru dimulai dari range pojok kiri atas range asalnya
2. selalu bernilai lebih dari nol (>0) alias minimal 1
3. yang dikosongkan akan berarti memiliki jumlah kolom yang tetap alias sama dengan jumlah kolom range asalnya

Contoh penerapannya akan diletakkan pada prosedur berikut ini :
   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 :

     rngAnchor.Resize(1, 15)

• kemudian disimpan ke variabel rngResize1 pada bagian :

     Set rngResize1 =

• di baris berbunyi :

     Set rngResize1 = rngAnchor.Resize(1, 15)

• Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngResize1 ke Clipboard oleh baris kode :

     rngResize1.Copy

• 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 :

     rngResize1.Resize(17, 3)

• kemudian disimpan ke variabel rngResize2 pada bagian :

     Set rngResize2 =

• di baris berbunyi :

     Set rngResize2 = rngResize1.Resize(17, 3)

• Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngResize2 ke Clipboard oleh baris kode :

     rngResize2.Copy

• 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 :

     rngResize2.Resize(, 9)

• kemudian disimpan ke variabel rngResize2 pada bagian :

     Set rngResize3 =

• di baris berbunyi :

     Set rngResize3 = rngResize2.Resize(, 9)

• Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngResize3 ke Clipboard oleh baris kode :

     rngResize3.Copy

• 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 :

     rngAnchor.Resize(3, 4)

• kemudian disimpan ke dirinya sendiri, yaitu variabel rngAnchor pada bagian :

     Set rngAnchor =

• di baris berbunyi :

     Set rngAnchor = rngAnchor.Resize(3, 4)

• Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngAnchor yang sudah memiliki luasan baru ke Clipboard oleh baris kode :

     rngAnchor.Copy

• 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 :

     Set rng = Range("A1").Offset(1, 3).Resize(11, 2)

• Proses dilanjutkan dengan meng-Copy range dalam variabel rng ke Clipboard oleh baris kode :

     rng.Copy

• 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 :

1. adalah untuk mendapatkan suatu range dengan luasan yang sama dengan range asalnya dijarak tertentu dari sisi baris dan kolomnya.
2. tidak pernah mengubah luasan. Artinya, luasan hasil Offset selalu sama dengan luasan range asalnya.
3. bisa diberi nilai negatif, nol, atau positif. Jika dikosongkan, maka akan dianggap bernilai 0.
4. sisi baris negatif berarti keATAS, positif berarti keBAWAH, dan nol adalah TETAP pada baris range asal
5. sisi kolom negatif berarti keKIRI, positif berarti keKANAN, dan nol adalah TETAP pada kolom range asal
6. jarak baris maksimum adalah sebanyak baris tersisa setelah baris range asal dikurangi jumlah baris range asal ditambah 1, ketika diberi nilai positif
7. jarak baris minimum adalah sebanyak baris yang ada sebelum baris range asal ketika diberi nilai negatif
8. jarak kolom maksimum adalah sebanyak kolom tersisa setelah kolom range asal dikurangi jumlah kolom range asal ditambah 1, ketika diberi nilai positif
9. jarak kolom minimum adalah sebanyak kolom yang ada sebelum kolom range asal ketika diberi nilai negatif

• Properti Resize :

1. adalah untuk mendapatkan suatu range dengan luasan yang baru dengan mengubah nilai-nilai dimensi baris dan kolom dari range asalnya.
2. tidak pernah berpindah alamat pojok kiri atasnya.
3. hanya bisa menerima nilai positif atau selalu lebih dari nol (>0) alias minimal 1 (satu).
4. jika nilai jumlah baris baru atau nilai jumlah kolom baru dikosongkan, maka akan dianggap sama dengan dimensi sisi tersebut milik range asalnya.
5. arah perluasan selalu keKANAN atau keBAWAH karena hanya bisa menerima nilai positif saja.
6. jumlah baris baru maksimum adalah sebanyak baris tersisa setelahnya dari baris range asal
7. jumlah kolom baru maksimum adalah sebanyak kolom tersisa setelahnya mulai dari kolom range asal

• Properti Offset dan Resize :

1. 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.
2. 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

<previous | next>

Kasus 001 BelajarVBA - UpTo 011 Range 03

Keisengan Mr. Kid

Materi ini adalah contoh kasus sederhana untuk BelajarVBA sampai dengan BelajarVBA 011 - Range 03 (UsedRange,Intersect,Union). File contoh yang bisa di-unduh adalah Imp001.xlsm.
Kasus :

• Sheet 'Sumber' di range A2:B5. Data item (kolom A) dan jumlah record yang harus ditulis (kolom B).
• Sheet 'Output', A1:C1 adalah header tabel output. Terdiri dari Item, Waktu, dan Nomor
• Record pertama output mulai dari A2:C2.
• Jika nama item (range A2:A5) di-doubleclick, maka nama item akan ditulis pada sheet Output sebagai record baru (ditulis mulai baris kosong pertama di Output) sebanyak nilai kolom B di sheet Sumber.
• Tanggal dan jam saat di-doubleclick juga ditulis di sheet Output, yaitu ke kolom B.
• Nomor urut dari 1 sampai nilai yang di sheet Sumber pada kolom B di item yang di-doubleclick
• Diakhiri dengan menampilkan pesan tentang nama item, waktu, dan jumlah record yang ditulis.

Langkah menyusun otomasi :
1. Menyiapkan data dan menyimpan file dengan tipe Macro Enabled Workbook (.xlsm)

• Buat workbook baru
• Siapkan data di sheet bernama Sumber.
• Siapkan header data di sheet bernama Output.
• Susun data seperti gambar.
• Simpan file sebagai .xlsm atau .xls (lihat : BelajarVBA 001 - VBE (Visual Basic Editor) bagian akhir)
• Ke area penulisan kode alias ke VBE dengan ALT F11

2. Mendefinisikan pemicu proses

• Proses bekerja jika cell tertentu di sheet Sumber di-doubleclick. Artinya, Worksheet bernama Sumber harus bisa merespon kegiatan user. Hal ini berarti harus menggunakan event worksheet. Berarti ke VBE dan aktifkan code window milik sheet bernama Sumber. (lihat : BelajarVBA 007 - Property, Methods, Events bagian Events dalam object Worksheet)
• Kegiatan yang harus dilakukan user adalah melakukan doubleclick pada sebuah cell. Artinya, sesaat sebelum proses doubleclick bekerja (yang biasanya akan membuat cell bisa diedit isinya) harus segera menjalankan proses penyelesaian kasus yang dihadapi ini. Jadi dibutuhkan event worksheet bernama BeforeDoubleClick
• Cara membuatnya prosedur sub event worksheet bisa dilihat di BelajarVBA 007 - Property, Methods, Events bagian Events dalam object Worksheet
• Bentuk blok prosedur sub event worksheet untuk BeforeDoubleClick adalah :

      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

      End Sub

• Baris kosong sebelum END SUB adalah lokasi penulisan baris kode proses.
• Variabel Target adalah range yang di-doubleclick oleh user.
• Variabel Cancel adalah penerima data pembatalan proses doubleclick oleh user alias dipaksa tidak jadi di-doubleclick

3. Memastikan bahwa yang di-doubleclick oleh user adalah bagian dari range yang ditentukan, yaitu range A2:A5

• Area range yang memicu proses hanyalah A2:A5 di sheet Sumber.
• User bisa saja melakukan doubleclick dirange selain range yang ditentukan tersebut.
• Maka harus tahu lebih dulu, apakah range yang di-doubleclick berpotongan dengan range A2:A5 yang ditentukan. Artinya, dibutuhkan proses perpotongan antara range yang di-doubleclick oleh user (variabel Target) dengan area range tertentu (range A2:A5).
• Proses perpotongan akan menggunaan Intersect.
• Hasil perpotongan adalah suatu area range dan harus disimpan disuatu variabel object range. Hal ini mengindikasikan bahwa dibutuhkan deklarasi variabel object range penyimpan hasil perpotongan.
• Kemudian variabel tersebut digunakan untuk menyimpan hasil proses perpotongan.
• Jadi, urutannya adalah menyediakan variabel object range penyimpan hasil perpotongan lalu digunakan untuk menyimpan hasil proses perpotongan. (lihat : BelajarVBA 011 - Range 03 (UsedRange,Intersect,Union) bagian Perpotongan Range)
• Pada baris kosong blok prosedur bisa diisi :

     Dim rngHasil As Range      
     Set rngHasil = Intersect( Target , Range("A2:A5") )      

• Hasil perpotongan adalah berupa object (benda) Range. Untuk memeriksa benda adalah dengan pertanyaan 'Apakah ada rangenya ?'. Kata 'rangenya tidak ada' dalam VBA berbunyi Range is Nothing (karena datatype-nya object). Sedangkan 'ada rangenya' bisa dikatakan dengan 'TIDAK dari rangenya tidak ada' yang dalam VBA berbunyi Not Range is Nothing. Karena range hasil perpotongan disimpan dalam variabel bernama rngHasil, maka untuk mengatakan rngHasil ada bendanya dalam VBA bisa dikatakan dengan NOT rngHasil is Nothing.
• Jadi, kondisi untuk memeriksa hasil perpotongan adalah berbunyi NOT rngHasil is Nothing. Pemeriksaan dengan IF. (lihat : BelajarVBA 008 - Blok IF). Bunyi blok pemeriksaan dengan IF adalah :

     IF NOT rngHasil is Nothing THEN
       'baris kode ketika rngHasil ada bendanya
     ELSE
       'baris kode ketika rngHasil tidak ada bendanya
     END IF

• Sampai terbentuknya blok IF ini, bentuk blok prosedur sudah menjadi :

     Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
       Dim rngHasil As Range
       'tempat deklarasi variabel lainnya jika diperlukan

       'mulai baris kode proses
       Set rngHasil = Intersect( Target , Range("A2:A5") )  
       IF NOT rngHasil is Nothing THEN
         'baris kode ketika rngHasil ada bendanya
       ELSE
         'baris kode ketika rngHasil tidak ada bendanya
       END IF
     End Sub

4. Mendefinisikan isi proses disetiap keadaan dalam blok kondisi IF
Jika IF menghasilkan TRUE, artinya rngHasil ada bendanya, maka proses pengerjaan sampai penulisan ke sheet output akan dilakukan. Jika IF menghasilkan FALSE, artinya rngHasil tidak ada bendanya, maka tidak melakukan apapun. Jadi, fokus penyusunan baris kode adalah diarea blok IF yang bernilai TRUE, yaitu mengisi baris kode dibagian yang bertuliskan :
   'baris kode ketika rngHasil ada bendanya

5. Mendefinisikan nilai-nilai yang bisa didapatkan dan akan digunakan
Prosesnya adalah menulis nama item yang di-doubleclick, berarti dibutuhkan teks nama item tersebut. Nama item akan disimpan pada sebuah variabel ber-datatype String karena berupa data teks. Tentukan saja nama variabel nama item adalah sItem dengan type String. Selain itu, harus diketahui nilai Record untuk item yang di-doubleclick. Nilai record tersimpan di kolom B sheet Sumber pada baris item yang di-doubleclick. Jadi, harus tahu lebih dulu indeks baris Excel si item yang di-doubleclick.

Nilai indeks baris Excel adalah suatu bilangan yang ber-type numerik bilangan bulat. Maka variabel penyimpan indeks baris Excel bisa dipilih ber-type Long agar bisa menampung baris Excel yang sampai 1 juta. Nama variabel indeks baris Excel diberi nama saja dengan lBarisItem. Untuk bisa merujuk ke kolom B pada indeks baris Excel yang disimpan oleh lBarisItem, dibutuhkan teks alamat range si nilai record. Berarti dibutuhkan variabel ber-type String. Namakan saja variabel tersebut dengan sAlamatRecordItem.

Setelah tahu alamat range yang menyimpan nilai Record, maka nilai Record bisa diambil dan disimpan dalam sebuah variabel. Nilai record selalu bilangan bulat dan diasumsikan tidak lebih dari 32,000 maka dipilih datatype Integer. Nilai Record akan menjadi nilai maksimal nomor urut yang akan ditulis dari nomor 1 untuk setiap doubleclick oleh user, maka nama variabelnya adalah iRecordMax. Karena nomor akan ditulis dari nomor 1 sampai nomor maksimumnya (yang disimpan dalam iRecordMax), maka dibutuhkan variabel nomor yang ber-datatype sama dengan iRecordMax. Nama variabel nomor dipilih iRecordAwal yang ber-datatype Integer. Salah satu data yang akan ditulis nantinya adalah waktu user melakukan doubleclick yang berisi data tanggal dan jam. Berarti data ini akan ber-datatype datetime. Variabel penyimpan nilai tersebut dipilih datatype VB/VBA bernama Date. Nama variabelnya adalah dtWaktu (lihat BelajarVBA 004 - Tipe data, Variabel, Konstanta).

Sampai disini, sudah diketahui bahwa dibutuhkan beberapa variabel. Variabel-variabel itu harus dideklarasikan lebih dulu. Deklarasi prosedur umumnya ditulis diawal blok prosedur untuk scope level procedure (lihat : BelajarVBA 003 - Scope seri 1). Bunyi baris deklarasi variabel tambahannya adalah :
   Dim sItem As String, lBarisItem As Long
   Dim sAlamatRecordItem As String
   Dim iRecordMax As Integer, iRecordAwal As Integer, dtWaktu As Date

Kemudian, variabel-variabel baru tersebut diisi nilainya. Variabel sItem diisi dengan teks item yang disimpan oleh rngHasil sebagai perpotongan range yang di-doubleclick user dengan area A2:A5. Bunyi baris kode untuk ambil nilai dari suatu variabel range (dalam hal ini dari rngHasil) adalah :
   sItem = rngHasil.Value
Variabel lBarisItem diisi dengan indeks baris Excel dari rngHasil, yang diperoleh dengan baris kode :
   lBarisItem = rngHasil.Row
Variabel sAlamatRecordItem diisi dengan teks alamat range untuk kolom B di indeks baris yang disimpan oleh lBarisItem. Maka baris kode penyusun alamat range Record adalah :
   sAlamatRecordItem = "B" & lBarisItem
Variabel iRecordMax diisi dengan nilai kolom B yang alamatnya disimpan di sAlamatRecordItem dengan baris kode :
   iRecordMax = Range( sAlamatRecordItem ).Value
Variabel iRecordAwal diisi dengan angka 1, karena harus selalu dimulai dari angka 1 setiap proses doubleclick oleh user, dengan baris kode :
   iRecordAwal = 1
Variabel dtDate akan diisi datetime saat user melakukan doubleclick, yang berarti adalah waktu saat ini (Now). Jadi beris kodenya adalah :
   dtWaktu = Now
Semua proses pengambilan nilai ini masuk ke blok IF bagian TRUE. Jadi diletakkan setelah baris keterangan berbunyi :
   'baris kode ketika rngHasil ada bendanya
sehingga event worksheet BeforeDoubleClick akan menjadi :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rngHasil As Range, sItem As String, lBarisItem As Long
    Dim sAlamatRecordItem As String        'teks alamat si item
    Dim iRecordMax As Integer, iRecordAwal As Integer, dtWaktu As Date

    'mulai baris kode proses
    Set rngHasil = Intersect( Target , Range("A2:A5") )        
    IF NOT rngHasil is Nothing THEN
        'ambil nilai dari sheet Sumber
        sItem = rngHasil.Value
        lBarisItem = rngHasil.Row
        sAlamatRecordItem = "B" & lBarisItem
        iRecordMax = Range( sAlamatRecordItem ).Value
        iRecordAwal = 1
        dtWaktu = Now       
    ELSE
        'baris kode ketika rngHasil tidak ada bendanya
    END IF   
End Sub

6. Mendefinisikan lokasi penulisan
Penulisan dilakukan di sheet bernama 'Output'. Berarti akan bekerja pada worksheet 'Output'. Supaya lebih fleksibel, dibuat variabel penyimpan worksheet penulisan. Variabel ber-type object dari worksheet dideklarasikan dengan datatype Worksheet. Nama variabelnya adalah shtTulis.

Penulisan dilakukan di baris kosong selanjutnya. Berarti harus tahu area range yang sudah terpakai di sheet Output. Variabel object Range penyimpan area yang sudah terpakai dideklarasikan ber-type Range. Misalnya diberi nama rngTerpakai. Untuk tahu baris kosong pertama lokasi penulisan, harus diketahui jumlah baris yang sudah terpakai. Jumlah baris yang terpakai berupa bilangan bulat yang bisa sebanyak jumlah baris Excel. Datatype Long akan mampu menampungnya. Nama variabelnya adalah lBarisTerpakai.

Sampai disini, sudah ada beberapa variabel baru yang akan dideklarasikan lebih dulu. Baris-baris deklarasi tersebut adalah :
   Dim shtTulis As Worksheet, rngTerpakai As Range, lBarisTerpakai As Long
Kemudian, variabel-variabel baru tersebut diisi.
Variabel shtTulis diisi dengan worksheet Output dengan baris kode definisi variabel object :
   Set shtTulis = Sheets("Output")
Variabel rngTerpakai diisi dengan range yang sudah terpakai di sheet 'Output'. Properti UsedRange milik worksheet tersebut. Lihat BelajarVBA 011 - Range 03 (UsedRange,Intersect,Union). Baris kode pendefinisian rngTerpakai agar terisi dengan hasil properti UsedRange adalah :
   Set rngTerpakai = shtTulis.UsedRange
Variabel lBarisTerpakai diisi jumlah baris pada variabel range yang sudah terpakai yang bernama rngTerpakai dengan baris kode :
   lBarisTerpakai = rngTerpakai.Rows.Count
Baris-baris kode untuk mengisi nilai variabel-variabel baru tersebut adalah lanjutan proses kerja, jadi diletakkan setelah baris kode pengisian nilai yang nomor 5. Sampai disini, bentuk blok prosedur akan menjadi :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rngHasil As Range, sItem As String, lBarisItem As Long
    Dim sAlamatRecordItem As String        'teks alamat si item
    Dim iRecordMax As Integer, iRecordAwal As Integer, dtWaktu As Date
    Dim shtTulis As Worksheet, rngTerpakai As Range, lBarisTerpakai As Long

    'mulai baris kode proses
    Set rngHasil = Intersect( Target , Range("A2:A5") ) 
    IF NOT rngHasil is Nothing THEN
        'ambil nilai dari sheet Sumber
        sItem = rngHasil.Value
        lBarisItem = rngHasil.Row
        sAlamatRecordItem = "B" & lBarisItem
        iRecordMax = Range( sAlamatRecordItem ).Value
        iRecordAwal = 1
        dtWaktu = Now
       
        'ambil informasi lokasi penulisan
        Set shtTulis = Sheets("Output")
        Set rngTerpakai = shtTulis.UsedRange
        lBarisTerpakai = rngTerpakai.Rows.Count       
    ELSE
        'baris kode ketika rngHasil tidak ada bendanya
    END IF   
End Sub

7. Membentuk blok kerja penulisan
Penulisan akan dilakukan sebanyak nilai iRecordMax, dimulai dari iRecordAwal bernilai 1. Artinya akan ada proses penulisan berulang kali alias proses loop. Meski iRecordAwal saat ini selalu dimulai dari 1, bisa jadi nanti suatu saat akan berubah dan tidak akan lagi dimulai dari 1. Oleh sebab itu dibutuhkan variabel penyimpan baris baru lokasi penulisan, yang selalu bertambah 1 dari baris penulisan sebelumnya dalam proses loop. Artinya, sesaat sebelum memasuki proses loop, sudah harus diketahui lebih dulu lokasi baris baru penulisan untuk kegiatan tulis data yang pertama. Untuk keperluan ini, dibutuhkan penyimpan nomor baris baru penulisan yang bisa menampung nilai jumlah baris Excel seluruhnya. Datatype Long bisa menampungnya. Nama variabelnya adalah lBarisBaru. Baris deklarasinya diletakkan pada bagian deklarasi variabel dengan baris kode berbunyi :
   Dim lBarisBaru As Long
Nilai lBarisTerpakai adalah yang sudah terpakai. Maka baris baru penulisan pertama adalah 1 baris setelah lBarisTerpakai. Jadi lBarisBaru sebelum proses loop bisa ditentukan dengan baris kode :
   lBarisBaru = lBarisTerpakai + 1

Karena yang diketahui adalah nilai awal dan akhir proses loop, maka dipilih loop berupa blok FOR ... NEXT. Blok For ... Next membutuhkan variabel loop. Karena loop-nya pada nilai iRecordAwal sampai iRecordMax, maka dideklarasikan variabel loop bernama iNomor dengan datatype Integer. Baris kode deklarasinya diletakkan pada bagian deklarasi variabel. Baris kodenya berbunyi :
   Dim iNomor As Integer
Kemudian blok FOR ... NEXT dibuat dalam blok IF sebagai kelanjutan proses kerja. Bentuk blok FOR ... EACH nya adalah :
   FOR iNomor = iRecordAwal To iRecordMax

   NEXT iNomor
Blok prosedur akan menjadi :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rngHasil As Range, sItem As String, lBarisItem As Long
    Dim sAlamatRecordItem As String        'teks alamat si item
    Dim iRecordMax As Integer, iRecordAwal As Integer, dtWaktu As Date
    Dim shtTulis As Worksheet, rngTerpakai As Range, lBarisTerpakai As Long
    Dim lBarisBaru As Long, iNomor As Integer

    'mulai baris kode proses
    Set rngHasil = Intersect( Target , Range("A2:A5") )
    IF NOT rngHasil is Nothing THEN
        'ambil nilai dari sheet Sumber
        sItem = rngHasil.Value
        lBarisItem = rngHasil.Row
        sAlamatRecordItem = "B" & lBarisItem
        iRecordMax = Range( sAlamatRecordItem ).Value
        iRecordAwal = 1
        dtWaktu = Now
       
        'ambil informasi lokasi penulisan
        Set shtTulis = Sheets("Output")
        Set rngTerpakai = shtTulis.UsedRange
        lBarisTerpakai = rngTerpakai.Rows.Count
       
        'blok kerja penulisan
        lBarisBaru = lBarisTerpakai + 1
        FOR iNomor = iRecordAwal To iRecordMax
            'detil kerja penulisan disini
        NEXT iNomor       
    ELSE
        'baris kode ketika rngHasil tidak ada bendanya
    END IF   
End Sub

8. Menulis data dibaris baru penulisan dan menentukan baris baru untuk data berikutnya
Ketika proses memasuki proses loop dan lokasi baris baru penulisan sudah diketahui, maka proses selanjutnya adalah menulis seluruh nilai yang diketahui ke baris baru penulisan pada kolom masing-masing nilai. Lihat BelajarVBA 011 - Range 01 (berdasar alamat range). Nilai item dalam sItem akan ditulis di kolom A pada lBarisBaru. Baris kode penulisannya berbunyi :
   shtTulis.Range("A" & lBarisBaru).Value = sItem
Nilai waktu dalam dtWaktu akan ditulis di kolom B pada lBarisBaru. Baris kode penulisannya berbunyi :
   shtTulis.Range("B" & lBarisBaru).Value = dtWaktu
Nilai nomor urut dalam iNomor (yang dipakai oleh loop) akan ditulis di kolom C pada lBarisBaru. Baris kode penulisannya berbunyi :
   shtTulis.Range("C" & lBarisBaru).Value = iNomor

Setelah seluruh data ditulis, maka nilai baris baru untuk proses penulisan pada loop berikutnya harus ditentukan.
Artinya, lBarisBaru harus bertambah 1. Jadi, saat proses loop mengolah iNomor berikutnya, baris penulisannya sudah dibaris yang baru lagi. Penambahan 1 untuk lBarisBaru adalah dengan baris kode :
   lBarisBaru = lBarisBaru + 1
Sampai disini, blok prosedur akan menjadi :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rngHasil As Range, sItem As String, lBarisItem As Long
    Dim sAlamatRecordItem As String        'teks alamat si item
    Dim iRecordMax As Integer, iRecordAwal As Integer, dtWaktu As Date
    Dim shtTulis As Worksheet, rngTerpakai As Range, lBarisTerpakai As Long
    Dim lBarisBaru As Long, iNomor As Integer

    'mulai baris kode proses
    Set rngHasil = Intersect( Target , Range("A2:A5") ) 
    IF NOT rngHasil is Nothing THEN
        'ambil nilai dari sheet Sumber
        sItem = rngHasil.Value
        lBarisItem = rngHasil.Row
        sAlamatRecordItem = "B" & lBarisItem
        iRecordMax = Range( sAlamatRecordItem ).Value
        iRecordAwal = 1
        dtWaktu = Now
       
        'ambil informasi lokasi penulisan
        Set shtTulis = Sheets("Output")
        Set rngTerpakai = shtTulis.UsedRange
        lBarisTerpakai = rngTerpakai.Rows.Count
       
        'blok kerja penulisan
        lBarisBaru = lBarisTerpakai + 1
        FOR iNomor = iRecordAwal To iRecordMax
            'detil kerja penulisan disini
            shtTulis.Range("A" & lBarisBaru).Value = sItem
            shtTulis.Range("B" & lBarisBaru).Value = dtWaktu
            shtTulis.Range("C" & lBarisBaru).Value = iNomor
           
            'membuat baris baru penulisan untuk loop berikutnya
            lBarisBaru = lBarisBaru + 1
        NEXT iNomor       
    ELSE
        'baris kode ketika rngHasil tidak ada bendanya
    END IF   
End Sub

9. Membuat proses akhir
Seluruh proses pokok penulisan data telah selesai. Karena proses dijalankan saat cell di-doubleclick, maka harus dipastikan bahwa proses doubleclick tidak membuat cell masuk ruang edit data cell. Hal ini dilakukan dengan membuat variabel Cancel milik event worksheet BeforeDoubleClick harus bernilai TRUE. Baris kodenya adalah :
   Cancel = TRUE
Proses pembatalan terhadap dampak doubleclick yang akan masuk ruang edit data cell dilakukan tepat setelah proses loop. Tahapan berikutnya adalah proses akhir yang biasanya diisi dengan penampilan pesan selesai dan sebagainya. Pesan kepada user bisa disampaikan setelah blok loop dengan FOR ... NEXT. Misalnya dengan menggunakan kotak pesan (MsgBox) seperti baris kode berikut :
MsgBox "Selesai." & vbcrlf & "Item : " & sItem & vbcrlf & _
    "Pada : " & dtWaktu & vbcrlf & "Jumlah record : " & iRecordMax _
    ,vbInformation,"Contoh Kasus Belajar VBA 001"
Blok prosedur lengkapnya akan menjadi :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rngHasil As Range, sItem As String, lBarisItem As Long
    Dim sAlamatRecordItem As String        'teks alamat si item
    Dim iRecordMax As Integer, iRecordAwal As Integer, dtWaktu As Date
    Dim shtTulis As Worksheet, rngTerpakai As Range, lBarisTerpakai As Long
    Dim lBarisBaru As Long, iNomor As Integer

    'mulai baris kode proses
    Set rngHasil = Intersect( Target , Range("A2:A5") )
    IF NOT rngHasil is Nothing THEN
        'ambil nilai dari sheet Sumber
        sItem = rngHasil.Value
        lBarisItem = rngHasil.Row
        sAlamatRecordItem = "B" & lBarisItem
        iRecordMax = Range( sAlamatRecordItem ).Value
        iRecordAwal = 1
        dtWaktu = Now
       
        'ambil informasi lokasi penulisan
        Set shtTulis = Sheets("Output")
        Set rngTerpakai = shtTulis.UsedRange
        lBarisTerpakai = rngTerpakai.Rows.Count
       
        'blok kerja penulisan
        lBarisBaru = lBarisTerpakai + 1
        FOR iNomor = iRecordAwal To iRecordMax
            'detil kerja penulisan disini
            shtTulis.Range("A" & lBarisBaru).Value = sItem
            shtTulis.Range("B" & lBarisBaru).Value = dtWaktu
            shtTulis.Range("C" & lBarisBaru).Value = iNomor
           
            'membuat baris baru penulisan untuk loop berikutnya
            lBarisBaru = lBarisBaru + 1
        NEXT iNomor

        'batalkan dampak doubleclick pada cell
        Cancel = TRUE
       
        'pesan untuk user
        MsgBox "Selesai." & vbcrlf & "Item : " & sItem & vbcrlf & _
            "Pada : " & dtWaktu & vbcrlf & "Jumlah record : " & iRecordMax _
            ,vbInformation,"Contoh Kasus Belajar VBA 001"
    ELSE
        'baris kode ketika rngHasil tidak ada bendanya
    END IF
End Sub

:)

Selesai sudah proses detil dalam menyelesaikan kasus sederhana ini. Setelah dipahami alurnya dan semakin terbiasa melakukan coding, biasanya prosedur akhir tersebut, dengan batasan materi sampai 011_03 dan record awal selalu mulai dari 1, bisa menjadi lebih ringkas seperti ini :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim sItem As String, iRecordMax As Integer, dtWaktu As Date
    Dim lBarisTerpakai, iNomor As Integer

    If Not Intersect(Target, Range("A2:A5")) Is Nothing Then
        'ambil nilai dari sheet Sumber
        sItem = Target.Value
        iRecordMax = Range("B" & Target.Row).Value
        dtWaktu = Now
       
        'blok kerja penulisan
        lBarisTerpakai = Sheets("Output").UsedRange.Rows.Count
        For iNomor = 1 To iRecordMax
            'detil kerja penulisan disini
            Sheets("Output").Range("A" & lBarisTerpakai + iNomor).Value = sItem
            Sheets("Output").Range("B" & lBarisTerpakai + iNomor).Value = dtWaktu
            Sheets("Output").Range("C" & lBarisTerpakai + iNomor).Value = iNomor
        Next iNomor
       
        Cancel = True   'batalkan dampak doubleclick pada cell
       
        'pesan untuk user
        MsgBox "Selesai." & vbCrLf & "Item : " & sItem & vbCrLf & _
            "Pada : " & dtWaktu & vbCrLf & "Jumlah record : " & iRecordMax _
            , vbInformation, "Contoh Kasus Belajar VBA 001"
    End If
End Sub