Ngobrolin EVALUATE di-VBA yuk...

Dongengan Mr. Kid



Kid mau dongeng di lepas tengah malem...

Pada jaman kuda sudah pakai whatsap, terdengar selentingan ocehan tentang Evaluate...

Ocehannya begini...

Evaluate : sebuah methods (sebut saja prosedur fungsi) yang menjadi anggota object Application, Worksheet, dan Chart.
Evaluate bisa digunakan dengan 2 cara, yaitu :

• Evaluate( inputnya_berdatatype_string )
• [ inputnya_bunyi_yang_bisa_dipasang_jadi_formula_atau_nama_object_dalam_chart ]

Guna evaluate : mengkomputasi yang di-input-kan

Datatype hasil : VARIANT

artinya :

• jika yang diinputkan menghasilkan suatu object, maka hasilnya ber-datatype object.
• jika yang diinputkan menghasilkan suatu nilai skalar, maka hasilnya ber-datatype nilai skalar hasil tersebut

Jadi, ketika yang diinputkan adalah kegiatan pada worksheet (ndak usah membahas yang inputnya chart dulu ya), maka hasilnya bisa ada 2 datatype, yaitu :

1. jika yang diinputkan menghasilkan object Range (karena kegiatan worksheet dalam hal ini artinya tulisan formula), maka hasilnya ber-datatype object Range
2. jika yang diinputkan menghasilkan nilai skalar, maka hasilnya ber-datatype sesuai datatype nilai hasil tersebut.

Contoh 001 :

di cell Excel, misal di cell G7, bisa diberi formula berbunyi =A1

1. Formula tersebut, hasilnya di cell excel, menghasilkan nilai yang ada di A1 ya kan...

2. Kegiatan yang terjadi dalam formula tersebut sebenarnya suatu proses evaluate. Dalam VBA, methods evaluate bisa digunakan dengan bunyi :

Evaluate("A1")
atau
Evaluate("=A1")
atau
[A1]  
(lihat lagi cara penggunaan Evaluate diatas).

3. hasil Evaluate adalah object range, karena formula di G7 yang dipakai dalam evaluate (di VBA) akan menghasilkan object range dan bukan nilai skalar.

Jadi, kaidah datatype akan berlaku dalam hal ini (sudah baca BelajarVBA tentang variabel dan datatype ?)

Mari kita buktikan.
Isi cell A1 dengan bilangan (bertype numerik) senilai 12345
Tulis disebuah prosedur (buat sendiri prosedurnya) di sebuah general module.

Jika ada variabel yang dideklarasikan dengan baris kode : (ingat ya, A1 isinya bilangan 12345)

Dim rng As Range

Dim dblNilai As Double

'definisi object range -> selalu pakai Set kan
Set rng = Range("A1")          'biasanya begini kan cara definisi sebuah variabel object range
'kalau pakai evaluate

Set rng = Evaluate("A1")   'inputnya harus teks kan (lihat cara pakai evaluate diatas)

'atau

Set rng = [A1]   'inputnya harus bunyi yang bisa dipasang sebagai formula dalam cell kan

'kedua baris diatas tidak akan menghasilkan error, karena evaluate akan menghasilkan object range

'sekarang pakai nilai skalar ada di object range

'nilai di range A1 bisa diambil dengan bunyi :

dblNilai = Range("A1").Value

'karena default properti object range adalah proeperti VALUE, maka kebanyakan pengguna VBA yang menulis :

dblNilai = Range("A1")    'tidak akan menghasilkan error, tapi tetap saja baris ini sama dengan bunyi baris Range("A1").Value

'jadi dblNilai bukan diisi oleh object range A1, tapi diisi oleh nilai yang ada dalam properti VALUE dari range A1

'kalau dblNilai akan diisi dengan object range A1,
'maka bunyi baris kodenya harus Set dblNilai = Range("A1")
'dan ini pasti error, coba aja kalo gak percaya.
'Kenapa kok error hayo ?

'mengisi variabel dblNilai yang berdatatype Double yang berisi nilai skalar bisa menggunakan evaluate seperti :

dblNilai = Evaluate("A1").Value             'bagian Evaluate("A1") menghasilkan object Range
                                                                                     'karena bunyi inputannya menghasilkan suatu range

'atau kebanyakan VBA yang pengen pakai default value dari object range akan menulis :

dblNilai = Evaluate("A1")              'yang sama dengan baris kode berbunyi : dblNilai = Range("A1")

'kalau pakai bracket (kurung siku) dalam menggunakan evaluate, jadinya :

dblNilai = [A1].Value                    'hasil bagian [A1] adalah object range karena bunyi inputannya menghasilkan suatu range

'atau yang pengen pakai default properti object range yang berupa properti VALUE, bisa menulis :

dblNilai = [A1]

'sekali lagi, proses 4 baris diatas bukan mengisi dblNilai dengan range A1, tapi mengisi dblNilai dengan nilai properti VALUE milik range A1

Sekarang,
Contoh 002 :

di A2 diisi teks berbunyi BeExcel

1. di cell F5 bisa diberi formula berbunyi :  =A2  dan akan tampak dicell isi F5 adalah sama dengan isi A2 dan ber-datatype string (teks)

2. untuk pakai evaluate seperti contoh 001, ketika memanfaatkan hasil evaluate yang ber-type Object (karena formula berbunyi =A2 menghasilkan object range), variabel object range bernama rng yang ada di contoh 001 masih bisa dipakai. Cara pakai evaluate ke variabel rng :

'definisi object range -> selalu pakai Set kan
Set rng = Range("A2")          'biasanya begini kan cara definisi sebuah variabel object range
'kalau pakai evaluate

Set rng = Evaluate("A2")   'inputnya harus teks kan (lihat cara pakai evaluate diatas)

'atau

Set rng = [A2]   'inputnya harus bunyi yang bisa dipasang sebagai formula dalam cell kan

'kedua baris diatas tidak akan menghasilkan error, karena evaluate akan menghasilkan object range

'tapi kalau untuk ambil nilai dalam object range hasil evaluate, dibutuhkan variabel ber-datatype string. Jadi harus ada deklarasi variabel seperti :

Dim sNilai As String

'lalu variabel sNilai bisa dipakai
sNilai = Range("A2").Value

'atau kalau mau pakai default properti milik object range yang bernama properti VALUE,

sNilai = Range("A2")

'untuk hasil evaluate juga demikian, bisa ditulis :

sNilai = Evaluate("A2").Value

'atau kalau mau pakai default properti milik object range yang bernama properti VALUE,

sNilai = Evaluate("A2")

'pakai evaluate yang notasinya bracket (kurung siku) juga bisa,

sNilai = [A2].Value

'atau kalau mau pakai default properti milik object range yang bernama properti VALUE,

sNilai = [A2]

simpulan dari contoh 001 dan contoh 002 (inputannya menghasilkan object)
1. jadi, kalau ambil hasil evaluate yang nilai skalarnya, berarti mengambil properti VALUE hasil evaluate jika inputan evaluate menghasilkan Object, bisa disimpan divariabel yang datatype-nya sesuai dengan si datatype nilai skalar yang akan diambil

2. kalau akan ambil object range hasil evaluate-nya, maka harus disimpan dalam variabel object range (variabel object juga bisa, cuma lebih lama)

Yuk mainan yang inputannya bukan object.

Contoh 003 :

di A1 sudah ada data numerik 12345

di A2 sudah ada data teks berbunyi BeExcel

di E3 bisa diberi formula berbunyi :  =A1 & " milik " & A2  dan hasilnya adalah teks berbunyi '12345 milik BeExcel'

Hasil formula di E3 ini bukanlah ber-datatype object Range, tapi nilai skalar berbunyi '12345 milik BeExcel' yang ber-type String (teks)

1. menyusun bunyi formula seperti formula E3 dalam VBA, disimpan dalam variabel bernama sFormula yang ber-type String

Dim sFormula As String

'menulis petik dua (") dalam ekspresi string di VBA harus diganti dengan 2 (dua) buah petik dua ("")

'jadi, bunyi formula : A1 & " milik " & A2 harus ditulis di-VBA menjadi A1 & "" milik "" & A2

'karena ekspresi string di-VBA juga harus diapit petik dua ("), maka bentuknya menjadi :

sFormula = "A1 & "" milik "" & A2"

'cek deh hasilnya, pastikan di E3 ada formula berbunyi =A1 & " milik " & A2

msgbox "Formula di E3 : " & range("e3").formula _

     & vbcrlf & "Formula di sFormula : " & sformula _

     & vbcrlf & "Hanya selisih karakter sama dengan ya....",vbinformation,"Jas Inpo ajah"

2. Jadi, variabel object range bernama rng di prosedur yang ada, sudah ndak bisa dipakai untuk menyimpan nilai hasil formula yang bunyinya seperti formula di E3 atau yang disimpan dalam variabel sFormula.

'artinya, kalau baris kode berikut diaktifkan dan dijalankan prosedurnya, akan menghasilkan error

'Set rng = Range(sFormula)

'jadi kita lewatkan saja bagian yang akan menyimpan sebagai object range (soale ndak mungkin kalau langsung pakai formula)

3. pakai evaluate, bunyi formula : A1 & " milik " & A2

'hasilnya akan berupa nilai skalar ber-type string. Jadi disiapkan dulu variabel penyimpannya

Dim sHasilEval As String

'baru deh si variabel sHasilEval dipakai

sHasilEval = Evaluate( sFormula )     'kalau pakai Evaluate() harus diberi inputan teks kan ya, oleh sebab itu pakai isi dalam sFormula

sHasilEval = [A1 & " milik " & A2]        'kalau pakai bracket (kurung siku) bisa tulis formula dalam cell tanpa sama dengan kan

Sekarang pakai formula yang lebih ruwet ya... coba secara perlahan saja.

Contoh 004 :

di B1 diisi teks berbunyi Ini Angka

di B2 diisi teks berbunyi Ini milis kita

di C6 diberi formula VLookUp berbunyi : =VLOOKUP("BeExcel",$A$1:$B$2,2,0)

-> me-lookup teks 'BeExcel' di A1:B2 dan ambil nilai di kolom ke-2 dari A1:B2 (ambil kolom B) dengan pencarian yang Exact (sama percis)

hasil formula vLookUp adalah nilai skalar. Begitu juga dengan fungsi LookUp jika formula menggunakan fungsi LookUp. Jadi hasil evaluate disimpan pada variabel skalar dan bukan variabel object.

'karena kolom B ber-type teks semua, maka hasil evaluate akan ber-type string. Jadi pakai sHasilEval

sHasilEval = [VLOOKUP("BeExcel",$A$1:$B$2,2,0)]

'kalau mau pakai evaluate(), berarti susun bunyi formula dalam VBA, simpan di sFormula dan lakukan sHasilEval untuk menyimpan hasil Evaluate()

sFormula = "VLOOKUP(""BeExcel"",$A$1:$B$2,2,0)"

sHasilEval = Evaluate( sFormula )

Contoh 005 :

pakai formula me-lookup data yang lain ya...

di C8 diberi formula Index Match berbunyi =INDEX($B$1:$B$2,MATCH("BeExcel",$A$1:$A$2,0))

hasil formula Index bisa berupa object range. Formula Index Match (yang paling luarnya kan Index tuh) di atas menghasilkan object Range. Begitu juga dengan formula Offset dalam bentuk Offset Match. Jadi, variabel object range bernama rng bisa digunakan.

'ketika hasil evaluate formula Index bisa menghasilkan object range, maka variabel rng bisa dipakai sebagai berikut :

Set rng = [INDEX($B$1:$B$2,MATCH("BeExcel",$A$1:$A$2,0))]

'atau jika akan menggunakan Evaluate(), harus membuat teks formulanya
sFormula = "INDEX($B$1:$B$2,MATCH(""BeExcel"",$A$1:$A$2,0))"

Set rng = Evaluate( sFormula )

'karena kolom B ber-type teks semua, maka nilai properti VALUE dari object range hasil evaluate akan ber-type string. Jadi pakai sHasilEval

sHasilEval = [INDEX($B$1:$B$2,MATCH("BeExcel",$A$1:$A$2,0))].Value

'kalau mau pakai evaluate(), berarti susun bunyi formula dalam VBA, simpan di sFormula dan lakukan sHasilEval untuk menyimpan nilai properti VALUE dari object range hasil Evaluate()

sFormula = "INDEX($B$1:$B$2,MATCH(""BeExcel"",$A$1:$A$2,0))"

sHasilEval = Evaluate( sFormula ).Value

Nah, gimana kalau yang disimpan dalam cell adalah teks formula. Artinya si cell bukan berisi formula tapi teksnya formula.

Kita pakai formula vLookUp dan Index Match lagi ya... supaya manteb puyengnya...

Contoh 006 :

yang Index Match dulu ya

di B11 ditulis bunyi teks INDEX($B$1:$B$2,MATCH("BeExcel",$A$1:$A$2,0))     [tidak pakai sama dengan loh ya]

karena data di B11 berisi teks formula (bukan berisi formula), maka tipe data di B11 adalah teks (string) karena bunyi tulisannya dan bukan karena hasil olahan bunyi tulisannya.

jadi, kalau akan menggunakan evaluate yang bracket, hasilnya akan berupa string dan harus disimpan dalam variabel string. Simpan saja ke sFormula ya, karena teks di B11 bunyi teks formula.

'B11 berisi teks yang bunyinya terdengar seperti formula.

sFormula = [B11]     'akan menghasilkan teks isi B11

Msgbox "Isi sFormula setelah dikenai [B11] adalah " & sFormula,vbinformation,"jas inpo"    'cek aja deh

'untuk mendapatkan hasil olahan dari teks formula yang tertulis di cell B11, maka harus digunakan Evaluate().

'karena sFormula sudah diisi bunyi teks formula dari B11 oleh baris sFormula = [B11] , berarti bisa tinggal dipakai dong

'misal disimpan dalam variabel rng (karena bunyi teksnya terdengar bunyi formula Index yang bisa menghasilkan object range)

Set rng = Evaluate( sFormula )

'atau tetap mau dapat nilai properti VALUE dari object range hasil evaluate terhadap bunyi teks formula yang terdengar seperti Index yang menghasilkan object range, maka bisa :

sHasilEval = Evaluate( sFormula ).Value

'atau pakai default properti,

sHasilEval = Evaluate( sFormula )

Contoh 007 :

sekarang pakai yang vLookUp. Hasilnya nilai skalar, jadi variabel rng ndak bisa dipakai.

di B12 ditulis bunyi teks VLOOKUP("BeExcel",$A$1:$B$2,2,0)        [tidak pakai sama dengan loh ya]

karena data di B12 berisi teks formula (bukan berisi formula), maka tipe data di B12 adalah teks (string) karena bunyi tulisannya dan bukan karena hasil olahan bunyi tulisannya.

jadi, kalau akan menggunakan evaluate yang bracket, hasilnya akan berupa string dan harus disimpan dalam variabel string. Simpan saja ke sFormula ya, karena teks di B12 bunyi teks formula.

'B12 berisi teks yang bunyinya terdengar seperti formula.

sFormula = [B12]     'akan menghasilkan teks isi B12

Msgbox "Isi sFormula setelah dikenai [B12] adalah " & sFormula,vbinformation,"jas inpo"    'cek aja deh

'untuk mendapatkan hasil olahan dari teks formula yang tertulis di cell B12, maka harus digunakan Evaluate().

'karena sFormula sudah diisi bunyi teks formula dari B12 oleh baris sFormula = [B12] , berarti bisa tinggal dipakai dong

'bunyi teksnya seperti formula vLookUp yang hanya bisa menghasilkan nilai skalar. Kolom B berisi datatype teks (string). Berarti hasil olahan teks formula di sFormula akan ber-type String

'jadi dipakailah variabel sHasilEval yang ber-type string untuk menampung hasil Evaluate()

sHasilEval = Evaluate( sFormula )       'hanya bisa demikian bentuknya. Jika diubah jadi sHasilEval = Evaluate( sFormula ).Value akan error... Kenapa hayo ?

brrr.... ih dingiin... 

badannya kedinginan tapi jarinya panas karena bengkak nulis dongengan...

kayanya lebih enak kalo ndongeng-nya langsung secara lisan deh...

kopdar yuk...
[eh ada yang nyahut...] ayok...
asik....
kapan, dimana, berapa yang mau dateng, di Jakarta atau dipinggiran Jakarta atau malah didaerah lain ?
[xixixi... ada yang ngegerundel...] ih... kepo amat...
wakakaka... kepo adalah sebagian tanda kecerdasan, sayangnya, harus dipakai secara cerdas juga kali ya...
kayanya detox-nya masih kurang banyak nih... ocehannya jadi gak karuan..
udah ah..

met mencoba ya guys...
kasih deh filenya. dilengkapi sendiri dengan msgbox penampil angka hasilnya ya. sila unduh disini.

BelajarVBA 011 - Range 03 (UsedRange,Intersect,Union)

Coretan Mr. Kid

Bisa jadi, salah satu alasan Tuhan memberi pilihan kebaikan dan keburukan adalah untuk membuat manusia berpikir dengan akalnya menjadi tahu bahwa hanya Tuhan yang Maha Pengampun dan hanya Tuhan yang bisa mengampuni segala dosa dan kesalahan sebesar apapun, sehingga manusia mau bersegera memohon ampunan Tuhan. Apakah kita termasuk orang yang berpikir ?

Setelah pembahasan yang lalu diakhiri dengan mendapatkan rujukan ke seluruh cell, maka mulai pembahasan kali ini adalah tentang mendapatkan suatu rujukan range yang lebih spesifik. Pembahasan kali ini akan menggunakan sebuah data di-sheet bernama 'Data15' yang ada difile bernama BelajarVBA011_03.xlsm.

Gambar berikut ini adalah bentuk data dalam sheet bernama 'Data15' tersebut.

Mendapatkan sebuah area range yang berisi seluruh data

Pembahasan terakhir yang lalu adalah mendapatkan seluruh range dalam worksheet. Sangat jarang terjadi data akan tersebar dari cell pertama (A1) sampai dengan baris terkahir di kolom terkanan suatu worksheet. Pada umumnya, data dalam sebuah worksheet akan membentuk kumpulan-kumpulan dataset atau bahkan hanya berisi sebuah tabel atau dataset kecil. Tentu saja merujuk ke seluruh range dalam worksheet akan menjadi terasa berlebihan karena sebaran area data tidak sampai seluas worksheet.

Object worksheet memiliki properti bernama UsedRange yang dapat digunakan untuk mendapatkan sebuah area yang melingkupi seluruh sebaran data. Pada gambar diatas, data tersebar mulai dari range B2 sampai L18. Baris 1 ataupun kolom A tidak berisi data. Begitu juga dengan kolom K sampai kolom terkanan dan baris 19 sampai baris terakhir range dalam worksheet. Prosedur berikut ini adalah contoh penggunaan properti UsedRange milik object Worksheet untuk mendapatkan sebuah area yang melingkupi seluruh sebaran data di sheet bernama 'Data15'.
   Public Sub TentangUsedRange()
      Dim rng As Range
   
      Set rng = Sheets("Data15").UsedRange
      rng.Copy
      MsgBox "UsedRange : " & rng.Address & vbCrLf & _
            "Jumlah area : " & rng.Areas.Count
   End Sub
Proses yang terjadi dalam prosedur tersebut adalah :

• Deklarasi variabel ber-type object Range sebagai penyimpan range yang dirujuk pada baris kode :

     Dim rng As Range

• Mengisi variabel range dengan rujukan berupa range terpakai (UsedRange) yang ada di sheet bernama 'Data15' pada baris kode :

     Set rng = Sheets("Data15").UsedRange

• Contoh proses yang dilakukan terhadap area range yang sudah didapatkan dan disimpan pada variabel bernama rng. Kali ini contohnya adalah meng-Copy ke Clipboard, pada baris kode :

     rng.Copy

• Menampilkan pesan kepada user dengan sebuah kotak pesan (MsgBox) yang diisi teks informasi tentang alamat range terpakai (UsedRange) melalui bagian berbunyi rng.Address. Pesan juga berisi (digabung) dengan jumlah area range yang tersimpan dalam variabel bernama rng melalui bagian berbunyi rng.Areas.Count. Teks pesan tentang jumlah area diletakkan pada baris baru, dengan adanya bagian berbunyi vbCrLF. Seluruh teks informasi dirangkai menjadi sebuah teks panjang dengan cara menggabungkannya, yaitu dengan memanfaatkan operator penggabungan teks berupa karakter ampersand (&).

Karena sebaran data dimulai dari B2 sampai L18, maka prosedur diatas akan menghasilkan alamat UsedRange berupa teks berbunyi '$B$2:$L$18' dengan jumlah area range adalah 1 (satu) yang artinya sebagai sebuah range yang diblok dari B2 sampai L18.

Beberapa hal yang sebaiknya dicoba adalah ketika :

1. minimal sebuah cell di baris 1 mulai kolom B diisi data, sedangkan seluruh kolom A adalah kosong, maka UsedRange akan berubah menjadi '$B$1:$L$18'
2. minimal sebuah cell di kolom A mulai baris 2 diisi data, sedangkan seluruh baris 1 adalah kosong, maka UsedRange akan berubah menjadi '$A$2:$L$18'
3. cell A1 diisi data, sedangkan di baris 1 mulai kolom B adalah kosong dan di kolom A mulai baris 2 adalah kosong, maka UsedRange akan berubah menjadi '$A$1:$L$18'
4. sebuah cell di baris 1 mulai kolom B diisi data dan sebuah cell di kolom A mulai baris 2 diisi data serta cell A1 adalah kosong, maka UsedRange akan berubah menjadi '$A$1:$L$18'

Uji coba serupa juga bisa dilakukan pada area kolom mulai kolom L diseluruh barisnya atau mulai baris 19 diseluruh kolomnya. Hasil uji coba akan membantu untuk memahami bahwa UsedRange akan selalu menunjukkan suatu area mulai cell terpojok kiri atas yang berisi data sampai dengan cell terkanan bawah yang terisi data.

Perpotongan range

Antara 2 (dua) area range yang saling berpotongan bisa didapatkan suatu area range perpotongan. Salah satu kegunaan dari memperoleh perpotongan range adalah sebagai alat cek keberadaan range yang diubah user dengan suatu area range data yang boleh diubah user. Jika ada range perpotongannya, maka user melakukan pengubahan pada tempatnya dan proses pengubahan boleh dilakukan. Jika tidak ada hasil perpotongannya, maka proses pengubahan tidak boleh terjadi.

Perpotongan range bisa didapatkan dengan menggunakan methods milik Application yang bernama Intersect. Bagian ini akan menjabarkan konsep perpotongan range dengan methods bernama Intersect tersdebut. Contohnya, pada data yang ada dalam gambar diatas, dibutuhkan data milik header data h2 dan h3 dari kedua dataset cabang. Range data (yang di-border) bisa didapatkan dari perpotongan antara kolom D:E dan I:K dengan baris 6:16. Prosedur untuk proses ini bisa berbentuk seperti prosedur berikut ini :
   Public Sub TentangIntersection()
      Dim rngCabang As Range, rngBarisData As Range
      Dim rngPotongan As Range
   
      Set rngCabang = Range("D:E,I:J")
      Set rngBarisData = Range("6:16")
      Set rngPotongan = Intersect(rngCabang, rngBarisData)
      rngPotongan.Copy
      MsgBox "Data Kolom : " & rngCabang.Address & vbCrLf & _
            "Data Baris : " & rngBarisData.Address & vbCrLf & _
            "Potongan : " & rngPotongan.Address & vbCrLf & _
            "Jumlah area : " & rngPotongan.Areas.Count
   End Sub
Proses yang terjadi pada prosedur diatas adalah :

• Deklarasi variabel rngVabang ber-type object Range sebagai wadah range yang merujuk ke kolom-kolom yang berisi header tabel h2 dan h3 dikedua dataset. Juga dideklarasikan variabel rngBarisData yang ber-type object Range sebagai wadah range yang merujuk ke baris-baris data dari kedua dataset cabang.
• Deklarasi variabel rngPotongan ber-type object Range sebagai wadah range hasil perpotongan antara rngCabang dengan rngBarisData.
• Mendefinisikan isi variabel rngCabang dengan range dikolom-kolom dengan header h2 dan h3 di kedua dataset cabang pada baris kode :

     Set rngCabang = Range("D:E,I:J")

• Mendefinisikan isi variabel rngBarisData dengan range dibaris-baris kedua dataset cabang pada baris kode :

     Set rngBarisData = Range("6:16")

• Mendefinisikan isi variabel rngPotongan dengan hasil pemanfaatan methods Intersect, dengan range yang diperpotongkan adalah rngCabang dengan rngBarisData. Proses ini ada dibaris kode :

     Set rngPotongan = Intersect(rngCabang, rngBarisData)

• Memanfaatkan hasil perpotongan range. Pada prosedur diatas, contoh pemanfaatannya adalah meng-Copy hasil perpotongan range ke Clipboard pada baris kode :

     rngPotongan.Copy

• Menampilkan pesan kepada user menggunakan sebuah kotak pesan (MsgBox) yang berisi alamat dari rngCabang, alamat rngBarisData dan alamat hasil perpotongan (rngPotongan) yang memanfaatkan properti Address milik object Range. Juga diinformasikan jumlah area range dalam hasil perpotongan (rngPotongan) menggunakan properti Count milik object collection Areas, dimana object collection Areas adalah salah satu properti milik object Range. Seluruh informasi disusun menggunakan operator penggabungan teks berupa karakter ampersand (&) dan diatur letak barisnya dengan vbCrLf.

Ketika prosedur diatas dijalankan, maka akan didapat 2 (dua) area range hasil perpotongan. Artinya, dalam variabel rngPotongan ada 2 (dua) area range. Hal ini disebabkan karena rngCabang berisi 2 (dua) area range, yaitu area range kolom D:E dan area range kolom I:J, dipotongkan dengan rngBarisData yang berisi 1 (satu) area range, yaitu baris 6 sampai baris 16.

Menyimpan banyak area range kedalam sebuah variabel object Range

Kadangkala ditemukan suatu kondisi bahwa data yang akan diolah membentuk banyak area dan tidak didapatkan secara sekaligus. Sedangkan pengolahan lanjutan akan mengolahnya secara berkesinambungan. Oleh sebab itu, area-area range yang telah didapatkan tersebut harus disimpan dalam sebuah variabel object Range.

Menyimpan berbagai area range ke dalam sebuah variabel object Range bisa dilakukan dengan memanfaatkan methods Union milik Application. Contohnya, pada gambar diatas, telah didapatkan 3 (tiga) area range yang berupa data-data di header h2 dan h3 dikedua dataset cabang yang memiliki nilai di header h2-nya. Artinya, ada area range D6:E13, I6:J10 dan I12:J15. Ketiga area range tersebut akan disimpan disebuah variabel object range agar bisa diolah lebih lanjut sebagai satu kesatuan data yang dibutuhkan. Prosedur untuk contoh ini dapat berbunyi :
   Public Sub TentangUnion()
      Dim rngCabang1 As Range
      Dim rngCabang2A As Range, rngCabang2B As Range
      Dim rngGabung As Range
    
      Set rngCabang1 = Range("D6:D13")
      Set rngCabang2A = Range("i6:i10")
      Set rngCabang2B = Range("i12:i15")
      Set rngGabung = Union(rngCabang1, rngCabang2A, rngCabang2B)
      MsgBox "Range 1 : " & rngCabang1.Address & vbCrLf & _
            "Range 2 : " & rngCabang2A.Address & vbCrLf & _
            "Range 3 : " & rngCabang2B.Address & vbCrLf & _
            "Gabungan : " & rngGabung.Address & vbCrLf & _
            "Jumlah area : " & rngGabung.Areas.Count
   End Sub
Proses yang terjadi pada prosedur diatas adalah :

• Deklarasi variabel rngCabang1 ber-type object Range sebagai wadah data header h2 dan h3 di dataset cabang1.
• Deklarasi variabel rngCabang2A ber-type object Range sebagai wadah data header h2 dan h3 di dataset cabang2 pada area atas sebelum baris cell kosong di range I11:J11. Sedang variabel rngCabang2B adalah untuk setelah baris kosong tersebut.
• Deklarasi variabel rngGabung ber-type object Range sebagai wadah hasil penggabungan seluruh area tersebut kedalam sebuah variabel object range.
• Mendefinisikan range untuk rngCabang1 dengan range D6:D13
• Mendefinisikan range untuk rngCabang2A dengan range I6:J10
• Mendefinisikan range untuk rngCabang2B dengan range I12:J15
• Mendefinisikan range untuk rngGabung dengan hasil penggabungan seluruh range yang diketahui dengan menggunakan methods Union. Proses ini adalah pada baris kode :

     Set rngGabung = Union(rngCabang1, rngCabang2A, rngCabang2B)

• Menampilkan pesan kepada user menggunakan sebuah kotak pesan (MsgBox) berisi alamat masing-masing range yang digabungkan dan alamat range hasil penggabungan. Juga disertai informasi jumlah area hasil penggabungan.

Simpulan

Pengolahan data membutuhkan rujukan range yang spesifik pada yang dibutuhkan saja. Proses otomasi atau aplikasi berbasis Excel juga dituntut untuk bisa mendapatkan rujukan range yang spesifik tersebut.

Proses perujukan range yang spesifik pada bahasan kali ini baru pada area range yang berisi data saja dan beberapa cara mendapatkan range yang dibutuhkan dari suatu range yang luas melalui perpotongan (Intersect). Selain itu juga dibahas tentang menyatukan tempat penyimpanan range dalam memori komputer (dalam variabel) menggunakan Union.

Pembelajaran lebih lanjut untuk bisa mendapatkan area data yang akan di-Intersect-kan atau yang akan di-Union-kan sangat diperlukan, sehingga proses otomasi atau aplikasi berbasis Excel yang sedang dibangun bisa menjadi lebih dinamis terhadap perubahan jumlah record data yang akan diolah.

:)

Insya Allah bahasan selanjutnya tentang Offset dan Resize. Kedua hal ini termasuk tulang punggung untuk mendinamiskan perujukan range yang dibutuhkan dalam suatu proses otomasi atau aplikasi berbasis Excel.

<previous | next>

BelajarVBA 011 - Range 02 (seluruh baris/kolom)

Coretan Mr. Kid

Meremehkan dosa sekecil apapun adalah awal bencana diri. Janganlah pernah meremehkan dosa sekecil apapun. Segeralah meminta ampunan-Nya.

Kadangkala dalam mengotomasi ataupun membangun aplikasi berbasis Excel, dibutuhkan pemilihan range utuh seluruh kolom atau seluruh baris. Bagian ini akan membahas tentang mendapatkan range di seluruh baris atau seluruh kolom tersebut.

Mendapatkan range disuatu baris

Prosedur yang digunakan untuk menampung script belajar pada kegiatan ini adalah prosedur bernama RangeSuatuBaris yang memiliki bentuk blok prosedur sebagai berikut :
   Public Sub RangeSuatuBaris()
     Dim rng As Range
   
     'baris kode akan dimulai dari baris kosong setelah baris ini
   
   End Sub

Beberapa cara merujuk range suatu baris adalah sebagai berikut :
Satu baris saja, yaitu baris 3
'>> baris 3 saja
'dengan Rows diberi inputan nilai index baris
Set rng = Rows(3)       'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address      'pesan bahwa range terpilih sudah ter-copy

'dengan Rows diberi inputan teks baris
Set rng = Rows("3")     'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address      'pesan bahwa range terpilih sudah ter-copy

'dengan Rows diberi inputan teks baris seperti inputan range
Set rng = Rows("3:3")   'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address      'pesan bahwa range terpilih sudah ter-copy

'dengan Range diberi inputan teks baris dari dan sampai
Set rng = Range("3:3")  'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address      'pesan bahwa range terpilih sudah ter-copy

'dengan Range berdasar sebuah range dalam baris tersebut
Set rng = Range("A3").EntireRow  'simpan di variabel bernama rng
rng.Copy                         'copy range terpilih ke clipboard
MsgBox rng.Address               'pesan bahwa range terpilih sudah ter-copy

Dari contoh diatas, untuk mendapatkan seluruh range pada sebuah baris (misal pada baris 3) dapat dilakukan dengan menggunakan :

1. properti Rows milik Application yang dapat diberi inputan berupa :
   • nilai index baris yang bertipe numerik (3)
   • teks nomor baris yang bertipe teks ("3")
   • teks alamat baris untuk seluruh kolom dari awal sampai akhir seperti ("3:3")
2. object Range yang :
   • merujuk langsung dengan inputan berupa teks alamat baris untuk seluruh kolom dari awal sampai akhir seperti ("3:3")
   • menggunakan properti EntireRow milik object range berdasar sebuah range di baris tersebut seperti baris kode :
        Range("A3").EntireRow
     yang berarti pada range A3 dilanjutkan proses untuk memilih suatu baris di range tersebut

Proses untuk menyusun alamat range seperti yang dijelaskan pada pembahasan sebelumnya juga bisa dimanfaatkan.

Beberapa baris yang kontinyu, yaitu dari baris 3 sampai baris 7
Baris yang kontinyu artinya akan terkumpul banyak range yang saling bersebelahan tanpa putus membentuk 1 (satu) area range. Untuk merujuk ke satu area range dari baris 3 sampai baris 7 dibutuhkan alamat range bertipe teks yang berbunyi "3:7". 
'>> dari baris 3 sampai baris 7
'dengan Rows diberi inputan teks baris
Set rng = Rows("3:7")   'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address      'pesan bahwa range terpilih sudah ter-copy

'dengan Range diberi inputan teks baris dari dan sampai
Set rng = Range("3:7")  'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address      'pesan bahwa range terpilih sudah ter-copy

'dengan Range berdasar suatu range dalam baris-baris tersebut
Set rng = Range("A3:A7").EntireRow  'simpan di variabel bernama rng
rng.Copy                            'copy range terpilih ke clipboard
MsgBox rng.Address                  'pesan bahwa range terpilih sudah ter-copy

Dari contoh diatas, properti Rows masih bisa digunakan karena yang dirujuk masih berupa 1 (satu) area range. Seluruh inputan sudah bertipe teks berupa alamat range.

Beberapa baris yang saling terpisah, yaitu baris 3, 8, dan dari baris 15 sampai baris 17
Teks alamat range untuk merujuk baris-baris seperti ini akan berbunyi "3:3,8:8,15:17". Hasilnya akan berupa 3 (tiga) buah area range, yaitu area range baris 3, area range baris 8, dan area range baris 15 sampai 17. Karena sudah ada lebih dari 1 (satu) area range, maka properti Rows sudah tidak dapat digunakan. Jadi range dirujuk menggunakan object Range.
'>> baris 3, 8, 15:17
'dengan Range diberi inputan teks alamat range
Set rng = Range("3:3,8:8,15:17")    'simpan di variabel bernama rng
rng.Copy                            'copy range terpilih ke clipboard
MsgBox rng.Address                  'pesan bahwa range terpilih sudah ter-copy

'dengan Range berdasar suatu range dalam baris-baris tersebut
Set rng = Range("A3,A8,A15:A17").EntireRow  'simpan di variabel bernama rng
rng.Copy                                    'copy range terpilih ke clipboard
MsgBox rng.Address                          'pesan range terpilih sudah ter-copy

Mendapatkan range disuatu kolom

Pada dasarnya, proses untuk mendapatkan range disuatu kolom mirip dengan untuk mendapatkan seluruh baris. Perbedaan mendasar adalah alamat range yang digunakan adalah nama-nama kolom Excel. Indeks kolom Excel bisa digunakan ketika memanfaatkan properti Columns dan hanya untuk merujuk 1 (satu) kolom saja. Seperti halnya properti Rows ketika diberi inputan berupa teks alamat range, properti Columns hanya bisa digunakan untuk merujuk ke 1 (satu) area range suatu kolom utuh.

Prosedur yang digunakan untuk menampung script belajar pada kegiatan ini adalah prosedur bernama RangeSuatuKolom yang memiliki bentuk blok prosedur sebagai berikut :
   Public Sub RangeSuatuKolom()
     Dim rng As Range
   
     'baris kode akan dimulai dari baris kosong setelah baris ini
   
   End Sub

Beberapa cara merujuk range suatu kolom adalah sebagai berikut :
Satu kolom saja, yaitu kolom B
'>> kolom B saja
'dengan Columns diberi inputan nilai indeks kolom
Set rng = Columns(2)    'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address      'pesan bahwa range terpilih sudah ter-copy

'dengan Columns diberi inputan teks kolom
Set rng = Columns("B")  'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address      'pesan bahwa range terpilih sudah ter-copy

'dengan Columns diberi inputan teks kolom seperti inputan range
Set rng = Columns("B:B")    'simpan di variabel bernama rng
rng.Copy                    'copy range terpilih ke clipboard
MsgBox rng.Address          'pesan bahwa range terpilih sudah ter-copy

'dengan Range diberi inputan teks kolom dari dan sampai
Set rng = Range("B:B")  'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address      'pesan bahwa range terpilih sudah ter-copy

'dengan Range berdasar sebuah range dalam kolom tersebut
Set rng = Range("B1").EntireColumn  'simpan di variabel bernama rng
rng.Copy                            'copy range terpilih ke clipboard
MsgBox rng.Address                  'pesan bahwa range terpilih sudah ter-copy

Contoh-contoh diatas menggunakan properti Columns milik Application dan ada yang menggunakan properti EntireColumn milik object Range.

Beberapa kolom yang kontinyu, yaitu dari kolom B sampai kolom E
Teks alamat range untuk merujuk ke suatu kolom dari kolom B sampai kolom E akan berbunyi "B:E".
'>> dari kolom B sampai kolom E
'dengan Columns diberi inputan teks kolom seperti inputan range
Set rng = Columns("B:E")    'simpan di variabel bernama rng
rng.Copy                    'copy range terpilih ke clipboard
MsgBox rng.Address          'pesan bahwa range terpilih sudah ter-copy

'dengan Range diberi inputan teks kolom dari dan sampai
Set rng = Range("B:E")  'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address      'pesan bahwa range terpilih sudah ter-copy

'dengan Range berdasar suatu range dalam kolom tersebut
Set rng = Range("B1:E1").EntireColumn   'simpan di variabel bernama rng
rng.Copy                                'copy range terpilih ke clipboard
MsgBox rng.Address                      'pesan range terpilih sudah ter-copy

Dari contoh diatas, penggunaan indeks kolom Excel sudah tidak dapat digunakan lagi karena akan merujuk ke banyak kolom. Inputan sudah berupa teks alamat range.

Beberapa kolom yang saling terpisah, yaitu kolom B, F, dan dari kolom K sampai kolom M
Teks alamat range untuk merujuk kolom-kolom seperti ini akan berbunyi "B:B,F:F,K:M". Hasilnya akan berupa 3 (tiga) buah area range, yaitu area range kolom B, area range kolom F, dan area range kolom K sampai M. Karena sudah ada lebih dari 1 (satu) area range, maka properti Columns sudah tidak dapat digunakan. Jadi range dirujuk menggunakan object Range.
'>> kolom B, F, K:M
'dengan Range diberi inputan teks kolom dari dan sampai
Set rng = Range("B:B,F:F,K:M")  'simpan di variabel bernama rng
rng.Copy                        'copy range terpilih ke clipboard
MsgBox rng.Address              'pesan bahwa range terpilih sudah ter-copy

'dengan Range berdasar suatu range dalam kolom tersebut
Set rng = Range("B1,F1,K1:M1").EntireColumn 'simpan di variabel bernama rng
rng.Copy                                    'copy range terpilih ke clipboard
MsgBox rng.Address                          'pesan range terpilih sudah ter-copy

Mendapatkan seluruh range dalam worksheet

Keadaan ini bisa juga diartikan seluruh baris atau seluruh kolom yang ada dalam worksheet. Prosedur yang digunakan untuk menampung script belajar pada kegiatan ini adalah prosedur bernama RangeSemuanya yang memiliki bentuk blok prosedur sebagai berikut :
   Public Sub RangeSemuanya()
      Dim rng As Range
   
      'baris kode akan dimulai dari baris kosong setelah baris ini
   
   End Sub

Beberapa cara merujuk range seluruh range dalam worksheet adalah sebagai berikut :
Cara 1 : memanfaatkan properti Cells
'Cara 1 : Cells
Set rng = Cells     'simpan di variabel bernama rng
rng.Copy            'copy range terpilih ke clipboard
MsgBox rng.Address  'pesan bahwa range terpilih sudah ter-copy

Cara 2 : merujuk seluruh range pada baris pertama kemudian merujuk ke seluruh kolomnya
'Cara 2 : Rows 1 dan EntireColumn
Set rng = Rows(1)           'merujuk baris pertama secara utuh
Set rng = rng.EntireColumn  'hasilnya dirujuk ke seluruh kolom
rng.Copy                    'copy range terpilih ke clipboard
MsgBox rng.Address          'pesan bahwa range terpilih sudah ter-copy
Cara 3 : merujuk seluruh range pada kolom pertama kemudian merujuk ke seluruh barisnya
'Cara 3 : Columns 1 dan EntireRow
Set rng = Columns(1)        'merujuk kolom pertama secara utuh
Set rng = rng.EntireRow     'hasilnya dirujuk ke seluruh kolom
rng.Copy                    'copy range terpilih ke clipboard
MsgBox rng.Address          'pesan bahwa range terpilih sudah ter-copy

Simpulan

Beberapa hal yang menjadi benang merah dari contoh-contoh diatas adalah :

• Properti Rows atau Columns dapat digunakan untuk merujuk ke seluruh range dalam baris-baris tersebut atau kolom-kolom tersebut selama hasilnya membentuk 1 (satu) area range. Jadi, baris-baris atau kolom-kolom yang dirujuk haruslah kontinyu.
• Indeks baris maupun indeks kolom bisa digunakan sebagai inputan pada penggunaan properti Rows atau Columns untuk merujuk ke 1 (satu) baris saja atau 1 (satu) kolom saja. Hal ini dikarenakan area range yang dihasilkan adalah sebanyak 1 (satu) area range, yaitu 1 (satu) baris saja atau 1 (satu) kolom saja.
• Untuk merujuk lebih dari satu baris atau satu kolom, maka dibutuhkan alamat range yang menunjukkan dari range awal sampai range akhir yang bertipe teks.
• Object Range memiliki keluwesan yang lebih tinggi daripada properti Rows atau Columns, karena bisa merujuk ke banyak area.
• Object Range membutuhkan inputan berupa teks alamat range.
• Properti EntireColumn atau EntireRow milik object Range dapat digunakan untuk merujuk ke seluruh range pada baris-baris atau kolom-kolom yang dibutuhkan dengan diketahui sebuah range dimasing-masing baris-baris atau kolom-kolom yang dibutuhkan.
• Properti Cells akan menghasilkan seluruh range dalam worksheet.
• Merujuk ke seluruh range dalam worksheet dapat juga dilakukan dengan merujuk ke seluruh range pada baris pertama lalu merujuk ke seluruh kolom yang ada, atau sebaliknya, yaitu merujuk ke seluruh range pada kolom pertama lalu merujuk ke seluruh baris yang ada.

:)

Insya Allah, pembahasan selanjutnya tentang dirty range yang sering disebut Used Range, perpotongan range, perujukan ke banyak range yang terpisah dalam sebuah variabel melalui banyak proses (Union).

<previous | next>

BelajarVBA 011 - Range 01 (berdasar alamat range)

Coretan Mr. Kid



Sudahkah kita mensyukuri apa yang kita dapatkan hari ini ?

Kegiatan VBA dalam Excel tidak pernah bisa lepas dari melakukan sesuatu terhadap range. Range adalah object yang paling pokok dalam Excel. Ketika bisa memanfaatkan range dengan baik, maka sebagian besar kegiatan otomasi ataupun pembangunan aplikasi berbasis Excel akan menjadi lebih mudah. Bahasan kali ini adalah tentang range yang akan dimulai dari hal dasarnya, yaitu tahu cara merujuk ke suatu range yang dibutuhkan. Apapun tindakan terhadap range adalah permasalahan yang bisa dipelajari lebih lanjut setelah tahu cara mendapatkan range yang dibutuhkan. Usahakan untuk mencobanya secara langsung pada sebuah workbook ber-macro (.xlsm) disebuah module umum.

Mendapatkan range dialamat tertentu

Hal dasar dalam merujuk suatu range adalah mengetahui ada dimana range yang dibutuhkan tersebut. Yang paling sering adalah merujuk range yang sudah diketahui alamatnya, seperti range di B2 atau di D4 sampai G9 dan sebagainya. Jika sudah diketahui alamatnya, maka alamat tersebut disusun berupa data teks. Jika ditulis langsung, maka pasti harus diapit karakter tanda petik dua ("). Jika disimpan dalam sebuah variabel, maka variabelnya harus ber-datatype String.

Untuk bahasan pertama ini, seluruh script akan diletakkan pada sebuah prosedur bernama RangeAlamatTertentu di sebuah module umum. Bunyi blok prosedur tersebut adalah :
Public Sub RangeAlamatTertentu()
    Dim rng As Range
    Dim sAlamat As String, lBaris As Long, sKolom As String
    Dim sAlamat1 As String, lBaris1 As Long, sKolom1 As String
    Dim sAlamat2 As String, lBaris2 As Long, sKolom2 As String
    Dim sAlamatDari As String, sAlamatSampai As String
    Dim lBarisDari As Long, lBarisSampai As Long
    Dim sKolomDari As String, sKolomSampai As String
   
    'baris kode akan dimulai dari baris kosong setelah baris ini
   
End Sub

Beberapa cara merujuk range berdasar alamat yang sudah diketahui :
Sebuah range dengan alamat B2
'>> alamat B2 saja
'langsung
Set rng = Range("B2")           'simpan di variabel bernama rng
rng.Value = "Be-Excel"          'tulis ke range
rng.Copy                        'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address   'pesan bahwa range terpilih sudah ter-copy 

'pakai variabel alamat
sAlamat = "B2"                  'simpan alamat di variabel bernama sAlamat
Set rng = Range(sAlamat)        'simpan di variabel bernama rng
rng.Value = "Be-Excel"          'tulis ke range
rng.Copy                        'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address   'pesan bahwa range terpilih sudah ter-copy 

'nomor baris divariabel tertentu
lBaris = 2                      'simpan nomor baris di variabel bernama lBaris
sAlamat = "B" & lBaris          'susun alamat berbunyi B2 dan disimpan di sAlamat
Set rng = Range(sAlamat)        'simpan di variabel bernama rng
rng.Value = "Be-Excel"          'tulis ke range
rng.Copy                        'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address   'pesan bahwa range terpilih sudah ter-copy

'nama kolom divariabel tertentu
sKolom = "B"                    'simpan nama kolom di variabel bernama sKolom
sAlamat = sKolom & 2            'susun alamat berbunyi B2 dan disimpan di sAlamat
Set rng = Range(sAlamat)        'simpan di variabel bernama rng
rng.Value = "Be-Excel"          'tulis ke range
rng.Copy                        'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address   'pesan bahwa range terpilih sudah ter-copy 

'nomor baris dan nama kolom divariabel tertentu
lBaris = 2                      'simpan nomor baris di variabel bernama lBaris
sKolom = "B"                    'simpan nama kolom di variabel bernama sKolom
sAlamat = sKolom & lBaris       'susun alamat berbunyi B2 dan disimpan di sAlamat
Set rng = Range(sAlamat)        'simpan di variabel bernama rng
rng.Value = "Be-Excel"          'tulis ke range
rng.Copy                        'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address   'pesan bahwa range terpilih sudah ter-copy

Dari contoh script diatas tampak bahwa kegiatan yang utama adalah mendapatkan alamat range yang dibutuhkan. Apapun informasi yang sudah dimiliki, harus bisa disusun menjadi alamat range yang dibutuhkan, meski harus memprosesnya lebih dulu seperti penyusunan alamat pada baris-baris kode sAlamat = blablabla.

Beberapa range yang terpisah, yaitu K2 dan M2
Alamat range disusun dengan menggabungkan seluruh alamat yang dibutuhkan dengan dipisah karakter koma (,). Untuk merujuk ke range K2 dan M2, maka teks alamat harus tersusun berbunyi "K2,M2".
'>> alamat K2 dan M2
'langsung
Set rng = Range("K2,M2")            'simpan di variabel bernama rng
rng.Value = "Be-Excel"              'tulis ke range
rng.Copy                            'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address       'pesan bahwa range terpilih sudah ter-copy

'pakai variabel alamat
sAlamat = "K2,M2"                   'simpan alamat di variabel bernama sAlamat
Set rng = Range(sAlamat)            'simpan di variabel bernama rng
rng.Value = "Be-Excel"              'tulis ke range
rng.Copy                            'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address       'pesan bahwa range terpilih sudah ter-copy

'alamatnya didapat dari 2 variabel berbeda
sAlamat1 = "K2"                     'simpan alamat1 di variabel bernama sAlamat1
sAlamat2 = "M2"                     'simpan alamat2 di variabel bernama sAlamat2
sAlamat = sAlamat1 & "," & sAlamat2 'susun alamat lengkap dan simpan di sAlamat
Set rng = Range(sAlamat)            'simpan di variabel bernama rng
rng.Value = "Be-Excel"              'tulis ke range
rng.Copy                            'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address       'pesan bahwa range terpilih sudah ter-copy

'semua alamatnya berupa elemen penyusun
lBaris1 = 2                         'simpan baris alamat1 di lBaris1
sKolom1 = "K"                       'simpan kolom alamat1 di sKolom1
sAlamat1 = sKolom1 & lBaris1        'susun alamat1 dan simpan di sAlamat1
lBaris2 = 2                         'simpan baris alamat2 di lBaris2
sKolom2 = "M"                       'simpan kolom alamat2 di sKolom2
sAlamat2 = sKolom2 & lBaris2        'susun alamat2 dan simpan di sAlamat2
sAlamat = sAlamat1 & "," & sAlamat2 'susun alamat lengkap dan simpan di sAlamat
Set rng = Range(sAlamat)            'simpan di variabel bernama rng
rng.Value = "Be-Excel"              'tulis ke range
rng.Copy                            'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address       'pesan bahwa range terpilih sudah ter-copy

Dari contoh script diatas, apapun proses untuk mendapatkan masing-masing alamat, tetap saja harus ada sebuah proses yang memastikan bahwa ada alamat yang berbunyi "K2,M2" alias adanya karakter koma yang menjadi delimiter antar alamat range yang dibutuhkan. Penyusunan alamat yang memasang karakter koma tersebut adalah pada baris kode berbunyi :
   sAlamat = sAlamat1 & "," & sAlamat2
yaitu teks alamat pertama digabungkan dengan karakter koma dan digabungkan juga dengan teks alamat kedua.

Dari range pertama sampai range kedua, yaitu D2 sampai G9
Alamat range disusun dengan menggabungkan seluruh alamat yang dibutuhkan dengan dipisah karakter titik dua (:). Untuk merujuk ke range D2 sampai G9, maka teks alamat harus tersusun berbunyi "D2:G9".
'>> alamat D2 sampai G9
'langsung
Set rng = Range("D2:G9")         'simpan di variabel bernama rng
rng.Value = "Be-Excel"           'tulis ke range
rng.Copy                         'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address    'pesan bahwa range terpilih sudah ter-copy

'pakai variabel alamat
sAlamat = "D2:G9"                'simpan alamat di variabel bernama sAlamat
Set rng = Range(sAlamat)         'simpan di variabel bernama rng
rng.Value = "Be-Excel"           'tulis ke range
rng.Copy                         'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address    'pesan bahwa range terpilih sudah ter-copy

'alamatnya didapat dari 2 variabel berbeda
sAlamatDari = "D2"                          'simpan dari di sAlamatDari
sAlamatSampai = "G9"                        'simpan sampai di sAlamatSampai
sAlamat = sAlamatDari & ":" & sAlamatSampai 'susunan lengkap, simpan di sAlamat
Set rng = Range(sAlamat)                    'simpan di variabel bernama rng
rng.Value = "Be-Excel"                      'tulis ke range
rng.Copy                                    'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address               'pesan sudah ter-copy

Dari contoh script diatas, Penyusunan alamat yang memasang karakter titik dua adalah pada baris kode berbunyi :
   sAlamat = sAlamat1 & ":" & sAlamat2
yaitu teks alamat pertama digabungkan dengan karakter titik dua dan digabungkan juga dengan teks alamat kedua. Sampai disini, tentu sudah bisa menyusun alamat range yang data penyusun setiap alamatnya terpisah-pisah seperti dari kolom D baris 2 sampai kolom G baris 9. Silakan dicoba.

Beberapa range tunggal terpisah dan sebuah area range, yaitu B10 dan dari C13 sampai L27
Alamat range disusun dengan menggabungkan kedua bentuk contoh diatas. Alamat range B10 digabungkan dengan alamat range dari C13 sampai L27 dengan karakter pemisah berupa karakter koma (,). Alamat range C13 sampai L27 itu sendiri disusun dengan menggabungkan alamat C13 dan L27 dengan karakter pemisah berupa karakter titik dua (:). Jadi alamat lengkapnya akan berupa teks berbunyi "B10,C13:L27".
'>> alamat B10 dan dari C13 sampai L27
'langsung
Set rng = Range("B10,C13:L27")   'simpan di variabel bernama rng
rng.Value = "Be-Excel"           'tulis ke range
MsgBox "Range " & rng.Address    'pesan alamat range terpilih

'pakai variabel alamat
sAlamat = "B10,C13:L27"          'simpan alamat di variabel bernama sAlamat
Set rng = Range(sAlamat)         'simpan di variabel bernama rng
rng.Value = "Be-Excel"           'tulis ke range
MsgBox "Range " & rng.Address    'pesan alamat range terpilih 

'alamatnya didapat dari variabel-variabel yang berbeda
sAlamat1 = "B10"                             'simpan alamat1 di sAlamat1
sAlamatDari = "C13"                          'simpan dari di sAlamatDari
sAlamatSampai = "L27"                        'simpan sampai di sAlamatSampai
sAlamat2 = sAlamatDari & ":" & sAlamatSampai 'susun alamat2 di sAlamat2
sAlamat = sAlamat1 & "," & sAlamat2          'susun lengkap di sAlamat
Set rng = Range(sAlamat)                     'simpan di variabel bernama rng
rng.Value = "Be-Excel"                       'tulis ke range
MsgBox "Range " & rng.Address                'pesan alamat range terpilih

Dari contoh diatas, tampak bahwa ada proses penyusunan alamat yang dari suatu range sampai range lainnya dengan karakter pemisah adalah karakter titik dua (:). Kemudian alamat lengkap disusun dengan menggabungkan alamat pertama dengan hasil penyusunan alamat kedua dengan karakter pemisah adalah karakter koma (,).

Pada contoh ini juga tidak ada proses meng-Copy range terpilih ke Clipboard. Hal ini disebabkan karena range terpilih membentuk 2 (dua) area range yang tidak memiliki jumlah baris atau jumlah kolom yang sama setiap areanya. Selain itu, juga tidak memiliki nomor baris atau lokasi kolom pojok kiri atas yang sama disetiap areanya. Area pertama adalah area range 1 baris x 1 kolom di range B10. Area kedua adalah C13:L27 yang terdiri dari 15 baris x 10 kolom. Area pertama tidak dimulai (pojok kiri atasnya) di baris 13 atau di kolom C. Jika area pertama membentu area dengan dimensi N baris x 10 kolom dengan pojok kiri atas area di kolom C atau 15 baris x N kolom dengan pojok kiri atas area di baris 13, maka proses Copy terhadap range terpilih dapat dilakukan dan tidak menimbulkan error.

Sampai disini, tentu sudah bisa menyusun alamat range yang setiap data penyusunnya terpisah seperti kolom A baris 10 dan dari kolom C baris 13 sampai kolom L baris 27. Silakan dicoba.

Simpulan

Merujuk ke suatu range berdasar alamatnya cenderung membutuhkan pemahaman bekerja dengan datatype String. Pemahaman untuk bekerja dengan datatype String digunakan untuk menyusun secara detil dan tepat alamat range yang akan dirujuk atau yang dibutuhkan. Pada umumnya, dengan proses setahap demi setahap yang membutuhkan banyak variabel seperti pada contoh-contoh diatas, akan memudahkan menyusun alur proses penyusunannya. Hal ini akan mengurangi kemungkinan munculnya kesalahan dalam menyusun baris kode. Biasanya, dengan latihan terus menerus akan tiba juga untuk bisa bekerja dengan datatype String dalam menyusun bentukan teks tertentu tanpa menggunakan banyak variabel.

Bahasan diatas bisa jadi terasa terlalu dasar dan tampak sepele. Tetapi, bahasan diatas adalah konsep dasar tentang merujuk suatu range. Semoga bahasan diatas bisa mudah dipahami, sehingga akan memudahkan pemahaman pada pembahasan-pembahasan berikutnya.

:)

Insya Allah bahasan berikutnya adalah tentang merujuk ke range diseluruh baris atau diseluruh kolom.

<previous | next>

Membuat List Data pada Sel

Kesalahan dalam pada saat menginput data tentunya bukan merupakan hal yang diharapkan. Karena kesalahan itulah hasil akhir dari perhitungan menjadi salah dan tidak sesuai dengan harapan. Untuk menghindari hal tersebut, dapat dilakukan dengan Membuat List Data pada Sel. Hal ini dilakukan agar nantinya proses input sesuai dengan yang diharapkan. Sehingga kesalahan tersebut dapat diminimalisir untuk menunjang proses perhitungan pada bisnis anda.

OK. Sekarang simak baik-baik ya Trik dari Info Excel dalam Membuat List Data pada Sel sebagai berikut :

1. Buka program Microsoft Exel 2007.
2. Pilihh menu Data – Data Validation
3. Pada Tab Setting,  aturlah sebagai berikut :

   	Keterangan : 1. Allow –> ListIgnore blank : hilangkan tanda cek, agar nantinya pada alamat sel tersebut tidak kosong. In-cell dropdown : data yang dimasukkan itu akan seperti menu dropdown (menyoroti ke bawah). 2. SourceIsi sesuai data yang akan Anda tampilkan pada sel tersebut.
   	Pilih Menu tab Eror Alert. Eror Alert merupakan tanda bahwa data yang dimasukkan tidak sesuai dengan list data pada sel tersebut, sehingga secara otomatis sistem akan memberhentikan kegiatan penginputan data. Tuliskan juga Error message : Input sesuai pilihan !!Setelah itu klik OK untuk melanjutkan perintah.

4. Setelah melakukan tutorial di atas, dapat dilihat hasilnya sebagai berikut :
5. Apabila dalam melakukan input data pada area sel tersebut tidak sesuai list, maka akan muncul pesan sebagai berikut :

Untuk hasil yang maksimal, Anda dapat memanfaatkan Conditional Formatting sebagai pembeda warna dari suatu sel.

Sekian dulu trik yang Info Excel tulis. Nantikan tulisan-tulisan dari Info Excel menarik lainnya. :D