Bekerja Dengan Objek Excel

BEKERJA DENGAN

OBJEK EXCEL

         

   Ada banyak kategori (kelas) dari object Excel yang dapat dikontrol dalam VBA. Hierarki objek menetapkan tingkat objek yang berbeda, dimulai dari application (Excel) yang merupakan level tertinggi sampai Ranges yang merupakan tingkat terendah.

            Bab ini akan  membahas berbagai object Excel seperti application, Workbook(s), worksheet(s) dan object ActiveSheet/Workbook (misalnya, cell dan range).

  o   Hierarki Objek

            Dalam excel visual Basic, setiap objek dapat berisi objek yang lain, dan objek yang lain tersebut dapat berisi objek yang lainnya lagi, dan begitu seterusnya. Dengan kata lain, pemprograman Excel VBA terdiri dari banyak objek yang diatur dalam satu hierarki objek.

            Ibu dari senua objek adalah Excel sendiri. Excel  disebut sebagai objek application. Objek application dapat berisi objek lain. Sebagai contoh,  objek dari objek application adalah objek workbook (Excel file) yang merupakan workbook yang telah anda buat.

            Objek workbook dapat berisi objek lain, seperti objek worksheet. Objek worksheet juga dapat berisi objek lain, seperti objek Range.

              Kode berikut menunjukkan sel A1, dalam worksheet pertama Book1.

Anda tidak perlu menulikan baris kode diatas dengan cara berikut (di bawah) karena excel VBA mengetahui bahwa Book1 adalah worksheet pertama ketika anda menempatkan tombol perintah di Book1.

Kode kedua menggunakan kata kunci objek Application (pernyataan secara eksplisit) sementara kode pertama tidak menggunakan kata kunci application (pernyataan secara impisit) tetapi kedua  kode tersebut tetap memberikan hasil yang sama.

              Perhatikan bahwa kata worksheets dan workbooks adalah bentuk jamak (lihat baris kode yang ditulis di atas). Hal ini dikarenakan keduanya merupakan koleksi worksheet dan workbook. Koleksi workbooks berisi semua objek workbook yang saat ini terbuka.

Koleksi worksheets berisi semua objek worksheet dalam workbook tersebut.

Anda dapat merujuk kesalah satu anggota koleksi, misalnya, worksheet(1), dengan dua cara.

              Anda dapat menggunakan cara penomoran indeks, misalnya, Worksheet(1) adalah worksheet pertama yang dimulai dari kiri atau anda dapat menggunakan cara penanaman anggota tersebut, misalnya,worksheets (“Sheet1”).

              Koleksi dan anggota koleksi masing-masing bila memiliki property dan method sendiri. Misalnya , property Count dari koleksi worksheets dan koleksi workbooks.

Contoh :

1. Tekan tombol Alt + F8 dan jalankan macro CountWorksheet. Kode berikut menghitung jumlah worksheet dalam workbook. Anda juga dapat menggunakan property Count untuk menghitung jumlah workbook yang aktif.

2. Tekan tombol F5 atau menu Run > Run Sub/UserForm untuk menjalankan macro tersebut.

Contoh :

     Kode berikut digunakan untuk membuat worksheet yang baru pada posisi sebelum worksheet anda saat ini.  Misalnya, jika anda berada di Worksheet(2) worksheet baru, worksheet(4) akan ditambahkan pada posisi sebelum worksheet(2). Anda juga dapat menggunakan metode Add untuk menambahkan worksheet baru.

    Sub TambahWorksheet ()

    Worksheets.Add

    End Sub >

Contoh :

 Kode berikut ini akan memilih baris 2. Pastikan anda berada di worksheet(1) ketika menjalankan macro tersebut. Apabila anda berada di worksheet yang lain, Excel akan menampilkan Run-time error ‘1004’.

    Sub PilihBaris ()

    Worksheets(1).Rows(2). Select

    End Sub

Contoh :

Kode berikut ini memilih kolom 5.

   Sub PilihKolom ()

   Worksheets (1).Columns (5).Select

   End Sub

       1)      Kode yang ditempatkan ke dalam modul dapat diakses oleh semua workbook dan worksheet. Tekan tombol Atl +F11 untuk membuka VBE (Visual Basic Editor)

       2)      Klik ganda module1, dan ketik kode berikut.

      Sub Tampil ()

     Range(“A1”).Value = “Pintar Excel Macro”

     End Sub

 3)  Tekan tombol F5 atau Run > Run Sub/UserForm untuk mengeksekusi kode.

   Kalimat “Pintar Excel Macro” akan ditempatkan ke dalam sel A1. Pastikan anda telah menempatkan kursor dalam makro (tampil) yang ingin anda eksekusi sebelum anda menekan tombol F5.

Lalu akan mucul seperti gambar dibawah ini :

4) pilih worksheet kedua dan eksekusi kode sekali lagi. Anda kan melihat bahwa kalimat “Pintar Excel Macro” akan ditempatkan pada worksheet kedua.

Lalu akan muncul seperti gambar dibawah ini :

5.) Sekarang buka satu workbook yang baru dan eksekusi makro sekali lagi. Anda akan melihat kalimat “Pintar Excel Macro” juga akan ditempatkan disana.

Akan muncul seperti gambar dibawah ini :

       VBA untuk object Application

Kode berikut akan menutup semua orkbook dan excel. Jika ada workbook terbuka yang belum dsimpan, Excel akan menampilkan kotak dialog yang menanyakan apakah anda ingin menyimpan perubahab tersebut.

Excel tidak akan menampilkan peringatan dan pesan ketika makro berjalan jika Application.DisplayAlerts diset ke False.

Excel akan menutup workbook yang telah dimodifikasi tanpa menyimpannya atau mengingatkan anda untuk menyimpannya lewat kotak dialog peringatan.

Contoh :

            Kode berikut akan membuat kotak dialog tidak ditampilkan ketika anda keluar dari Excel. Ketika property DisplayAlerts diset ke FALSE, maka excel tidak akan menampilkan kotak dialog perintah ketika anda keluar dari excel dengan workbook yang belum disimpan.

Anda juga dapat keluar dari excel dengan workbook belum disimpan tanpa menamplkan kotak dialog peringatan dengan menetapkan ActiveWorkbook.Saved ke TRUE.

Bila anda tidak mengingatkan layar anda mengikuti tindakan prosedur VBA, anda dapat memulai dan mengakhiri kode anda dengan kode berikut, tambahkan diawal makro.

Kemudian pada akhir makro, tambahkan kode ini.

Contoh :

Kode berikut akan memasukkan nilai 11 pada sel A1, kemudian akan berpindah satu sel ke bawah untuk memasukkan nilai 11 dan proses ini diulang sampai pada baris 999 lalu kembali ke sel A1.

Proses:

Hasil :

Contoh :

Kode berikut akan memasukkan nilai 22 mulai dari sel A1 sampai sel A999. Namun, dua baris kode baru telah ditambahkan untuk mencegah semua tindakan yang diakibatkan oleh macro untuk terlihat layar.

Proses :

Hasil :

                VBA untuk Workbooks

            Anda dapat membuat prosedur VBA dipanggil oleh sebuah peristiwa yang berkaitan dengan workbook (misalnya ketika anda membuka/menutup workbook).ThisWorkbook adalah workbook dimana anda menjalankan prosedur VBA.         

                                

Worbook dimana prosedur VBA (makro) anda berjalan akan disimpan.

Jika anda ingin menutup workbook dimana prosedur VBA (makro) anda berjalan tanpa menyimpan, tulis dua baris kode berikut.

Bila anda ingin memeriksa apakah file tertentu ada pada folder anda, anda dapat menggunakan kode berikut yang berarti “jika dile C:\Keuangan\2011.xlxs” tersebut tidak ada, maka…”.

Anda juga bias menggunakan kalimat yang berarti “jika file C:\Keuangan\2011.xlsx tersebut tidak ada,

maka...”.

Jika anda mencari satu file di folder yang letaknya sama dengan macro anda, kode VBA dapat disederhanakan seperti berikut :

               VBA untuk worksheet

Anda dapat membuat prosedur VBA yang dipanggil oleh sebuah peristiwa yang berhubungan dengan worksheet (misalnya terjadi ketika anda memilih worksheet).

Contoh :

Anda dapat memilih worksheet yang tersembunyi sehingga anda perlu menulis kode berikut :

Contoh :

Jika anda ingin menyembunyikan worksheets kembali, ketik kode berikut :

Contoh :

     Nama worksheet tidak boleh lebih dari 31 karakter dan tidak boleh terdiri dari karakter khusus seperti “:/\[]?’

Baris kode berikut akan menghasilkan run-time error ‘1004 karena terdapat lebih dari 31 karakter termasuk spasi.

Karakter khusus, yaitu tanda tittik dua ( : ) digunakan (yang tidak diijinkan).

Nama tidak boleh kosong

Contoh :

`    Anda tidak dapat berpindah secara langsung dari satu worksheet ke sel pada worksheet yang lain. Misalnya, jika lembar aktif adalah “Feb” dan anda ingin pindah ke sel A1 dari woksheet, “Aug”, maka anda tidak dapat menulis kode sepert berikut.

Melainkan anda harus melakukannya dalam dua langkah seperti berikut.

  o  Object, Properties, Method, dan Variabel

   Setiap baris kode umumnya memiliki struktur yang sama (yang juga dikenal sebagai sintaks).         Pemprograman Excel VBA berdasarkan konsep Object Orientated Programming (OOP) dan sintaks berikut mendefinisikan bagaimana anda menulis kode VBA.

Dalam Excel VBA,Object,property dan method terhubung dengan sebuah tanda titik. Pengenal (idebtifier) dan sub_identifer dapat berupa roperty, Method, atau Event.

Property adalah sesuatu yang dimiliki oleh object (property menggambarkan objek), sedangkan method adalah cara untuk melakukan sesuatu (method menggambarkan suatu tindakan atas objek) lalu Events merupakan tindakan seperti method tetapi sistem yang memanggil tindakan tersebut dan bukan pengguna (end user).

Seperti benda berbentuk fisik (misalnya, mobil atau kursi), objek Application juga memiliki Property dan Method (server Events).

Object	Property	Method
Mobil	Warna	mempercepat
ActiveCell	nilai
Worksheets(“Sheet1”)		Select

Contoh :

Subroutine yang tercantum di bawah ini terdiri dari object, property, dan method. Misalnya, worksheets(“Sheet1”) adalah Object sementara Select adalah Method  dan Value adalah Property.

Proses :

Hasil :

      Property

Property adalah atribut dari sebuah objek, misalnya nama worksheet.

Sintaks

Contoh :

Kode berikut akan mengubah nama worksheet “Sheet1” menjadi “Neraca 2011”. Dalam contoh ini, Worksheets(“Sheet1”) adalah Object, sementara Name adalah Property dan Neraca 2011 adalah Value.

Proses :

Hasil :

Kode berikut akan menempatkan rumus, A1 *2 di sel, B1. Jika nilai pada A1 adalah 10, maka nilai B1 akan menjadi 20 (A1*2),

Hasil :

     Methods

Methods adalah kegiatan/tindakan yang dilakukan oleh objek, misalnya memilih satu sel, menyisipkan worksheet, menghapus worksheet, dan seterusnya.

Sintaks

Contoh :

  Kode berikut akan memilih kisaran sel (A1:B10) dalam worksheet.

  

Hasil :

Contoh:

Kode berikut akan menghapus isi pada A1.

      Melihat semua Property & method dari objek

Untuk melihat daftar objek Range dan property serta metdenya, anda dapat membuka Visual Basic Editor, ketik Range dan tanda titik.

Ketika tanda titik (.) diketik, sebuah daftar drop down akan mucul dan menampilkan semua method dan propery dar ojek Range. Tanda jari tertunjuk mengacu pada property dan tanda kotak hijau mengau pada method dari objek range.

      Method Yang Mengandung Argumen

Ada beberapa method yang memberikan banyak pilihan argumen, misalnya meyisipkan worksheet. Argumen berikut berisi informasi tentang beberapa banyak worksheet yang ingin anda sisipkan, posisi dimana worksheet akan disisipkan dan jenis worksheet yang disisipkan.

Sintaks

Worksheets.Add ([Before:=], [After:=], [Count:=], {Type:=])

Add	Menambahkan worksheet baru
Before/After	Dimana posisi worksheet
Count	Berapa banyak worksheet
Type	Jenis worksheet (misalnya, worksheet,chart)

Contoh :

Kode berikut akan menempatkan dua worksheet baru setelah Sheet2. Tanda koma setelah Add merupakan argumen “Before” secara default. Jika argumen “Tyoe” diabaikan, maka VBA akan menganggap jenis Default, yaitu xlsx yang digunakan.

      Events

Events mirip seperti methods – perbedaanya adalah kapan dan apa yang memanggil tindakan tersebut. Events merupakan tindakan seperti method tetapi sistem yang tindakan tersebut dan bukan pengguna end user)

Hal ini sangat berguna ketika status sistem berubah dan andaperlu melakukan otomatisasi prosedur untuk pengguna.

Bila anda menggunakan fitur standar seperti data Validation atau Conditional Formatting, sistem secara otomatis akan membuat kode untuk sebah event sehingga ketika pengguna memasukkan nilai dalam sel, Excel secara otomatis akan memvalidasi dan memformat el yang aktif tanpa anda harus membuat dan memanggil makro secara manual. Jenis vent ini biasaynnya dikenal sebagai “ENTER” untuk sebuah Worksheet.

  o   WorksheetFunction

Anda dapat mengakses hampir semua fungsi Excel melalui objek Application.

Contoh 

Kode berikut akan menghitung nilai rata-rata di sel A1 dan A2 serta menempatkan hasilnya disel A3.

Dari pada menggunakan WorksheetFunction.Average, akan lebih mudah bagi anda untuk menggunakan WorksheetFunction.Average.

Untuk melihat daftar fungsi yang tersedia untuk worksheet, anda dapat membuka Visual Basic Editor dan ketik WorksheetFunction lalu keyik tanda titik (.).

Daftar yang muncul akan menampilkan semua fungsi yang tersedia untuk WorksheetFunction.

      Memanggil Fungsi Worksheet Dalam VBA

Hampir semua fungsi worksheet dapat ditampilkan dari VBA dengan menggunakan objek Application atau objek Application Worksheet. Sintaks dari tampil fungsi worksheet sama dengan fungsi worksheet itu sendiri. Misalnya, fungsi worksheet dalam satu sel bisa berupa seperti berikut.

Untuk menulis kode di VBA agar melakukan hal yang sama, anda dapat mengetik kode berikut.

Jumlah parameter yang dibutuhkan adalah sama saat memanggil fyngsi dari VBA dengan saat memanggil fungsi dari sel dalam worksheet.

Namun, ketika menjalankan macro, anda akan mendapat runtime error jika nilai 123 tidak ditemukan dalam range tersebut. Oleh karena itu, anda perlu menyertakan beberapa kode perangkap kesalahan (error handling).

     Error Handling Dengan Fungsi Worksheet

Ada dua sintaks dasar dalam penanganan error saat memanggil fungsi worksheet dalam VBA. Anda dapat menggunakan pertanyaan On Error dan kemudian menguji nilai Err.Number untuk melihat sumber keslahan atau anda dapat menentukan variabel hasil sebagai tipe Variant dan menggunakan ISERROR untuk melihat apakah variabel tersebut merupakan varian jenis Error.

Method yang anda gunakan tergantung pada apakah anda menggunakan property WorksheetFuction dalam kode anda. Jika anda menggunakan property WorksheetFunction, maka kesalahan akan tampak/muncul sebagai kesalahan runtime yang perlu pernyataan On Error dan tes dari jilai Err.Number.

Jika anda tidak menggunakan property WorksheetFunction, maka anda harus menyatakan variabel hasil sebagai tipe Variant dan menguji variabel tersebut dengan ISERROR.

Contoh :

Activworbook Dan Objek Worksheet

Objek ini berada dibawah objek application bersama dengan objek lainnya termasuk Chart dan Pivot Table yang berungsi untuk mengendalikan tindakan pada setiap workbook seperti membuat,membuka dan menutip dokumen.

Kata kundi tunggal, workbook, mengacu pada satu file yang ingin anda kontrol saat ini. Sedangkan workbooks adalah kata kunci jamak yang merupakan koleksi  dari satu atau lebih workbook yang ingin anda kontrol.

Contoh :

Koe berikut akan menampilkan workbook tang saat ini sedang digunakan.

Contoh :

Kode berikut akan menyimpan salinan workbook saat ini sebagai MacroBab3.xlsm.

Kode di atas jiga dapat ditulis sebagai berikut.

          Dengan menggunakan kata kunci, workbooks, yang merupakan kumpulan workbook saat ini, anda dapat memberikan nomor indeks (mulai dari 1 untuj dokumen pertama dan seterusnya) untuk mengeksekusi kode dengan menggunakan pengidentifikasi yang sama sebagai objek ActiveWorkbook.

Contoh :

Kode berikut akan menampilkan beberapa banyak workbook yang terbuka saat ini.

Object Workbooks tidak memiliki tanda kurung dan referensi nomor indeks ketika berharapan dengan banyak dokumen. Kode do atas juga akan menghitung semua dokumen yang terbuka dan yang tersembunyi.

  o   ActiveSheet dan objek worksheets

Object worksheet yang juga disebut ActiveWorkSheet dan ActiveWorkSheet ini akan mengontrol objek worksheet yang sekarang terbuka. Kata kunci jamak, worksheets, mengacu pada satu atau lebih worksheet dalam satu workbook dan memungkinkan anda untuk memanipulasi kumpulan worksheet tersebut.

Contoh :

Kode berikut akan memberi nama worksheet sebagai Neraca Jan.

Contoh :

Kode berikut akan menyisipkan worksheet yang baru dan menempatkan di akhir worksheet saat ini.

Hasil :

  o   Object Aktif

         Dalam objek Application, anda memiliki property lain yang bertindak sebagai pintas (Globals) ke objek utama. Termasuk di dalamnya ActiveCell, ActiveChart. ActivePrinter, ActiveSheet,ActiveWindow, dan ActiveWorkbook.

         Anda hanya dapat memiliki satu objek aktif ketika bekerja dalam antarmuka Excel. Bahkan saat kisaran sel yang anda pilih (objek Selection) hanya boleh memiliki satu sel yang aktif (Sel putih).

Hasil :