BelajarVBA 012 - Formula

Coretan Mr. Kid

Selamat Tahun Baru 1435 H. Mari bersegera melakukan hijrah ke sesuatu yang lebih baik menurut tuntunan agama.

Mari kita lanjutkan obrolan tentang VBA yang sempat tertunda beberapa saat ini. Pembahasan kali ini adalah tentang memasang formula ke suatu area range dengan VBA. File yang bisa digunakan untuk mencoba secara mandiri adalah BelajarVBA012.xlsm.

Excel Reference Style

Excel menyediakan dua style dalam mereferensi ke suatu range, yaitu A1 dan R1C1. Style tersebut adalah tentang cara menuliskan rujukan ke suatu range. Karakteristik rujukan dan fungsional dari kedua style adalah sama. Pada A1 Style, karakter A mewakili cara penulisan indeks kolom Excel dan karakter 1 mewakili indeks baris Excel. Pada R1C1 Style, karakter R berarti Row yang diikuti dengan nomor indeks baris Excel yang diwakili oleh karakter 1 dibelakang karakter R. Sedangkan karakter C berarti Column yang diikuti dengan nomor indeks kolom Excel yang diwakili oleh karakter 1 dibelakang karakter C. Contoh A1 Style adalah sebuah alamat cell yang ditulis dengan $C$17. Karakter C menunjukkan indeks kolom Excel ke-3 dan karakter 17 mewakili indeks baris Excel ke-17.  Jika alamat cell ber-A1 Style tadi ditulis dalam R1C1 style, maka menjadi R17C3 yang berarti Row ke-17 dan Column ke-3.

Banyak pengguna VBA yang berpendapat bahwa penggunaan R1C1 style dalam menyusun formula suatu range melalui VBA lebih mudah daripada menggunakan A1 style. Banyak juga pengguna VBA yang berpendapat bahwa penggunaan A1 style dalam menyusun formula suatu range melalui VBA lebih mudah daripada menggunakan R1C1 style. Pendapat saya pribadi, jika pengguna VBA saat berkegiatan di worksheet merasa lebih mudah menggunakan A1 style, biasanya akan lebih mudah menggunakan A1 style dalam menyusun formula suatu range melalui VBA. Begitu juga sebaliknya, jika pengguna VBA saat berkegiatan di worksheet merasa lebih mudah menggunakan R1C1 style, biasanya akan lebih mudah menggunakan R1C1 style dalam menyusun formula suatu range melalui VBA. Ketika pengguna VBA saat berkegiatan di worksheet merasa nyaman menggunakan style apapun, maka akan ada banyak opsi saat menyusun formula suatu range melalui VBA. Jadi, tetaplah pada apa yang telah dikuasai dan mulailah untuk dikembangkan. Tidak perlu memperdebatkan style mana yang lebih mudah digunakan dalam VBA.

Pemasangan formula dengan VBA adalah dengan memanfaatkan properti milik object range yang bernama :

1. Formula untuk A1 style dengan syntax :
   object_range.Formula = "=bunyi formula"


2. FormulaR1C1 untuk R1C1 style dengan syntax :
   object_range.FormulaR1C1 = "=bunyi formula"

Memasang formula ke suatu range dengan VBA

Data yang akan digunakan dalam pembahasan ini seperti yang ada di sheet bernama VBA dalam file BelajarVBA012.xlsm yang berisi seperti gambar berikut ini :

Pada gambar diatas, data menggunakan A1 style dan formula yang dibutuhkan adalah untuk menghitung :

1. Total Pembelian pada kolom total_beli dengan formulasi nilai kolom beli * nilai kolom qty
2. Total Penjualan pada kolom total_jual dengan formulasi nilai kolom jual * nilai kolom qty

Dari keadaan yang ada, maka dapat dijabarkan bahwa :

• Karena kolom total_beli dan total_jual merujuk ke kolom qty, maka pada rujukan ke kolom qty dalam formula membutuhkan absolute reference pada sisi kolom ketika merujuk ke kolom qty.
• Kolom total_beli berdampingan dengan kolom total_jual dan kolom beli yang dibutuhkan oleh formulasi total_beli juga berdampingan dengan kolom jual yang dibutuhkan formulasi total_jual, sehingga rujukan ke kolom beli saat menghitung total_beli, tidak perlu absolute reference pada sisi kolom agar formula bisa di-copy ke kolom total_jual yang secara otomatis akan membuat formulasi pada kolom total_jual akan merujuk ke kolom jual akibat proses copy formula tersebut.
• Baris yang diproses adalah pada baris yang sama dan baris data lainnya juga merujuk ke baris masing-masing. Artinya, pada sisi baris yang merujuk ke kolom beli, kolom jual, dan kolom qty tidak membutuhkan absolute reference, sehingga formula bisa di-copy ke baris data berikutnya.

Langkah-langkah pemasangan formula ke area range diseluruh record data pada kolom total_beli dan total_jual adalah sebagai berikut :

1. Susun formula universal pada worksheet untuk mengkalkulasi total_beli pada record pertamanya.
   • Bunyi formula sesuai reference style worksheet : (pilih salah satu)
     1. Formula dengan A1 style di cell i2 adalah =F2*$H2
     2. Formula dengan R1C1 style di cell R2C9 adalah =RC[-3]*RC8
   • Buat sebuah general module dan sebuah prosedur sub untuk memasang formula. Misalkan nama prosedurnya adalah PasangFormula dengan bentuk blok prosedur sebagai berikut :

     Public Sub PasangFormula()
     
     End Sub

2. Definisikan variabel kerja
   Dalam prosedur tersebut diberi baris kode deklarasi variabel kerja sebagai berikut :
   

   Dim rngData As Range    'area data (termasuk header)
   Dim rngTarget As Range  'area yang dipasangi formula
   Dim lRecords As Long    'jumlah record

3. Definisikan range yang akan dipasangi formula
   Pendefinisian dilakukan dengan informasi sebagai berikut :
   
   1. Data berada di sheet bernama VBA, yang dimulai pada cell pertama (baris ke-1 dan kolom ke-1) dan berbentuk tabel yang baik. Area data seluruhnya bisa didefiniskan dengan properti CurrentRegion dari Cells(1,1) pada sheet VBA.
   2. Jumlah baris yang akan dipasangi formula adalah sebanyak jumlah record data. Jumlah record data adalah jumlah baris data seluruhnya (termasuk header) dikurangi jumlah baris header (pada kasus ini, header membutuhkan 1 baris saja).
   3. Area range yang akan dipasangi formula adalah area data seluruhnya yang diubah dimensi barisnya (resize pada sisi baris) menjadi sebanyak jumlah record data.
   4. Jumlah kolom yang akan dipasangi formula yang berdampingan adalah sebanyak 2 kolom, yaitu kolom total_beli dan kolom total_jual yang berdampingan. Artinya, dibutuhkan pengubahan dimensi kolom dari seluruh area data menjadi sebanyak 2 kolom (resize pada sisi kolom menjadi 2).
   5. Range yang akan dipasangi formula terletak mulai dibawah header (mulai baris ke-2), artinya, diperlukan offset sebanyak jumlah baris header dari seluruh area data (termasuk header). Pada kasus ini, header data hanya membutuhkan 1 baris saja, maka dilakukan offset sebanyak 1 baris.
   6. Kolom pertama yang akan dipasangi formula terletak pada kolom ke-9, artinya, diperlukan offset sebanyak 8 kolom kekanan dari kolom pertama data.
   
   Dari pendefinisian diatas, maka dapat disusun baris kode untuk mendapatkan area range yang akan dipasangi formula sebagai berikut :
   

   Set rngData = Sheets("VBA").Cells(1, 1).CurrentRegion
   lRecords = rngData.Rows.Count - 1
   Set rngTarget = rngData.Resize(lRecords, 2).Offset(1, 8)

4. Memasang formula pada area range
   Informasi yang diketahui adalah sebagai berikut :
   
   • Area range yang akan dipasangi formula telah didefinisikan dalam variabel rngTarget. Artinya, properti milik variabel rngTarget yang akan digunakan.
   • Bunyi formula yang disusun dalam worksheet adalah : (pilih salah satu reference style)
     1. A1 style berbunyi =F2*$H2
     2. R1C1 style berbunyi =RC[-3]*RC8
   • Formula dalam worksheet telah bersifat universal atau dapat di-copy langsung ke seluruh area range yang harus dipasangi formula.
   
   Dari informasi diatas, baris kode untuk memasang formula pada area range rngFormula adalah sebagai berikut : (pilih style sesuai kenyamanan masing-masing)
   
   1. A1 style :

      rngTarget.Formula = "=F2*$H2"

   2. R1C1 style :

      rngTarget.FormulaR1C1 = "=RC[-3]*RC8"

Bentuk prosedur bernama PasangFormula akan menjadi :
Public Sub PasangFormula()
    '------------------------'
    'deklarasi variabel kerja'
    '------------------------'
    Dim rngData As Range        'area data (termasuk header)
    Dim rngTarget As Range      'area yang dipasangi formula
    Dim lRecords As Long        'jumlah record
   
    '-----------------------------------------------'
    'pendefinisian area range yang dipasangi formula'
    '-----------------------------------------------'
    '1. pendefinisian area data seluruhnya
    'data tersusun sebagai tabel yang baik -> currentregion
    Set rngData = Sheets("VBA").Cells(1, 1).CurrentRegion
   
    '2. pendefinisian jumlah record data
    'nilai 1 adalah jumlah baris untuk header data
    lRecords = rngData.Rows.Count - 1
   
    '3 sampai 6 pendefinisian area range yang dipasangi formula
    '- rngData diubah dimensinya (resize) menjadi lRecords baris dan 2 kolom
    '- kemudian di-offset sebanyak 1 baris dan 8 kolom
    Set rngTarget = rngData.Resize(lRecords, 2).Offset(1, 8)
   
    '-----------------------------------------------'
    'pemasangan formula'
    '-----------------------------------------------'
    rngTarget.Formula = "=F2*$H2"
End Sub

Memasang Array Formula

Array formula selalu menghasilkan suatu blok array. Range yang masuk dalam area blok array tersebut tidak dapat diubah kecuali seluruh range yang ada dalam blok array tersebut yang diubah. Dimensi terkecil dari array blok adalah 1 baris x 1 kolom yang berisi sebuah cell berarray formula. Karakteristik lain dari blok array adalah tidak dapat diubah isinya dengan paste ketika salah satu range atau seluruh range dari blok array tersebut sedang aktif dikenai proses copy.

Misalnya, cell C17 berisi array formula berbunyi =MIN( IF( A1:A5>0 , A1:A5 ) ) yang akan membentuk sebuah blok array berdimensi 1 baris x 1 kolom. Ketika formula di C17 juga akan dipasang pada area range D17 sampai F17, maka cell C17 di-copy ke clipboard dengan menekan CTRL C. Jika range C17 sampai F17 diblok dan dilakukan paste (menekan CTRL V), maka proses paste akan gagal karena cell C17 adalah blok array yang sedang aktif di-copy. Maka proses paste ke area D17 sampai F17 harus dengan cara memblok D17 sampai F17 tanpa menyertakan C17, yang kemudian dilakukan paste dengan menekan CTRL V.

Array formula dipasang ke suatu range dengan memanfaatkan properti FormulaArray milik object range dengan syntax :
   object_range.FormulaArray = "=bunyi array formula"
Ketika properti FormulaArray digunakan pada suatu object range yang berisi banyak cells, maka seluruh range yang dikenai properti FormulaArray akan membentuk sebuah blok array. Misalnya, ada sebuah variabel range yang bernama rngTarget yang berisi range D5:F9. Kemudian rngTarget dikenai properti FormulaArray dan diberi bunyi teks array formula apapun. Maka range D5:F9 akan membentuk sebuah blok array yang tidak akan mungkin diubah salah satu isi dari range D5:F9 tersebut.

Dengan demikian, pemasangan array formula membutuhkan ketelitian lebih dalam menentukan range yang akan diberi array formula agar blok array yang terbentuk sesuai kebutuhan. Pemasangan array formula dilakukan pada satu lokasi lebih dulu, kemudian di-copy ke lokasi lain. Artinya, diperlukan pendefinisian area range yang akan dipasangi array formula sebagai lokasi pertama dan pendefinisian area range yang akan menjadi lokasi paste dari array formula yang telah terpasang di lokasi pertama tadi. Syaratnya, area range lokasi paste tidak berpotongan sama sekali dengan area range lokasi pemasangan array formula pertama.

Contoh pemasangan array formula bisa memanfaatkan data yang ada di sheet bernama 'Array' seperti gambar berikut :

Kasus untuk memperlajari pemasangan array formula akan menggunakan A1 style. Contoh kasus tersebut adalah sebagai berikut :

1. Cell E2 diberi array formula berbunyi =A2, sehingga dalam cell E2 akan tampil di formula bar sebagai {=A2}. Kemudian array formula di E2 akan di-copy sampai E4. Misalkan nama prosedurnya adalah ArrayFormula1, maka bunyi prosedurnya adalah :
   Public Sub ArrayFormula1()
       Dim rngPertama As Range  'range pertama yang diberi array formula
       Dim rngPaste As Range    'range lokasi paste array formula
      
       'lokasi pasang array formula yang akan di-copy
       Set rngPertama = Sheets("Array").Range("E2")
      
       'lokasi paste array formula di rngPertama
       Set rngPaste = Sheets("Array").Range("E3:E4")
      
       'pasang array formula di lokasi pertama
       rngPertama.FormulaArray = "=A2"
      
       'proses copy paste formula
       rngPertama.Copy
       rngPaste.PasteSpecial xlPasteFormulas
   End Sub


2. Cell G2 diberi array formula berbunyi =A2, sehingga dalam cell G2 akan tampil di formula bar sebagai {=A2}. Kemudian array formula di G2 akan di-copy sampai H4. Proses paste dilakukan dalam 2 tahap, yaitu tahap 1 adalah ke area paste pertama di G3:G4 dan tahap 2 ke area paste kedua di H2:H4. Misalkan nama prosedurnya adalah ArrayFormula2, maka bunyi prosedurnya adalah :
   Public Sub ArrayFormula2()
       Dim rngPertama As Range   'range pertama yang diberi array formula
       Dim rngPaste1 As Range    'range lokasi paste 1 array formula
       Dim rngPaste2 As Range    'range lokasi paste 2 array formula
      
       'lokasi pasang array formula yang akan di-copy
       Set rngPertama = Sheets("Array").Range("G2")
      
       'lokasi paste array formula di rngPertama
       Set rngPaste1 = Sheets("Array").Range("G3:G4")
       Set rngPaste2 = Sheets("Array").Range("H2:H4")
      
       'pasang array formula di lokasi pertama
       rngPertama.FormulaArray = "=A2"
      
       'proses copy paste formula
       rngPertama.Copy
       rngPaste1.PasteSpecial xlPasteFormulas
       rngPaste2.PasteSpecial xlPasteFormulas
   End Sub


3. Range J2:K2 (1 baris x 2 kolom) diberi array formula berbunyi =A2:B2 (juga 1 baris x 2 kolom), sehingga dalam cell J2 dan K2 akan tampil di formula bar sebagai {=A2:B2}. Range J2:K2 akan menjadi sebuah blok array. Kemudian array formula di J2:K2 akan di-copy sampai J4:K4. Misalkan nama prosedurnya adalah ArrayFormula3, maka bunyi prosedurnya adalah :
   Public Sub ArrayFormula3()
       Dim rngPertama As Range  'range pertama yang diberi array formula
       Dim rngPaste As Range    'range lokasi paste array formula
      
       'lokasi pasang array formula yang akan di-copy
       Set rngPertama = Sheets("Array").Range("J2:K2")
      
       'lokasi paste array formula di rngPertama
       Set rngPaste = Sheets("Array").Range("J3:J4")
      
       'pasang array formula di lokasi pertama
       rngPertama.FormulaArray = "=A2:B2"
      
       'proses copy paste formula
       rngPertama.Copy
       rngPaste.PasteSpecial xlPasteFormulas
   End Sub


4. Range M2:M4 (3 baris x 1 kolom) diberi array formula berbunyi =A2:A4 (juga 3 baris x 1 kolom), sehingga dalam range M2:M4 akan tampil di formula bar sebagai {=A2:A4}. Range M2:M4 akan menjadi sebuah blok array. Kemudian array formula di M2:M4 akan di-copy sampai N2:N4. Misalkan nama prosedurnya adalah ArrayFormula4, maka bunyi prosedurnya adalah :
   Public Sub ArrayFormula4()
       Dim rngPertama As Range  'range pertama yang diberi array formula
       Dim rngPaste As Range    'range lokasi paste array formula
      
       'lokasi pasang array formula yang akan di-copy
       Set rngPertama = Sheets("Array").Range("M2:M4")
      
       'lokasi paste array formula di rngPertama
       Set rngPaste = Sheets("Array").Range("N2")
      
       'pasang array formula di lokasi pertama
       rngPertama.FormulaArray = "=A2:A4"
      
       'proses copy paste formula
       rngPertama.Copy
       rngPaste.PasteSpecial xlPasteFormulas
   End Sub


5. Range P2:Q4 (3 baris x 2 kolom) diberi array formula berbunyi =A2:B4 (juga 3 baris x 2 kolom), sehingga dalam range P2:Q4 akan tampil di formula bar sebagai {=A2:B4}. Range P2:Q4 akan menjadi sebuah blok array. Misalkan nama prosedurnya adalah ArrayFormula5, maka bunyi prosedurnya adalah :
   Public Sub ArrayFormula5()
       Dim rngTarget As Range    'range yang diberi array formula
      
       'lokasi pasang array formula
       Set rngTarget = Sheets("Array").Range("P2:Q4")
      
       'pasang array formula di lokasi pertama
       rngTarget.FormulaArray = "=A2:B4"   
   End Sub

Catatan

1. Kaidah tentang penulisan data teks dalam VB/VBA terhadap karakter tanda petik dua (") :
   • Ketika formula dalam worksheet berisi karakter tanda petik dua ("), maka seluruh karakter tanda petik dua harus diubah menjadi 2 buah tanda petik dua ("") saat bunyi formula tersebut akan dipasang dalam VBA. Misal, formula dalam worksheet berbunyi :
     
     =17 & " kolom"
   • maka dalam VBA, setiap karakter tanda petik dua yang mengapit kata 'kolom' harus diubah menjadi 2 buah tanda petik dua sesuai kaidah penulisan karakter tanda petik dua dalam suatu data teks di-VB/VBA, sehingga bunyi formula yang dipasang dalam VBA berubah menjadi :
     =17 & "" kolom""
   • Bila range yang akan dipasangi formula adalah variabel bernama rngPasang, maka baris kode menjadi :
     rngPasang.Formula = "=17 & "" kolom"""
     atau
     rngPasang.FormulaR1C1 = "=17 & "" kolom"""
   • Pada formula bar akan tampak formula berbunyi :
     =17 & " kolom"
2. Pemasangan formula pada kolom yang tidak berdampingan (berjejer) dan tidak dapat disusunkan sebuah formula universal (yang dapat di-copy keseluruh area secara langsung), maka proses yang dijabarkan diatas harus dilakukan terhadap masing-masing kolom dengan formula masing-masing.
3. Properti Formula, FormulaR1C1, maupun FormulaArray dapat diberi bunyi formula dengan referensi A1 style maupun R1C1 style. Formula yang tampak pada formula bar akan mengikuti setting reference style yang digunakan oleh Excel setempat.

:)

Insya Allah pembahasan berikutnya adalah memasang tombol dalam worksheet berupa shape dan sebuah ActiveX CommandButton untuk menjalankan sebuah prosedur VBA. Pembahasan ini adalah pembukaan untuk pembahasan tentang object module UserForm.

<previous | next>