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.