Bagaimana cara membuat fungsi yang ditentukan pengguna di vba?

Mungkin salah satu manfaat paling keren dari mempelajari VBA adalah kemampuan untuk membuat fungsi Anda sendiri.

Di Excel terdapat lebih dari 450 fungsi, dan beberapa di antaranya sangat berguna dalam pekerjaan Anda sehari-hari. Namun Excel memberi Anda opsi untuk membuat fungsi kustom menggunakan VBA. Ya kamu benar. USER‌ DEFINED Function, disingkat UDF, atau bisa juga disebut dengan fungsi VBA khusus.

Dan ada satu hal yang dapat saya katakan dengan yakin bahwa setiap pengguna VBA pemula ingin mempelajari cara membuat fungsi yang ditentukan pengguna. Bukan kamu? Katakan “Ya” di kolom komentar, jika Anda salah satu orang yang ingin membuat fitur custom.

Saya senang memberi tahu Anda bahwa ini adalah PANDUAN LENGKAP untuk membantu Anda membuat fungsi kustom pertama Anda menggunakan VBA dan selain itu saya telah membagikan beberapa contoh FUNGSI YANG DITENTUKAN PENGGUNA untuk membantu menginspirasi Anda.

1. Di sini saya akan menggunakan kata fungsi yang ditentukan pengguna, fungsi khusus, dan UDF secara bergantian. Jadi tetaplah bersama saya, Anda akan menjadi bintang rock VBA dalam beberapa menit ke depan.
2. Untuk membuat kode untuk fungsi khusus VBA Anda perlu menulisnya, Anda tidak dapat merekamnya menggunakan perekam makro .

Mengapa Anda harus membuat fungsi Excel khusus

Seperti yang saya katakan, ada banyak fungsi bawaan di Excel yang dapat membantu Anda memecahkan hampir semua masalah dan melakukan semua jenis perhitungan. Namun, terkadang dalam situasi tertentu Anda perlu membuat UDF.

Dan, di bawah ini saya telah mencantumkan beberapa alasan atau situasi di mana Anda harus menggunakan fungsi khusus.

1. Ketika tidak ada fungsi untuk ini

Ini adalah salah satu alasan umum untuk membuat UDF dengan VBA, karena terkadang Anda perlu menghitung sesuatu dan tidak ada fungsi khusus untuk itu. Saya dapat memberi Anda contoh menghitung kata dari sel dan untuk ini saya menemukan bahwa UDF dapat menjadi solusi yang tepat.

2. Gantikan rumus yang kompleks

Jika Anda bekerja dengan rumus, saya yakin Anda tahu bahwa rumus yang rumit sulit dibaca dan terkadang lebih sulit dipahami orang lain. Jadi, custom function bisa menjadi solusi permasalahan tersebut karena setelah Anda membuat UDF, Anda tidak perlu menulis rumus rumit ini lagi dan lagi.

3. Bila Anda tidak ingin menggunakan SUB Rutin

Meskipun Anda dapat menggunakan kode VBA untuk melakukan penghitungan, kode VBA tidak dinamis*. Anda harus menjalankan kode ini lagi jika ingin memperbarui perhitungan Anda. Namun jika Anda mengubah kode ini menjadi sebuah fungsi, Anda tidak perlu menjalankan kode ini lagi dan lagi karena Anda cukup memasukkannya sebagai sebuah fungsi.

Cara Membuat Fungsi Buatan Pengguna Pertama Anda di Excel

Oke kalau begitu lihat. Saya telah membagi seluruh proses menjadi tiga langkah:

1. Deklarasikan prosedur Anda sebagai suatu fungsi
2. Tentukan argumennya dan tipe datanya
3. Tambahkan kode untuk menghitung nilai yang diinginkan

Tapi izinkan saya memberi Anda:

Anda perlu membuat fungsi yang dapat mengembalikan nama hari dari nilai tanggal. Ya, kami memiliki fungsi yang mengembalikan nomor hari dalam seminggu tetapi bukan namanya. Apakah Anda mengerti apa yang saya katakan? Ya?

Jadi, mari ikuti langkah-langkah di bawah ini untuk membuat fungsi buatan pengguna pertama Anda:

1. Pertama-tama, buka editor Visual Basic Anda menggunakan tombol pintas ALT + F11 atau buka tab Pengembang dan cukup klik tombol “Visual Basic”.
   
2. Selanjutnya adalah memasukkan modul, jadi klik kanan pada jendela proyek VBA, lalu masuk ke sisipkan, lalu klik “Modul”. (PERINGATAN: Anda harus memasukkan USER-DEFINED FUNCTION hanya dalam modul standar. Baik modul Sheet maupun ThisWorkbook adalah jenis modul khusus dan jika Anda memasukkan UDF dalam dua modul ini, Excel tidak mengenali bahwa Anda sedang membuat UDF) .
   
3. Hal ketiga adalah menentukan nama untuk fungsi tersebut dan di sini saya menggunakan “myDayName”. Jadi, Anda perlu menulis “Fungsi mydayName”. Mengapa Fungsi sebelum Nama? Saat Anda membuat fungsi VBA, menggunakan kata “Fungsi” memberitahu Excel untuk memperlakukan kode ini sebagai fungsi (pastikan untuk membaca cakupan UDF sebelum dalam pesan).
4. Setelah itu Anda perlu mendefinisikan argumen untuk fungsi Anda. Jadi masukkan tanda kurung di depan dan tulis “InputDate As Date”. Di sini, InputDate adalah nama argumen dan tanggal adalah tipe datanya. Itu selalu yang terbaik untuk menentukan tipe data untuk argumen.
   
5. Sekarang tutup tanda kurung dan tulis “As String”. Di sini Anda menentukan tipe data dari hasil yang dikembalikan oleh fungsi dan sesuai keinginan Anda, nama hari berupa teks, maka tipe datanya harus “String”. Jika Anda ingin hasilnya selain string, pastikan Anda mengatur tipe datanya sesuai. (myDayName(InputDate As Date) Sebagai fungsi String).
   
6. Pada akhirnya, tekan ENTER. Pada titik ini, nama fungsi Anda, argumennya, tipe data argumen, dan tipe data fungsi telah ditetapkan dan Anda memiliki sesuatu seperti di bawah ini dalam modul Anda:
   
7. Sekarang di “Fungsi” dan “Fungsi Akhir” Anda perlu mendefinisikan perhitungan atau Anda bisa mengatakan cara kerja UDF ini. Di Excel ada fungsi lembar kerja yang disebut “Teks” dan kami menggunakan fungsi yang sama di sini. Dan untuk ini Anda harus menulis kode di bawah ini dan dengan kode ini Anda menentukan nilai yang harus dikembalikan oleh fungsi tersebut. myDayName = Fungsi Lembar Kerja.Teks(Tanggal Masukan, “dddddd”)
   
8. Sekarang tutup editor VB Anda dan kembali ke lembar kerja dan di sel B2 masukkan “=NamaHariSaya(A2)” tekan Enter dan Anda akan mendapatkan nama hari itu.
   

Selamat! Anda baru saja membuat fungsi buatan pengguna pertama Anda. Inilah saatnya Sukacita sejati. Bukan? Ketik “Joy” di bagian komentar.

Cara kerja fungsi ini dan mengembalikan nilai dalam sel

Fungsi kustom pertama Anda ada di sini, tetapi intinya adalah Anda perlu memahami cara kerjanya. Jika saya katakan secara sederhana itu adalah kode VBA tetapi Anda menggunakannya sebagai prosedur fungsi. Mari kita bagi menjadi tiga bagian:

• Anda memasukkannya ke dalam sel sebagai fungsi dan menentukan nilai input.
• Excel menjalankan kode di belakang fungsi dan menggunakan nilai yang Anda referensikan.
• Anda mendapatkan hasilnya di dalam sel.

Namun Anda perlu memahami cara kerja fungsi ini dari dalam. Jadi saya telah membagi seluruh proses menjadi tiga bagian berbeda di mana Anda dapat melihat bagaimana kode yang Anda tulis untuk fungsi tersebut sebenarnya bekerja.

Karena Anda telah menentukan “InputDate” sebagai argumen fungsi dan ketika Anda memasukkan fungsi ke dalam sel dan menentukan tanggal, VBA mengambil nilai tanggal tersebut dan memberikannya ke fungsi teks yang Anda gunakan dalam kode.

Dan pada contoh yang saya sebutkan di atas, tanggal yang ada di sel A1 adalah 01-Jan-2019.

Setelah itu, fungsi TEXT mengubah tanggal tersebut menjadi hari menggunakan kode format “dddddd” yang telah Anda sebutkan di kode fungsi. Dan hari ini yang dikembalikan oleh fungsi TEXT ditetapkan ke “myDayName”.

Jadi, jika hasil fungsi TEXT adalah Selasa, nilai ini akan ditetapkan ke “myDayName”.

Dan di sini pengoperasian fungsi tersebut berakhir. “MyDayName” adalah nama fungsi, jadi nilai apa pun yang ditetapkan ke “myDayName” akan menjadi nilai hasil dan fungsi yang Anda masukkan ke dalam lembar kerja akan mengembalikannya ke dalam sel.

Saat Anda menulis kode untuk fungsi kustom, Anda harus memastikan bahwa nilai yang dikembalikan oleh kode tersebut ditetapkan ke nama fungsi.

Cara Meningkatkan UDF untuk Kebaikan

Nah, Anda sudah tahu cara membuat fungsi VBA khusus.

SEKARANG…

Ada satu hal yang perlu Anda pastikan bahwa kode yang Anda gunakan harus cukup baik untuk menangani semua kemungkinan. Jika Anda berbicara tentang fungsi yang baru saja Anda tulis di atas, Anda dapat mengembalikan nama hari dari suatu tanggal.

Tujuan…

Bagaimana jika nilai yang Anda tentukan bukan tanggal? Bagaimana jika sel yang Anda maksud kosong? Mungkin ada kemungkinan lain, tapi saya yakin Anda mengerti maksud saya.

BENAR? Jadi mari kita coba meningkatkan fungsi kustom ini yang mungkin dapat memperbaiki masalah di atas. BAGUS. Pertama, Anda perlu mengubah tipe data argumen dan menggunakan:

 InputDate As Variant

Dengan ini, fungsi khusus Anda dapat mengambil semua jenis data sebagai masukan. Selanjutnya, kita perlu menggunakan pernyataan VBA IF untuk memeriksa InputDate untuk kondisi tertentu. Kondisi pertama adalah apakah sel tersebut kosong atau tidak. Dan untuk ini, Anda harus menggunakan kode di bawah ini:

 If InputDate = "" Then myDayName = ""

Ini akan membuat fungsi menjadi kosong jika sel yang Anda referensikan kosong.

Satu masalah selesai, mari kita lanjutkan ke masalah berikutnya. Selain tanggal, Anda mungkin memiliki nomor atau teks. Jadi untuk ini Anda juga perlu membuat kondisi yang harus memeriksa apakah nilai yang direferensikan adalah tanggal sebenarnya atau bukan.

Kodenya adalah:

 If IsDate(InputDate) = False Then myDateName = ""

Di sini saya menggunakan blank untuk kedua kondisi tersebut sehingga jika Anda memiliki data yang besar, Anda dapat dengan mudah memfilter nilai yang nilai inputnya tidak valid. Jadi setelah menambahkan ketentuan di atas, kodenya akan terlihat seperti:

 Function myDayName(InputDate As Variant) As String If InputDate = "" Then myDayName = "" Else If IsDate(InputDate) = False Then myDateName = "" Else myDayName = WorksheetFunction.Text(InputDate, "dddddd") End If End If End Function

Dan inilah cara kerjanya sekarang: Saya yakin Anda masih dapat melakukan perubahan pada fitur ini, tapi saya yakin Anda memahami dengan jelas maksud saya.

Cara menggunakan fungsi VBA khusus

Sampai di sini, secara kasar Anda sudah mengetahui cara membuat fungsi VBA di Excel. Namun begitu Anda memilikinya, Anda perlu tahu bagaimana cara menggunakannya. Dan di bagian postingan ini, saya akan berbagi dengan Anda bagaimana dan di mana Anda dapat menggunakannya. Jadi, mari kita masuk.

1. Cukup di spreadsheet

Mengapa kami membuat fungsi khusus? Sederhana. Untuk menggunakannya di spreadsheet. Anda cukup memasukkan UDF ke dalam spreadsheet menggunakan tanda sama dengan dan nama jenis fungsi, lalu tentukan argumennya.

Anda juga dapat memasukkan fungsi yang ditentukan pengguna dari perpustakaan fungsi. Buka tab Rumus ➜ Sisipkan Fungsi ➜ Buatan Pengguna.

Dari daftar ini Anda dapat memilih UDF yang ingin Anda masukkan.

2. Menggunakan subprosedur dan fungsi lainnya

Anda juga dapat menggunakan suatu fungsi di dalam fungsi lain atau dalam prosedur “Sub”. Di bawah ini adalah kode VBA di mana Anda menggunakan fungsi tersebut untuk mendapatkan nama hari untuk tanggal saat ini.

 Sub todayDay() MsgBox "Today is " & myDayName(Date) End Sub

Pastikan untuk membaca “Cakupan UDF” nanti di artikel ini untuk mempelajari lebih lanjut tentang penggunaan fungsi dalam prosedur lain.

3. Akses fungsi dari buku kerja lain

Jika Anda memiliki UDF di satu buku kerja dan ingin menggunakannya di buku kerja lain atau semua buku kerja, Anda melakukannya dengan membuat add-in untuknya. Ikuti langkah-langkah sederhana ini:

• Pertama, Anda perlu menyimpan file (di mana Anda memiliki kode fungsi khusus) sebagai add-in.
• Untuk melakukan ini, buka tab File ➜ Simpan sebagai ➜ “Excel Add-Ins (.xalm).

• Setelah itu, klik dua kali pada add-in tersebut dan instal.

Itu dia. Anda sekarang dapat menggunakan semua fungsi VBA Anda di buku kerja mana pun.

Berbagai cara untuk membuat fungsi VBA khusus [tingkat lanjutan]

Pada titik ini, Anda sudah mengetahui cara membuat fungsi kustom di VBA. Namun masalahnya adalah ketika kita menggunakan fungsi bawaan, fungsi tersebut memiliki jenis argumen yang berbeda.

Jadi, di bagian panduan ini, Anda akan mempelajari cara membuat UDF dengan tipe argumen berbeda.

• Tanpa argumen apa pun
• Dengan satu argumen
• Dengan beberapa argumen
• Menggunakan array sebagai argumen

… bergerak kedepan.

1. Tanpa argumen apapun

Ingat fungsi seperti SEKARANG dan HARI INI di mana Anda tidak perlu memasukkan argumen?

Ya. Anda dapat membuat fungsi yang ditentukan pengguna di mana Anda tidak perlu memasukkan argumen apa pun. Mari kita lakukan dengan sebuah contoh:

Mari buat fungsi khusus yang dapat mengembalikan lokasi file saat ini. Dan ini kodenya:

 Function myPath() As String Dim myLocation As String Dim myName As String myLocation = ActiveWorkbook.FullName myName = ActiveWorkbook.Name If myLocation = myName Then myPath = "File is not saved yet." Else myPath = myLocation End If End Function

Fungsi ini mengembalikan jalur lokasi penyimpanan file saat ini dan jika buku kerja tidak disimpan di mana pun, maka akan muncul pesan yang mengatakan “File belum disimpan”.

Sekarang, jika Anda memperhatikan kode untuk fungsi ini, Anda tidak perlu mendefinisikan argumen (dalam tanda kurung). Anda baru saja menentukan tipe data untuk hasil fungsi.

Aturan dasar untuk membuat fungsi tanpa argumen adalah kode di mana Anda tidak perlu mengetikkan apa pun.

Sederhananya, nilai yang ingin Anda peroleh kembali dari fungsi tersebut harus dihitung secara otomatis.

Dan dalam fungsi ini Anda memiliki hal yang sama.

ActiveWorkbook.FullName ini mengembalikan lokasi file dan ActiveWorkbook.Name ini mengembalikan namanya. Anda tidak perlu memasukkan apa pun.

2. Dengan satu argumen

Kita telah membahas hal ini dengan mempelajari cara membuat fungsi yang ditentukan pengguna. Namun mari kita gali lebih dalam dan buat fungsi yang berbeda. Ini adalah fungsi yang saya buat beberapa bulan lalu untuk mengekstrak URL dari hyperlink .

 Function giveMeURL(rng As Range) As String On Error Resume Next giveMeURL = rng.Hyperlinks(1).Address End Function

Sekarang dalam fungsi ini Anda hanya memiliki satu argumen.

Saat Anda memasukkannya ke dalam sel dan kemudian menentukan sel tempat Anda memiliki hyperlink, URL hyperlink tersebut akan dikembalikan. Sekarang dalam fungsi ini pekerjaan utama dilakukan oleh:

 rng.Hyperlinks(1).Address

Tapi rng itulah yang perlu Anda tentukan. Ucapkan “Mudah” di bagian komentar jika menurut Anda membuat UDF itu mudah.

3. Dengan beberapa argumentasi

Biasanya, sebagian besar fungsi bawaan Excel memiliki banyak argumen. Jadi, penting bagi Anda mempelajari cara membuat fungsi khusus dengan banyak argumen.

Mari kita ambil contoh: Anda ingin menghapus huruf tertentu dari string teks dan ingin mempertahankan bagian lainnya.

Nah, Anda memiliki fungsi seperti RIGHT dan LEN yang akan Anda gunakan dalam fungsi khusus ini. Tapi di sini kita tidak membutuhkan itu. Yang kita butuhkan hanyalah fungsi khusus menggunakan VBA.

Nah, inilah fungsinya:

 Function removeFirstC(rng As String, cnt As Long) As String removeFirstC = Right(rng, Len(rng) - cnt) End Functio

Oke kalau begitu lihat:

Dalam fungsi ini Anda memiliki dua argumen:

• rng: Dalam argumen ini, Anda harus menentukan sel tempat Anda ingin menghapus karakter pertama teks.
• cnt: Dan dalam argumen Anda perlu menentukan jumlah karakter yang akan dihapus (jika Anda ingin menghapus beberapa karakter dari teks).

Saat Anda memasukkannya ke dalam sel, ini berfungsi seperti di bawah ini:

3.1 Membuat fungsi yang ditentukan pengguna dengan argumen opsional dan wajib

Jika Anda berpikir tentang fungsi yang baru saja kita buat pada contoh di atas di mana Anda memiliki dua argumen berbeda, keduanya diperlukan. Dan jika Anda melewatkan salah satu dari ini, Anda akan mendapatkan kesalahan seperti ini.

Nah kalau dipikir logikanya, fungsi yang kita buat adalah menghilangkan karakter pertama. Namun di sini Anda perlu menentukan jumlah karakter yang akan dihapus. Jadi maksud saya adalah bahwa argumen ini harus bersifat opsional dan harus menjadikannya sebagai default.

Bagaimana menurutmu?

Katakan “Ya” di bagian komentar jika Anda setuju dengan saya mengenai hal ini.

Oke kalau begitu lihat. Untuk menjadikan argumen opsional, cukup tambahkan “Opsional” sebelumnya. Seperti itu:

Namun yang terpenting adalah membuat kode Anda berfungsi dengan atau tanpa nilai argumen ini. Jadi kode baru kita untuk fungsi yang sama akan terlihat seperti ini: Sekarang dalam kode, jika Anda mengabaikan penentuan argumen kedua.

4. Gunakan Array sebagai argumen

Ada beberapa fungsi bawaan yang dapat mengambil argumen array dan Anda juga dapat membuat fungsi VBA khusus untuk melakukan ini.

Mari kita lakukan ini dengan contoh sederhana di mana Anda perlu membuat fungsi di mana Anda menjumlahkan nilai rentang di mana Anda memiliki angka dan teks. Ini dia.

 Function addNumbers(CellRef As Range) Dim Cell As Range For Each Cell In CellRef If IsNumeric(Cell.Value) = True Then Result = Result + Cell.Value End If Next Cell addNumbers = Result End Function

Dalam kode fungsi di atas, kami menggunakan rentang bilangan bulat A1:A10, bukan nilai tunggal atau referensi sel.

Menggunakan loop FOR EACH, ia akan memeriksa setiap sel dalam rentang dan menjumlahkan nilainya jika sel berisi angka.

Cakupan fungsi yang ditentukan pengguna

Secara sederhana, cakupan suatu fungsi berarti apakah fungsi tersebut dapat dipanggil dari prosedur lain atau tidak. FDU dapat memiliki dua jenis cakupan yang berbeda.

1. Penonton

Anda bisa membuat fungsi kustom Anda menjadi publik sehingga Anda bisa memanggilnya di semua lembar kerja di buku kerja. Untuk menjadikan suatu fungsi publik, cukup gunakan kata “Publik”, seperti di bawah ini.

Namun suatu fungsi adalah fungsi publik secara default jika Anda tidak menjadikannya pribadi. Dalam semua contoh yang telah kami bahas, semuanya bersifat publik.

2. Pribadi

Saat Anda menjadikan suatu fungsi pribadi, Anda dapat menggunakannya dalam prosedur di modul yang sama.

Katakanlah jika Anda memiliki UDF di “Module1”, Anda hanya dapat menggunakannya dalam prosedur yang Anda miliki di “Module1”. Dan itu tidak akan muncul dalam daftar fungsi lembar kerja (saat Anda menggunakan tanda = dan mencoba mengetikkan namanya) tetapi Anda masih bisa menggunakannya dengan mengetikkan namanya dan menentukan argumen.

Batasan Fungsi Buatan Pengguna [UDF]

UDF sangat berguna. Tapi mereka terbatas dalam situasi tertentu. Berikut beberapa hal yang saya ingin Anda perhatikan dan ingat saat membuat fungsi khusus di VBA.

• Anda tidak dapat mengedit, menghapus, atau memformat sel dan rentang menggunakan fungsi kustom.
• Juga tidak bisa memindahkan, mengganti nama, menghapus atau menambahkan lembar kerja ke buku kerja.
• Ubah nilai sel lain.
• Itu juga tidak dapat mengubah opsi lingkungan.

Apakah ada perbedaan antara fungsi bawaan dan fungsi buatan pengguna?

Saya senang Anda bertanya. Nah, untuk menjawab pertanyaan tersebut, saya ingin membagikan beberapa poin yang menurut saya penting bagi Anda.

1. Lebih lambat dari fungsi bawaan: Jika Anda membandingkan kecepatan fungsi bawaan dan fungsi VBA, Anda akan menemukan bahwa fungsi bawaan lebih cepat. Alasan di balik ini adalah karena fungsi bawaan ditulis dalam C++ atau FORTRAN.
2. Kesulitan berbagi file: Kita sering berbagi file melalui email dan cloud. Jika Anda menggunakan salah satu fungsi khusus, Anda perlu membagikan file ini dalam format “xlam” agar orang lain juga dapat menggunakan fungsi khusus Anda.

Namun seperti yang saya katakan di atas dalam “Mengapa Anda Harus Membuat Fungsi Kustom Excel”, ada situasi tertentu di mana Anda dapat memilih fungsi VBAcustom.

Kesimpulan

Membuat fungsi yang ditentukan pengguna itu sederhana. Yang perlu Anda lakukan hanyalah menggunakan “Fungsi” sebelum nama untuk mendefinisikannya sebagai fungsi, menambahkan argumen, mengatur tipe data argumen, dan kemudian mengatur tipe data untuk nilai yang dikembalikan.

Pada akhirnya, tambahkan kode untuk menghitung nilai yang ingin Anda dapatkan kembali dari fungsi tersebut. Panduan yang saya bagikan kepada Anda hari ini adalah yang termudah untuk mempelajari cara membuat fungsi khusus di VBA dan saya yakin ini berguna bagi Anda.

Tapi sekarang beritahu aku satu hal.

UDF bermanfaat, bagaimana menurut Anda?

Silakan bagikan pandangan Anda dengan saya di bagian komentar. Saya ingin mendengar pendapat Anda, dan jangan lupa untuk membagikan postingan ini kepada teman-teman Anda, saya yakin mereka akan menghargainya.

Tutorial terkait

• Daftar fungsi VBA – dijelaskan dengan contoh
• Cara menjalankan makro di Excel
• Cara menggunakan pernyataan IF di VBA
• Cara bekerja dengan rentang dan sel di VBA
• Tambahkan lembar baru menggunakan kode VBA