Cara Cepat Membuat Keterangan Pinjaman di Excel dengan Menggunakan Macro VBA

Dalam dunia keuangan, mengelola catatan dan membuat keterangan pinjaman di Excel bisa menjadi tugas yang memakan waktu. Belum lagi untuk membuat catatan bahwa si Fulan angsurannya lancar atau ada tunggakan untuk bulan-bulan tertentu. Tetapi, dengan bantuan Macro VBA (Visual Basic for Applications) di Microsoft Excel, Anda dapat mengotomatiskan proses pembuatan keterangan pinjaman seperti di atas.

Artikel ini akan membahas langkah-langkah membuat keterangan pinjaman dengan mudah menggunakan Macro VBA.

Sebagai contoh, kita akan membuat riwayat angsuran pinjaman dari Patrick Star. Patrick Star, sahabat akrab SpongeBob SquarePants, memang terkenal dengan tingkah kocaknya. Namun, seperti manusia pada umumnya, Patrick juga bisa mengalami kendala keuangan.

Bagi Anda yang bekerja di departemen keuangan Bikini Bottom dan perlu membuat keterangan pinjaman di Excel untuk Patrick yang terlambat bayar pada bulan Juni 2023 dan September 2023. Berikut contoh tabelnya:

Menghitung Denda untuk Tunggakan Pinjaman

Sebelum menghitung denda, tentukan kondisi yang menunjukkan bahwa pinjaman sudah terlambat. Biasanya, hal ini melibatkan perbandingan antara tanggal pembayaran dan tanggal jatuh tempo. Dalam contoh kita, kita akan menggunakan rumus Excel berikut di sel E7:

=IF(D7>C7;100000;0)

Copy dan paste formula tersebut ke sel di bawahnya. Dan hasilnya adalah sebagai berikut:

Penjelasan Rumus

Formula membuat keterangan pinjaman di Excel ini digunakan untuk menghitung denda pinjaman berdasarkan kondisi tertentu di Microsoft Excel. Mari kita bahas deskripsi dari formula ini:

1. IF (Percabangan):

• Fungsi IF (If Statement) digunakan untuk membuat suatu keputusan berdasarkan kondisi tertentu. Dalam konteks ini, formula akan mengevaluasi apakah nilai dalam sel D7 (Tanggal Pembayaran) lebih besar dari nilai dalam sel C7 (Tanggal Jatuh Tempo).

2. D7 > C7 (Kondisi):

• Kondisi ini mengecek apakah tanggal pembayaran (D7) lebih besar dari tanggal jatuh tempo (C7), yang menunjukkan bahwa pembayaran telah terlambat.

3. 100000 (Nilai Jika Benar):

• Jika kondisinya benar (tanggal pembayaran lebih besar dari tanggal jatuh tempo), maka formula akan menghasilkan nilai denda sebesar 100000. Ini adalah jumlah denda yang diberlakukan ketika pinjaman terlambat.

4. 0 (Nilai Jika Salah):

• Jika kondisinya salah (tanggal pembayaran tidak lebih besar dari tanggal jatuh tempo), maka formula akan menghasilkan nilai 0, menunjukkan bahwa tidak ada denda yang dikenakan.

Jadi, kesimpulannya formula untuk membuat keterangan pinjaman di Excel ini memberikan keputusan berdasarkan apakah pinjaman terlambat dibayarkan atau tidak. Jika terlambat, maka nilai denda sebesar 100.000 akan muncul; jika tidak terlambat, nilai denda akan menjadi 0. Formula ini sangat berguna dalam menghitung denda secara otomatis berdasarkan tanggal pembayaran dan tanggal jatuh tempo, membantu efisiensi dan ketelitian dalam pengelolaan tunggakan pinjaman.

Membuat Keterangan Pinjaman di Excel untuk Angsuran

Untuk membuat keterangan pinjaman di Excel apakah yang bersangkutan menunggak atau tidak maka langkah pertama yang harus dipastikan adalah tab Developer sudah diaktifkan. Caranya:

1. Buka Excel dan pilih “File” > “Options”.
2. Pilih “Customize Ribbon” di jendela Excel Options.
3. Pilih kotak centang “Developer” dan klik “OK”.

Jika sudah aktif, di tab Developer, klik “Visual Basic” untuk membuka Editor VBA. Di dalam Editor VBA, Anda dapat membuat dan mengelola kode VBA untuk otomatisasi tugas di Excel.

Disini kita akan membuat modul. Caranya klik: Visual Basic >> Insert >> Module

Tulis Kode VBA untuk Keterangan Pinjaman

Berikut adalah kode VBA membuat keterangan pinjaman di Excel untuk Patrick Star yang terlambat bayar di bulan Juni 2023 dan September 2023:

Option Explicit

Public Function tunggakan(rgDeadLine As Range) As String
    Dim rgCell As Range
    Dim msg As String
    
    For Each rgCell In rgDeadLine
        With rgCell
            If .Offset(, 1).Value > .Value Then
                If Len(msg) > 0 Then
                    msg = msg & ", " & Format(.Value, "mmm yyyy;@")
                    Else
                    msg = Format(.Value, "mmm yyyy;@")
                End If
            End If
        End With
    Next rgCell
    If Len(msg) > 8 Then
        msg = Left(msg, Len(msg) - 8) & " dan " & Right(msg, 8)
    End If
    If Len(msg) > 0 Then
        msg = " terlambat bayar di bulan " & msg
        Else
        msg = " Bayar Tepat Waktu"
    End If
    tunggakan = msg
End Function

Berikut adalah penampakan dari kode untuk membuat keterangan pinjaman di Excel:

Penjelasan Kode VBA

Kode VBA ini adalah sebuah fungsi (function) dalam bahasa pemrograman VBA (Visual Basic for Applications) yang digunakan dalam Microsoft Excel. Fungsi ini dinamakan tunggakan dan menerima satu argumen, yaitu rgDeadLine, yang merupakan rentang (range) sel dalam Excel yang berisi tanggal-tanggal jatuh tempo pembayaran. Fungsi ini dirancang untuk mengembalikan string yang menyatakan apakah terdapat tunggakan pembayaran pada bulan-bulan tertentu.

Berikut adalah penjelasan langkah demi langkah:

1. Option Explicit:

• Ini adalah pernyataan opsional yang diletakkan di awal modul VBA. Ini memaksa deklarasi variabel sebelum digunakan, membantu mencegah kesalahan penulisan variabel.

2. Public Function tunggakan(rgDeadLine As Range) As String:

• Mendefinisikan fungsi tunggakan dengan satu parameter yaitu rgDeadLine yang bertipe Range (rentang sel dalam Excel) dan mengembalikan nilai bertipe String.

3. Dim rgCell As Range, msg As String:

• Mendeklarasikan dua variabel: rgCell yang digunakan sebagai variabel penampung untuk setiap sel dalam rgDeadLine, dan msg yang digunakan untuk menyimpan pesan tentang bulan-bulan yang memiliki tunggakan.

4. For Each rgCell In rgDeadLine:

• Memulai loop untuk setiap sel dalam rgDeadLine.

5. With rgCell:

• Membuka blok “With” yang mempersingkat penulisan kode dengan mengacu pada rgCell sebagai ..

6. If .Offset(, 1).Value > .Value Then:

• Menggunakan kondisi untuk mengecek apakah nilai sel berikutnya (Offset(, 1)) lebih besar dari nilai sel saat ini (Value). Ini menunjukkan apakah pembayaran terlambat.

7. If Len(msg) > 0 Then:

• Mengecek apakah pesan (msg) sudah memiliki isi sebelumnya.

8. msg = msg & “, ” & Format(.Value, “mmm yyyy;@”):

• Jika sudah ada pesan sebelumnya, menambahkan informasi bulan yang terlambat ke dalam pesan dengan format “mmm yyyy;@”.

9. msg = Format(.Value, “mmm yyyy;@”):

• Jika belum ada pesan sebelumnya, membuat pesan baru dengan format “mmm yyyy;@”.

10. Next rgCell:

• Menutup loop, bergerak ke sel berikutnya dalam rgDeadLine.

11. If Len(msg) > 8 Then:

• Mengecek apakah panjang pesan sudah lebih dari 8 karakter.

12. msg = Left(msg, Len(msg) – 8) & ” dan ” & Right(msg, 8):

• Jika panjang pesan lebih dari 8 karakter, menghapus 8 karakter terakhir (bulan yang terlambat terakhir) dan menambahkan kata “dan” serta bulan terakhir.

13. If Len(msg) > 0 Then:

• Mengecek apakah pesan memiliki isi.

14. msg = ” terlambat bayar di bulan ” & msg:

• Jika pesan memiliki isi, menambahkan teks “terlambat bayar di bulan” ke pesan.

15. Else:

• Jika pesan tidak memiliki isi, berarti tidak ada tunggakan.

16. msg = ” Bayar Tepat Waktu”:

• Menetapkan pesan “Bayar Tepat Waktu” jika tidak ada tunggakan.

17. tunggakan = msg:

• Mengembalikan pesan sebagai hasil fungsi.

Fungsi ini dapat digunakan di Excel dengan cara menulis formulanya. Dan hasilnya akan menunjukkan apakah pembayaran dilakukan tepat waktu atau terlambat dan bulan-bulan yang terlambat.

Jalankan Macro VBA

Simpan dan tutup Editor VBA. Kembali ke worksheet Excel. Kemudian ketik fungsinya di sel F7. Fungsi ini dapat digunakan di Excel dengan cara menulis formulanya. Dan formula yang bisa digunakan Hasilnya akan menunjukkan apakah pembayaran dilakukan tepat waktu atau terlambat dan bulan-bulan yang terlambat. Dan berikut adalah fungsinya:

=C2&tunggakan(C7:C18)

Dan berikut adalah hasil jadinya:

Macro akan secara otomatis menambahkan keterangan “Terlambat Bayar di bulan Jun 2023, dan Sep 2023” pada baris yang memenuhi kondisi tertentu (tanggal jatuh tempo telah lewat).

Penutup

Dengan menggunakan Macro VBA, Anda dapat menghemat waktu dan menghindari kesalahan manusiawi dalam membuat keterangan pinjaman di Excel untuk Patrick Star yang terlambat bayar. Semoga artikel ini bermanfaat!

Terakhir, bagi yang membutuhkan contoh file membuat keterangan pinjaman di Excel dengan VBA ini bisa didownload melalui tautan berikut: