Masih membahas seputar kode UDF, kali ini kami akan menjelaskan kegunaan suatu statement dalam UDF sebagai solusi dalam mengatasi permasalahan tertentu yaitu dengan menyertakan statement Application.Volatile pada UDF.
Contoh kasus pertama yang lebih mudah dipahami adalah jika Anda membuat suatu bilangan acak dalam sel namun setelah dihasilkan, bilangan tersebut jangan sampai berubah dulu dengan sendirinya hingga Anda memutuskan sendiri kapan bilangan tersebut berubah.
Seperti yang Anda ketahui, fungsi RAND atau RANDBETWEEN dalam Excel meskipun dapat menghasilkan bilangan acak, namun bilangan tersebut dengan sendirinya akan berubah terus menerus ketika sel mana saja dilakukan pengeditan karena worksheet melakukan rekalkulasi.
Untuk menghasilkan bilangan acak (tidak bergerak atau statis) misalnya antara 1 sampai 100 maka Anda bisa menyusun kode UDF berikut pada module standar dalam VBE:
AcakStatis = Int(Rnd() * 100)
End Function
Sehingga di dalam worksheet Anda masukkan =AcakStatis() pada sel mana saja tanpa perlu menuliskan argumen.
Kini UDF AcakStatis menghasilkan bilangan acak yang tidak akan berubah kecuali jika Anda mengedit sel tersebut dengan cara menekan tombol F2 lalu menekan Enter, atau jika Anda memaksa melakukan rekalkulasi pada seluruh sel dengan cara menekan tombol Ctrl+Alt+F9.
Lalu untuk apa statement Application.Volatile pada UDF tersebut?
Jika Anda ingin membuat UDF bekerja sebagaimana fungsi bawaan Excel yaitu RAND atau RANDBETWEEN yang berubah-ubah di saat rumus worksheet melakukan rekalkulasi atau sel berisi UDF dilakukan pengeditan, maka Anda bisa menyisipkan statement Application.Volatile ke dalam UDF sehingga menjadi:
Application.Volatile
AcakStatis = Int(Rnd() * 100)
End Function
Perlu diingat bahwa jika UDF tersebut digunakan pada sel-sel dengan jumlah yang cukup besar, maka kemungkinan akan menyita banyak waktu rekalkulasi yang dihasilkan Excel.
Contoh kasus kedua adalah jika Anda menghasilkan nama worksheet yang sedang aktif atau nama workbook yang aktif ke dalam suatu sel.
Cara menghasilkan nama worksheet ternyata tidaklah semudah yang dibayangkan, rumus berikut membuktikan bahwa rumus untuk menyelesaikan tugas semacam ini memang sangat sulit untuk diingat:
=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;32)
Meskipun rumus secara otomatis memperbarui nama yang dihasilkan ketika nama pada tab sheet diganti, workbook harus disimpan terlebih dahulu menjadi sebuah file Excel atau rumus akan menghasilkan error berupa #VALUE!.
Jika Anda ingin agar nama sheet tetap diperbarui pada saat tab sheet tersebut diganti, sekaligus workbook tidak perlu disimpan menjadi sebuah file maka UDF adalah solusi yang sangat tepat.
Manfaat lain yang didapatkan dari UDF adalah rumus yang Anda buat jadi lebih singkat dan lebih mudah diingat.
Kode berikut menunjukkan UDF dengan statement Application.Volatile:
Application.Volatile
NamaSh = ActiveSheet.Name
End Function
Jadi di dalam worksheet, Anda cukup mengetik rumus =NamaSh().
Untuk menghasilkan nama workbook yang sedang digunakan, jika Anda menggunakan rumus bawaan Excel ternyata lebih sulit lagi karena susunannya lebih panjang:
=MID(CELL("filename";A1);FIND("[";CELL("filename";A1))+1;FIND("]";CELL("filename";A1))-FIND("[";CELL("filename";A1))-1)
Sama juga seperti sebelumnya, workbook harus disimpan terlebih dahulu karena jika tidak, maka akan menghasilkan nilai error.
Solusi yang tepat adalah menggunakan UDF karena susunan rumus lebih singkat dan dapat menjalankan perintah meskipun workbook tidak disimpan ke dalam komputer sebagai file terlebih dahulu.
Application.Volatile
NamaWB = ActiveWorkbook.Name
End Function
Anda cukup mengetik rumus =NamaWB() dalam worksheet.
Itulah penjelasan mengenai fungsi volatile dalam kode UDF Macro VBA Excel.
Semoga postingan ini bermanfaat.
0 Comments