Membuat fungsi kustom di Excel

Meskipun Excel menyertakan banyak fungsi lembar kerja bawaan, kemungkinan fungsi tersebut tidak memiliki fungsi untuk setiap tipe penghitungan yang Anda lakukan. Desainer Excel tidak dapat antisipasi kebutuhan penghitungan setiap pengguna. Sebagai Excel, Anda dapat membuat fungsi kustom yang dijelaskan dalam artikel ini.

Fungsi kustom, seperti makro, menggunakan bahasa pemrograman Visual Basic for Applications (VBA). Perbedaannya berbeda dari makro dalam dua cara yang signifikan. Pertama, mereka menggunakan prosedur Fungsi dan bukan Prosedur sub. Artinya, fungsi dimulai dengan pernyataan Fungsi, bukan pernyataan Sub dan diakhiri dengan Fungsi End, bukan End Sub. Kedua, menjalankan penghitungan dan bukan melakukan tindakan. Jenis pernyataan tertentu, seperti pernyataan yang memilih dan memformat rentang, tidak termasuk 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.

Anggap perusahaan Anda menawarkan diskon jumlah 10 persen pada penjualan sebuah produk, jika pesanannya adalah lebih dari 100 unit. Dalam paragraf berikut, kami akan mendemonstrasikan 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.

Contoh formulir pesanan tanpa fungsi kustom

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

  1. Tekan Alt+F11 untuk membuka Editor Visual Basic (di Mac, tekan FN+ALT+F11), lalu klik Sisipkan > Module. Jendela modul baru akan 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
    

Catatan: Agar kode menjadi lebih mudah dibaca, Anda dapat menggunakan tombol Tab untuk mengindentasi baris. Indentasi hanya untuk manfaat Anda, dan bersifat opsional, karena kode akan dijalankan dengan atau tanpa indentasi. Setelah Anda mengetik baris yang berindentasi, Visual Basic Editor mengasumsikan baris berikutnya akan sama berindentasi. Untuk berpindah keluar (satu karakter tab ke kiri), tekan Shift+Tab.

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

=DISCOUNT(D7,E7)

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

Di baris pertama kode VBA, Function DISCOUNT(quantity, price), Anda mengindikasikan bahwa fungsi DISCOUNT memerlukan dua argumen, kuantitas danharga. Ketika Anda memanggil fungsi di 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 Excel menginterpretasikan prosedur fungsi ini. Saat menekan Enter,Excel mencari nama DISCOUNT dalam buku kerja saat ini dan menemukan bahwa itu adalah fungsi kustom dalam modul VBA. Nama argumen yang dimasukkan dalam kurung, kuantitas dan harga,adalah tempat penampung untuk nilai di mana perhitungan diskon didasarkan.

Contoh formulir pesanan dengan fungsi kustom

Pernyataan If dalam blok kode berikut ini 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 item yang terjual lebih besar dari atau sama dengan 100, VBA mengeksekusi 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 pernyataan ini mengevaluasi ekspresi di sisi kanan tanda sama dengan dan menetapkan hasil ke nama variabel di sebelah kiri. Karena variabel Discount memiliki nama yang sama dengan prosedur fungsi, nilai yang disimpan di variabel dikembalikan ke rumus lembar kerja yang disebut fungsi DISCOUNT.

Jika jumlah 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, Excel fungsi tersebut. Oleh karena itu, untuk menggunakan ROUND dalam pernyataan ini, Anda memberi tahu VBA untuk mencari metode Round (fungsi) dalam objek Application (Excel). Anda melakukan itu dengan menambahkan kata Aplikasi sebelum kata Bulat. Gunakan sintaks ini setiap kali Anda perlu mengakses Excel fungsi dari modul VBA.

Fungsi kustom harus diawali 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 seperti RAND dan NOW, yang tidak menggunakan argumen.

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

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

Salah satu tindakan yang bisa dilakukan prosedur fungsi (selain melakukan penghitungan) adalah menampilkan kotak dialog. Anda dapat menggunakan pernyataan InputBox dalam fungsi kustom sebagai cara mendapatkan input dari pengguna yang menjalankan fungsi tersebut. Anda dapat menggunakan pernyataan MsgBox sebagai cara 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 dimengerti dengan mengetik teks penjelasan dalam bentuk komentar. Anda menambahkan komentar dengan sebelum 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 saat waktu berlalu. Jika perlu membuat perubahan pada kode di masa mendatang, Anda akan memiliki waktu yang lebih mudah untuk memahami yang sebenarnya.

Contoh fungsi VBA dengan Komentar

Tanda kutip memberi tahu Excel untuk mengabaikan semuanya di sebelah kanan pada baris yang sama, sehingga Anda bisa membuat komentar baik di baris sendiri atau di sisi kanan baris yang berisi kode VBA. Anda mungkin 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 adalah memberinya nama deskriptif. Misalnya, daripada memberi nama Label makro,Anda dapat memberi nama MonthLabels untuk menjelaskan secara lebih spesifik tujuan makro berfungsi. Menggunakan nama deskriptif untuk makro dan fungsi kustom sangat membantu khususnya ketika Anda membuat banyak prosedur, terutama jika Anda membuat prosedur yang memiliki tujuan serupa tapi tidak identik.

Cara Anda mendokumentasikan makro dan fungsi kustom hanyalah preferensi pribadi. Apa yang penting adalah dengan mengadopsi beberapa metode dokumentasi, dan menggunakannya secara konsisten.

Untuk menggunakan fungsi kustom, buku kerja yang berisi modul tempat Anda membuat fungsi harus dibuka. Jika buku kerja itu tidak terbuka, Anda mendapatkan #NAME? ketika Anda mencoba menggunakan fungsi. Jika Anda mereferensikan fungsi dalam buku kerja berbeda, Anda harus mengawali 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 menyebut fungsi itu dari buku kerja yang lain, Anda harus mengetik =personal.xlsb!discount(), tidak hanya =discount().

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

kotak dialog sisipkan fungsi

Cara yang lebih mudah untuk membuat fungsi kustom Anda tersedia sepanjang waktu adalah dengan menyimpannya dalam buku kerja terpisah lalu menyimpan buku kerja itu sebagai add-in. Anda kemudian dapat menjadikan add-in tersedia setiap kali menjalankan Excel. Berikut cara melakukannya:

  1. Setelah membuat fungsi yang diperlukan, klik File dan >Simpan Sebagai.

    Di Excel 2007, klik tombol Microsoft Office,dan klik Simpan Sebagai

  2. Dalam kotak dialog Simpan Sebagai, buka daftar menurun Simpan Sebagai Tipe, lalu pilih Excel Add-In. Simpan buku kerja di bawah nama yang dikenali, seperti Fungsi Saya,di folder AddIns. Kotak dialog Simpan Sebagai akan mengusulkan folder itu, jadi yang perlu Anda lakukan hanyalah menerima lokasi default.

  3. Setelah Anda menyimpan buku kerja, klik File > Excel Options.

    Di Excel 2007, klik Tombol Microsoft Office,dan klik Excel Opsi.

  4. Dalam kotak dialog Excel Options, klik kategori Add-In.

  5. Di daftar turun bawah Kelola, pilih Excel Add-In. Lalu klik tombol Buka.

  6. Dalam kotak dialog Add-In, pilih kotak centang di samping nama yang Anda gunakan untuk menyimpan buku kerja, seperti yang diperlihatkan di bawah ini.

    kotak dialog add-in

  1. Setelah membuat fungsi yang diperlukan, klik File dan >Simpan Sebagai.

  2. Dalam kotak dialog Simpan Sebagai, buka daftar menurun Simpan Sebagai Tipe, lalu pilih Excel Add-In. Simpan buku kerja di bawah nama yang dikenali, seperti Fungsi Saya.

  3. Setelah menyimpan buku kerja, klik Alat > Excel Add-In.

  4. Dalam kotak dialog Add-In, pilih tombol Telusuri untuk menemukan add-in, klik Buka,lalu centang kotak di samping add-in Add-In dalam kotak Add-in Yang Tersedia.

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

modul bernama di vbe

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 dapat membuat fungsi sebanyak yang diperlukan dengan cara ini, dan fungsi tersebut akan selalu tersedia dalam kategori Ditetapkan Pengguna dalam kotak dialog Sisipkan Fungsi.

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

Perlu bantuan lainnya?

Anda selalu dapat bertanya kepada pakar di Komunitas Teknologi Excel, mendapatkan dukungan di Komunitas Jawaban, atau menyarankan fitur maupun fitur baru di Suara Pengguna Excel.

Perlu bantuan lainnya?

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

×