Kumpulan Script Vba Excel

Kumpulan Script Vba Excel – 20 Script VBA Excel Terbaik untuk Mengoptimalkan Pekerjaan Anda

oleh

Kumpulan Script Vba Excel berikut akan sangat membantu pekerjaan anda. VBA (Visual Basic for Applications) sendiri adalah bahasa pemrograman yang dapat digunakan untuk memanipulasi Microsoft Excel. Menggunakan VBA, Anda dapat menciptakan script yang membantu mengoptimalkan pekerjaan Anda dan mengurangi waktu dan tenaga yang Anda butuhkan untuk melakukan tugas-tugas yang berulang.

Dalam artikel ini, kami akan membahas 20 script VBA Excel terbaik yang dapat membantu Anda menghemat waktu dan membuat pekerjaan Anda lebih efisien. Dari menghitung jumlah baris pada tabel hingga menyimpan berkas secara otomatis setiap mengubah data, script-script ini akan membantu Anda mengatasi berbagai masalah dan menyelesaikan tugas-tugas dengan cepat dan mudah.

Cara Penggunaan

Sebelum menggunakan kumpulan coding vba excel di bawah ini, berikut adalah cara penggunaannya:

1. Buka Ms Excel , buka file baru dan save as sebagai file excel yang dapat menampung kode vba.

2. Buka Ms Visual Basic For Aplication dengan ALT + F11

3. Buat module baru. Caranya Insert -> Module

4. Kopikan kode yang ada di bawah

5. Jalankan dengan menggunakan tombol play warna hijau

Baiklah, tanpa berlama-lama lagi, mari kita mulai membahas 20 Kumpulan Script Vba Excel terbaik untuk mengoptimalkan pekerjaan Anda!

1. Menghitung Jumlah Baris Tabel:

Sub CountRows()
  Dim LastRow As Long
  LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
  MsgBox "Jumlah baris pada tabel ini adalah: " & LastRow
End Sub

Penjelasan:

  • Dim LastRow As Long:
    Script vba ini mendefinisikan variabel bernama LastRow yang tipe datanya Long (bilangan bulat 32-bit).
  • LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, “A”).End(xlUp).Row:
    Berguna untuk mendapatkan nomor baris terakhir dari kolom A.
  • MsgBox “Jumlah baris pada tabel ini adalah: ” & LastRow:
    Dengan baris kode ini akan tampil pesan box yang menampilkan jumlah baris pada tabel

Berikut hasilnya setelah di eksekusi

Kode VBA Untuk Menghitung Jumlah Baris Tabel - Kumpulan script VBA Excel

2. Memformat Sel yang Terpilih Menjadi Merah:

Sub FormatSelection()
  Selection.Interior.Color = RGB(255, 0, 0)
End Sub

Penjelasan:

  • Selection.Interior.Color = RGB(255, 0, 0): Menentukan warna latar belakang sel yang terpilih dengan warna merah (RGB 255, 0, 0).

Penggunaan :

Pilih sell atau range, dan jalankan kode vba nya. Maka hasilnya seluruh cell atau range akan berwarna merah

3. Mencari Nilai Maksimal dalam Kolom

Sub FindMaxValue()
  Dim MaxValue As Double
  MaxValue = Application.WorksheetFunction.Max(Columns(1))
  MsgBox "Nilai maksimal dalam kolom A adalah: " & MaxValue
End Sub

Penjelasan:

  • Dim MaxValue As Double: Mendefinisikan variabel bernama MaxValue yang tipe datanya Double (bilangan pecahan 64-bit).
  • MaxValue = Application.WorksheetFunction.Max(Columns(1)): Mendapatkan nilai maksimal dari kolom A.
  • MsgBox “Nilai maksimal dalam kolom A adalah: ” & MaxValue: Menampilkan nilai maksimal dalam kolom A dengan menggunakan kotak pesan (message box).

Berikut hasilnya setelah di eksekusi

4. Menghapus Baris Kosong

Sub DeleteEmptyRows()
    Dim lastRow As Long
    Dim i As Long
    
    lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    
    For i = lastRow To 1 Step -1
        If WorksheetFunction.CountA(Rows(i)) = 0 Then
            Rows(i).Delete
        End If
    Next i
End Sub

Penjelasan baris per baris:

  1. Sub DeleteEmptyRows(): Ini adalah sub-rutin yang akan menghapus baris kosong di lembar kerja.
  2. Dim lastRow As Long: Mendeklarasikan variabel lastRow sebagai tipe data Long. Variabel ini akan digunakan untuk menyimpan nomor baris terakhir di lembar kerja.
  3. Dim i As Long: Mendeklarasikan variabel i sebagai tipe data Long. Variabel ini akan digunakan sebagai perulangan untuk memeriksa setiap baris.
  4. lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row: Menggunakan metode SpecialCells dan properti xlCellTypeLastCell untuk mendapatkan nomor baris terakhir di lembar kerja. Nilai ini akan disimpan dalam variabel lastRow.
  5. For i = lastRow To 1 Step -1: Memulai perulangan dari nomor baris terakhir (lastRow) ke 1 dengan langkah mundur -1.
  6. If WorksheetFunction.CountA(Rows(i)) = 0 Then: Menggunakan fungsi CountA dari objek WorksheetFunction untuk menghitung jumlah sel non-kosong di baris i. Jika hasilnya adalah 0, artinya baris tersebut kosong.
  7. Rows(i).Delete: Jika baris kosong ditemukan, baris tersebut dihapus menggunakan metode Delete dari objek Rows.
  8. Next i: Melanjutkan ke baris berikutnya dalam perulangan.

Setelah kode dijalankan, semua baris kosong di lembar kerja akan dihapus. Berikut videonya

5. Code untuk menghapus sheet

Sub DeleteSheet()
  Application.DisplayAlerts = False
  Sheets("Sheet1").Delete
  Application.DisplayAlerts = True
End Sub

Penjelasan:

  • Code ini menghapus sheet dengan nama “Sheet1”.
  • Application.DisplayAlerts digunakan untuk mematikan prompt konfirmasi ketika menghapus sheet.
  • Sheets("Sheet1").Delete digunakan untuk menghapus sheet “Sheet1”.
  • Setelah selesai menghapus sheet, Application.DisplayAlerts kembali diaktifkan untuk menampilkan prompt konfirmasi ketika melakukan aksi lain.

6. Code untuk menyortir data pada tabel

Sub SortData()
'Memilih tabel yang akan disortir
Range("A1:D10").Select
'Mengsortir data pada tabel
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A10"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:D10")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Penjelasan:

  • Pertama, subrutin “SortData” didefinisikan.
  • Kemudian, tabel yang akan disortir dipilih menggunakan perintah “Range(“A1:D10″).Select”.
  • Setelah itu, data dalam tabel disortir menggunakan perintah “ActiveWorkbook.Worksheets(“Sheet1″).Sort.SortFields.Add”. Dalam perintah ini, kolom yang akan disortir (kolom A dari baris 2 hingga 10) ditentukan dan diatur untuk diurutkan dari A hingga Z.
  • Setelah itu, opsi-opsi tambahan seperti header, sort method, dan orientasi ditentukan dengan memasukkan perintah dalam blok “With ActiveWorkbook.Worksheets(“Sheet1″).Sort”.
  • Terakhir, data dalam tabel di-apply dan disortir menggunakan perintah “.Apply”.

7. Script VBA Excel untuk memfilter data berdasarkan kriteria

Sub FilterData()
Dim LastRow As Long
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

ActiveSheet.Range("A1:D" & LastRow).AutoFilter Field:=3, Criteria1:="Penjualan"

MsgBox "Data berdasarkan kriteria 'Penjualan' sudah terfilter."
End Sub

Penjelasan:

  1. Dim LastRow As Long: Mendeklarasikan variabel LastRow sebagai Long (integer).
  2. LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, “A”).End(xlUp).Row: Mendapatkan baris terakhir dari tabel menggunakan metode End(xlUp).
  3. ActiveSheet.Range(“A1:D” & LastRow).AutoFilter Field:=3, Criteria1:=”Penjualan”: Menyaring data pada tabel menggunakan AutoFilter, dengan kriteria pada kolom ke-3 (field 3) adalah “Penjualan”.
  4. MsgBox “Data berdasarkan kriteria ‘Penjualan’ sudah terfilter.”: Menampilkan pesan konfirmasi bahwa data sudah terfilter berdasarkan kriteria.

8. Script VBA Excel Memformat Tabel Secara Otomatis dan penjelasannya

Sub AutoFormatTable()
  Dim rng As Range
  Set rng = Selection
  
  rng.Select
  rng.AutoFormat Format:=xlTable1, NumberFormat:="@"
  
End Sub

Penjelasan:

  • Baris pertama mendeklarasikan variabel rng sebagai tipe Range.
  • Baris ke-2 memasukkan pilihan yang saat ini dipilih oleh pengguna ke dalam variabel rng.
  • Baris ke-3 memilih area yang dipilih oleh variabel rng.
  • Baris ke-4 menggunakan metode AutoFormat untuk memformat area yang dipilih sebagai tabel menggunakan format xlTable1 dan format angka menjadi tipe "@" (sebagai teks).
  • Baris terakhir menandakan akhir dari prosedur.

Dengan kode ini, setiap kali dipanggil, akan memformat area seleksi menjadi tabel dengan format tabel standar pertama.

9. Script VBA Excel untuk membuat grafik berdasarkan data

Sub CreateChart()
  
  'Deklarasi variabel
  Dim MyChart As Chart
  Dim ws As Worksheet
  Dim ChartData As Range
  
  'Set worksheet
  Set ws = ThisWorkbook.Sheets("Sheet1")
  
  'Set range data yang akan digunakan sebagai sumber data grafik
  Set ChartData = ws.Range("A1:B10")
  
  'Menambahkan grafik bar
  Set MyChart = ws.Shapes.AddChart2(251, xlBarClustered, ChartData).Chart
  
  'Menambahkan judul grafik
  MyChart.HasTitle = True
  MyChart.ChartTitle.Text = "Grafik Penjualan"
  
  'Menampilkan garis horizontal pada sumbu Y
  MyChart.Axes(xlValue).HasMajorGridlines = True

End Sub

Penjelasan:

  1. Pertama-tama, kode ini menentukan variabel-variabel yang akan digunakan, yaitu MyChart sebagai variabel grafik, ws sebagai variabel worksheet, dan ChartData sebagai variabel range data yang akan digunakan sebagai sumber data grafik.
  2. Kode kemudian menentukan worksheet yang akan digunakan, yaitu “Sheet1”.
  3. Langkah selanjutnya adalah menentukan range data yang akan digunakan sebagai sumber data grafik. Dalam hal ini, range data diambil dari cell A1 sampai B10.
  4. Kode kemudian menambahkan grafik bar dengan menggunakan Shapes.AddChart2 method dan memasukkan range data sebagai parameter.
  5. Selanjutnya, kode menambahkan judul grafik dengan menentukan bahwa grafik memiliki judul dan memasukkan teks “Grafik Penjualan” sebagai judul tersebut.
  6. Terakhir, kode menampilkan garis horizontal pada sumbu Y dengan menentukan bahwa Axes(xlValue) memiliki garis grid utama.

Dengan menjalankan kode ini, maka Anda akan memiliki grafik bar yang terhubung dengan data pada tabel Anda dan dapat memberikan visualisasi yang jelas tentang data tersebut. Masih kurang? yuk lanjut ke kumpulan Script Vba Excel berikutnya

10. Script VBA Excel untuk menyimpan berkas secara otomatis setiap ada perubahan data pada sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveWorkbook.Save
End Sub

Penjelasan:

  • Private Sub Worksheet_Change(ByVal Target As Range) adalah event handler yang dipanggil setiap ada perubahan data pada sheet.
  • ActiveWorkbook.Save akan menyimpan berkas secara otomatis setiap ada perubahan data pada sheet.

10 Kumpulan Script Vba Excel berikut juga sering digunakan dalam pekerjaan sehari-hari. Saya akan beri penjelasan apa maksud dari hasilnya, namun tidak lagi baris per baris, agar anda latihan.

11. Menghitung Jumlah Sel yang Dipilih

Contoh Kode:

Sub HitungJumlahSelDipilih()
    MsgBox "Jumlah sel yang dipilih: " & Selection.Count
End Sub

Script vba ini akan menghitung jumlah sel yang dipilih pada lembar kerja aktif dan menampilkan hasilnya dalam sebuah kotak pesan.

12. Menghitung Jumlah Baris dan Kolom pada Lembar Kerja

Contoh:

Sub HitungJumlahBarisKolom()
    MsgBox "Jumlah baris: " & ActiveSheet.UsedRange.Rows.Count & vbNewLine & _
           "Jumlah kolom: " & ActiveSheet.UsedRange.Columns.Count
End Sub

Script ini akan menghitung jumlah baris dan kolom yang digunakan pada lembar kerja aktif dan menampilkan hasilnya dalam sebuah kotak pesan.

13. Menyalin Isi Sel ke Sel Lain

Contoh:

Sub SalinSel()
    Selection.Copy Destination:=Range("A1")
End Sub

Script ini akan menyalin isi sel yang dipilih dan menempelkannya pada sel A1 pada lembar kerja aktif.

14. Membuat Daftar Drop-down

Contoh:

Sub BuatDropDown()
    Dim ws As Worksheet
    Dim rng As Range
    
    Set ws = ActiveSheet
    Set rng = ws.Range("A1")
    
    With rng.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Merah,Hijau,Biru"
    End With
End Sub

Hasil: Script ini akan membuat daftar drop-down pada sel A1 pada lembar kerja aktif yang terdiri dari tiga opsi: Merah, Hijau, dan Biru.

15. Mengubah Warna Sel Berdasarkan Kondisi

Contoh:

Sub WarnaSel()
    Dim rng As Range
    Set rng = Range("A1:A10")
    
    For Each cell In rng
        If cell.Value > 10 Then
            cell.Interior.ColorIndex = 4 'Merah
        Else
            cell.Interior.ColorIndex = 3 'Hijau
        End If
    Next
End Sub

Hasil: Script ini akan mengubah warna sel pada rentang A1:A10 pada lembar kerja aktif menjadi merah jika nilainya lebih besar dari 10, dan hijau jika tidak.

16. Menghitung Rata-rata dan Jumlah pada Rentang Sel

Contoh:

Sub HitungRataJumlah()
    Dim rng As Range
    Set rng = Range("A1:A10")
    
    MsgBox "Jumlah: " & WorksheetFunction.Sum(rng) & vbNewLine & _
           "Rata-rata: " & WorksheetFunction.Average(rng)
End Sub

Hasil: Script ini akan menghitung jumlah dan rata-rata dari nilai pada rentang A1:A10 pada lembar kerja aktif dan menampilkan hasilnya dalam sebuah kotak pesan.

17. Menyembunyikan atau Menampilkan Kolom dan Baris

Contoh:

Sub SembunyikanKolom()
    Columns("A:B").EntireColumn.Hidden = True
End Sub

Hasil: Script ini akan menyembunyikan kolom A dan B pada lembar kerja aktif.

18. Mengambil Nilai dari Sel Lain

Contoh:

Sub AmbilNilaiSelLain()
    Range("A1").Value = Range("B1").Value
End Sub

Hasil: Script ini akan mengambil nilai dari sel B1 pada lembar kerja aktif dan menempelkannya pada sel A1.

19. Membuka File Excel Lain

Contoh:

Sub BukaFileExcelLain()
    Workbooks.Open Filename:="C:\Data\Contoh.xlsx"
End Sub

Hasil: Script ini akan membuka file Contoh.xlsx yang berada di dalam folder Data pada drive C.

20. Menyimpan File Excel dengan Nama dan Format Baru

Contoh:

Sub SimpanFileExcelBaru()
    ActiveWorkbook.SaveAs Filename:="C:\Data\Baru.xlsx", FileFormat:=xlOpenXMLWorkbook
End Sub

Hasil: Script ini akan menyimpan file Excel yang sedang aktif dengan nama Baru.xlsx dan format file Excel baru (xlsx) di dalam folder Data pada drive C.

21. Menghapus Seluruh Baris yang di pilih

Baris kode vba berikut akan mempersingkat waktu anda untuk menghapus seluruh baris yang di pilih dengan satu kali klik.

Sub DeleteSelectedRows()
    Dim selectedRange As Range
    Dim i As Long
    
    ' Check if any rows are selected
    If Not Selection.Rows.Count > 0 Then
        MsgBox "Please select at least one row to delete.", vbInformation
        Exit Sub
    End If
    
    ' Loop through each selected row and delete
    For i = Selection.Rows.Count To 1 Step -1
        Selection.Rows(i).Delete
    Next i
End Sub

22. Menggunakan Array Untuk Menghitung total penjualan

Berikut adalah contoh kode VBA Excel terkait penjualan dengan menggunakan array beserta penjelasan baris per baris. Ini adalah kumpulan Script Vba Excel terakhir ya

Dim salesData(1 To 10) As Integer

Baris ini mendeklarasikan variabel array “salesData” yang memiliki 10 elemen dengan tipe data Integer. Array ini akan digunakan untuk menyimpan data penjualan.

Sub CalculateTotalSales()
    Dim salesData(1 To 10) As Integer
    Dim totalSales As Integer
    Dim i As Integer

    ' Mengisi array dengan data penjualan
    salesData(1) = 100
    salesData(2) = 150
    salesData(3) = 200
    ' ...
    salesData(10) = 300

    ' Menghitung total penjualan menggunakan perulangan
    For i = 1 To 10
        totalSales = totalSales + salesData(i)
    Next i

    ' Menampilkan hasil total penjualan
    MsgBox "Total penjualan: " & totalSales
End Sub

Fungsi ini merupakan sub-rutin yang menghitung total penjualan dengan menggunakan array “salesData”. Pada baris pertama di dalam sub-rutin, array “salesData” dideklarasikan dengan 10 elemen.

Selanjutnya, data penjualan diisi ke dalam array menggunakan indeks 1 hingga 10. Kemudian, perulangan dilakukan untuk menjumlahkan semua data penjualan yang ada di array dan hasilnya disimpan dalam variabel “totalSales”. Terakhir, hasil total penjualan ditampilkan menggunakan MsgBox.

Harap dicatat bahwa contoh di atas hanya merupakan ilustrasi sederhana. Dalam implementasi nyata, data penjualan mungkin akan diambil dari sumber data yang sesuai, seperti lembar kerja Excel atau basis data, dan array akan diisi dengan data yang relevan sebelum dihitung.

7 Tips Mengoptimalkan Kumpulan Script Vba Excel

Berikut adalah beberapa tips untuk mengoptimalkan kumpulan coding vba excel diatas:

  1. Gunakan variabel dengan bijak: Deklarasikan variabel sebelum penggunaannya dan berikan nama yang deskriptif agar mudah dipahami dan diikuti.
  2. Gunakan kode singkat: Buat kode Anda seefisien dan se-singkat mungkin dengan menggunakan operator yang tepat seperti “For Each” dan “If… Then… Else”.
  3. Gunakan Array: Array memungkinkan Anda untuk memproses sejumlah data sekaligus, sehingga bisa lebih cepat daripada melakukan pengolahan satu per satu.
  4. Hindari pemakaian “Select” dan “Activate”: Penggunaan “Select” dan “Activate” dapat membuat kode VBA Excel lambat dan tidak efisien.
  5. Hapus objek yang tidak digunakan: Setelah selesai menggunakan objek, hapus objek yang tidak digunakan dengan menggunakan perintah “Set objek = Nothing”.
  6. Matikan fitur-fitur yang tidak dibutuhkan: Matikan fitur-fitur yang tidak dibutuhkan seperti Auto Calculation, Auto Format, dan Auto Correct.
  7. Perbarui perangkat lunak dan hardware: Pastikan perangkat lunak dan hardware yang digunakan sudah diperbarui untuk memastikan kinerja VBA Excel yang optimal.

Dengan mengoptimalkan kode VBA Excel, Anda dapat meningkatkan kinerja kode, menghemat waktu, dan memaksimalkan produktivitas Anda.

Kesimpulan

Kumpulan script VBA Excel di atas adalah contoh dari beberapa fungsi dan fitur penting yang dapat digunakan dalam pekerjaan sehari-hari dengan menggunakan Microsoft Excel.

Dengan memahami dan menguasai beberapa script tersebut, Anda dapat mempercepat dan memudahkan pekerjaan Anda dalam memanipulasi data, mengatur format, dan membuat laporan.

Jangan ragu untuk mencoba script-script tersebut dan menyesuaikannya dengan kebutuhan Anda sendiri. Dengan mempelajari VBA Excel, Anda akan menjadi lebih efisien dan produktif dalam pekerjaan Anda.

Satu pemikiran pada “Kumpulan Script Vba Excel – 20 Script VBA Excel Terbaik untuk Mengoptimalkan Pekerjaan Anda”

  1. Ping-balik: Aplikasi Point of Sale Excel (POS) Gratis - Solusi Palam Raya

Tinggalkan komentar