Program Budgeting System pada Excel menggunakan VBA (Visual Basic)

Kali ini saya akan membahas tetang tutorial pembuatan program yang menggunakan VBA pada Excel. Projek pembuatan Budgeting System ini saya buat untuk memenuhi tugas akhir Praktik Office di STIMIK AKAKOM Yogyakarta.

Langkah awal untuk melakukan projek ini, kita harus menggaktifkan terlebih dahulu tab Developer. Disini saya menggunakan Microsoft Office 2013, caranya klik File - Options - Customize Ribbon - beri tanda centang pada Developer - OK

Setelah selesai mengaktifkan tab Developer mari kita ikuti langkah – langkah membuat form isian data pada Excel menggunakan VBA (Visual Basic).

1.       Buatlah dua sheet seperti dibawah ini. Sheet pertama untuk menginputkan data sedangkan sheet kedua untuk menampilkan data dari hasil inputan.

2.       Membuat button, textbox dan combo box, klik tab Developer - Insert - pilihlah seperti dibawah ini.

 Buatlah seperti dibawah ini pada sheet INPUT_Biaya_Program

3.       Beri nama Variabel pada masing – masing komponen. Klik tab Developer - klik Design Mode - klik Properties - maka akan muncul kotak dialog. Klik pada komponen yang akan diberi inisial.

4.       Langkah selanjutnya adalah memberi code agar dapat digunakan seperti yang kita inginkan. Pada tab Developer, klik View Code maka akan tampil jendala baru VBA seperti dibawah ini.

5.       Memberi Source Code

a.       Membuat method pendukung agar nanti bisa langsung dipanggil.

-          Method tutup

Method ini digunakan untuk menonaktifkan komponen yang dipilih.

Sub tutup()

txtKd_Program.Enabled = False

txtTglMasuk.Enabled = False

txtNamaProgram.Enabled = False

cmbLokasi.Enabled = False

txtHrgPertabel.Enabled = False

txtHrgPermenu.Enabled = False

txtJmlTabel.Enabled = False

txtJmlMenu.Enabled = False

txtTotalTabel.Enabled = False

txtTotalMenu.Enabled = False

txtBiayaLain.Enabled = False

txtGrandTotal.Enabled = False

btnHitung.Enabled = False

btnSimpan.Enabled = False

End Sub

-          Method buka

Method ini digunakan untuk mengaktifkan komponen yang dipilih.

Sub buka()

txtKd_Program.Enabled = False

txtTglMasuk.Enabled = False

txtNamaProgram.Enabled = True

cmbLokasi.Enabled = True

txtHrgPertabel.Enabled = True

txtHrgPermenu.Enabled = True

txtJmlTabel.Enabled = True

txtJmlMenu.Enabled = True

txtTotalTabel.Enabled = False

txtTotalMenu.Enabled = False

txtBiayaLain.Enabled = True

txtGrandTotal.Enabled = True

btnHitung.Enabled = True

btnSimpan.Enabled = True

End Sub

-          Method nomor otomatis

Method ini digunakan untuk memberi nomor otomatis pada Kode Program.

Sub autonum()

Set ws_biayaprogram = Sheets("Biaya_Program")

Dim BarisDatabase As Integer 'memberi variabel dengan tipe data

Dim AmbilNo As Variant

Dim Panjang As Variant

Dim NoUrut As Variant

Dim DataNo As Variant

With ws_biayaprogram

If Sheets("Biaya_Program").Range("A2") = "" Then '--mengecek apakah pd range A2 datanya masih kosong

txtKd_Program.Text = "2200001"

Else

    BarisDatabase = .Cells(.Rows.Count, "A"). _

    End(xlUp).Offset(0, 1).Row '----"A" adalah kolom kuncinya agar dpt next ke row selanjutnya

    AmbilNo = Right(.Cells(BarisDatabase, 1).Value, 5) '—-pengambilan nomor sebanyak 5 digit

    Panjang = AmbilNo + 1 '—--angka 5 digit tersebut ditambah 1

        '—--melakukan pemilihan, jika angka sudah 10, dst. Maka secara otomatis 0 akan hilang satu, dst.

        Select Case Len(Panjang)

        Case 1: NoUrut = "0000" & Panjang

        Case 2: NoUrut = "000" & Panjang

        Case 3: NoUrut = "00" & Panjang

        Case 4: NoUrut = "0" & Panjang

        Case 5: NoUrut = Panjang

        End Select

   

    txtKd_Program.Text = "22" & NoUrut

End If

End With

End Sub

-          Method kosong

Method ini digunakan untuk membersihkan kembali from isian.

Sub kosong()

txtKd_Program.Text = ""

txtTglMasuk.Text = ""

txtNamaProgram.Text = ""

cmbLokasi.Text = ""

txtHrgPertabel.Text = ""

txtHrgPermenu.Text = ""

txtJmlTabel.Text = ""

txtJmlMenu.Text = ""

txtTotalTabel.Text = ""

txtTotalMenu.Text = ""

txtBiayaLain.Text = ""

txtGrandTotal.Text = ""

End Sub

-          Method pesan

Method ini digunakan untuk menampilkan pesan pada action tertentu.

Sub tampilkanPesan(data As String, pesan As String)

If pesan = "ksong" Then

MsgBox data & " Masih Kosong", vbOKOnly

Exit Sub

    ElseIf pesan = "berhasil" Then

    MsgBox data & " Berhasil Disimpan", vbOKOnly

    Exit Sub

End If

End Sub

-          Method tanggal

Method ini digunakan untuk menampilkan tanggal secara otomatis sesuai pada system komputer.

Sub tanggal()

    txtTglMasuk.Text = format(Now(), "DD - MM - YYYY")

End Sub

b.      Memberi kode pada masing – masing komponen.

-          Button Tambah

Pada button tambah saya tambahkan script seperti dibawah ini yang bertujuan jika diklik button TAMBAH maka tulisan akan berupa BATAL kemudian memanggil method autonum, tanggal dan buka, tetapi jika diklik lagi maka tulisan akan berubah TAMBAH kemudian memanggil method tutup dan kosong.

Private Sub btnTambah_Click()

If btnTambah.Caption = "TAMBAH" Then

btnTambah.Caption = "BATAL"

autonum

tanggal

buka

    Else

    btnTambah.Caption = "TAMBAH"

    tutup

    kosong

    Exit Sub

   

End If

End Sub

-          Button Simpan

Private Sub btnSimpan_Click()

Set ws_biayaprogram = Sheets("Biaya_Program")

Dim BarisDatabase As Integer 'memberi variabel dengan tipe data

'---pemeriksaan bahwa file sudah diinput

If txtNamaProgram.Text = "" Then

Call tampilkanPesan("Nama Program", "ksong")'----memanggil method pesan

Exit Sub

   

    ElseIf txtGrandTotal.Text = "" Then

    Call tampilkanPesan("Grand Total", "ksong")'----memanggil method pesan

    Exit Sub

End If

'--script untuk menginputkan data kedalam database

With ws_biayaprogram

BarisDatabase = .Cells(.Rows.Count, "A"). _

End(xlUp).Offset(0, 1).Row '----"A" adalah kolom kuncinya agar dpt next ke row selanjutnya

.Cells(BarisDatabase + 1, 1).Value = txtKd_Program.Text

.Cells(BarisDatabase + 1, 2).Value = txtTglMasuk.Text

.Cells(BarisDatabase + 1, 3).Value = txtNamaProgram.Text

.Cells(BarisDatabase + 1, 4).Value = cmbLokasi.Text

.Cells(BarisDatabase + 1, 5).Value = txtHrgPertabel.Text

.Cells(BarisDatabase + 1, 6).Value = txtHrgPermenu.Text

.Cells(BarisDatabase + 1, 7).Value = txtJmlTabel.Text

.Cells(BarisDatabase + 1, 8).Value = txtJmlMenu.Text

.Cells(BarisDatabase + 1, 9).Value = txtTotalTabel.Text

.Cells(BarisDatabase + 1, 10).Value = txtTotalMenu.Text

.Cells(BarisDatabase + 1, 11).Value = txtBiayaLain.Text

.Cells(BarisDatabase + 1, 12).Value = txtGrandTotal.Text

'----memanggil method pesan

Call tampilkanPesan("Data", "berhasil")

'---mengosongkan & menonaktifkan data setelah menginput dg memanggil method yg tlah dibuat

Call kosong

Call tutup

btnTambah.Caption = "TAMBAH"

End With

End Sub

-          Button Lihat

Private Sub btnLihat_Click()

Sheets("Biaya_Program").Select '---lngsung menuju pada Sheet Biaya_Program

End Sub

-          Button Keluar

Disini saya membuat button keluar menuju ke halaman depan.

Private Sub btnKeluar_Click()

Sheets("HOME").Select

End Sub

-          Button Hitung

Private Sub btnHitung_Click()

txtGrandTotal.Text = Val(txtTotalTabel) + Val(txtTotalMenu) + Val(txtBiayaLain)

'---memberi format angka ketika tampil

txtGrandTotal = format(txtGrandTotal * 1, "#,##0")

End Sub

-          Textbox  Nama Program

Memberi validasi agar Value yang dimasukkan berhuruf Kapital.

Private Sub txtNamaProgram_Change()

txtNamaProgram = UCase(txtNamaProgram)

End Sub

-          Textbox Harga Per-Tabel

Memberi validasi agar Value yang dimasukkan harus berupa angka.

Private Sub txtHrgPertabel_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii

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

Case Else

KeyAscii = 0

End Select

End Sub

Berikan juga Source Code diatas pada :

·         Textbox Jumlah Menu

·         Textbox Jumlah Tabel

·         Textbox Harga Per-Menu

·         Textbox Biaya Lainnya

-          Textbox Total Jumlah Tabel

Private Sub txtJmlTabel_Change()

txtTotalTabel.Text = Val(txtHrgPertabel) * Val(txtJmlTabel)

End Sub

-          Textbox Total Jumlah Menu

Private Sub txtJmlMenu_Change()

txtTotalMenu.Text = Val(txtHrgPermenu) * Val(txtJmlMenu)

End Sub

 

-          Combo box Lokasi

                   Membuat List Combo Box sangatlah mudah, anda hanya perlu membuat list lokasi pada
                   worksheet kemudian klik Developer - klik Design Mode - Properties - klik combo box -
                   pada menu properties pilih ListFillRange - masukkan range tempat anda menaruh daftar
                   list untuk dijadikan data Combo Box.

6.       Setelah form inputan selesai mari kita buat sheet untuk menampilkan data sheet Biaya_Program

Disini kita hanya membaut tabelnya saja dan beberapa button untuk action tertentu. Berikut contoh tampilannya yang telah saya buat.

Demikian tutorial pembuatan Budgeting System ini, anda biasa menambahkan sesuai dengan keinginan dan hasil kreasi anda sendiri.

Berikut saya tampilkan Screenshoot  dan beberapa Source Code Budgeting System pekerjaan saya :

1.       Menu Login

Private Sub btnExit_Click()

pesan = MsgBox("Yakin Kagak Bos Keluarnya", vbInformation + vbOKCancel, "PERHATIAN !!!")

If pesan = vbCancel Then

MsgBox "Kan Kagak Yakin"

Exit Sub

ElseIf pesan = vbOK Then

MsgBox "Naaaah Yakin Orangnya"

ActiveWorkbook.Save

ActiveWorkbook.Close

End If

End Sub

Private Sub btnLogin_Click()

If Me.txtUser.Text = Range("A1") And Me.txtPass.Text = Range("B1") Then

        kosong

        Sheets("HOME").Select

        Exit Sub

        Else

         MsgBox "AKSES DITOLAK !!!", vbCritical + vbOKOnly, "Error Password"

    kosong

    Exit Sub

    End If

MsgBox "Periksa Kembali Username dan Password", vbCritical + vbOKOnly, "Warning !!!"

kosong

End Sub

Sub kosong()

txtUser.Text = ""

txtPass.Text = ""

End Sub

2.  Menu Home

Private Sub btnAnggaran_Click()

Sheets("INPUT_Budgeting").Select

End Sub

Private Sub btnBiayaProgram_Click()

Sheets("INPUT_Biaya_Program").Select

End Sub

Private Sub btnGrafik_Click()

Sheets("Grafik_Budget").Select

End Sub

Private Sub btnInputKaryawan_Click()

Sheets("INPUT_Data_Karyawan").Select

End Sub

Private Sub btnKeluar_Click()

Sheets("LOGIN").Select

End

End Sub

Private Sub btnPengguna_Click()

Sheets("PENGGUNA").Select

End Sub

'--DATE TIME

Sub datetime()

Dim running

running = Not running

    Do While running = True

    DoEvents

    labTanggal.Caption = format(Now(), "DD - MM - YYYY")

    labJam.Caption = format(Now(), "HH:MM:SS")

    Loop

End Sub

Private Sub Worksheet_Activate()

datetime

Unload Me

End Sub

3.       Menu Pengguna

Private Sub btnKeluar_Click()

Sheets("HOME").Select

End Sub

Private Sub btnSimpan_Click()

Set ws_pengguna = Sheets("PENGGUNA")

Dim BarisDatabase As Integer

    If txtUser.Text = "" Then

    Call tampilkanPesan("User", "ksong")

    Exit Sub

   

    ElseIf txtPass.Text = "" Then

    Call tampilkanPesan("Password", "ksong")

    Exit Sub

    ElseIf txtNamaPengguna.Text = "" Then

    Call tampilkanPesan("Nama Pengguna", "ksong")

    Exit Sub

End If

'--script untuk menginputkan data kedalam database

With ws_pengguna

BarisDatabase = .Cells(.Rows.Count, "B"). _

End(xlUp).Offset(0, 1).Row '----"A" adalah kolom kuncinya agar dpt next ke row selanjutnya

.Cells(BarisDatabase, 2).Value = txtNamaPengguna.Text

.Cells(BarisDatabase, 3).Value = txtUser.Text

.Cells(BarisDatabase, 4).Value = txtPass.Text

Call tampilkanPesan("Data", "berhasil")

'---mengosongkan data setelah menginput

kosong

End With

End Sub

Sub kosong()

txtUser.Text = ""

txtPass.Text = ""

txtNamaPengguna = ""

End Sub

Sub tampilkanPesan(data As String, pesan As String)

If pesan = "ksong" Then

MsgBox data & " Masih Kosong", vbOKOnly

Exit Sub

    ElseIf pesan = "berhasil" Then

    MsgBox data & " Berhasil Disimpan", vbOKOnly

    Exit Sub

End If

End Sub

'---validasi KAPITAL

Private Sub txtNamaPengguna_Change()

txtNamaPengguna = UCase(txtNamaPengguna)

End Sub

4.       Menu Input Anggaran Program

5.       Menu menampilkan data Anggaran Program

6.       Menu Input Data Karyawan

7.       Menu menampilkan data Karyawan

8.       Menu Grafik Per Bulan dan banyak orderan pada masing-masing daerah

Semoga bermanfaat...............