[TUTORIAL] FUNCTION OF RECORD MACRO | FUNGSI REKAM MAKRO

Microsoft Excel menyediakan fungsi untuk membuat macro secara otomatis kemudian dapat disimpan lalu dijalankan kembali. Sangat mudah menggunakan Record Macro karena Ms. Excel akan secara otomatis men-generate semua tindakan kita ke dalam bahasa Visual Basic. Singkatnya anda tidak perlu mengetik kode Visual Basic untuk membuat Macro.

Kali ini saya akan membahas mengenai fungsi record macro dalam mempermudah penggunaan Ms. Excel, data yang digunakan dalam tutorial ini berada pada post sebelumnya.

1. Tempatkan Active cell pada W1. Pastikan menge-klik Use Relative References pada tab menu View -> Tab Macros -> pilih Use Relative References.

2. Kemudian klik Record Macro. Kalau kita sudah siap melakukannya, maka akan muncul popup menu isian, isikan seperlunya, jangan lupa memberi nama macro dan membuat shortcut Ctrl+…(misal Ctrl+W). Ini bertujuan jika hendak menjalan macro kita tinggal menggunakan shorcut tersebut.

3. Berikutnya adalah membuat satu tabel secara manual (untuk data satu siswa saja). Caranya sisipkan kolom sebelah kanan siswa pertama (cell W1) sebanyak yang diperlukan (dalam contoh ini saya sisipkan sebanyak 8 kolom).

4. Lakukan pekerjaan secara manual: bisa menambahkan Tulisan pada beberapa cell seperti No, Mata Pelajaran, Semester 3, 4, 5 dll, memindah kolom tabel, memformat garis batas (border), memberikan warna cell, menulis rumus perhitungan tertentu dll sesuai format tabel yang kita harapkan. Jika semua sudah selesai format tabel seperti yang diharapkan, letakkan pointer pada cell pada nama siswa diurutan ke-3 (cell K1). Jika sudah selesai silahkan klik Stop Recording.
5. Sampai disini kita bisa mencoba kerja macro untuk tabel siswa ke dua, caranya tempatkan pointer pada cell nama siswa ke-3 lalu tekan shorcut key Ctrl+w seperti yang anda tentukan sebelumnya (saat akan me-record macro).
6. Jika hasilnya persis tabel yang kita buat namun berisi data siswa ke-2 maka itu berarti pekerjaan kita telah benar.

7. Apabila siswanya banyak sekali anggaplah 200 siswa maka kita perlu menekan Ctrl+w tadi sebanyak sisa-nya yang belum dibuatkan tabel. Agar hanya cukup sekali klik tombol Ctrl+w kita lakukan sedikit tambahan pada macro. Caranya klik menu macro -> view macro – klik edit dan tambahkan baris (setelah baris komentar yg ditandai awal dengan ‘…..) ketikkan:

Do Until Activecell.Value=””
dan pada bagian bawah sebelum end sub tulisan
Loop seperti ilustrasi berikut:

Coba jalankan macro tadi dan tunggu beberapa detik kemudian data Anda akan terubah secara menyeluruh menjadi tabel sesuai yang diharapkan. Atau tekan F5 sebagai shortcut.

Selesai! Semoga tutorial ini membantu Anda. ☺

Read more

[TUTORIAL] Transpose Data in Ms. Excel | MEN-TRANSPOS DATA PADA MS. EXCEL

 

Terkadang Anda perlu beralih atau memutar sel. Anda bisa melakukan ini dengan menyalin, menempelkan, dan menggunakan opsi Ubah urutan. Tapi melakukannya membuat data diduplikasi. Jika Anda tidak ingin, Anda bisa mengetikkan rumus menggunakan fungsi TRANSPOSE. Misalnya, dalam gambar berikut ini rumus =TRANSPOSE(A1:B4) membawa sel A1 hingga B4 dan mengatur mereka secara horizontal seperti gambar di atas.

Berikut akan saya lampirkan cara lain dalam men-transpos sebuah data secara manual tanpa menggunakan fungsi transpose pada microsoft excel.

1. Ketikkan data yang akan digunakan dengan cara ketik manual. Seperti contoh dibawah:

2. Pekerjaan yang dapat kita lakukan adalah mengolah data tersebut salah satunya dengan men-TRANSPOSE data agar formatnya mirip dengan format akhir. Dalam hal ini daftar mata pelajaran semula berada di bagian baris atas diubah menjadi bagian kolom paling kiri, sedangkan kolom nama yang tadinya berada pada kolom kiri diubah menjadi bagian baris di atas.Cara men-TRANSPOSE:

1. Copy bagian yang penting saja (dalam contoh ini saya hanya mengcopy range mata pelajaran dan nama siswa).
2. Tempatkan pointer pada cell dimana data tersebut akan di letakkan.
3. Klik kanan pada cell tadi pada pilih Transpose pada Paste Option.

3. Pada kolom A kita lakukan format text yang mengarah vertikal agar mendaftar, blok kolom tersebut, klik kanan, Format Cells -> Alignment -> Ubah orientasi teks menjadi 0 derajat (degre) sehingga hasilnya seperti ilustrasi berikut.

4. Langkah berikutnya adalah menghapus kolom A (tapi kita mesti ingat urutan mata pelajarannya) dan juga baris 8 dan 15 karena ini tidak kita perlukan, juga border (garis-garis tabel) perlu dihapus. Sisipkan 2 baris kosong di bawah baris nama (untuk judul kolom tabel) dan beri warna beda pada baris nilai semester 4 (baris 10-15), untuk membedakan nilai semester 3, 4, dan 5. Hasilnya seperti gambar dibawah:

Sedikit trik terkait fungsi iterasi yang akan kita gunakan, tambahakan pada ujung sebelah kanan kolom akhir (kolom U) tulisan apa saja misalnya "stop" (hilangkan tanda kutip), agar semua data terproses, apabila sudah selesai boleh hapus kolom akhir ini.

Read more

[TUTORIAL] INPUT DATA WITH VBA | INPUT DATA DENGAN VISUAL BASIC FOR APPLICATIONS

Visual Basic for Applications (VBA) adalah sebuah turunan bahasa pemrograman Visual Basic yang dikembangkan oleh Microsoft dan dirilis pada tahun 1993, atau kombinasi yang terintegrasi antara lingkungan pemrograman(Visual Basic Editor)dengan bahasa pemrograman(Visual Basic)yang memudahkan user untuk mendesain dan membangun program Visual Basic dalam aplikasi utama Microsoft Office, yang ditujukan untuk aplikasi-aplikasi tertentu. VBA didesain untuk melakukan beberapa tugas, seperti halnya mengkustomisasi sebuah aplikasi layaknya Microsoft Office atau Microsoft Visual Studio. Kegunaan VBA adalah mengotomatisasi pekerjaan. Pekerjaan yang dimaksud adalah pekerjaan yang dilakukan secara berulang-ulang dan pekerjaan yang kompleks. VBA berbeda dengan Microsoft Visual Basic, Microsoft Visual Basic memberi banyak pemrograman dan fungsi tingkat lanjut hingga Microsoft Visual Basic dapat dihasilkan program yang lebih kompleks untuk sistem operasi Microsoft Windows maupun Office. Sedangkan VBA hanya dapat dibangun pada aplikasi utama Microsoft Office mengendalikan fungsi aplikasi tersebut melakukan serangkaian objek terprogram. Versi VBA terbaru saat ini adalah versi 6.3 yang dirilis pada tahun 2001, yang mendukung semua program dalam Microsoft Office, yakni Microsoft Excel, Microsoft Access, Microsoft Word, Microsoft Outlook, Microsoft FrontPage, serta Microsoft PowerPoint dan juga Microsoft Visual Studio.

1. Membuka VBA. Setelah membuka MS. Excel langsung saja tekan kombinasi pada keyboard yaitu tombol ALT + F11. Lalu akan muncul tampilan seperti ini.

 2. Membuat suatu UserForm. Membuat userform cukup dengan membuka menu insert lalu klik UserForm. UserForm adalah basis kita untuk membuat VBAnya seperti kanvas untuk melukis. Kita dapat mengeditnya sesuai keinginan.

3 3.       Membuat Label

Cara membuat label adalah dengan menggunakan Toolbox yang ada disebelah Userform kita.

Lalu ganti caption pada label dengan mengklik tulisan “label 1” sekali dan buatlah label lagi sampai terbentuk menjadi seperti ini :

4.      4.  Membuat TextBox

Selanjutnya ialah membuat textbox sebagai field yang akan diisikan pada saat proses entry datanya. Untuk membuat textbox bisa mengklik pada toolbox dan menyeret pada userform. Jangan lupa mengganti nama textbox sesuai label yang diisi misalnya untuk NIS dapat diganti dengan “TXTNis”.

Lalu buat lah textbox sesuai yang diinginkan

Dan gantilah setiap nama dari textbox susai dengan Label yang telah dibuat. Hal ini juga bertujuan untuk memudahkan membuat code pada userform.

Textbox1 ganti dengan    TXTNis
Textbox2 ganti dengan    TXTNama
Textbox3 ganti dengan    TXTTempatLahir
Textbox4 ganti dengan    TXTTglLahir
Textbox5 ganti dengan    TXTALamat
Textbox6 ganti dengan    TXTNISN
Textbox7 ganti dengan    TXTHP
Textbox8 ganti dengan    TXTSKHUN
Textbox9 ganti dengan    TXTIjasah
Textbox10 ganti dengan    TXTNamaIbu
Textbox11 ganti dengan    TXTThnLahirIbu
Textbox12 ganti dengan    TXTPekIbu
Textbox13 ganti dengan    TXTNamaAyah
Textbox14 ganti dengan    TXTThnAyah
Textbox15 ganti dengan    TXTPekAyah
Textbox16 ganti dengan    TXTPengAyah
Textbox17 ganti dengan    TXTAlamatOrtu

5.       5. Membuat ComboBox

ComboBox adalah suatu field pengisian data tetapi telah disediakan beberapa opsi atau pilihan yang tinggal kita pilih. Cara membuatnya tinggal mengklik combobox pada ToolBox dan menyeretnya ke userform. Pada contoh kali ini kita gunakan untuk memasukan data “Jenis Kelamin” dan “Pendidikan”.

Nah kalau sudah dibuat ganti nama combobox sesuai label
ComboBox1 ganti dengan CBO CBOKelamin
ComboBox2 ganti dengan CBO CBOPendidikanIbu
ComboBox3 ganti dengan CBO CBOPendidikanAyah

Hasil akhir dari UserFormnya kira-kira seperti ini

6.       6. Membuat CommandButton

CommandButton adalah tombol yang memberikan instruksi sesuai script yang dibuat.kita dapat membuat commandbutton melalui ToolBox dan menyeretnya ke userform.

Buatlah seperti gambar diatas dan ganti namanya menjadi "TBLSimpan", "TBLCariData", CMDClose".

7.       7. Membuat Script VBA

Nah pada tahap ini kita akan membuat script atau barisan bahasa pemograman yang berisi perintah untuk melakukan sesuatu.

Silahkan buka tampilan scriptnya dengan meng klik kanan pada userform dan klik view code. Akan tampil seperti berikut.

kemudian tuliskan script berikut untuk membuat initial pada UserForm

Private Sub UserForm_Initialize()With CBOKelamin    .AddItem "Laki-Laki"    .AddItem "Perempuan"End WithWith CBOPendidikanIbu    .AddItem "Tidak Sekolah"    .AddItem "SD"    .AddItem "SMP"    .AddItem "SMA"    .AddItem "D1"    .AddItem "D2"    .AddItem "D3"    .AddItem "S1"    .AddItem "S2"    .AddItem "S3"End WithWith CBOPendidikanAyah    .AddItem "Tidak Sekolah"    .AddItem "SD"    .AddItem "SMP"    .AddItem "SMA"    .AddItem "D1"    .AddItem "D2"    .AddItem "D3"    .AddItem "S1"    .AddItem "S2"    .AddItem "S3"End WithEnd Sub
Selanjutnya kita akan membuat sebuah script VBA untuk simpan data siswa silakan ketikkan script VBA nya di bawah iniPrivate Sub TBLSimpan_Click()Dim iRow As LongDim Ws As WorksheetSet Ws = Worksheets("databasesiswa")
'menemukan baris kosong pada database siswaiRow = Ws.Cells(Rows.Count, 1) _  .End(xlUp).Offset(1, 0).Row
'check untuk sebuah nisIf Trim(Me.TXTNis.Value) = "" Then  Me.TXTNis.SetFocus  MsgBox "Masukan NIS terlebih dahulu Kang.."  Exit SubEnd If

'copy data ke database siswaWs.Cells(iRow, 1).Value = Range("X1").ValueWs.Cells(iRow, 2).Value = Me.TXTNis.ValueWs.Cells(iRow, 3).Value = Me.TXTNama.ValueWs.Cells(iRow, 4).Value = Me.TXTTempatLahir.ValueWs.Cells(iRow, 5).Value = Me.TXTTglLahir.ValueWs.Cells(iRow, 6).Value = Me.CBOKelamin.ValueWs.Cells(iRow, 7).Value = Me.TXTAlamat.ValueWs.Cells(iRow, 8).Value = Me.TXTNISN.ValueWs.Cells(iRow, 9).Value = Me.TXTHP.ValueWs.Cells(iRow, 10).Value = Me.TXTSKHUN.ValueWs.Cells(iRow, 11).Value = Me.TXTIjasah.ValueWs.Cells(iRow, 12).Value = Me.TXTNamaIbu.ValueWs.Cells(iRow, 13).Value = Me.TXTThnLahirIbu.ValueWs.Cells(iRow, 14).Value = Me.TXTPekIbu.ValueWs.Cells(iRow, 15).Value = Me.CBOPendidikanIbu.ValueWs.Cells(iRow, 16).Value = Me.TXTNamaAyah.ValueWs.Cells(iRow, 17).Value = Me.TXTThnAyah.ValueWs.Cells(iRow, 18).Value = Me.TXTPekAyah.ValueWs.Cells(iRow, 19).Value = Me.CBOPendidikanAyah.ValueWs.Cells(iRow, 20).Value = Me.TXTPengAyah.ValueWs.Cells(iRow, 21).Value = Me.TXTAlamat.Value
'clear data siswaMe.TXTNis.Value = ""Me.TXTNama.Value = ""Me.TXTTempatLahir.Value = ""Me.TXTTglLahir.Value = ""Me.CBOKelamin.Value = ""Me.TXTAlamat.Value = ""Me.TXTNISN.Value = ""Me.TXTHP.Value = ""Me.TXTSKHUN.Value = ""Me.TXTIjasah.Value = ""Me.TXTNamaIbu.Value = ""Me.TXTThnLahirIbu.Value = ""Me.TXTPekIbu.Value = ""Me.CBOPendidikanIbu.Value = ""Me.TXTNamaAyah.Value = ""Me.TXTThnAyah.Value = ""Me.TXTPekAyah.Value = ""Me.CBOPendidikanAyah.Value = ""Me.TXTPengAyah.Value = ""Me.TXTAlamatOrtu.Value = ""Me.TXTNis.SetFocus
'Simpan dataApplication.ActiveWorkbook.SaveEnd Sub

Membuat perintah CLOSE silakan ketikkan script nya dibawah ini dengan cara double klik tombol CLOSE

Private Sub CMDClose_Click()Unload MeEnd Sub

Membuat sebuah validasi data untuk textbox yang berisi data angka agar data yang diinput hanya berupa data angka buka hurfu. Apabila data yang dimasukan pada textbox bukan berupa data angka maka secara otomatis akan menampilkan pesan error dan ini dia script nya

Private Sub HanyaAngka()    If TypeName(Me.ActiveControl) = "TextBox" Then        With Me.ActiveControl            If Not IsNumeric(.Value) And .Value <> vbNullString Then                MsgBox "Maaf, Masukan data angka saja"                .Value = vbNullString            End If        End With    End IfEnd Sub

Anda bisa menggunakan variasi apabila ketika textbox aktif atau dipilih maka warna akan berubah. Ini dia script nya. Script ini juga akan menghasilkan perintah error yang merupakan pemanggilan prosedur dari tahap  diatas.

Private Sub TXTNISN_Change()HanyaAngkaEnd Sub
Private Sub TXTHP_Change()HanyaAngkaEnd Sub
Private Sub txtnis_Enter()TXTNis.BackColor = &H80000005End SubPrivate Sub txtnis_Exit(ByVal Cancel As MSForms.ReturnBoolean)HanyaAngkaTXTNis.BackColor = &HE0E0E0End Sub
Private Sub txtnama_enter()TXTNama.BackColor = &H80000005End SubPrivate Sub txtnama_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTNama.BackColor = &HE0E0E0End Sub
Private Sub txttempatlahir_enter()TXTTempatLahir.BackColor = &H80000005End SubPrivate Sub txttempatlahir_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTTempatLahir.BackColor = &HE0E0E0End Sub
Private Sub txttgllahir_enter()TXTTglLahir.BackColor = &H80000005End SubPrivate Sub txttgllahir_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTTglLahir.BackColor = &HE0E0E0End Sub
Private Sub txtalamat_Enter()TXTAlamat.BackColor = &H80000005End SubPrivate Sub txtalamat_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTAlamat.BackColor = &HE0E0E0End Sub
Private Sub cbokelamin_Enter()CBOKelamin.BackColor = &H80000005End SubPrivate Sub cbokelamin_Exit(ByVal Cancel As MSForms.ReturnBoolean)CBOKelamin.BackColor = &HE0E0E0End Sub
Private Sub txtnisn_Enter()TXTNISN.BackColor = &H80000005End SubPrivate Sub txtnisn_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTNISN.BackColor = &HE0E0E0End Sub
Private Sub txthp_Enter()TXTHP.BackColor = &H80000005End SubPrivate Sub txthp_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTHP.BackColor = &HE0E0E0End Sub
Private Sub txtskhun_Enter()TXTSKHUN.BackColor = &H80000005End SubPrivate Sub txtskhun_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTSKHUN.BackColor = &HE0E0E0End Sub
Private Sub txtijasah_Enter()TXTIjasah.BackColor = &H80000005End SubPrivate Sub txtijasah_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTIjasah.BackColor = &HE0E0E0End Sub
Private Sub txtnamaibu_Enter()TXTNamaIbu.BackColor = &H80000005End SubPrivate Sub txtnamaibu_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTNamaIbu.BackColor = &HE0E0E0End Sub
Private Sub txtthnlahiribu_Enter()TXTThnLahirIbu.BackColor = &H80000005End SubPrivate Sub txtthnlahiribu_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTThnLahirIbu.BackColor = &HE0E0E0End Sub
Private Sub txtpekibu_Enter()TXTPekIbu.BackColor = &H80000005End SubPrivate Sub txtpekibu_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTPekIbu.BackColor = &HE0E0E0End Sub
Private Sub cbopendidikanibu_Enter()CBOPendidikanIbu.BackColor = &H80000005End SubPrivate Sub cbopendidikanibu_Exit(ByVal Cancel As MSForms.ReturnBoolean)CBOPendidikanIbu.BackColor = &HE0E0E0End Sub
Private Sub txtnamaayah_Enter()TXTNamaAyah.BackColor = &H80000005End SubPrivate Sub txtnamaayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTNamaAyah.BackColor = &HE0E0E0End Sub
Private Sub txtthnayah_Enter()TXTThnAyah.BackColor = &H80000005End SubPrivate Sub txtthnayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTThnAyah.BackColor = &HE0E0E0End Sub
Private Sub txtpekayah_Enter()TXTPekAyah.BackColor = &H80000005End SubPrivate Sub txtpekayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTPekAyah.BackColor = &HE0E0E0End Sub
Private Sub cbopendidikanayah_Enter()CBOPendidikanAyah.BackColor = &H80000005End SubPrivate Sub cbopendidikanayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)CBOPendidikanAyah.BackColor = &HE0E0E0End Sub
Private Sub txtpengayah_Enter()TXTPengAyah.BackColor = &H80000005End SubPrivate Sub txtpengayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTPengAyah.BackColor = &HE0E0E0End Sub
Private Sub txtalamatortu_Enter()TXTAlamatOrtu.BackColor = &H80000005End SubPrivate Sub txtalamatortu_Exit(ByVal Cancel As MSForms.ReturnBoolean)TXTAlamatOrtu.BackColor = &HE0E0E0End Sub

8.       Membuat WorkSheet sebagai penyimpan data

Untuk tahap terakhir silakan buat sebuah Worksheet untuk menyimpan database yang telah disimpan apabila menekan tombol simpan data maka data siswa akan tersimpan pada sheet yang telah ditentukan misalnya saya ganti sheet untuk menyimpan database dengan nama "DatabaseSiswa"

Selesai !!

Sudah selesai tahapan pembuatan Visual Basic Application dengan menggunakan Macro pada MS.Excell

Silahkan klik RUN untuk mencoba menggunaka VBA nya.

Jangan lupa save file Macronya dengan mengganti formatnya menjadi seperti ini

Agar pada saat kita ingin membuka lagi dapat tertera juga macro yang kita buat.

Read more