Langsung ke konten utama

TUTORIAL MACRO EXCEL DAN CONDITIONAL FORMATTING PADA MICROSOFT EXCEL 2013

Macro adalah sederetan fungsi dan perintah program yang disimpan dalam menu Visual Basic dan juga Macro excel disebut sebagai sebuah tool yang kita gunakan untuk membuat suatu masalah menjadi lebih gampang dikerjakan dan biasanya digunakan untuk analisa permasalahan di dalam database-database besar.

SCREENSHOT PROGRAM
 




Disini saya ingin membuat sebuah program yaitu program yang bertujuan untuk menyeleksi calon mahasiswa yang ingin masuk ke sebuah perguruan tinggi. Baiklah adapun tahap-tahap atau tutorialnya adalah sebagai berikut :

1)        Disini saya menggunakan Microsoft office excel 2013, untuk dapat bekerja dengan menggunakan macro excel terlebih dahulu kita aktifkan ribbon developer yaitu dengan cara klik FILE => OPTIONS => CUSTOMIZE RIBBON => centang pada main tabnya pada developer kemudian klik ok.

2)        Selanjutnya klik pada RIBBON DEVELOPER => VISUAL BASIC maka akan tampil windows baru yang akan kita gunakan untuk membuat sebuah form aplikasi. Kemudian klik VBA PROJECT => INSERT => PILIH USERFORM. Agar database terjaga dengan aman maka disini saya membuat sebuah form login jadi disini saya menggunakan 2 form yaitu untuk login admin dan untuk pengisian data seleksi mahsiswa. Seperti halnya kita membuat aplikasi dengan menggunakan visual basic yaitu dengan cara klik pada menu toolbox dan pilih menu yang kita perlukan disana. Disini saya menggunakan 3 button pada form login yaitu login, delete dan exit. Adapun kodingan dan penjelasanya adalah sebagai berikut :

BUTTON LOGIN
Private Sub CommandButton1_Click()
‘digunakan untuk button login
Dim a, b, c, d, e
a = TextBox1.Text
b = TextBox2.Text
If a = "rizki" And b = "mufrizal" Then
c = MsgBox("Selamat Datang Admin", vbOKOnly + vbInformation, "Info")
TextBox1 = ""
TextBox2 = ""
UserForm1.Hide
UserForm2.Show
Else
If a = "rizki" Or b = "mufrizal" Then
d = MsgBox("Maaf password dan username tidak sesuai", vbOKOnly + vbExclamation, "Info")
TextBox1 = ""
TextBox2 = ""
Else
e = MsgBox("username dan password salah", vbOKOnly + vbExclamation, "Info")
TextBox1 = ""
TextBox2 = ""
End If
End If
End Sub
           
            Diatas adalah kodingan untuk button login, penjelasanya adalah :

            Deklarasikan terlebih dahulu textbox yang ada kemudian kita disini menggunakan seleksi kondisi if yang bertujuan untuk seleksi password yang akan kita masukkan. Jika username yang dimasukkan adalah rizki dan password nya adalah mufrizal maka akan keluar pesan dengan bacaan selamat datang admin dan form pengisian seleksi calon mahasiswa juga akan tampil, tpi jika salah satu salah maka akan tampil pesan Maaf password dan username tidak sesuai dan begitu pula jika keduanya salah akan tampil pesan username dan password salah.

BUTTON CLEAR
Private Sub CommandButton2_Click()
‘digunakan untuk button clear
TextBox1 = ""
TextBox2 = ""
End Sub

            Diatas adalah kodingan untuk button clear, penjelasanya adalah :

Apabila kita menekan button clear maka textbox tersebut akan kosong karena disini kita mendeklarasikannya dengan tanda “” (double titik 2) yang artinya kosong.

BUTTON EXIT
Private Sub CommandButton3_Click()
‘digunakan untuk button exit
Dim a
a = MsgBox("anda yakin ingin keluar ?", vbYesNo + vbInformation, "Info")
If a = vbYes Then
ActiveWorkbook.Save
ActiveWorkbook.Close
ActiveWindow.Close
End
End If
End Sub
            Diatas adalah kodingan untuk button exit,penjelasanya adalah :
           
Pertama kita deklarasikan terlebih dahulu untuk tampilan pesan, dan kita menggunakan kondisi if. Jadi apabila kita menekan yes maka program akan ditutup sedangkan kita tekan no maka program tidak akan tertutup.    

3)        Setelah selesai pada form pertama sekarang kita lanjut pada userform pemasukan database seleksi calon mahasiswa. Disini saya menggunakan 3 button dan 4 radiobutton. Membuat userform pemasukan databse seleksi calon mahasiswa ini sama seperti kita membuat form login hanya saja kita menambahkan radiobutton pada userform ini. Adapun kodingannya untuk form ini adalah :

BUTTON SAVE
Private Sub CommandButton1_Click()
‘digunakan untuk button save
ChDir "F:\Programing\tugas macro excel"
Workbooks.Open Filename:="F:\Programing\tugas macro excel\database.xlsx"
Dim lilik As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
'menemukan baris kosong pada database
lilik = ws.Cells(Rows.Count, 1) _
            .End(xlUp).Offset(1, 0).Row
'copy data ke database
ws.Cells(lilik, 1).Value = Me.nama.Value
ws.Cells(lilik, 2).Value = Me.pendaftaran.Value
ws.Cells(lilik, 3).Value = Me.NISN.Value
ws.Cells(lilik, 4).Value = Me.UN.Value
ws.Cells(lilik, 7).Value = Me.benar.Value
ws.Cells(lilik, 8).Value = Me.salah.Value
ActiveWorkbook.Save
ActiveWindow.Close
Me.nama.Value = ""
Me.pendaftaran.Value = ""
Me.NISN.Value = ""
Me.UN.Value = ""
Me.benar.Value = ""
Me.salah.Value = ""
Me.S1.Value = 0
Me.D3.Value = 0
Me.TI.Value = 0
Me.SI.Value = 0
End Sub

Diatas adalah kodingan untuk button save, penjelasanya adalah :

Karena kita menggunakan file database lain maka untuk button save kita harus terlebih dahulu membuka databasenya dan memasukkan datanya ke dalam database. Kemudian kita deklarasikan sheets database tersebut. Kemudian kita membuat kodingan agar pada saat kita memasukkan maka record tersebut akan otomatis bertambah dan Jangan lupa kodingan untuk penyesuaian textbox dengan cells yang terdapat tersebut pada excel. Dan diakhiri dengan
ActiveWorkbook.Save
ActiveWindow.Close
Yang bertujuan untuk save data pada file database excel dan menutup windows database excel tersebut. 

BUTTON DELETE
Private Sub CommandButton2_Click()
‘digunakan untuk button delete
Me.S1.Value = 0
Me.D3.Value = 0
Me.TI.Value = 0
Me.SI.Value = 0
Me.nama.Value = ""
Me.pendaftaran.Value = ""
Me.NISN.Value = ""
Me.UN.Value = ""
Me.benar.Value = ""
Me.salah.Value = ""
End Sub


Diatas adalah kodingan untuk button save, penjelasanya adalah :

Untuk kodingan Me.S1.Value = 0 itu adalah kodingan untuk mengkosongkan radiobutton sedangkan Me.nama.Value = ”” untuk mengkosongkan textbox yang ada.

BUTTON LOGOUT
Private Sub CommandButton3_Click()
‘digunakan untuk button logout
Dim a
a = MsgBox("anda yakin ingin logout ?", vbYesNo + vbInformation, "Info")
If a = vbYes Then
UserForm2.Hide
UserForm1.Show
End If
End Sub

Diatas adalah kodingan untuk button logout, penjelasanya adalah :

Sama halnya dengan kodingan pada button exit yang ada pada userform login hanya saja disini jika kita menekan yes maka userform login akan tampil kembali karena kita logout.

RADIOBUTTON D3,S1,TI,SI
Private Sub D3_Click()
‘digunakan untuk radiobutton D3
ChDir "F:\Programing\tugas macro excel"
Workbooks.Open Filename:="F:\Programing\tugas macro excel\database.xlsx"
Dim lilik As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
lilik = ws.Cells(Rows.Count, 1) _
            .End(xlUp).Offset(1, 0).Row
ws.Cells(lilik, 5).Value = Me.D3.Caption
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
RADIOBUTTON S1
Private Sub S1_Click()
‘digunakan untuk radiobutton S1
ChDir "F:\Programing\tugas macro excel"
Workbooks.Open Filename:="F:\Programing\tugas macro excel\database.xlsx"
Dim lilik As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
lilik = ws.Cells(Rows.Count, 1) _
            .End(xlUp).Offset(1, 0).Row
ws.Cells(lilik, 5).Value = Me.S1.Caption
ActiveWorkbook.Save
ActiveWindow.Close
End Sub


RADIOBUTTON SI

Private Sub SI_Click()
‘digunakan untuk radiobutton SI
ChDir "F:\Programing\tugas macro excel"
Workbooks.Open Filename:="F:\Programing\tugas macro excel\database.xlsx"
Dim lilik As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
lilik = ws.Cells(Rows.Count, 1) _
            .End(xlUp).Offset(1, 0).Row
ws.Cells(lilik, 6).Value = Me.SI.Caption
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
RADIOBUTTON TI

Private Sub TI_Click()
‘digunakan untuk radiobutton TI
ChDir "F:\Programing\tugas macro excel"
Workbooks.Open Filename:="F:\Programing\tugas macro excel\database.xlsx"
Dim lilik As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
lilik = ws.Cells(Rows.Count, 1) _
            .End(xlUp).Offset(1, 0).Row
ws.Cells(lilik, 6).Value = Me.TI.Caption
ActiveWorkbook.Save
ActiveWindow.Close
End Sub


Diatas adalah kodingan untuk radiobutton D3,S1,TI,SI, penjelasanya adalah :

                 Kodingan untuk radiobutton D3,S1,TI,SI sama halnya dengan kodingan save karena fungsinya yaitu pada saat kita tekan radiobutton maka informasi yang ada pada radiobutton tersebut akan dimasukkan ke dalam database excel.

4)        Kemudian agar pada saat kita membuka file excel dan form login tersebut tampil maka kita harus membuat sebuah kodingan terlebih dahulu yaitu dengan cara : klik VBA PROJECT => INSERT => PILIH MODULE kemudian masukkan kodingan dibawah ini :

‘digunakan untuk menampilkan form login pada saat file excel dibuka
Sub Auto_Open()
UserForm1.Show
End Sub

Private Sub Workbook_Open()
UserForm1.Show
End Sub

5)        Setelah semuanya selesai kemudian kita save file excel ini dengan ektensi .xlsm atau workbook macro.

6)        Kemudian kita beralih ke databasenya.agar data tersusun dengan rapi atau untuk menjaring data maka kita menggunakan filter dengan cara klik RIBBON DATA => FILTER ATAU DENGAN SHORTCUT CTRL + SHIFT + L .disini kita tidak menggunakan macro tapi disini kita menggunakan rumus-rumus excel untuk seleksi calon mahasiswa yang ada. Adapun rumus tersebut terdapat pada cell mutu nilai seleksi, nilai huruf, hasil seleksi dan juga keterangan. Baiklah adapun rumus-rumus excel tersebut adalah sebagai berikut :

CELL NILAI SELEKSI
=IF(G10="";"";((G10*4)-(H10*2)))
Digunakan untuk menghitung nilai dari seleksi

CELL NILAI HURUF
=IF(I9="";"";IF(I9>=90;"A";IF(I9>=80;"B";IF(I9>=70;"C";IF(I9>=60;"D";"E")))))Digunakan untuk menghitung atau seleksi nilai huruf

CELL HASIL SELEKSI
=IF(J9="";"";IF(AND(J9="A";D9>=80);"LULUS";IF(AND(J9="A";D9<80);"GAGAL";IF(AND(J9="B";D9>=80);"LULUS";IF(AND(J9="B";D9<80);"GAGAL";IF(AND(J9="C";D9>=80);"LULUS";IF(AND(J9="C";D9<80);"GAGAL";IF(AND(J9="D";D9>=80);"GAGAL";IF(AND(J9="D";D9<80);"GAGAL";IF(AND(J9="E";D9>=80);"GAGAL";IF(AND(J9="E";D9<80);"GAGAL")))))))))))
Digunakan untuk menentukan lulus atau gagalnya seorang calon mahasiswa

CELL KETERANGAN
=IF(J9="";"";IF(AND(J9="A";D9>=80);"Sempurna";IF(AND(J9="A";D9<80);"Nilai UN tidak mencukupi";IF(AND(J9="B";D9>=80);"sangat baik";IF(AND(J9="B";D9<80);"Nilai UN tidak mencukupi";IF(AND(J9="C";D9>=80);"Baik";IF(AND(J9="C";D9<80);"Nilai UN tidak mencukupi";IF(AND(J9="D";D9>=80);"Nilai seleksi tidak mencukupi";IF(AND(J9="D";D9<80);"Nilai UN dan seleksi tidak mencukupi";IF(AND(J9="E";D9>=80);"Nilai Seleksi tidak   mencukupi";IF(AND(J9="E";D9<80);"Nilai UN dan Seleksi tidak mencukupi")))))))))))

7)        Agar tampilan lebih menarik maka disini saya menggunakan conditional formatting yang bertujuan untuk memberi warna pada teks yang berbeda, membuat grafik presentasi antar nilai, mebuat table apabila terdapat data secara otomatis dan banyak lagi lainnya.

8)        Baiklah pertama-tama untuk membuat table secara otomatis pada saat kita mengisi data maka langkah-langkahnya adalah : BLOK SEMUA COLUMS HINGGA BARIS 1048576 DENGAN CARA KLIK NAMEBOX LALU KETIKAN B9:B1049576 BERTUJUAN AGAR SELURUH COLUMS DAPAT DIFORMAT DENGAN CONDITIONAL FOMATTING => KLIK RIBBON HOME => CONDITIONAL FORMATTING => NEW RULE => USE FORMULA TO DETERMINE WHICH CELLS TO FORMAT => ISI DENGAN KODINGAN =NOT(ISBLANK($B9)) PADA KOLOM VALUES WHERE THIS FORMULA IS TRUE => KLIK FORMAT => KLIK BORDER => PILIH BORDER YANG TITIK-TITIK => KLIK OK untuk rumus ini berfungsi pada cell nama,nomor pendaftaran, NISN, kelompok, jurusan. Maksud kodingan =NOT(ISBLANK($B9)) adalah pada saat cell B9 ada data maka border akan terbentuk secara otomatis dan jika cell kosong maka border tidak akan terbentuk.

9)        Selanjutnya untuk membuat tulisan warna yang berbeda kita juga menggunakan conditional formatting yaitu dengan langkah sama seperti diatas hanya saja pada saat klik NEW RULE => PILIH FORMAT ONLY CELLS THAT CONTAIN=>PILIH BETWEEN => MASUKKAN NILAI =80 DAN NILAI =1000 => FORMAT => PILIH COLOR FONT BLUE => PADA BORDER PILIH GARIS TITIK-TITIK => KLIK OK. Dan ini berlaku untuk nilai =79 hingga -1000 hanya saja kita mengganti warnanya menjadi merah dan nilainya saja. Maka apabila kita mengisi data kita ambil contoh kita memasukkan nilai 85 maka nilai tersebut akan berwarna biru dan border akan terbentuk secara otomatis dan jika memasukkan nilai 65 maka nilai tersebut akan berwarna merah dan border juga terbentuk. Ini berfungsi untuk colums Nilai UN, jawaban benar,jawaban salah, mutu nilai seleksi, nilai huruf, hasil seleksi dan keterangan.

10)    Kemudian kita membuat grafik atau perbandingan dengan menggunakan conditional formatting yaitu dengan langkah seperti diatas hanya yang berbeda : KLIK NEW RULE => FORMAT ALL CELS BASED ON THEIR VALUES => PADA FORMAT STYLE PILIH DATA BAR DAN PILIH WARNA YANG ANDA SUKAI => KLIK OK. Ini digunakan untuk colums mutu nilai seleksi agar kita dapat mengetahui nilai yang calon mahasiswa yang tertinggi dan yang terendah.

11)    Setelah semuanya selesai sekarang kita save dengan ektensi .xlsx. setelah semua telah selesai kemudian kita jalankan macro excel kemudian anda isikan data sesuai dengan yang diminta, kemudian close program macro dan liat hasilnya di dalam database yang kita buat tadi, jika data yang kita buat telah sesuai dengan hasil yang ada di dalam databse berarti program macro yang kita buat telah berhasil. Jika masih ada kesalahan maka periksa kembali kodingannya atau penyesuaian textbox dengan cells yang kita gunakan tersebut. Sekian program yang saya buat lebih dan kurang saya mohon maaf. Terima kasih. 

Komentar

  1. Artikelnya bagus n bermanfaat bgt. Sy sekarang sdg belajar ky ginian. artikel ini membantu bgt. Klo blh sy minta filenya dong gan, biar lebih jelas untuk belajar. Soale artikelnya nurut sy kurang gambar. Trims.

    BalasHapus
  2. Siiiipppp... postingannya beneran bermanfaat... thanks.....

    BalasHapus
  3. alhamdulillah... thx bgt gan.. ane bru pndah kelas, ada materi ini...
    [ketinggalan jaauh..]

    keep posting, gan.. :D

    BalasHapus
  4. Keren bgt nih designnya
    http://pandaivb.blogspot.co.id

    BalasHapus

Posting Komentar

Postingan populer dari blog ini

JSON VS XML

JSON VS XML
1.Pengertian JSON
JSON (JavaScript Object Notation) adalah format pertukaran data yang ringan, mudah dibaca dan ditulis oleh manusia, serta mudah diterjemahkan dan dibuat (generate) oleh komputer. Format ini dibuat berdasarkan bagian dari Bahasa Pemprograman JavaScript, Standar ECMA-262 Edisi ke-3 - Desember 1999 (http://www.json.org).
JSON adalah salah satu bahasa markup yang dapat melakukan pertukaran data dimana JSON ini dibuat berdasarkan javascript dan pastinya sintaknya lebih ke javascript. Dengan membuat sebuah JSON sama halnya dengan kita membuat sebuah object pada javascript itu sendiri. Di dalam membuat JSON pasti kita bakal berkenalan dengan yang namanya array pada javascript sehingga memudahkan bagi para deveploper/programmer.
1.1.Mengapa kita menggunakan JSON ?
JSON sama halnya dengan XML yaitu yang berfungsi sebagai pembawa data atau sebagai pertukaran data antar client server. JSON dapat digunakan di berbagai bahasa pemograman, dapat digunakan dalam bahasa pemo…

TUTORIAL BACKTRACK 5 R3

Backtrack, OS Linux yang installernya biasanya dibagikan secara gratis kalo kita ikut seminar yang berbau hacking. Backtrack menyediakan tools attacking/hacking didalamnya yang sangat lengkap menurut saya. Untuk yang belum punya bisa didownload disini :http://www.backtrack-linux.org/. Dipost ini saya akan menjelaskan cara install Backtrack, saya menggunakan Backtrack 5 R3 Gnome. Selain menggunakan kata - kata, saya juga menambahkan gambar saat proses installasinya. Click gambar agar terlihat jelas. Berikut adalah cara install Backtrack 5 R3  :
Insert media installasi (DVD/FlashDisk) ke PC anda, tunggu sampai tampilan seperti ini :
tekan  enter,
Ketik Startx (Fungsi Startx adalah untuk memulai / memunculkan GUI Backtrack 5 R3 Gnome)Pilih BackTract Text, click enter.
Click Install BackTrack,
Pilih English, setelah itu click Forward, tunggu sampai setting up the clock selesai,
Sesuaikan Religion dan Time Zone sesuai keinginan anda lalu click Forward :
Pilih Keyboard layout yang anda inginkan. Lal…