Belajar VB-Excel
MEMBUAT
BERKAS YANG MEMILIKI MACRO PADA EXCEL 2007
Karena
menggunakan Excel 2007 maka berkas Excel yang kita buat haruslah menggunakan
extensi “.xslm”. Sekarang mari kita membuat sebuah berkas baru Excel,
berkas tersebut masih dalam format “.xslx”.
Buka berkas
tersebut, kemudian simpan sebagai (save as) “fungsi sendiri.xslm”.
Maka kita
telah membuat sebuah berkas Excel 2007 yang dapat menggunakan macro (macro
enabled). Lalu kita atur keamanan dan privasi Excel kita pada Macro
Settings dan Trusted Location (lokasi terpercaya tempat kita
menyimpan berkas-berkas excel ber-macro), sehingga macro yang telah kita
buat dapat bekerja. Kedua pengaturan tersebut terdapat pada Excel Option-Trust
Center-Trust_Center Settings.
Pada
pengaturan Macro, pilih “Disabled All Macro Except Digitally Signed
Macros” (pilihan ke 3) atau “Enabled All Macro” (pilihan ke 4),
penulis menyarankan pilihan ke-4 bagi pemula.
Kemudian
kita atur juga lokasi folder yang terpercaya (Trusted Location), di
komputer penulis terletak di “E:\VBA\”. Jangan lupa untuk mencentang pilihan “Subfolder
of this location also trusted” agar subfolder dari folder ini juga
dipercaya.
MEMBUAT
FUNGSI MERUBAH ANGKA KE KATA UNTUK ANGKA TUNGGAL
Untuk
membuat fungsi sendiri, maka kita perlu membuat prosedur fungsi (function
procedure) VBA pada module di Visual Basic Editor.
Sekarang
kita telah membuat sebuah fungsi bernama “angka_ke_kata” yang akan menghasilkan
sebuah kata (tipe data string) dengan argumen sebuah angka (tipe data double).
Tipe data string diperuntukkan untuk teks, sedangkan tipe data double
diperuntukkan untuk bilangan asli dan pecahan.
Function
angka_ke_kata(angka As Double) As String
Dalam fungsi
ini pertama kita akan menyiapkan array yang berisi teks yang
berassosiasi dengan bilangan yang dimaksud. Array itu kita beri nama
“kata_angka”, berukuran 10, yang beranggotakan kata untuk bilangan dari nol
sampai sembilan.
Dim
kata_angka(10) As String
kata_angka(0)
= "nol"
kata_angka(1)
= "satu"
kata_angka(2)
= "dua"
kata_angka(3)
= "tiga"
kata_angka(4)
= "empat"
kata_angka(5)
= "lima"
kata_angka(6)
= "enam"
kata_angka(7)
= "tujuh"
kata_angka(8)
= "delapan"
kata_angka(9)
= "sembilan"
Berdasarkan array
ini kita sudah dapat membuat sebuah fungsi yang merubah angka tunggal menjadi
kata. Kita cukup menambahkan kode VBA yang mengembalikan kata yang sesuai
dengan argumen “angka” ketika fungsi “kata_ke_angka” dipanggil.
angka_ke_kata
= kata_angka(angka)
Sekarang
kita kembali ke “sheet1” pada workbook Excel untuk mencoba fungsi yang
telah dibuat. Isikan angka pada sel “A1” kemudian pada sel “A2” masukkan fungsi
excel “angka_ke_kata”. Fungsi ini terletak pada pilihan fungsi “user defined”.
MEMBUAT
FUNGSI MERUBAH ANGKA KE KATA UNTUK ANGKA LEBIH DARI 9
Sejauh ini
kita telah membuat sebuah fungsi yang merubah angka ke kata. Tapi fugnsi ini
bekerja hanya untuk angka-angka tunggal (dari nol sampai Sembilan). Lebih dari
itu maka fugnsi init akan menghasilkan kesalahan, sebagai contoh kita tuliskan
nilai 10 atau 200 di sel “A1”.
Selanjutnya
kita akan melengkapi kode-kode di fungsi yang telah kita buat, sehingga dapat
merubah angka lebih dari 9 menjadi kata. Algoritma nya sebenarnya cukup mudah,
fungsi yang kita buat pertamaakan menghitung panjang angka yang akan diubah.
Setelah itu fungsi tersebut akan merubah tiap anggota angka menggunakan array
“kata_angka” seperti bahaasan sebelumnya, kemudian menggabungkan
hasil-hasilnya. Sehingga hasil yang akan didapat nanti adalah berupa gabungan
kata-kata, atau lebih tepatnya disebut kalimat.
Dalam Visual
Basic dikenal fungsi “Len(pernyataan)” yang berguna untuk mengetahui
panjang sebuah teks (kata atau kalimat). Karena angka yang kita ubah berupa
bilangan bukan teks, maka kita perlu merubah angka tersebut menjadi teks dengan
fungsi “Cstr(pernyataan)”. Bilangan dalam format data double yang telah
dikonversi menjadi format data string akan kita simpan dalam variabel
“angka_dlm_teks”. Sedangkan panjangnya kita taruh dalam variabel “panjang_angka”.
angka_dlm_teks
= CStr(angka)
panjang_angka
= Len(angka_dlm_teks)
Sekarang
mari kita uji hasilnya dengan angka 5 , 317, 105076.
Berdasarkan
informasi panjang angka tersebut, kita dapat membuat sebuah loop untuk
merubah tiap anggota angka menjadi kata dan menggabungkannya kembali. Dalam loop
tersebut setiap karakter dalam variabel “angka_dlm_teks” akan kita ambil
meggunakan fungsi “Mid(string,awal,jumlah)”. Kemudian memasukan karakter itu ke
dalam variabel “index_angka”. Variabel ini akan digunakan untuk mengambil data
dari anggota array “kata_angka” yang berasosiasi dengan karakter
tersebut. Untuk menggabungkan hasilnya dari setiap kali proses tersebut
diulang, maka digunakan operator penggabungan “&”.
For i = 1 To
panjang_angka
index_angka = Mid(angka_dlm_teks, i, 1)
angka_ke_kata = angka_ke_kata & " " & kata_angka(index_angka)
Next
Sekarang
mari ktia tes fungsi ini dengan angka 1, 720 dan 30827.
Auto Run
Ada
beberapa cara untuk membuat macros yang kita buat berjalan secara
otomatis ketika pertama kali membuka workbook. Yang pertama adalah Auto
Open Method, yang diletakkan di modules, kedua adalah Workbook
Open Method, yang diletakkan di pada obyek Workbook (lihat
penjelasan pada langkah 3). Dua Contoh kode berikut akan menampilkan pesan “hi”
ketika Workbook pertama kali dibuka.
Sub Auto_Open( )
Msgbox “hi”
End Sub
Private Sub Workbook_Open( )
Msgbox “hi”
End Sub
Menghitung Rows, Columns dan Sheet
Kode
berikut digunakan untuk menghitung berapa jumlah rows (baris) atau columns(kolom)
yang telah kita sorot dengan kursor.
Sub Hitung( )
hitung_baris = Selection.Rows.Count
hitung_kolom = Selection.Columns.Count
MsgBox hitung_baris & " " & hitung_kolom
End Sub
Sub hitung_sheet( )
hitung_sheet = Application.Sheets.Count
Msgbox hitung_sheet
End Sub
Meng-kopi Range
Contoh
berikut akan meng-kopi range A1 sampai A3 ke D1 sampai D3
Sub Kopi_Range( )
Range (“A1:A3”).Copy Destination:=Range(“D1:D3”)
End Sub
Waktu Sekarang
Contoh
berikut akan menampilkan waktu pada saat ini
Sub sekarang( )
Range (“A1”)= Now
End Sub
Mengetahui Posisi Sel yang Sedang Aktif
Sub posisi( )
baris = ActiveCell.Row
kolom = ActiveCell.Column
Msgbox baris & “,” & kolom
End Sub
Menghapus Baris yang Kosong
Sub hapus_baris_kosong( )
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
For i = 1 To Rng
If ActiveCell.Value = "" Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next I
End Sub
Menebalkan
dan Mewarnai Huruf (Font)
Contoh
berikut akan menebalkan dan memberi warna merah pada huruf dimana sel sedang
aktif.
Sub tebal_merah( )
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End Sub
Mengirimkan Workbook melalui Email
Sub email( )
ActiveWorkbook.SendMail recipients:= ”excel@ahoo.c”
End Sub
Fungsi Excel
Menggunakan
fungsi bawaan Excel dalam VBE hampir sama dengan menggunakannya dalam Excel.
Misal fungsi round untuk membulatkan sebuah angka, dalam spreadsheet
akan terlihat seperti ini
= round(1.2367, 2)
Dalam
VBE Anda cukup menggunakan Application kemudian disusul fungsi yang akan
dipakai.
Sub bulat( )
ActiveCell = Application.Round(ActiveCell, 2)
End Sub
Menghapus Nama-Nama Range
Contoh berikut akan menghapus semua nama-nama range
di dalam workbook Anda
Sub hapus_nama_range( )
Dim NameX As Name
For Each NameX In Names
ActiveWorkbook.Names(NameX.Name).Delete
Next NameX
End Sub
Layar Berkedip
Program
dalam macros yang sedang berjalan dapat membuat layar berkedip-kedip,
untuk menghentikannya Anda dapat menyisipkan kode berikut.
Application.ScreenUpdating = False
Menuju Range Tertentu
Untuk
menuju suatu range tertentu, kode-kode berikut dapat digunakan.
Application.Goto Reference:=”A1”
Atau,
Range(“A1”).Select
Menuju Sheet tertentu
Sedangkan
untuk menuju worksheet tertentu, gunakan kode-kode berikut.
Sheets(1).Select
Atau
Sheet1.Select
Untuk
menuju Sheet terdepan (nomor 1)
Sheet(“coba”).Select
Untuk
menuju Sheet bernama “coba”
Menyembunyikan WorkSheet
Kode
berikut berfungsi untuk menyembunyikan Sheet1
Sheet1.Visible = xlSheetVeryHidden
Pengguna
tidak dapat membuka sheet yang telah disembunyikan dengan cara ini,
hanya dengan kode VBE sheet dapat dibuka kembali.
Input Box
Kode
berikut berguna untuk memunculkan Input Box
InputBox(“Masukkan Nama”)
Menyisipkan Baris dan Kolom
Kode
berikut akan menyisipkan baris diatas range A1,
Range(“A1”).Select
Selection.EntireRow.Insert
Sedang
yang berikut akan menyisipkan satu kolom disamping kiri range A1,
Range(“A1”).Select
Selection.EntireColumn.Insert
Mengatur Ulang Ukuran Range
Selection.Resize(7,7).Select
Memberi Nama Range
Selection.Name = “nama”
Menyimpan File
Kode
berikut berguna untuk menyimpan file tanpa memberi nama,
ActiveWorkbook.Save
Sedangkan
bila Anda hendak memberi nama (SaveAs), gunakan kode berikut,
ActiveWorkbook.SaveAs Filename:=”C:\coba.xls”
Penjadwalan
Kadang-kadang
kita hendak menjadwalkan sebuah tugas kepada Excel, contohnya menyimpan file
pada jam-jam tertentu. VBE dapat melakukannya dengan menggunakan fungsi Application.OnTime.
Sebagai contoh, kode dibawah ini akan menjalankan prosedur Simpan( ) pada jam 12:00 dan 16:00, prosedur Simpan( ) sendiri berisi perintah untuk menyimpan file,
Sub tugas()
Application.OnTime TimeValue("12:00:00"),
"Simpan"
Application.OnTime TimeValue("16:00:00"),
"Simpan"
End Sub
Sub Simpan()
ActiveWorkbook.Save
End Sub
Bila
Anda hendak merubah jamnya, missal jam 10:03:05, maka rubah TimeValue menjadi TimeValue(“10:03:05”).
Sedangkan
bila Anda hendak melakukannya satu jam setelah prosedur tugas( ) dijalankan
maka rubahlah kodenya seperti demikian,
Sub
tugas()
Application.OnTime Now +
TimeValue("01:00:00"), "Simpan"
End
Sub
Perhatikan
penambahan kata “Now”. Kode-kode di atas bekerja bila disimpan dalam module,
bula Anda ingin menyimpannya di dalam Sheet1 (atau worksheet manapun),
maka rubahlah “Simpan” menjadi Sheet1.Simpan
Pada bab-bab sebelumya
sebenarnya kita telah membentuk sebuah obyek bernama "Module1"
yang dapat dipanggil dengan menekan Ctrl+q, dan memberinya prosedur bernama "coba".
Perhatikan ketika kita
mengetik "Module1", kemudian mengetik "." Maka akan muncul
tampilan seperti diatas. Sebuah kotak dengan sebuah gambar berwarna hijau
dengan tulisan "coba".
Pada penjelasan berikut, kita
akan membuat prosedur-prosedur buatan kita sendiri pada Worksheet dan Workbook.
Mengapa? Karena kode-kode program dalam prosedur yang kita buat di sebuah Worksheet
dan Workbook tertentu, hanya akan bekerja pada Worksheet atau Workbook
tersebut. Sedang prosedur yang tertulis pada Modules, bekerja pada Worksheet
dalam Worbook yang aktif.
Sebagai contoh ketikan kode
ini dalam prosedur Sub Coba() dalam Module 1:
Range("A1").Value= "coba"
lalu buka contoh.xls-sheet1,
jalankan program,
buka contoh.xls-Sheet2,
jalankan program,
kemudian sheet3,
Kemudian buat sebuah Workbook
baru,
Pada Workbook baru,
bernama Book1 ini, buka Sheet1, jalankan program,
Bila Anda melanjutkan pada book1.xls-Sheet2
dan Sheet3, program yang kita buat pada contoh.xls-Module1 akan
dikerjakan pada setiap Worksheet yang aktif, walapun Worksheet
tersebut berada pada Worbook lain.
Hal ini akan merepotkan bila
Anda hanya ingin program VBE yang dibuat bekerja pada Worbook tertentu,
sedang dalam pekerjaan Anda sehari-hari Anda harus membuka banyak Workbook.
Pemograman Sheet
Untuk memulai, masuklah ke
dalam Sheet1 dengan cara double klik pada windows project,
tampilan berikut akan muncul ;
Setelah itu buatlah sebuah
prosedur bernama lembar1,
lalu isikan kode berikut;
pergi ke Module1, dan
isikan kode berikut
Kembali ke Ms. Excell,
buka Sheet1, jalankan program dengan menekan Ctrl + q , hasilnya
adalah ;
Hapus kata "lembar1"
di Sheet1, kemudian buka Sheet2, kemudian tekan Cttrl + q,
dan hasilnya adalah
range A1 tidak
terisi apapun pada Sheet2, bukalah Sheet1 dan Anda akan
mendapatkan bahwa pada range A1 terdapat kata "lembar1".
Membuat Shortkey untuk
Program pada Sheet
Untuk membuat Shortcut key
dari program yang telah kita buat, maka tekan Alt + F8, atau menggunakan
menu Tools—Macro—Macros
akan tampil
sorot sheet1.lembar,
tekan tombol Option,
pada isian Shorcut Key,
isikan huruf w.
Kembali ke Excell, dan
tekan Ctrl + w, lihat apa yang terjadi.
Menyisipkan Control Object
pada WorkSheet
Seperti VB6, pada VBE terdapat
pula obyek-obyek kontrol, seperti Command Button, Text Box, Option
Button, Label, dan sebagainya. Tetapi, tidak semua kontrol yang
ada di VB6 terdapat di VBA Excel.
Pertama-tama kita perlu
menghidupkan Control Toolbox, dimana terdapat kontrol-kontrol
yang kita perlukan. Untuk itu arahkan kursor ke menu View-Toolbars-Control
Toolbox seperti gambar dibawah,
kemudian tekan dan akan
tampil:
Tarik Box tersebut ke
arah bawah agar tidak menghalangi WorkSheet,
Untuk menyisipkan kontrol dan
merubah-rubah propertinya, maka kita perlu menghidupkan Design Mode.
tekan gambar segitiga yang
memiliki nama Design Mode, sehingga gambar tersebut terlihat
terang
sebagai contoh kita akan
menyisipkan Command Button di Sheet1,
tekan Icon Command
Button pada ToolBox,
lalu kursor akan berubah
menjadai tanda "+" , gunakan kursor untuk membentuk sebuah Command
Button dengan cara klik kiri pada mouse.
untuk memasukan kode maka double-
clik kiri mouse pada Command Button sehinnga Visual Basic
Editor muncul
masukkan kode yang diinginkan
ke dalam
Private Sub CommandButton1_Click()
End Sub
CommandButton1_Click berarti
program akan dijalankan pada saat Command Button ditekan. Seperti
terlihat bahwa prosedur ini terdapat di dalam Sheet1, WorkSheet
dimana Command Button disisipkan.
Berikut adalah salah satu contoh
program
kembali ke Excel dan
maitkan Design Mode dengan cara menekannya, sehingga tampilannya tidak
terang lagi,
lalu tekan Command Button,
maka akan tampil :
Kita dapat mengakses properti Command
Button, dengan cara menyalakan kembali Design Mode lalu klik kanan Command
Button,
tekan Properties
maka akan tampil,
di sisi sebelah kiri akan
tampil Windows Properties.
Kita dapat merubah tampilan (Caption)
pada Command Button dengan cara merubah isian Caption di Properties,
atau dengan mengedit Command
Button,
bila ditekan, akan tampil
lalu rubahalah Caption-nya,
Menggunakan UserForm
Untuk menggunakan UserForm,
pertama sisipkan dahulu obyek ini kedalam project kita.
tampilan berikut akan muncul
selanjutnya Anda dapat
melakukan langkah-langkah pemograman seperti di VB6.
Pada contoh berikut kita akan
menyisipakan sebuah CommandButton dan sebuah TextBox ke dalam
form kita. Isi dari sebuah range (kita pilih range A1) di salah
satu WorkSheet (pada contoh ini kita pilih Sheet1) akan sama
dengan isi TextBox ketika CommandButton ditekan.
Pertama-tama kita sisipkan
sebuah CommandButton dan TextBox pada UserForm,
lalu klik dua kali
CommandButton, hingga tampilan berikut muncul
isikan kode berikut
Range("A1").Value = TextBox1.Value
Selanjutnya kembali ke Sheet1
(pada Visual Basi Editor), isikan kode beirkut di Prosedur "lembar1",
UserForm1.Show
Kode di atas memerintahkan
agar UserForm1 muncul
kembali ke Excell, dan
tekan Ctrl+w untuk menjalankan Prosedur "lembar1".
isi TextBox dengan kata
"sudah" lalu tekan CommandButton1,
Sebagai catatan, karena UserForm
muncul maka Anda tidak dapat menggunakan WorkSheet pada Excel. Bila Anda
menginginkan agar, bisa beralih ke WorkSheet, maka kode berikut dapat
digunakan (hanya bekerja pada Excell 2000 ke atas).
UserForm1.Show vbModeless
terimaksih untuk tutorial vb di ms excelnya sangat membatu sekali buat saya dalm menyelesaikan tugas.
ReplyDelete