Membuat fungsi kustom di Excel

Catatan:  Kami ingin secepatnya menyediakan konten bantuan terbaru dalam bahasa Anda. 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 di bagian bawah halaman ini? Berikut artikel dalam bahasa Inggris untuk referensi.

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

Fungsi kustom, seperti makro, gunakan Visual Basic for Applications (VBA) bahasa pemrograman. Mereka berbeda dari makro dalam dua cara signifikan. Pertama, mereka gunakan fungsi prosedur bukan Sub prosedur. Yaitu, mereka mulai dengan pernyataan fungsi bukan pernyataan Sub dan end dengan Fungsi akhir bukan End Sub. Kedua, mereka melakukan perhitungan daripada mengambil tindakan. Jenis pernyataan, seperti laporan yang memilih dan memformat rentang, dikecualikan dari fungsi kustom. Di artikel ini, Anda akan mempelajari cara membuat dan menggunakan fungsi kustom. Untuk membuat makro dan fungsi, Anda bekerja dengan Visual Basic Editor (VBE), yang terbuka di jendela baru terpisah dari Excel.

Anggap perusahaan Anda menawarkan adalah jumlah diskon 10 persen penjualan produk, disediakan urutan untuk lebih dari 100 unit. Di paragraf berikut ini, kami akan menunjukkan fungsi untuk menghitung diskon ini.

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

Contoh formulir tanpa fungsi kustom

Untuk membuat fungsi kustom diskon di buku kerja ini, ikuti langkah-langkah ini:

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

  2. Salin dan tempelkan kode berikut ini untuk 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 Anda lebih mudah dibaca, Anda bisa menggunakan tombol Tab untuk menginden baris. Indentasi untuk manfaat Anda saja, dan bersifat opsional, seperti kode akan berjalan dengan atau tanpa itu. Setelah Anda mengetikkan baris yang terindentasi, Visual Basic Editor mengasumsikan baris berikutnya Anda akan sama diindentasikan. Untuk keluar (yaitu, di sebelah kiri) satu tab karakter, tekan Shift + Tab.

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

=DISCOUNT(D7,E7)

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

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

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

Contoh formulir pesanan dengan fungsi kustom

Jika pernyataan dalam blok berikut kode 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 menjalankan pernyataan berikut ini, yang mengalikan nilai jumlah nilai harga dan lalu mengalikan hasilnya dengan 0,1:

Discount = quantity * price * 0.1

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

Jika kuantitas kurang dari 100, VBA menjalankan pernyataan berikut ini:

Discount = 0

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

Discount = Application.Round(Discount, 2)

Tidak ada fungsi ROUND memiliki VBA, tapi Excel tidak. Oleh karena itu, untuk menggunakan ROUND dalam pernyataan ini, Anda mengetahui VBA untuk mencari metode Round (fungsi) di dalam objek aplikasi (Excel). Anda lakukan dengan menambahkan kata aplikasi sebelum kata Round. Gunakan sintaks ini kapan pun Anda memerlukannya untuk mengakses fungsi Excel dari modul VBA.

Fungsi kustom harus dimulai dengan pernyataan fungsi dan diakhiri dengan pernyataan fungsi akhir. Selain nama fungsi, fungsi pernyataan biasanya menentukan satu atau beberapa argumen. Namun, Anda dapat 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 melakukan perhitungan menggunakan argumen yang diteruskan ke fungsi. Akhirnya, tempat di prosedur fungsi, Anda harus menyertakan pernyataan yang menetapkan nilai ke variabel dengan nama yang sama sebagai fungsi. Nilai ini dikembalikan ke rumus yang memanggil fungsi.

Jumlah kata kunci VBA Anda dapat menggunakan fungsi kustom yang lebih kecil dari angka yang bisa Anda gunakan di makro. Fungsi kustom tidak diperbolehkan untuk melakukan sesuatu selain return nilai rumus dalam lembar kerja, atau ekspresi yang digunakan dalam makro VBA atau fungsi lain. Sebagai contoh, fungsi kustom tidak bisa mengubah ukuran jendela, edit rumus dalam sel, atau mengubah font, warna, atau pola opsi untuk teks dalam sel. Jika Anda menyertakan kode "tindakan" semacam ini dalam prosedur fungsi, fungsi akan mengembalikan #VALUE! kesalahan.

Salah satu tindakan prosedur fungsi bisa lakukan (Selain melakukan penghitungan) menampilkan kotak dialog. Anda bisa menggunakan pernyataan InputBox dalam fungsi kustom untuk mendapatkan input dari pengguna yang menjalankan fungsi. Anda bisa menggunakan pernyataan MsgBox sebagai cara untuk menyampaikan informasi untuk pengguna. Anda juga bisa menggunakan kotak dialog kustom, atau UserForms, tapi itu adalah subjek luar lingkup pengenalan ini.

Fungsi kustom dan bahkan sederhana makro sulit dibaca. Anda bisa membuatnya lebih mudah dipahami dengan mengetik teks penjelasan di formulir komentar. Anda menambahkan komentar dengan cara mengawali teks penjelasan dengan tanda kutip. Sebagai contoh, contoh berikut menunjukkan fungsi diskon dengan komentar. Menambahkan komentar seperti ini membuatnya lebih mudah bagi Anda atau orang lain untuk mempertahankan kode VBA Anda seiring berjalannya waktu. Jika Anda perlu membuat perubahan ke bagian kode di masa mendatang, Anda akan memiliki waktu lebih mudah memahami apa yang Anda lakukan pada awalnya.

Contoh fungsi VBA dengan komentar

Tanda kutip memberitahu Excel untuk mengabaikan semuanya di sebelah kanan pada baris yang sama, sehingga Anda bisa membuat komentar baik di baris dengan konstanta itu sendiri atau di sebelah kanan baris yang berisi VBA kode. Anda mungkin mulai blok relatif panjang kode dengan komentar yang menjelaskan tujuan keseluruhan dan lalu menggunakan komentar sebaris dokumen individual laporan.

Cara lain untuk makro dan fungsi kustom dokumen adalah memberi nama deskriptif. Sebagai contoh, bukan nama makro label, Anda bisa Namai MonthLabels untuk menggambarkan lebih khusus tujuan makro menyajikan. Menggunakan nama deskriptif untuk makro dan fungsi kustom sangat membantu saat Anda telah membuat banyak prosedur, terutama jika Anda membuat prosedur yang memiliki tujuan serupa tapi tidak sama.

Bagaimana Anda dokumen makro dan fungsi kustom akan masalah preferensi pribadi. Apa penting adalah menggunakan beberapa metode dokumentasi, dan gunakan secara konsisten.

Untuk menggunakan fungsi kustom, buku kerja yang berisi modul yang Anda buat fungsi harus terbuka. Jika buku kerja tersebut tidak terbuka, Anda mendapatkan #NAME? kesalahan ketika Anda mencoba menggunakan fungsi. Jika Anda referensi fungsi di buku kerja berbeda, Anda harus mendahului nama fungsi dengan nama buku kerja di mana fungsi tersebut berada. Misalnya, jika Anda membuat fungsi disebut diskon dalam buku kerja yang disebut Personal.xlsb dan fungsi tersebut call dari buku kerja yang lain, Anda harus mengetikkan =personal.xlsb!discount(), bukan sekadar =discount().

Anda bisa menyimpan sendiri beberapa tombol (dan kemungkinan kesalahan mengetik) dengan memilih fungsi kustom Anda dari kotak dialog sisipkan fungsi. Fungsi kustom Anda muncul di kategori ditentukan pengguna:

kotak dialog sisipkan fungsi

Cara yang lebih mudah untuk memastikan Anda fungsi kustom tersedia di sepanjang waktu untuk menyimpannya di buku kerja terpisah dan lalu menyimpan buku kerja itu sebagai add-in. Anda dapat lalu membuat add-in tersedia setiap kali Anda menjalankan Excel. Berikut ini cara untuk melakukan hal ini:

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

    Di Excel 2007, klik Tombol Microsoft Office, dan klik Simpan sebagai

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

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

    Di Excel 2007, klik Tombol Microsoft Office, dan 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. Di kotak dialog Add-in , pilih kotak centang di samping nama Anda digunakan 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 , dan pilih Excel Add-In. Simpan buku kerja di bawah nama dikenal, seperti MyFunctions.

  3. Setelah Anda telah 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 Anda Add-In di kotak Add-in tersedia .

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

modul bernama di vbe

Klik ganda modul tersebut di Project Explorer menyebabkan Visual Basic Editor untuk menampilkan kode fungsi Anda. Untuk menambahkan fungsi baru, Tentukan titik penyisipan Anda setelah pernyataan akhir fungsi yang berakhir fungsi di jendela kode, dan mulailah mengetik. Anda bisa membuat banyak fungsi yang Anda butuhkan dengan cara ini, dan mereka akan selalu menjadi tersedia dalam kategori ditentukan pengguna dalam kotak dialog Sisipkan fungsi .

Konten ini pada awalnya dibuat oleh tanda Dodge dan Craig Stinson sebagai bagian dari Microsoft Office Excel 2007 di dalam dan luarbuku mereka. Itu karena telah diperbarui untuk menerapkan ke versi 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.

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.

×