WorkSheetFunction

Ada banyak cara untuk menggunakan fungsi-fungsi didalam VBA. Karena VBA sudah dilengkapi dengan banyak fungsi bawaan.

Selain menggunakan fungsi bawaan, kamu bahkan dapat membuat fungsi sendiri atau lebih dikenal dengan User Defined Function (UDF).

Namun, selain itu kamu juga bisa memanfaatkan banyak fungsi standar bawaan Excel didalam VBA yaitu dengan menggunakan WorksheetFunction.

Cara Menggunakan WorksheetFunction dalam VBA

WorksheetFunction digunakan untuk memanggil atau menggunakan rumus bawaan standar Excel, dengan isian argumen yang sama seperti di Excel.

Untuk mengakses fungsi Excel di VBA, tambahkan Application.WorksheetFunction di depan fungsi yang ingin kamu panggil.

Sebagai contoh, di bawah ini kita akan memanggil fungsi Max dari bawaan Excel, maka sintax nya menjadi

Dim NilaiMax as long
NilaiMax = Application.WorksheetFunction.Max(Range("A1").Value, Range("A2").Value)

Argumen yang diisikan kedalam MAX tersebut sama seperti di Excel.

WorksheetFunction adalah sebuah method dari objek Application. Metode ini memungkinkan untuk mengakses banyak (meskipun tidak semua) fungsi worksheet standar Excel.

Secara umum, ini digunakan untuk mengakses fungsi-fungsi rumus Excel yang belum ada versi VBA-nya.

Application.WorksheetFunction vs. Application

Ada dua cara untuk mengakses rumus bawaan excel didalam VBA,

1. Application.WorksheetFunction
2. Tanpa WorksheetFunction

Contohnya:

'Dengan WorksheetFunction
NilaiMax = Application.WorksheetFunction.Max(Range("A1").Value, Range("A2").Value)

'Tanpa WorksheetFunction
NilaiMax = Application.Max(Range("A1").Value, Range("A2").Value)

Apa saja perbedaannya dan apa saja kelebihan dan kekurangannya.

jika kita menghilangkan WorksheetFunction, tentu saja fitur Intellisense tidak akan muncul. dan juga tidak akan mengalami Error.

Jika menggunakan Application langsung dan fungsi yang digunakan menghasilkan error, maka VBA akan mengembalikan nilai error tersebut kedalam Variable.

Sedangkan, jika menggunakan metode WorksheetFunction, VBA akan memunculkan error run-time. Dimana Error ini bisa diolah lebih lanjut dengan Error handler.

Mari kita lihat perbedaan ini dengan contoh:

Misalkan disini saya akan menggunakan rumus SUM dengan menjumlahkan A+B . tentu saja ini akan error karana TEXT tidak bisa dijumlahkan.

Application.WorksheetFunction akan menampilkan Error Runtime ketika hasil rumus di rumus SUM menghasilkan hasil Error.

Sedangkan tanpa WorksheetFunction, Program tidak mengalami Error Runtime, tetapi Nilai Error dari hasil rumus SUM dimasukan kedalam Variable Nilai.

Membuat Property WorksheetFunction

Ini adalah tips jika kamu banyak menggunakan WorksheetFunction untuk aplikasi VBA yang kamu buat.

Daripada menulis banyak Application.WorksheetFunction.Rumus setiap kali ingin menggunakan rumus Excel, ada baiknya WorksheetFunction ini dibuat sebagai Property baru untuk memudahkan penulisan script.

Caranya, pada Standar Module tambahkan Script dibawah ini

Public Property Get WF() As WorksheetFunction
    Set WF = Application.WorksheetFunction
End Property

Pada script diatas WorksheetFunction sudah digantikan dengan WF

Selanjutnya dimanapun baik di Standar Module ataupun di Userform Module, cukup gunakan WF sebagai pengganti WorksheetFunction, contoh :

Sub TestWF()
    Nilai = WF.Sum(20, 60)
End Sub

Atau misalnya ingin menampilkan hari dan tanggal Indonesia karena fungsi bawaan VBA yaitu Format tidak support LCID, dan yang support Locale Indonesia hanya ada di rumus bawaan Excel yaitu rumus TEXT.

Maka scriptnya bisa dibuat seperti dibawah ini:

Sub TanggalIndo()
    TextTanggal = WF.Text(Date, "[$-421] DDDD, DD MMMM YYYY")
End Sub