Membuat fungsi kustom di Excel

Meskipun Excel menyertakan banyak fungsi lembar kerja bawaan, kemungkinan Excel tidak memiliki fungsi untuk setiap jenis penghitungan yang Anda lakukan. Para desainer Excel tidak mungkin mengantisipasi kebutuhan perhitungan setiap pengguna. Sebagai gantinya, Excel memberi Anda kemampuan untuk membuat fungsi kustom, yang dijelaskan dalam artikel ini.

Membuat fungsi kustom sederhana

Fungsi kustom, seperti makro, menggunakan bahasa pemrograman Visual Basic for Applications (VBA). Mereka berbeda dari makro dalam dua cara yang signifikan. Pertama, mereka menggunakan prosedur Fungsi , bukan Sub prosedur. Artinya, mereka memulai dengan pernyataan Fungsi alih-alih Sub pernyataan dan diakhiri dengan End Function alih-alih End Sub. Kedua, mereka melakukan perhitungan alih-alih mengambil 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 terpisah dari Excel.

Misalkan perusahaan Anda menawarkan diskon kuantitas sebesar 10 persen untuk penjualan suatu produk, asalkan pesanan lebih dari 100 unit. Dalam paragraf berikut, kami akan mendemonstrasikan fungsi untuk menghitung diskon ini.

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

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

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

2. Salin dan tempel 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
   
   

Menggunakan fungsi kustom

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

=DISKON(D7,E7)

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

Di baris pertama kode VBA Anda, Fungsi DISCOUNT(quantity, price), Anda menunjukkan bahwa fungsi DISCOUNT memerlukan dua argumen, quantity dan price. Ketika 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 dapat menyalin rumus DISKON ke G8:G13 untuk mendapatkan hasil yang ditunjukkan di bawah ini.

Mari kita pertimbangkan bagaimana Excel menafsirkan prosedur fungsi ini. Saat Anda menekan Enter, Excel mencari nama DISCOUNT di buku kerja saat ini dan menemukan bahwa nama tersebut adalah fungsi kustom dalam modul VBA. Nama argumen yang diapit dalam tanda kurung, kuantitas dan harga, adalah placeholder untuk nilai yang menjadi dasar perhitungan diskon.

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

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

Jika jumlah barang yang terjual 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 penugasan , karena mengevaluasi ekspresi di sisi kanan tanda sama dengan dan menetapkan hasilnya ke nama variabel di sebelah kiri. Karena variabel Diskon memiliki nama yang sama dengan prosedur fungsi, nilai yang disimpan dalam variabel akan dikembalikan ke rumus lembar kerja yang disebut fungsi DISCOUNT.

Jika kuantitas kurang dari 100, VBA menjalankan pernyataan berikut:

Discount = 0

Terakhir, pernyataan berikut membulatkan nilai yang ditetapkan untuk variabel Diskon menjadi dua tempat desimal:

Discount = Application.Round(Discount, 2)

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

Memahami aturan fungsi kustom

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.

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

Menggunakan kata kunci VBA dalam fungsi kustom

Jumlah kata kunci VBA yang dapat Anda gunakan dalam fungsi kustom lebih kecil daripada jumlah yang dapat Anda gunakan dalam makro. Fungsi kustom tidak boleh melakukan apa pun selain mengembalikan nilai ke rumus dalam lembar kerja, atau ke ekspresi yang digunakan dalam makro atau fungsi VBA lainnya. 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" semacam ini dalam prosedur fungsi, fungsi akan mengembalikan nilai #VALUE! .

Satu tindakan yang dapat dilakukan prosedur fungsi (selain melakukan penghitungan) adalah menampilkan kotak dialog. Anda dapat menggunakan pernyataan InputBox dalam fungsi kustom sebagai sarana untuk mendapatkan input dari pengguna yang menjalankan fungsi tersebut. Anda dapat menggunakan pernyataan MsgBox sebagai sarana untuk menyampaikan informasi kepada pengguna. Anda juga dapat menggunakan kotak dialog kustom, atau Formulir Pengguna, tetapi itu adalah subjek di luar cakupan pengantar ini.

Mendokumentasikan makro dan fungsi kustom

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

Apóstrof memberi tahu Excel untuk mengabaikan semuanya di sebelah kanan pada baris yang sama, sehingga Anda dapat membuat komentar baik pada baris sendiri maupun di sisi kanan baris yang berisi kode VBA. Anda dapat memulai blok kode yang relatif panjang dengan komentar yang menjelaskan tujuan keseluruhannya, lalu menggunakan komentar sebaris untuk mendokumentasikan pernyataan individual.

Cara lain untuk mendokumentasikan makro dan fungsi kustom Anda adalah dengan memberi mereka nama deskriptif. Misalnya, daripada menamai makro Labels, Anda dapat menamainya MonthLabels untuk menjelaskan secara lebih spesifik tujuan makro tersebut. Menggunakan nama deskriptif untuk makro dan fungsi kustom sangat membantu jika Anda telah membuat banyak prosedur, terutama jika Anda membuat prosedur yang memiliki tujuan yang serupa tetapi tidak identik.

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

Membuat fungsi kustom Anda tersedia di mana saja

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

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

Cara yang lebih mudah untuk membuat fungsi kustom Anda tersedia setiap saat adalah dengan menyimpannya di buku kerja terpisah, lalu menyimpan buku kerja tersebut sebagai add-in. Anda kemudian dapat membuat add-in tersedia setiap kali Anda menjalankan Excel. Berikut cara melakukannya:

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

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

Tentang penulis

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

Perlu bantuan lainnya?

Anda selalu dapat bertanya kepada ahli di Komunitas Teknologi Excel atau mendapatkan dukungan di Komunitas.