Membuat fungsi kustom di Excel

Fungsi kustom, seperti makro, menggunakan bahasa pemrograman Visual Basic for Applications (VBA ). Keduanya berbeda dari makro dengan dua cara yang signifikan. Pertama, mereka menggunakan prosedur Fungsi dan bukan Sub prosedur. Yaitu, pernyataan diawali dengan pernyataan Fungsi , bukan pernyataan Sub dan diakhiri dengan Fungsi Akhir , bukan End Sub. Kedua, mereka melakukan penghitungan, bukan melakukan tindakan. Jenis pernyataan tertentu, seperti pernyataan yang memilih dan memformat rentang, dikecualikan dari fungsi kustom. Dalam artikel ini, Anda akan mempelajari cara membuat dan menggunakan fungsi kustom. Untuk membuat fungsi dan makro, Anda bekerja dengan Visual Basic Editor (VBE), yang terbuka di jendela baru yang terpisah dari Excel.

Misalkan perusahaan Anda menawarkan diskon kuantitas 10 persen pada penjualan produk, asalkan pesanannya untuk lebih dari 100 unit. Dalam paragraf berikut, kami akan menunjukkan fungsi untuk menghitung diskon ini.

Contoh di bawah ini memperlihatkan formulir pesanan yang mencantumkan setiap item, kuantitas, harga, diskon (jika ada), dan harga diperpanjang yang dihasilkan.

Untuk membuat fungsi DISKON kustom dalam buku kerja ini, ikuti langkah-langkah ini:

1. Tekan Alt+F11 untuk membuka Visual Basic Editor (di Mac, tekan FN+ALT+F11), lalu klik SisipkanModul >. Jendela modul baru muncul di sisi kanan Visual Basic Editor.

2. Salin dan tempelkan kode berikut ke modul baru.

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

Sekarang Anda sudah siap untuk menggunakan fungsi DISCOUNT baru. Tutup Editor Visual Basic, pilih sel G7, lalu ketikkan hal berikut:

=DISCOUNT(D7,E7)

Excel menghitung diskon 10 persen untuk 200 unit pada $47,50 per unit dan mengembalikan $950,00.

Di baris pertama kode VBA Anda, Fungsi DISCOUNT(kuantitas, harga), Anda menunjukkan bahwa fungsi DISCOUNT memerlukan dua argumen, kuantitas dan harga. Saat Anda memanggil fungsi dalam sel lembar kerja, Anda harus menyertakan dua argumen tersebut. Dalam rumus =DISCOUNT(D7,E7), D7 adalah argumen kuantitas , dan E7 adalah argumen harga . Sekarang Anda bisa menyalin rumus DISCOUNT ke G8:G13 untuk mendapatkan hasil yang diperlihatkan di bawah ini.

Mari kita pertimbangkan bagaimana Excel menginterpretasikan prosedur fungsi ini. Saat Anda menekan Enter, Excel mencari nama DISKON di buku kerja saat ini dan menemukan bahwa itu adalah fungsi kustom dalam modul VBA. Nama argumen yang dimasukkan dalam tanda kurung, kuantitas dan harga, adalah tempat penampung untuk nilai di mana perhitungan diskon didasarkan.

Pernyataan If dalam blok kode berikut memeriksa argumen kuantitas dan menentukan apakah jumlah item yang dijual lebih besar dari atau sama dengan 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Jika jumlah item yang dijual lebih besar dari atau sama dengan 100, VBA menjalankan pernyataan berikut, yang mengalikan nilai kuantitas dengan nilai harga lalu mengalikan hasilnya dengan 0,1:

Discount = quantity * price * 0.1

Hasilnya disimpan sebagai variabel Diskon. Pernyataan VBA yang menyimpan nilai dalam variabel disebut pernyataan penetapan , karena mengevaluasi ekspresi di sisi kanan tanda sama dengan dan menetapkan hasil ke nama variabel di sebelah kiri. Karena variabel Diskon memiliki nama yang sama dengan prosedur fungsi, nilai yang disimpan dalam variabel dikembalikan ke rumus lembar kerja yang disebut fungsi DISCOUNT.

Jika kuantitas kurang dari 100, VBA menjalankan pernyataan berikut:

Discount = 0

Akhirnya, pernyataan berikut membulatkan nilai yang ditetapkan ke variabel Diskon ke dua tempat desimal:

Discount = Application.Round(Discount, 2)

VBA tidak memiliki fungsi ROUND, tetapi Excel tidak. Oleh karena itu, untuk menggunakan ROUND dalam pernyataan ini, Anda memberi tahu VBA untuk mencari metode Round (fungsi) dalam objek Aplikasi (Excel). Anda melakukannya dengan menambahkan kata Aplikasi sebelum kata Bulat. Gunakan sintaks ini setiap kali Anda perlu mengakses fungsi Excel dari modul VBA.

Fungsi kustom harus dimulai dengan pernyataan Fungsi dan diakhiri dengan pernyataan Fungsi Akhir. Selain nama fungsi, pernyataan Fungsi biasanya menentukan satu atau beberapa argumen. Namun, Anda dapat membuat fungsi tanpa argumen. Excel menyertakan beberapa fungsi bawaan—RAND dan NOW, misalnya—yang tidak menggunakan argumen.

Mengikuti pernyataan Fungsi, prosedur fungsi menyertakan satu atau beberapa pernyataan VBA yang membuat keputusan dan melakukan penghitungan menggunakan argumen yang diteruskan ke fungsi. Akhirnya, di suatu tempat dalam prosedur fungsi, Anda harus menyertakan pernyataan yang menetapkan nilai ke variabel dengan nama yang sama seperti fungsi. Nilai ini dikembalikan ke rumus yang memanggil fungsi.

Jumlah kata kunci VBA yang bisa Anda gunakan dalam fungsi kustom lebih kecil daripada angka yang bisa Anda gunakan dalam makro. Fungsi kustom tidak diperbolehkan untuk melakukan apa pun selain mengembalikan nilai ke rumus dalam lembar kerja, atau ke ekspresi yang digunakan dalam makro atau fungsi VBA lain. Misalnya, fungsi kustom tidak dapat mengubah ukuran jendela, mengedit rumus dalam sel, atau mengubah opsi font, warna, atau pola untuk teks dalam sel. Jika Anda menyertakan kode "tindakan" jenis ini dalam prosedur fungsi, fungsi mengembalikan #VALUE! .

Tindakan yang dapat dilakukan prosedur fungsi (selain melakukan penghitungan) adalah menampilkan kotak dialog. Anda dapat menggunakan pernyataan InputBox dalam fungsi kustom sebagai sarana mendapatkan input dari pengguna yang menjalankan fungsi tersebut. Anda dapat menggunakan pernyataan MsgBox sebagai sarana untuk menyampaikan informasi kepada pengguna. Anda juga bisa menggunakan kotak dialog kustom, atau UserForms, tapi itu adalah subjek di luar lingkup pengenalan ini.

Bahkan makro sederhana dan fungsi kustom bisa sulit dibaca. Anda dapat membuatnya lebih mudah dipahami dengan mengetikkan teks penjelasan dalam bentuk komentar. Anda menambahkan komentar dengan mendahului teks penjelasan dengan apostrof. Misalnya, contoh berikut ini memperlihatkan fungsi DISCOUNT dengan komentar. Menambahkan komentar seperti ini memudahkan Anda atau orang lain untuk mempertahankan kode VBA Anda seiring berjalannya waktu. Jika Anda perlu membuat perubahan pada kode di masa mendatang, Anda akan memiliki waktu yang lebih mudah untuk memahami apa yang Anda lakukan pada awalnya.

Tanda kutip memberi tahu Excel untuk mengabaikan semuanya ke kanan pada baris yang sama, sehingga Anda bisa membuat komentar baik di baris sendiri atau di sisi kanan garis yang berisi kode VBA. Anda mungkin memulai blok kode yang relatif panjang dengan komentar yang menjelaskan keseluruhan tujuannya lalu menggunakan komentar sebaris untuk mendokumentasikan pernyataan individual.

Cara lain untuk mendokumentasikan makro dan fungsi kustom Anda adalah dengan memberinya nama deskriptif. Misalnya, daripada memberi nama Label makro, Anda bisa memberi nama MonthLabels untuk menjelaskan lebih spesifik tujuan makro melayani. Menggunakan nama deskriptif untuk makro dan fungsi kustom sangat membantu ketika Anda telah membuat banyak prosedur, terutama jika Anda membuat prosedur yang memiliki tujuan yang serupa tetapi tidak identik.

Bagaimana Anda mendokumentasikan makro dan fungsi kustom Anda adalah masalah preferensi pribadi. Yang penting adalah mengadopsi beberapa metode dokumentasi, dan menggunakannya secara konsisten.

Untuk menggunakan fungsi kustom, buku kerja yang berisi modul tempat Anda membuat fungsi harus terbuka. Jika buku kerja tersebut tidak terbuka, Anda akan mendapatkan #NAME? saat Anda mencoba menggunakan fungsi tersebut. Jika Anda mereferensikan fungsi dalam buku kerja yang berbeda, Anda harus mendahului nama fungsi dengan nama buku kerja tempat fungsi berada. Misalnya, jika Anda membuat fungsi yang disebut DISCOUNT dalam buku kerja yang disebut Personal.xlsb dan Anda memanggil fungsi tersebut dari buku kerja lain, Anda harus mengetik =personal.xlsb!discount(), bukan hanya =discount().

Anda dapat menyimpan beberapa penekanan tombol (dan kemungkinan kesalahan pengetikan) dengan memilih fungsi kustom dari kotak dialog Sisipkan Fungsi. Fungsi kustom Anda muncul dalam kategori Yang Ditentukan Pengguna:

Cara yang lebih mudah untuk membuat fungsi kustom Anda tersedia sepanjang waktu adalah dengan menyimpannya dalam buku kerja terpisah lalu menyimpan buku kerja tersebut sebagai add-in. Anda kemudian bisa membuat add-in tersedia setiap kali Anda menjalankan Excel. Berikut cara melakukan hal ini:

Setelah Anda mengikuti langkah-langkah ini, fungsi kustom Anda akan tersedia setiap kali Anda menjalankan Excel. Jika Anda ingin menambahkan ke pustaka fungsi Anda, kembali ke Visual Basic Editor. Jika Anda melihat Project Explorer Editor Visual Basic di bawah judul VBAProject, Anda akan melihat modul yang dinamai setelah file add-in Anda. Add-in Anda akan memiliki ekstensi .xlam.

Mengklik ganda modul tersebut di Project Explorer menyebabkan Visual Basic Editor menampilkan kode fungsi Anda. Untuk menambahkan fungsi baru, posisikan titik penyisipan setelah pernyataan Fungsi Akhir yang mengakhiri fungsi terakhir di jendela Kode, dan mulailah mengetik. Anda bisa membuat fungsi sebanyak yang Anda perlukan dengan cara ini, dan fungsi tersebut akan selalu tersedia dalam kategori Ditentukan Pengguna dalam kotak dialog Sisipkan Fungsi .

Konten ini awalnya ditulis oleh Mark Dodge dan Craig Stinson sebagai bagian dari buku mereka Microsoft Office Excel 2007 Inside Out. Sejak itu juga diperbarui untuk diterapkan ke versi Excel yang lebih baru.