BelajarVBA 101 - Controls Dalam Worksheet

Coretan Mr. Kid

Belajar sedikit-sedikit secara rutin dan konsisten akan berdampak lebih besar daripada belajar banyak sesekali.

Worksheet bisa diberi Form Controls dan ActiveX Controls untuk memudahkan interaksi dengan user. Object berupa shape juga bisa dimanfaatkan sebagai sebuah tombol untuk menjalankan prosedur VBA yang telah dibuat dalam sebuah general module. Bahasan kali ini adalah tentang menggunakan sebuah shape, Button Form Control, dan Command Button ActiveX Control untuk menjalankan sebuah prosedur VBA. File yang bisa digunakan untuk mencoba secara mandiri adalah BelajarVBA101_01.xlsm. Sebaiknya dalam membaca pembahasan ini disertai dengan membuka file BelajarVBA101_01.xlsm pada sheet bernama 'shapes_n_controls'.

Mengenal object control

Object controls adalah object yang dapat digunakan untuk mengatur interaksi dengan user sesuai jenis control tersebut. Misalnya, sebuah List Box dapat digunakan untuk mengatur daftar item yang dapat dipilih oleh user. Ketika sebuah item dalam List Box dipilih user, maka dapat digunakan untuk mengatur proses yang harus terjadi terhadap pemilihan item tersebut.
Object control berada di ribbon Developer -> group Controls -> item Insert

Object control yang tersedia terbagi dalam 2 kelompok besar, yaitu :

1. Form Controls
   • Object controls bawaan Excel.
   • Tidak termasuk object controls yang diwaspadai oleh Security Excel.
   • Hanya memiliki prosedur sub event click saja.
   • Controls yang disediakan adalah Button, Label, Combo Box, List Box, Spin Button, Scroll Bar, Check Box, Option Button, Group Box.
   • Controls tambahan ketika menggunakan MS Excel 5.0 Dialog adalah Text Field, Combo List, dan Combo Drop Down.
   • Beberapa controls seperti Combobox, Listbox, Spin Button, dan sebagainya memiliki banyak properti yang bisa diset langsung melalui Format Control pada tab Control.
   • Beberapa controls, seperti Spin Button, Combobox, dan semacamnya, memberikan suatu nilai output. Nilai output tersebut dapat digunakan ketika control tersebut di-link ke suatu cell.
   • Form control berupa List Box dan Combo Box membutuhkan rujukan ke suatu range satu kolom saja untuk mengisi daftar pilihan yang dapat dipilih user.
2. ActiveX Controls
   • Object controls tambahan dari library windows bernama mscomctl.ocx atau mscomctl2.ocx.
   • Memiliki banyak properti melalui dialog window Properties.
   • Memiliki banyak macam prosedur sub event.
   • Bisa digunakan dalam sebuah sheet maupun dalam sebuah UserForm.
   • Controls yang disediakan adalah Command Button, Label, Combo Box, List Box, Spin Button, Scroll Bar, Check Box, Option Button, Text Box, Image, Toggle Button.
   • Beberapa controls memberikan suatu nilai output. Nilai output tersebut bisa digunakan melalui baris-baris kode VBA maupun di-link ke sebuah cell.
   • Pengisian daftar pada control seperti Combo Box ataupun List Box bisa dengan merujuk ke suatu range tertentu ataupun melalui baris-baris kode VBA. Jumlah kolom dalam Combo Box atau List Box juga bisa lebih dari satu kolom.
   • Bisa menambahkan ActiveX Controls lain yang belum ada dalam daftar yang disediakan melalui :

     ribbon Developer -> group Controls -> Insert -> bagian ActiveX Controls -> item More Controls

   • Pengaturan nilai-nilai properti maupun penataan baris-baris kode dalam events dilakukan ketika dalam keadaan Design Mode ON, yaitu dengan cara :

     ribbon Developer -> group Controls -> klik Design Mode agar aktif (terjadi perubahan warna pada menu)

   • Controls akan dapat berfungsi ketika Design Mode dalam keadaan OFF, yaitu dengan mengklik ulang item Design Mode dalam ribbon Developer.

Mengenal properti khusus pada sebuah form control

Properti khusus yang dimiliki sebuah form control diletakkan pada tab Control dalam dialog window Format Control. Properti khusus tersebut dapat diakses dengan :

klik kanan form control -> Format Control -> klik tab Control

Form Control yang memiliki properti khusus pada umumnya adalah controls yang bisa memberikan nilai output, kecuali control bernama Group Box yang berfungsi membentuk sebuah group check box maupun option button. Berikut ini adalah form controls yang bisa memberi nilai output :

• Combo Box (Drop Down)
   berupa nomor indeks item (item pertama bernomor indeks 1) yang dipilih user
• List Box
   berupa nomor indeks item (item pertama bernomor indeks 1) yang dipilih user
• Spin Button
   berupa nilai bilangan bulat setelah kegiatan naik atau turun terakhir
• Scroll Bar
   berupa nilai bilangan bulat setelah kegiatan naik atau turun terakhir
• Check Box
   berupa nilai TRUE ketika dalam status checked dan nilai FALSE ketika dalam status unchecked
• Option Button
   berupa nomor indeks dari option button (item pertama bernomor indeks 1) dalam sebuah grup option

Mengenal properti ActiveX Control

Setiap ActiveX Control memiliki banyak properti yang bisa diatur nilainya melalui baris kode VBA maupun melalui dialog window properties. Cara menampilkan dialog window properties dari sebuah ActiveX Control yang diletakkan pada sebuah sheet adalah dengan :

• Aktifkan Design Mode
• Membuka dialog window properties

  klik kanan control -> pilih Properties

  
  
  
  

Properti utama dari setiap ActiveX Control adalah properti '(Name)' yang berfungsi untuk menentukan nama object control tersebut. Pada ActiveX Control yang diletakkan pada sebuah sheet, penggantian nilai properti '(Name)' juga dapat dilakukan melalui Name Box yang disediakan Excel. Teks yang diserahkan ke properti '(Name)' inilah yang akan aktif digunakan sebagai rujukan ke object control tersebut dalam baris-baris kode VBA.

Beberapa properti, yang ada disetiap ActiveX Control, yang sering digunakan adalah :

• Enabled
  diisi TRUE jika boleh digunakan oleh user, dan diisi FALSE jika tidak boleh digunakan
• Visible
  diisi TRUE jika akan ditampilkan kepada user, dan diisi FALSE jika akan disembunyikan dari pandangan user

Pada beberapa ActiveX Controls yang dapat memberikan nilai output, yang disimpan oleh si control pada properti bernama Value atau Text (bila ada), disediakan properties tambahan untuk mendukung fungsi control tersebut. Misalnya properti ListFillRange pada ComboBox dan ListBox atau properti SmallChange pada SpinButton atau ScrollBar. Beberapa ActiveX Control memiliki properti yang tidak ditampilkan dalam dialog window properties, tetapi memiliki fungsi yang sangat penting, seperti properti ListIndex pada ComboBox dan ListBox.

Mengenal event dari ActiveX Controls

Kelebihan dari ActiveX Controls adalah tersedianya banyak event yang menangkap perlakuan user terhadap control tersebut. Seperti event bernama Click yang akan menangkap perlakuan user saat mengklik control tersebut. Ada juga event bernama Change yang menangkap perlakuan user ketika mengubah nilai yang ada dalam control tersebut, seperti mengetik setiap karakter dalam Text Box Control atau mengubah pilihan dalam List Box maupun Combo Box dan sebagainya.

Dengan diketahuinya perlakuan user terhadap si control melalui event yang diproses tersebut, maka alur proses bisa diatur untuk melakukan suatu kegiatan komputasi tertentu. Misalnya, ketika user mengklik tombol tertentu, maka event Click akan diproses dan didalam event tersebut diberi baris-baris kode untuk menjalankan suatu proses copy data dari sheet input ke sheet database.

Dari sekian banyak event yang dimiliki setiap ActiveX Control, ada beberapa event yang selalu ada dan sering digunakan, antara lain adalah :

1. GotFocus
   • Bereaksi ketika suatu proses atau user mengaktifkan control tersebut. Event ini bisa digunakan salah satu contohnya adalah untuk menata (bahkan sampai memvalidasi nilai) dari control tersebut atau controls yang lain.
   • Misalnya control TextBox bernama Negara. Ketika TextBox Negara diaktifkan, maka event GotFocus dari textbox ini akan beraksi dan dijalankan. Pada event GotFocus milik si textbox ini bisa diisi dengan proses menghapus nilai dalam textbox dan memeriksa nilai dalam textbox lain seperti textbox Nama harus sudah terisi lebih dulu. Jika textbox Nama belum terisi, maka proses akan memerintahkan textbox Nama untuk diaktifkan.
2. LostFocus
   • Bereaksi ketika suatu proses atau user mengaktifkan object lain. Event ini bisa digunakan salah satu contohnya adalah untuk memvalidasi nilai yang telah diinputkan pada control tersebut.
   • Misalnya control ComboBox bernama PIC yang sedang aktif. Ketika user mengaktifkan control lain, maka event LostFocus milik combobox PIC akan beraksi dan dijalankan. Pada event ini bisa diisi dengan pemeriksaan bahwa combobox PIC telah diisi dengan salah satu item yang ada dalam daftar yang disediakan. Jika tidak ada, maka proses bisa mengembalikan control yang aktif ke combobox PIC.
3. KeyDown
   • Bereaksi ketika si control sedang aktif dan user menekan sebuah tombol keyboard. Event ini menyediakan informasi kode tombol keyboard yang ditekan oleh user dalam variabel bernama KeyCode. Informasi tentang tertekannya tombol shift atau tidak disediakan dalam variabel bernama Shift. Event ini bisa digunakan untuk membatasi tombol keyboard yang bisa mempengaruhi si control.
   • Misalnya sebuah textbox bernama NIP yang harus berisi angka saja bisa diatur agar tombol keyboard yang membuat si textbox berubah nilainya adalah tombol-tombol angka saja. Contoh lainnya adalah sebuah CommandButton bisa diatur agar proses penekanan (klik) bisa dengan menekan tombol keyboard huruf 'p' ketika si control dalam keadaan sedang aktif.
4. MouseMove
   • Bereaksi ketika pointer mouse melintasi si control. Event ini biasanya digunakan untuk menampilkan informasi tentang si control. Salah satu media penulisan informasi yang disediakan adalah properti bernama ControlTipText. Properti ini hanya ada ketika control digunakan dalam sebuah userform. Ketika control diletakkan dalam sebuah sheet, maka control seperti Label bisa dijadikan media penampil informasi tentang si control.

Setiap ActiveX Controls juga memiliki events yang khusus untuk menunjang kerjanya. Beberapa events penunjang tersebut ada yang dapat digunakan dalam UserForm saja. Jadi, dalam penggunaan ActiveX Control perlu diketahui lokasi penggunaannya beserta events yang bisa digunakan dilokasi tersebut. Cara yang mudah adalah dengan melihat ComboBox event dan prosedur dalam VBE saat ComboBox object berisi control tersebut [silakan baca BelajarVBA 001 - VBE (Visual Basic Editor)]

Tombol pemanggil prosedur VBA

Beberapa kegiatan otomasi dengan VBA membutuhkan interaksi dengan user untuk menjalankan prosedur-prosedur otomasi yang telah dibuat. Sebuah tombol yang dapat ditekan oleh user akan memudahkan user dalam menjalankan suatu prosedur otomasi. Tombol bisa berupa Shape, Form Control, atau ActiveX Control.

Cara menambahkan suatu object yang akan berfungsi sebagai tombol dalam sebuah worksheet adalah sebagai berikut :

• Menggunakan shape
  

  ribbon Insert -> group Illustrations -> Shapes -> pilih sebuah shape -> gambar kotak dalam worksheet

• Menggunakan Form Control
  

  ribbon Developer -> group Controls -> Insert -> lihat bagian Form Controls (sisi atas) -> pilih Button -> gambar kotak dalam worksheet

• Menggunakan ActiveX Control

  ribbon Developer -> group Controls -> Insert -> lihat bagian Form Controls (sisi atas) -> pilih Button -> gambar kotak dalam worksheet

Cara mengganti nama tombol antara lain dengan :

• Menggunakan shape

  klik kanan shape dalam worksheet -> klik Name Box (pojok kiri atas worksheet [diatas nomor baris Excel 1]) -> ganti teks yang ada (misal menjadi shpPesan) -> tekan ENTER

• Menggunakan Form Control

  klik kanan Button dalam worksheet -> klik Name Box (pojok kiri atas worksheet [diatas nomor baris Excel 1]) -> ganti teks yang ada (misal menjadi ctlBPesan) -> tekan ENTER

• Menggunakan ActiveX Control
  • Aktifkan Design Mode
  • Ganti nama control

    klik kanan Command Button dalam worksheet -> klik Name Box (pojok kiri atas worksheet [diatas nomor baris Excel 1]) -> ganti teks yang ada (misal menjadi cmdPesan) -> tekan ENTER

  • Non aktifkan Design Mode

Pengaturan nama prosedur yang akan dijalankan oleh si tombol selalu diawali dengan adanya prosedur yang akan dijalankan. Misalkan pada sebuah general module telah dibuat sebuah prosedur bernama PesanShapeNFormControl yang berupa :
   Public Sub PesanShapeNFormControl()
       MsgBox "Hello " & Environ("USERNAME")
   End Sub
Kemudian diinginkan untuk dijalankan dengan object berupa sebuah shape atau sebuah form control Button.

Cara mengatur tombol berupa shape atau form control Button yang telah ada dalam worksheet, sehingga bisa menjalankan prosedur diatas ketika ditekan, adalah sebagai berikut :

• Menggunakan shape
  • klik kanan shape dalam worksheet -> pilih Assign Macro
    
    
  
  
  • pilih nama prosedur PesanShapeNFormControl -> OK
    
    


• Menggunakan Form Control
  seperti menggunakan shape, tetapi yang diklik kanan adalah Button
  • klik kanan Button dalam worksheet -> pilih Assign Macro
  • pilih nama prosedur PesanShapeNFormControl -> OK

Pada penggunaan ActiveX Control, proses pengaturan CommandButton untuk dapat menjalankan sebuah prosedur harus melalui event yang dimiliki si control CommandButton tersebut. Misalkan sebuah CommandButton bernama cmdPesan dijadikan sebuah tombol yang akan menjalankan prosedur lain bernama PesanActiveXControl.

Prosedur PesanActiveXControl berupa blok prosedur sebagai berikut :
   Public Sub PesanActiveXControl()
       MsgBox "Welcome, " & Environ("USERNAME")
   End Sub

Cara mengatur Command Button tersebut adalah sebagai berikut :

• Aktifkan Design Mode


• Bentuk prosedur event click (ketika tombol di-click maka akan diproses)
  

  double click Command Button dalam worksheet -> muncul blok event Click milik tombol tersebut (gambar bawah sisi kiri) -> isi dengan nama prosedur PesanActiveXControl

  


• Non aktifkan Design Mode

Catatan

• Form Controls adalah bawaan Excel, sehingga cenderung dapat digunakan disemua komputer yang memiliki Excel dan tidak memerlukan pengaturan Excel Security.
• ActiveX Controls tergantung pada file library, sehingga controls tersebut dapat berfungsi dengan baik jika komputer setempat (yang membuka workbook) juga memiliki file library yang berisi si control.
• ActiveX Controls termasuk object yang diawasi oleh Excel Security, sehingga membutuhkan pengaturan Excel Security agar dapat berfungsi.
• ActiveX Controls mudah ditata dengan baris-baris VBA dan memiliki banyak events.

:)

Insya Allah pembahasan berikutnya adalah tentang beberapa Form Control dan ActiveX Control yang sering digunakan seperti ComboBox, ListBox, dan sebagainya. Pembahasan masih tentang penggunaan controls dalam worksheet.

<previous | next>

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>