a1 ~ mata arjuna.pdf

3/20/15 A1 ~ Mata Arjuna

1/21irembun.blogspot.com/2014/11/flashdisc-toshiba-8gb.html

MACRO EXCEL 2007 / 2010

Secara umum Macro Excel dapat diartikan sebagai

program yang berisi rangkaian perintah untuk

mengatur beberapa aspek pada Excel sehingga

pekerjaan dapat menjadi lebih efektif dan efisien.

Berikut ini dua istilah yang berkaitan dengan Macro

Excel yang perlu Anda ketahui.

Visual Basic Editor

Yang dimaksud dengan Visual Basic Editor atau Excel

VBA Integrated Development Environment (IDE) adalah

lingkungan tempat program macro Excel dibuat (lihat

Gambar 1.24). Lingkungan kerja Visual Basic Editor

pada Excel 2007/2010.

VBA

Bahasa pemrograman yang digunakan dalam Macro

Excel 2007/2010 adalah Visual Basic for Application



atau yang biasa disingkat menjadi VBA. Sesungguhnya

VBA tidak hanya digunakan untuk Microsoft Excel,

tetapi juga digunakan oleh beberapa produk Microsoft

lainnya seperti Microsoft Word, Microsoft Acces, dan

Microsoft PowerPoint. Selanjutnya masing-masing

aplikasi yang menggunakan VBA disebut sebagai host

application. VBA juga digunakan oleh sistem

pemrograman yang berdiri sendiri yang dikenal dengan

Visual Basic (VB).

Pendahuluan aplikasi gudangA. Workbook event

Contoh penggunaan workbook event open pada

workbook

Private Sub Workbook_Open()

Msgbox "Workboo dibuka"

End Sub

Ketika workbook terbuka untuk pertama kalinya, maka

prosedur diatas akan dijalankan dan mengeksekusi

sintaks (Kode) didalam prosedur tersebut, yaitu

menampilkan kotak pesan "Workboo dibuka".



A1 Koding saat workbook event open

Berikut ini gambar event workbook yang akan

digunakan dalam pembuatan aplikasi gudang

1. Event workbook open

2. Event workbook before close



Sebagaimana yang sudah dijelaskan diatas event

workbook open mempunyai koding atau sintaks

defauld dibawah ini


'Modul

End Sub

Sedangkan untuk event workbook before close

merupakan kebalikan dari event workbook open, yaitu

menjalankan koding atau sintaks saat workbook

hendak ditutup, cara penulisan koding atau

prosedurnya sama halnya dengan event workbook

open, dan berikut ini kode defauld event workbook

before close

Private Sub Workbook_BeforeClose(Cancel As

Boolean)

ISI KODING

End Sub



A1 Koding saat workbook event open - membuka

userform


'Nama userform project

Userform1.show

End Sub

A2 Koding saat workbook event open - Memilih sheet

tertentu saat workbook aktif


Sheets("Sheet3").Select

End Sub

A3 Koding saat workbook event open - Menampilkan

kotak dialog "Selamat datang" saat workbook aktif

Saat workbook aktif atau pertama kali dibuka maka

akan ada kotak dialog Selamat datang pak hatta


Msgbox "Selamat datang pak hatta"

End Sub

A4 Koding saat workbook event open - Memberikan

nilai pada range worksheet

Saat workbook aktif atau pertama kali dibuka maka

pada sheet1 range A1 akan bernilai Selamat datang

pak hatta


Sheets("Sheet1").range("A1").value = "Selamat

datang pak hatta"

End Sub

A5. Koding event workbook before close -

Menyembunyikan sheet



Private Sub Workbook_BeforeClose(Cancel As

Boolean)

Sheets("Sheet1").Visible = xlSheetVeryHidden



End Sub

B. Userform

Userform adalah basic dari VBA untuk membuat antar

muka/interface. berikut ini merupakan event

padauserform yang akan digunakan pada pembuatan

aplikasi gudang

B.1 Koding event userform aktif

Coba baca lagi event workbook open, kurang lebih

begitulah pengertian dari event userform aktif, contoh

syintaks atau koding userform aktif sebagai berikut

Private Sub UserForm_Activate()

With Me

.height = 100

.Left = 20

.Width = 2007

end with

End Sub

Koding diatas merupaka pengaturan lebar tinggi dan

letak userform saat pertama kali userform muncul

atau aktif, sebagaimana event workbook aktif yang

bisa dituliskan syntaks kotak dialaog atau perintah

memberikan nilai pada range tertentu.

B.2 Event userform Query Close

Event userform QueryClose merupakan kebalikan dari

event userform aktif, kodig defauldnya sebagai berikut

Private Sub UserForm_QueryClose(Cancel As

Integer, CloseMode As Integer)

'ISI PROSEDUR / SINTAK / KODING



Msgbox "userform ditutup"

End Sub

Koding tersebut akan dijalankan ketika userform

hendak ditutup.

C. Textbox

TextBox adalah Field yang digunakan untuk

memasukkan teks. Text box dapat dibiarkan kosong

dan pengisiannya diserahkan sepenuhnya kepada

user, atau diprogram untuk mengisi teks-teks tertentu

sesuai dengan pilihan pengguna.

C.1 Textbox hanya bisa diisi angka

Koding yang bisa membuat kolom textbox hanya bisa

diisi angka, jadi kolom textbox tidak akan bereaksi jika

diketikan nilai selain angka 0 9

Private Sub Textbox1_KeyPress(ByVal KeyAscii

_

As MSForms.ReturnInteger)

'Validasi angka TextBox1

Select Case KeyAscii

Case Asc("0") To Asc("9")

Case Else

KeyAscii = 0

End Select

End Sub

C.2 format mata uang ( pemisahan angka 000 )

Koding untuk membuat angka menjadi bilangan

desimal, misalnya untuk memisahkan angka 10000

menjadi 10.000, dibawah ini kode macronya

Private Sub Textbox1_Change()

Textbox1.Value = Format(Textbox1.Value,

"#,##0")

End Sub



D. Pendukung

Berikut ini merupakan kumpulan pendukung partai

yang akan saya dirikan :D

D.1 Mengurutkan data secara Ascending

Mengurutkan berdasarkan dari jumlah terkecil sampai

jumlah yang terbesar, atau dari huruf A-Z

Sub AscendingA ()

'iPaSel adalah worksheet Sheet1

Set iPaSel = Sheets("Sheet1")

'Jika data yang ditampilkan merupakan hasil

penyaringan

If iPaSel.FilterMode Then

'Tampilkan seluruh data dalam worksheet

Sheet1

iPaSel.ShowAllData

End If

'Mengurutkan Sheet1 kolom Nama Field secara

Ascending

iPaSel.Range("Sheet1").Sort Key1:="Nama

Field", _

Order1:=xlAscending, Header:=xlYes

End sub

D.2 Mengurutkan data secara Descending

Merupakan kebalikan dari Ascending

Sub DescendingA ()



'Jika data yang ditampilkan merupakan hasil

penyaringan

If iPaSel.FilterMode Then

'Tampilkan seluruh data dalam worksheet

Sheet1

iPaSel.ShowAllData

End If

'Mengurutkan Sheet1 kolom Nama Field secara

Descending



iPaSel.Range("Sheet1").Sort Key1:="Nama

Field", _

Order1:=xlDescending, Header:=xlYes

End sub

PemanasanA. Entri data dengan userform

Membuat entri data dengan userform

Buka folder "B1"

A.1 Entri sederhana

Kode macro entri sederhana

Private Sub CommandButton1_Click()

Sheets("Sheet1").Range("A1").Value =

TextBox1.Value

Sheets("Sheet1").Range("A2").Value =

TextBox2.Value

End Sub

Entri data akan menimpa data yang lama dengan data

yang baru

A.2 Entri data secara dinamis

Entri data secara dinamis yaitu membuat entri data

selalu memilih range kosong dengan kriteria sel

tertentu



'Merupakan deklarasi tempat entri sheet1

SelKosong = iPaSel.Cells(iPaSel.Rows.Count,

"A"). _

End(xlUp).Offset(0, 0).Row

'Mencari sel kosong pada sheet1 dimulai pada

sel atau range A kebawah

With iPaSel

'Masukkan entri data di bawah record terakhir

kolom A

.Cells(SelKosong + 1, 1).Value = TextBox1.Value




End With

End Sub

Keterangan tambahan

1. Kode macro "iPaSel" bisa diganti sesuai keinginan ,

namun dalam satu prosedur tidak diperkenankan

mendeklarasikan object dengan nama deklarasi yang

sama, contoh deklarasi yang salah




End Sub

Deklarasi yang benar



Set TabSel = Sheets("Sheet2")

End Sub

2. Kode macro "With iPaSel" merupakan

penyederhanaan sesuai fungsi "with", lihat contoh

penjabaran kode macro dibawah ini dengan tanpa

"with"

iPaSel.Cells(SelKosong + 1, 1).Value =

TextBox1.Value

iPaSel.Cells(SelKosong + 1, 2).Value =

TextBox2.Value

Dengan"With"

With iPaSel


kolom A



End With

Ingat setiap ada tag pembuka "With" harus ditutup

dengan "End With" katakan saja tag penutup

3. Mencari sel kosong pada range B kebawah,

perhatikan kode macro mencari sel kosong pada range

A




"A"). _


Merubah ke rang B


"B"). _


With iPaSel


kolom B



End With

Merubah ke rang C


"C"). _


With iPaSel


kolom C



End With

Dan seterusnya

A.3 Edit entri data

Buka folder B1 file B3

Edit data tertentu menggunakan primer key atau

acuan nama range, nama range bisa dibuat pada sub

menu formula>Define Name > Nwe Name, Contoh

ketika saya akan edit data dengan acuan range A,

maka saya akan buat nama range

=OFFSET(Sheet1!$A$2;1;0;COUNTA(Sheet1!$A:$A)-2;1)

Acuan range B

=OFFSET(Sheet1!$B$2;1;0;COUNTA(Sheet1!$B:$B)-1;1)

Acuan range C

=OFFSET(Sheet1!$C$2;1;0;COUNTA(Sheet1!$C:$C)-1;1)

dan seterusnya

Kode macro edit entri data





Set KeyRangeA = iPaSel.Range("RangeA")

Set c = KeyRangeA.Find(TextBox1.Value, _

LookIn:=xlValues) 'primer key

c.Offset(0, 1).Value = TextBox2.Value



End Sub

Keterangan

1. Kode macro " Set KeyRangeA =

iPaSel.Range("RangeA") " bisa dibilang deklarasi untuk

range A dengan catatan entri data yang ada pada

kolom A tidak ada entri data yang kembar, jika

terdapat entri data kembara atau ganda maka entri

data yang akan diedit adalah entri yang paling bawah

2. Kode macro " Set c =

KeyRangeA.Find(TextBox1.Value, LookIn:=xlValues) "

bisa disebut deklarasi untuk mencari salah satu nilai

entri data yang ada pada kolom A dengan

menggunakan textbox1, jadi syarat untuk edit entri

data yang wajib adalah Nilai dari textbox1 harus

sesuai dengan salah satu nilai entri data yang ada

pada kolom A .

3. Kode macro " c.Offset(0, 1).Value " perhatikan urutan

(0, 1) (0, 2) (0, 3), artinya nilai 0 adalah nilai range A,

sedangkan nilai 1,2,3 adalah 1 kolom setelah kolom A

(B), 2 kolom setelah kolom A (C), dan 3 kolom setelah

kolom A (D)

dan seterusnya

4. Sifat primer key adalah unik, tidak boleh ada data

kembar atau data yang sama pada primer key

A4. Menghapus entri data

Buka folder B1 file B4

Menghapus entri data bisa diasumsikan sama halnya

dengan edit entri data, kode hapus entri data adalah





Set KeyRangeA = iPaSel.Range("RangeA")

Set C = KeyRangeA.Find(TextBox1.Value, _

LookIn:=xlValues) ' primer key

C.Offset(0, 1).Delete Shift:=xlUp



C.Offset(0, 0).Delete Shift:=xlUp ' primer key

End Sub

Keterangan

Mengapus entri data sesuai dengan kriteria

sebagaimana edit entri data, dan penghapusan "range

acuan (primer key)" dilakukan paling akhir, jika

diletakan diawal maka akan tejadi error

A.5 Membuat lookup

Buka file B5

Lookup adalah menampilkan data sesuai primer key,

data yang ditampilkan merupakan data yang sejajar

dengan primer key baik sebelum primer key maupun

sesudah primer key (jangan bingung), lookup bisa

dimanfaatkan untuk melengkapi project edit entri data

atu hapus entri data.

Kode macro lookup sama halnya kode macro edit data

dan hapus data, hanya dibalik balik :)

Private Sub TextBox1_Change()

Set C =

Sheets("Sheet1").Range("RangeA").Find(TextBox1.Value,

LookIn:=xlValues) ' primer key

TextBox2.Value = C.Offset(0, 1)



End Sub

B. Menampilkan entri data dalam listbox

B.1 Membuat header listbox

Buka folder C1 file C1

Kode macro Membuat header listbox



Sub HEADER()

'Menghapus ListBox

ListBox1.Clear

With ListBox1

'Menambahkan item ListBox sebagai judul

kolom

.AddItem

'Judul ListBox kolom pertama

.List(.ListCount - 1, 0) = "HEADER 1"

'Judul ListBox kolom kedua


'Judul ListBox kolom ketiga


'Lebar masing-masing kolom dalam ListBox

.ColumnWidths = 30 & ";" & 40 & ";" & 50

End With

End Sub

Ketrangan

Jumlah kolom diatas adalah 3, listbox secara defauld

hanya menyediakan 1 kolom, untuk merubah 1 kolom

menjadi 3 kolom lihatlah properties pada listbox >Pilih

ColumnCount dan BoundColumn, rubah nilainya dari 0

menjadi 3 , atau tambahkan kode macro "

.BoundColumn = 3 " dan " .ColumnCount = 3 " dibawah

kode macro " with listbox1 ".

B.2 Menampilkan entri data dalam listbox

Buka file C2

Kode macro menampilkan entri data kedalm listbox

'Sub Procedure untuk menampilkan hasil

pencarian dalam ListBox

Sub TampilkanEntriData()



'Menghapus ListBox

ListBox1.Clear

With ListBox1

'Menambahkan judul kolom ListBox

.AddItem




.List(.ListCount - 1, 0) = "A"


.List(.ListCount - 1, 1) = "B"


.List(.ListCount - 1, 2) = "C"


.ColumnWidths = 65 & ";" & 60 & ";" & 65

End With

With iPaSel

'SRangeA adalah range A yang ditampilkan

Set SRangeA = iPaSel.Range("A1:A10"). _

SpecialCells(xlCellTypeVisible)

'sTampil merupakan pengulangan setiap sel

dalam SRangeA

For Each sTampil In SRangeA

With ListBox1

'Memasukkan item ke dalam ListBox

.AddItem sTampil.Value

'Menampilkan data pada range A

.List(.ListCount - 1, 0) = sTampil.Value

'Menampilkan data pada range d

.List(.ListCount - 1, 1) = sTampil.Offset(0,

1).Value

'Menampilkan data pada range c


2).Value

End With

Next sTampil

End With

End Sub

Silahkan dilihat lagi tentang edit entri data pada

halaman sebelumnya

B.3 Filter data menggunakan listbox

Buka file C3

Kode macro filter data menggunakan listbox

Prosedur Menampilkan seluruh data kedalam listbox

Sub TampilkanEntriData()





'Menghapus ListBox

ListBox1.Clear

With ListBox1

'Menambahkan judul kolom ListBox

.AddItem


.List(.ListCount - 1, 0) = "A"


.List(.ListCount - 1, 1) = "B"


.List(.ListCount - 1, 2) = "C"


.ColumnWidths = 65 & ";" & 60 & ";" & 65

End With

With iPaSel

'SRangeA adalah range A yang ditampilkan

Set SRangeA = iPaSel.Range("RangeA"). _

SpecialCells(xlCellTypeVisible)

'sTampil merupakan pengulangan setiap sel

dalam SRangeA

For Each sTampil In SRangeA

With ListBox1

'Memasukkan item ke dalam ListBox

.AddItem sTampil.Value

'Menampilkan data pada range A

.List(.ListCount - 1, 0) = sTampil.Value

'Menampilkan data pada range d


1).Value

'Menampilkan data pada range c


2).Value

End With

Next sTampil

End With

End Sub

Melakukan pencarian menggunakan textbox1

berdasarkan entri data pada kolom B






'iPaData adalah range Sheet1

Set iPaData = iPaSel.Range("Data")

'TmptFltr adalah range L2:N3 worksheet Sheet1

Set TmptFltr = iPaSel.Range("G2:J3")

'Pencarian pada range Range B

With iPaSel.Range("RangeB")

'c merupakan sel hasil pencarian

Set c = .Find(TextBox1.Value, LookIn:=xlValues)

'Jika tidak sel berisi kata kunci Range B

If c Is Nothing Then

listCari.Clear

'Keluar dari Sub Procedure

Exit Sub

'Jika ada sel berisi kata kunci Range B

Else

iPaSel.Range("G3:j3").ClearContents

'Memasukkan Range B ke dalam sel M3

iPaSel.Range("H3").Value = "*" & TextBox1.Value

& "*"

'Penyaringan data menggunakan Advanced

Filter

iPaData.AdvancedFilter Action:=xlFilterInPlace,

_

CriteriaRange:=TmptFltr

'Memanggil Sub Procedure TampilkanSemua

Call TampilkanEntriData

End If

End With

End Sub

Keterangan kode pencarian data

1. Penggunaan nama range "define name"

nama range "Data" pada kode macro "Set iPaData =

iPaSel.Range("Data")" merupakan tempat range seluruh

entri data, dari range A sampai D,

=OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A:$A)-1;4)

2. Pencarian pada textbox1 berdasarkan nilai entri

data yang ada pada range B, menggunakan nama

range "RangeB" pada kode macro " With

iPaSel.Range("RangeB") "

=OFFSET(Sheet1!$B$2;1;0;COUNTA(Sheet1!$B:$B)-1;1)



, pada file C3 saya sertakan pencarian dengan

textbox2 yang mengacu pada nilai range C, dengan

nama range "RangeC" kode macro silahkan dilihat

pada file contoh

=OFFSET(Sheet1!$C$2;1;0;COUNTA(Sheet1!$C:$C)-1;1)

3. Kode macro " iPaSel.Range("H3").Value = "*" &

TextBox1.Value & "*" " membuat nilai pada range H3

dengan Textbox1, dimana nama field (header) pada

range H2 harus sama dengan nama header pada

range B2

4. Kode macro " iPaData.AdvancedFilter

Action:=xlFilterInPlace, CriteriaRange:=TmptFltr " saya

akan pretelin kodenya satu satu ya...

Kode macro " iPaData " merupakan nilai entri

data pada seluruh range, lhat kode macro " Set

iPaData = iPaSel.Range("Data") " dimana " iPaSel

" merupakan deklarasi dari sheet1

Kode macro " TmptFltr " merupakan nilai filter

pada range " "G2:J3" " lihat kode macro " Set

TmptFltr = iPaSel.Range("G2:J3") " , biasanya

filter gagal kebanyakan offside (istilah main

bola) pada pengaturan tempat filter ini

Jika kode macro " iPaData.AdvancedFilter

Action:=xlFilterInPlace, CriteriaRange:=TmptFltr

" ditulis ulang tanpa deklarasi maka akan

menjadi seperti ini "

Sheets("Sheet1").Range("Data").AdvancedFilter

Action:=xlFilterInPlace,

CriteriaRange:=Sheets("Sheet1").Range("G2:J3")

"

B.4 Export data dari listbox ke worksheet

Buka file C4

Export data dari listbox kedalam worksheet

merupakan istilah lain dari membuat entri data, biar

agak kerenan maka saya gunakan istilah export, meski

terasa aneh namun semoga anda senang dan

memnghilangkan kepenatan (padahal tidak aneh, alias

aneh yang dipaksakan). Menurut pengalaman pribadi

saya, belajar VBA atau macro tidak memerlukan



ketelitian yang tinggi namun kita hanya perlu

menggunakan ketelitian tingkat paling tinggi, lebih

lebih jika anda tidak mempunyai basic dalam

pemograman visual basic, dulu (mau cerita yah) saat

saya pernah mengalami masa masa prustasi dengan

macro, sampai sampai saya tidak pernah menyentuh

lagi apa yang ada hubunganya dengan macro selama

3 bulan lebih. Akhirnya (masih cerita) saya menemukan

setitik harapan secara sengaja saya membaca sebuah

tulisan diblog yang menyatakan bahwa "visual basic

itu tidaklah sulit, yang terpenting adalah logika anda

jalan maka semuanya akan beres", bersambung

(ceritanya bersambung). Okelah sekarang saatnya

memahami kode macro export data dari listbox

kedalam worksheet

B.4.1 Memahami komponen nilai pada listboX

Nilai listbox dibagi menjadi header dan isi atau nilai itu

sendiri, dan biasanya header pada listbox tidak

disertakan atau tidak dihitung sebagai nilai.

B.4.2 Kode macro export data dari listbox kedalam

worksheet

Lihat kode macronya buka file C4


Set iPasel2 = Sheets("Sheet2")

SelAkhir = iPasel2.Cells(iPasel2.Rows.Count,

"A"). _


For No = 1 To ListBox1.ListCount - 1 ' Struktur

pengulangan berdasarkan jumlah nilai listbox

yg Tidak menyertakan header

iPasel2.Cells(SelAkhir + No, 1).Value =

ListBox1.List(No, 0)

'lihat perbedaanya iPasel2.Cells(SelKosong + 1,

2).Value = TextBox1.Value





Next No



End Sub

Keterangan

Kode macro " For No = 1 To ....." merupakan struktur

pengulangan berdasarkan banyaknya nilai yang

terdapat pada listbox, jika nilai yang ada pada listbox

berjumlah 5 baris maka kode yang diapit dibawah " for

no " akan diulang sampai 5 kali dengan pembatasan

kode " Next No "

C. Combobox

Combobox merupakan salah satu kontrol dalam VBA

yang berfungsi untuk menampilkan beberapa nilai opsi

untuk dipilih salah satu dari beberapa nilai opsi yang

disediakan, opsi ditampilkan berbentuk dropdown list.

Buka folder D1 file D1

Contoh kode sederhana untuk membuat list suatu

combobox

Sub Sederhana1 ()

Combobox1.AddItem "Satu"

Combobox1.AddItem "Dua"

Combobox1.AddItem "Tiga"

Combobox1.AddItem "Empat"

End sub

Contoh kedua membuat list combobox berdasarkan

nilai yang ada pada worksheet

Sub ISISATU()

Set Iparengan = Sheets("Sheet1")

ComboBox2.Clear

For Each sIparengan In

Iparengan.Range("A1:A10")

With Me.ComboBox2

.AddItem sIparengan.Value

End With

Next sIparengan

ComboBox2.Value = ComboBox2.List(0)

End Sub



Keterangan

Kode macro " For Each sIparengan In

Iparengan.Range("A1:A10") " menampilkan entri data

yang ada pada range A1 sampai range A10, jika nilai

range berubah rubah gunakanlah "nama range"

Contoh ketiga mebuat list combobox dua atau tiga

kolom

Sub ISIDUA()

Set Iparengan = Sheets("Sheet1")

For Each sIparengan In

Iparengan.Range("A1:A10")

With Me.ComboBox3

.ColumnCount = 2

.AddItem sIparengan.Value

.List(.ListCount - 1, 1) = sIparengan.Offset(0,

1).Value

End With

Next sIparengan

ComboBox3.Value = ComboBox3.List(0)

End Sub

Keterangan

Kode macro " .ColumnCount = 2 " merupakan jumlah

kolom yang bisa dirubah menjadi nilai 3,4,5 atau 6

sesuai dengan kebutuhan, untuk menetukan lebar

kolom, lihat properties cari menu " ColumnWidths "

pada kolom sebelah kanan masukan nilai " 60 pt, 80 pt

" .

Setelah melakukan pemanasan saatnya anda berputar

mengelilingi lapangan sebanyak 20 kali, sebagai

catatan lapangan berdiameter 5 KM persegi , heheheh

a1 ~ mata arjuna.pdf

Documents