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>