Materi Perkuliahan

Membuat Daftar Pembelajaan VBA Excel

Dipublikasikan pada : 29 Januari 2020.

Private Sub CommandButton1_Click()
Dim lembar As Worksheet
Set lembar = Worksheets(2)
lembar.Range(“b2”) = “No”
lembar.Range(“b2”).Font.Name = “arial”
lembar.Range(“b2”).HorizontalAlignment = xlCenter
lembar.Range(“b2”).VerticalAlignment = xlCenter
lembar.Range(“b2”).ColumnWidth = 5
lembar.Range(“b2”).Interior.Color = vbBlue
lembar.Range(“b2”).Borders.Color = vbBlack
lembar.Range(“b2”).Font.Color = vbWhite

lembar.Range(“c2”) = “Tanggal”
lembar.Range(“c2”).Font.Name = “arial”
lembar.Range(“c2”).HorizontalAlignment = xlCenter
lembar.Range(“c2”).VerticalAlignment = xlCenter
lembar.Range(“c2”).ColumnWidth = 18
lembar.Range(“c2”).Interior.Color = vbBlue
lembar.Range(“c2”).Borders.Color = vbBlack
lembar.Range(“c2”).Font.Color = vbWhite

lembar.Range(“d2”) = “Keperluan”
lembar.Range(“d2”).Font.Name = “arial”
lembar.Range(“d2”).HorizontalAlignment = xlCenter
lembar.Range(“d2”).VerticalAlignment = xlCenter
lembar.Range(“d2”).ColumnWidth = 32
lembar.Range(“d2”).Interior.Color = vbBlue
lembar.Range(“d2”).Borders.Color = vbBlack
lembar.Range(“d2”).Font.Color = vbWhite

lembar.Range(“e2”) = “Debit”
lembar.Range(“e2”).Font.Name = “arial”
lembar.Range(“e2”).HorizontalAlignment = xlCenter
lembar.Range(“e2”).VerticalAlignment = xlCenter
lembar.Range(“e2”).ColumnWidth = 10
lembar.Range(“e2”).Interior.Color = vbBlue
lembar.Range(“e2”).Borders.Color = vbBlack
lembar.Range(“e2”).Font.Color = vbWhite

lembar.Range(“f2”) = “Kredit”
lembar.Range(“f2”).Font.Name = “arial”
lembar.Range(“f2”).HorizontalAlignment = xlCenter
lembar.Range(“f2”).VerticalAlignment = xlCenter
lembar.Range(“f2”).ColumnWidth = 10
lembar.Range(“f2”).Interior.Color = vbBlue
lembar.Range(“f2”).Borders.Color = vbBlack
lembar.Range(“f2”).Font.Color = vbWhite

lembar.Range(“g2”) = “Jumlah”
lembar.Range(“g2”).Font.Name = “arial”
lembar.Range(“g2”).HorizontalAlignment = xlCenter
lembar.Range(“g2”).VerticalAlignment = xlCenter
lembar.Range(“g2”).ColumnWidth = 10
lembar.Range(“g2”).Interior.Color = vbBlue
lembar.Range(“g2”).Borders.Color = vbBlack
lembar.Range(“g2”).Font.Color = vbWhite

Dim alamat As Range
Dim nilai As Integer
nilai = lembar.Cells(lembar.Rows.Count, “b”).End(xlUp).Row
lembar.Range(“c” & nilai + 1) = Range(“a1”)
lembar.Range(“c” & nilai + 1).HorizontalAlignment = xlCenter
lembar.Range(“c” & nilai + 1).VerticalAlignment = xlCenter

lembar.Range(“d” & nilai + 1) = Range(“b12”)
lembar.Range(“d” & nilai + 1).HorizontalAlignment = xlCenter
lembar.Range(“d” & nilai + 1).VerticalAlignment = xlCenter

If Range(“b9”) = “pemasukan” Then
lembar.Range(“e” & nilai + 1) = Range(“d9”)
lembar.Range(“e” & nilai + 1).HorizontalAlignment = xlCenter
lembar.Range(“e” & nilai + 1).VerticalAlignment = xlCenter
ElseIf Range(“b9”) = “pengeluaran” Then
lembar.Range(“f” & nilai + 1) = Range(“d9”)
lembar.Range(“f” & nilai + 1).HorizontalAlignment = xlCenter
lembar.Range(“f” & nilai + 1).VerticalAlignment = xlCenter
End If

lembar.Range(“b” & nilai + 1) = nilai – 1
lembar.Range(“b” & nilai + 1).HorizontalAlignment = xlCenter
lembar.Range(“b” & nilai + 1).VerticalAlignment = xlCenter

lembar.Range(“g” & nilai + 1).Font.Color = vbBlack
lembar.Range(“g” & nilai + 1).HorizontalAlignment = xlCenter
lembar.Range(“g” & nilai + 1).VerticalAlignment = xlCenter

End Sub

id_IDIndonesian