VBA: Belajar Membuat UDF (User Defined Function)

Halo Excel Warriors!

Pernah kepikiran bikin fungsi Excel versi kamu sendiri? Misalnya, fungsi rata-rata yang bisa kamu atur sendiri logikanya? Nah, kita bisa bikin itu lewat VBA—dan disebutnya User Defined Function (UDF). Tapi sabar dulu... kadang kamu nggak perlu bikin apa-apa. Kenapa? Karena Excel udah punya banyak fungsi bawaan yang canggih banget.

Di artikel ini kita bahas:

• Gimana bikin UDF buat rata-rata.
• Berbagai pendekatan dari paling sederhana sampai yang lebih aman.
• Kapan sebaiknya nggak usah bikin UDF, cukup pakai WorksheetFunction.

Daftar Isi

1. Apa Itu UDF?
2. Pendekatan 1: UDF Paling Sederhana
3. Pendekatan 2: UDF yang Lebih Aman
4. Pendekatan 3: Pakai WorksheetFunction
5. Kapan Sebaiknya pakai UDF?
6. Kesimpulan

📚 Artikel Terkait:

• VBA: Cara Gampang Manipulasi Isi dan Format Sel
• VBA Excel: Trik Sakti Isi, Baca, Format, dan Kondisional di Sel
• Memaksimalkan Formula Excel dengan Named Range

Apa Itu UDF?

UDF adalah fungsi bikinan sendiri di VBA, yang bisa kamu pakai langsung di sel Excel, misalnya:

=MyAverage(A1:A10)

UDF berguna kalau kamu butuh perhitungan yang nggak bisa dilakukan oleh fungsi bawaan Excel. Tapi ingat, kalau Excel sudah bisa, ngapain repot?

Pendekatan 1: UDF Paling Sederhana

Ini adalah versi starter pack-nya. Kita bikin fungsi yang hitung rata-rata dari angka-angka dalam satu range.

Kode:

Function MyAverage(rng As Range) As Double
    Dim cell As Range
    Dim total As Double
    Dim count As Long

    For Each cell In rng
        If IsNumeric(cell.Value) Then
            total = total + cell.Value
            count = count + 1
        End If
    Next cell

    If count > 0 Then
        MyAverage = total / count
    Else
        MyAverage = 0
    End If
End Function

Penjelasan:

• For Each cell In rng: kita telusuri setiap sel dalam rentang yang diberikan.
• If IsNumeric(cell.Value): hanya angka yang dihitung (teks dan kosong di-skip).
• total menjumlahkan nilai, count menghitung berapa banyak angka.
• Kalau count > 0, kita bagi totalnya dengan jumlah angka.
• Kalau count = 0, kita balikin 0 biar nggak error.

Catatan: Cocok untuk belajar dasar UDF, tapi belum tahan banting. Belum bisa handle error atau input aneh-aneh.

Pendekatan 2: UDF yang Lebih Aman (Error Handling)

Versi ini lebih tangguh, bisa:

• Mengabaikan nilai kosong atau teks.
• Menangani error dengan elegan.
• Memberikan output error standar Excel seperti #DIV/0! atau #VALUE!.

Kode:

Function MyAverageSafe(rng As Range) As Variant
    On Error GoTo ErrorHandler
    Dim cell As Range
    Dim total As Double
    Dim count As Long

    For Each cell In rng
        If IsNumeric(cell.Value) And Not IsEmpty(cell.Value) Then
            total = total + cell.Value
            count = count + 1
        End If
    Next cell

    If count > 0 Then
        MyAverageSafe = total / count
    Else
        MyAverageSafe = CVErr(xlErrDiv0)
    End If
    Exit Function

ErrorHandler:
    MyAverageSafe = CVErr(xlErrValue)
End Function

Penjelasan:

• On Error GoTo ErrorHandler: kalau terjadi error, kode langsung loncat ke bagian penanganan error.
• CVErr(xlErrDiv0): kalau tidak ada angka valid, kembalikan error #DIV/0!.
• CVErr(xlErrValue): kalau ada kesalahan input atau lainnya, kembalikan #VALUE!.

Tips: Kalau kamu bagikan fungsi ini ke orang lain, ini versi yang aman dan ramah pengguna. Gagal dengan sopan itu penting!

Pendekatan 3: Pakai WorksheetFunction (Kalau Excel Sebenarnya Sudah Bisa)

Sekarang, ini pendekatan yang sering dilupakan: pakai saja fungsi bawaan Excel lewat VBA! Maksudnya gimana? Gini—Excel itu sudah punya fungsi AVERAGE, jadi kita tinggal “pinjam” saja fungsinya lewat WorksheetFunction di VBA.

Kode:

Function MyAverageViaExcel(rng As Range) As Variant
    On Error GoTo ErrorHandler
    MyAverageViaExcel = WorksheetFunction.Average(rng)
    Exit Function

ErrorHandler:
    MyAverageViaExcel = CVErr(xlErrValue)
End Function

Penjelasan:

• WorksheetFunction.Average(rng): ini sama saja seperti kamu menulis =AVERAGE(A1:A10) di Excel, tapi dipanggil dari VBA.
• Kalau misalnya input salah atau error, kita tangani dengan CVErr(xlErrValue).

Catatan : Kalau kamu cuma mau bikin fungsi yang sama persis dengan bawaan Excel, sebaiknya langsung pakai fungsi Excel aja. Kenapa repot?

Kapan Sebaiknya Pakai UDF? Kapan Cukup Pakai Fungsi Excel?

Mr. Kid paham banget, bikin UDF itu menyenangkan. Tapi, sebelum kamu terjun coding di VBA, pikirkan dulu:

• Pakai fungsi Excel langsung kalau:
  • Fungsi yang kamu mau sudah tersedia (contoh: AVERAGE, COUNTIFS, SUMPRODUCT, dll).
  • Tidak perlu logika khusus atau perhitungan kompleks.
• Pakai UDF kalau:
  • Kamu butuh logika unik (misalnya hitung rata-rata tapi hanya untuk nilai lebih besar dari 70).
  • Kamu ingin bikin fungsi yang readable dan reusable.
  • Kamu perlu menyederhanakan formula rumit jadi satu fungsi khusus.

Dan satu lagi: UDF tidak bekerja di semua kondisi, seperti saat kamu menyimpan workbook dalam format .xls lama, atau saat digunakan di Excel Online. Jadi pertimbangkan konteksnya juga, ya!

Kesimpulan

Jadi, kita tadi sudah bahas tiga pendekatan:

1. Pendekatan dasar: belajar konsep looping dan logika dasar.
2. Pendekatan aman: tambahkan penanganan error supaya tidak nyusahin pengguna.
3. Pendekatan realistis: gunakan WorksheetFunction kalau Excel sebenarnya sudah punya fungsi yang kamu butuhkan.

Pesan terakhir : Jangan buru-buru bikin UDF hanya karena keliatan keren. Keren itu saat kamu tahu kapan harus ngoding, dan kapan cukup pakai yang sudah ada!

Selamat ngoding, selamat bereksperimen!