Membuat fungsi kustom di Excel

Meskipun Excel menyertakan banyak fungsi lembar kerja bawaan, kemungkinannya tidak memiliki fungsi untuk setiap tipe perhitungan yang Anda lakukan. Desainer Excel tidak mungkin mengantisipasi setiap kebutuhan perhitungan pengguna. Sebagai gantinya, Excel memberi Anda kemampuan untuk membuat fungsi kustom, yang dijelaskan di artikel ini.

Apakah Anda mencari informasi tentang cara membuat fungsi kustom JavaScript yang bisa Anda jalankan di Excel untuk Windows, Excel untuk Mac, atau Excel untuk web? Jika Anda, lihat artikel Excel gambaran umum fungsi kustom.

Fungsi kustom, seperti makro, gunakan bahasa pemrograman Visual Basic for Applications (VBA) . Mereka berbeda dari makro dengan dua cara yang signifikan. Pertama, mereka menggunakan prosedur fungsi dan bukan Sub prosedur. Yaitu, mereka mulai dengan pernyataan fungsi dan bukan Sub pernyataan dan diakhiri dengan fungsi end dan bukan Sub akhir. Kedua, mereka melakukan penghitungan dan bukannya 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 sebesar 10 persen pada penjualan produk, asalkan pesanan lebih dari unit 100. Dalam paragraf berikut, kami akan memperlihatkan fungsi untuk menghitung diskon ini.

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

Contoh formulir pesanan tanpa fungsi kustom

Untuk membuat fungsi diskon 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 akan muncul di sisi kanan Editor Visual Basic.

  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: Untuk membuat kode lebih mudah dibaca, Anda bisa menggunakan tombol tab untuk mengindentasi baris. Indentasi adalah untuk manfaat Anda saja, dan bersifat opsional, karena kode akan berjalan dengan atau tanpa itu. Setelah Anda mengetikkan garis indentasi, Visual Basic editor akan mengasumsikan baris Anda berikutnya akan diindentasi dengan sama. Untuk berpindah (yaitu, ke kiri) satu karakter tab, tekan Shift + tab.

Sekarang Anda siap menggunakan fungsi diskon baru. Tutup Editor Visual Basic, pilih sel G7, dan ketik yang berikut ini:

= DISKON (D7, E7)

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

Di baris pertama kode VBA Anda, fungsi DISCOUNT (Quantity, Price), Anda mengindikasikan 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 Price . Sekarang Anda bisa menyalin rumus diskon ke G8: G13 untuk mendapatkan hasil yang diperlihatkan di bawah ini.

Mari kita pertimbangkan bagaimana Excel menafsirkan 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 diapit dalam tanda kurung, kuantitas dan hargaadalah tempat penampung untuk nilai perhitungan diskon tersebut.

Contoh formulir pesanan dengan fungsi kustom

Pernyataan if dalam blok kode berikut ini menguji 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 diskonvariabel. Pernyataan VBA yang menyimpan nilai dalam variabel disebut pernyataan tugas , 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 kuantitasnya kurang dari 100, VBA mengeksekusi pernyataan berikut:

Discount = 0

Terakhir, pernyataan berikut membulatkan nilai yang ditetapkan ke variabel diskon ke dua tempat desimal:

Discount = Application.Round(Discount, 2)

VBA tidak memiliki fungsi ROUND, tapi Excel tidak. Oleh karena itu, untuk menggunakan ROUND dalam pernyataan ini, Anda akan memberi tahu VBA untuk mencari metode Round (function) di objek aplikasi (Excel). Anda melakukannya dengan menambahkan aplikasi Word sebelum kata bulat. Gunakan sintaks ini kapan pun Anda perlu mengakses fungsi Excel dari modul VBA.

Fungsi kustom harus diawali dengan pernyataan fungsi dan diakhiri dengan pernyataan fungsi end. Selain nama fungsi, pernyataan fungsi biasanya menentukan satu atau beberapa argumen. Namun, Anda bisa membuat fungsi tanpa argumen. Excel menyertakan beberapa fungsi bawaan — RAND dan sekarang, misalnya — yang tidak menggunakan argumen.

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

Jumlah kata kunci VBA yang bisa Anda gunakan dalam fungsi kustom lebih kecil dari angka yang bisa Anda gunakan dalam makro. Fungsi kustom tidak diperbolehkan 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 bisa mengubah ukuran jendela, mengedit rumus di 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! .

Prosedur satu tindakan yang bisa dilakukan (Selain melakukan perhitungan) 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 alat untuk menyampaikan informasi kepada pengguna. Anda juga bisa menggunakan kotak dialog kustom, atau UserForms, tapi itu subjek di luar lingkup pengenalan ini.

Makro sederhana dan fungsi kustom mungkin sulit dibaca. Anda dapat membuatnya lebih mudah dipahami dengan mengetikkan teks penjelasan dalam bentuk komentar. Anda menambahkan komentar dengan mendahului teks penjelasan dengan tanda kutip. Misalnya, contoh berikut memperlihatkan fungsi DISCOUNT dengan komentar. Menambahkan komentar seperti ini memudahkan Anda atau orang lain untuk mempertahankan kode VBA 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 semula.

Contoh fungsi VBA dengan komentar

Tanda kutip memberi tahu Excel untuk mengabaikan semuanya di sebelah kanan pada baris yang sama, jadi Anda bisa membuat komentar baik di garis sendiri maupun di sisi kanan baris yang berisi kode VBA. Anda mungkin mulai blok kode yang relatif panjang dengan komentar yang menjelaskan tujuan keseluruhannya dan kemudian 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 labelmakro, Anda bisa menamakannya sebagai tanda bulanuntuk menguraikan secara lebih spesifik tujuan yang dibuat makro. Menggunakan nama deskriptif untuk makro dan fungsi kustom sangat membantu saat Anda membuat banyak prosedur, terutama jika Anda membuat prosedur yang memiliki tujuan yang sama tapi tidak identik.

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

Untuk menggunakan fungsi kustom, buku kerja yang berisi modul yang Anda buat fungsinya harus terbuka. Jika buku kerja itu tidak terbuka, Anda mendapatkan #NAME? kesalahan 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 tersebut berada. Misalnya, jika Anda membuat fungsi yang disebut diskon dalam buku kerja yang disebut Personal. xlsb dan Anda memanggil fungsi itu dari buku kerja lain, Anda harus mengetik = personal. xlsb! Discount (), not Simply = 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:

kotak dialog sisipkan fungsi

Cara yang lebih mudah untuk membuat fungsi kustom Anda selalu tersedia untuk menyimpannya dalam buku kerja terpisah lalu menyimpan buku kerja tersebut sebagai add-in. Anda kemudian dapat membuat Add-in tersedia setiap kali Anda menjalankan Excel. Berikut ini cara melakukan ini:

  1. Setelah Anda membuat fungsi yang Anda perlukan, klik File > Simpan sebagai.

    Di Excel 2007, klik tombol Microsoft Office, lalu klik Simpan sebagai

  2. Dalam kotak dialog Simpan sebagai , buka daftar turun bawah Simpan sebagai tipe , lalu pilih Add-in Excel. Simpan buku kerja di bawah nama yang dikenali, seperti Myfunctions, dalam folder Add -in. Kotak dialog Simpan sebagai akan mengusulkan folder tersebut, sehingga yang perlu Anda lakukan adalah menerima lokasi default.

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

    Di Excel 2007, klik tombol Microsoft Office, lalu klik opsi Excel.

  4. Dalam kotak dialog opsi Excel , klik kategori Add-in .

  5. Di daftar turun bawah Kelola , pilih Add-in Excel. Lalu klik tombol buka .

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

    kotak dialog add-in

  1. Setelah Anda membuat fungsi yang Anda perlukan, klik File > Simpan sebagai.

  2. Dalam kotak dialog Simpan sebagai , buka daftar turun bawah Simpan sebagai tipe , lalu pilih Add-in Excel. Simpan buku kerja di bawah nama yang dikenali, seperti Myfunctions.

  3. Setelah Anda menyimpan buku kerja, klik alat > Add-in Excel.

  4. Dalam kotak dialog Add-in , pilih tombol Telusuri untuk menemukan Add-in, klik buka, lalu centang kotak di samping Add-in Anda di kotak Add- in yang tersedia.

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 Editor Visual Basic. Jika Anda mencari di Project Explorer Visual Basic editor di bawah judul VBAProject, Anda akan melihat modul yang dinamai setelah file Add-in. Add-in Anda akan memiliki ekstensi. xlam.

modul bernama di vbe

Mengklik ganda modul tersebut di Project Explorer menyebabkan Editor Visual Basic untuk menampilkan kode fungsi Anda. Untuk menambahkan fungsi baru, letakkan titik penyisipan Anda setelah pernyataan fungsi end yang mengakhiri fungsi Last dalam jendela kode, dan mulailah mengetik. Anda bisa membuat sebanyak mungkin fungsi yang Anda perlukan dengan cara ini, dan mereka akan selalu tersedia dalam kategori yang ditentukan pengguna dalam kotak dialog sisipkan fungsi .

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

Perlu bantuan lainnya?

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

Catatan:  Halaman ini diterjemahkan menggunakan mesin dan mungkin terdapat kesalahan tata bahasa atau masalah keakuratan. Kami bertujuan menyediakan konten yang bermanfaat untuk Anda. Dapatkah Anda memberi tahu kami apakah informasi ini bermanfaat untuk Anda? Berikut adalah artikel dalam bahasa Inggris untuk referensi.

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

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×