Prosedur "Fungsi" dan "Sub" di VBA

Fungsi VBA bawaan

Sebelum Anda mulai membuat fungsi VBA Anda sendiri, ada baiknya mengetahui bahwa Excel VBA memiliki banyak koleksi fungsi bawaan bawaan yang dapat Anda gunakan saat menulis kode.

Daftar fungsi ini dapat dilihat di editor VBA:

• Buka buku kerja Excel dan luncurkan editor VBA (klik untuk melakukan ini Alt + F11), lalu tekan F2.
• Pilih perpustakaan dari daftar dropdown di kiri atas layar VBA.
• Daftar kelas dan fungsi VBA bawaan akan muncul. Klik pada nama fungsi untuk menampilkan deskripsi singkatnya di bagian bawah jendela. mendesak F1 akan membuka halaman bantuan online untuk fitur tersebut.

Selain itu, daftar lengkap fungsi VBA bawaan dengan contoh dapat ditemukan di Pusat Pengembang Visual Basic.

Prosedur khusus "Fungsi" dan "Sub" di VBA

Di Excel Visual Basic, satu set perintah yang melakukan tugas tertentu ditempatkan dalam prosedur. fungsi (Fungsi) atau Sub (Subrutin). Perbedaan utama antara prosedur fungsi и Sub apakah itu prosedurnya? fungsi mengembalikan hasil, prosedur Sub - bukan.

Oleh karena itu, jika Anda perlu melakukan tindakan dan mendapatkan beberapa hasil (misalnya, menjumlahkan beberapa angka), maka prosedur biasanya digunakan fungsi, dan untuk hanya melakukan beberapa tindakan (misalnya, mengubah pemformatan sekelompok sel), Anda harus memilih prosedur Sub.

kasus

Berbagai data dapat diteruskan ke prosedur VBA menggunakan argumen. Daftar argumen ditentukan saat mendeklarasikan prosedur. Misalnya prosedur Sub di VBA menambahkan bilangan bulat (Integer) yang diberikan ke setiap sel dalam rentang yang dipilih. Anda dapat meneruskan nomor ini ke prosedur menggunakan argumen, seperti ini:

Sub AddToCells(i As Integer) ... End Sub

Ingatlah bahwa memiliki argumen untuk prosedur fungsi и Sub di VBA adalah opsional. Beberapa prosedur tidak memerlukan argumen.

Argumen Opsional

Prosedur VBA dapat memiliki argumen opsional. Ini adalah argumen yang dapat ditentukan pengguna jika mereka mau, dan jika dihilangkan, prosedur menggunakan nilai default untuk mereka.

Kembali ke contoh sebelumnya, untuk membuat argumen integer ke fungsi opsional, itu akan dideklarasikan seperti ini:

Sub AddToCells(Opsional i As Integer = 0)

Dalam hal ini, argumen bilangan bulat i defaultnya adalah 0.

Mungkin ada beberapa argumen opsional dalam suatu prosedur, yang semuanya terdaftar di akhir daftar argumen.

Melewati argumen berdasarkan nilai dan referensi

Argumen dalam VBA dapat diteruskan ke prosedur dengan dua cara:

• OlehVal – meneruskan argumen berdasarkan nilai. Ini berarti bahwa hanya nilai (yaitu, salinan argumen) yang diteruskan ke prosedur, dan oleh karena itu setiap perubahan yang dibuat pada argumen di dalam prosedur akan hilang saat prosedur keluar.
• OlehRef – menyampaikan argumen dengan referensi. Artinya, alamat sebenarnya dari lokasi argumen di memori diteruskan ke prosedur. Setiap perubahan yang dibuat pada argumen di dalam prosedur akan disimpan saat prosedur keluar.

Menggunakan kata kunci OlehVal or OlehRef dalam deklarasi prosedur, Anda dapat menentukan bagaimana argumen diteruskan ke prosedur. Ini ditunjukkan dalam contoh di bawah ini:

Sub AddToCells(ByVal i As Integer) ... End Sub

Sub AddToCells(ByRef i As Integer) ... End Sub

Ingat bahwa argumen dalam VBA diteruskan dengan referensi secara default. Dengan kata lain, jika kata kunci tidak digunakan OlehVal or OlehRef, maka argumen akan diteruskan dengan referensi.

Sebelum melanjutkan dengan prosedur fungsi и Sub secara lebih rinci, akan berguna untuk melihat kembali fitur dan perbedaan antara kedua jenis prosedur ini. Berikut ini adalah diskusi singkat tentang prosedur VBA fungsi и Sub dan contoh sederhana ditampilkan.

Prosedur VBA «Fungsi»

Editor VBA mengenali prosedurnya fungsiketika menemukan sekelompok perintah yang diapit antara pernyataan pembuka dan penutup berikut:

Fungsi ... Fungsi Akhir

Seperti disebutkan sebelumnya, prosedur fungsi di VBA (sebagai lawan dari Sub) mengembalikan nilai. Aturan berikut berlaku untuk mengembalikan nilai:

• Tipe data dari nilai kembalian harus dideklarasikan di header prosedur fungsi.
• Variabel yang berisi nilai kembalian harus diberi nama yang sama dengan prosedur fungsi. Variabel ini tidak perlu dideklarasikan secara terpisah, karena selalu ada sebagai bagian integral dari prosedur. fungsi.

Ini diilustrasikan dengan baik dalam contoh berikut.

Contoh Fungsi VBA: Melakukan Operasi Matematika pada 3 Angka

Berikut ini adalah contoh kode prosedur VBA fungsi, yang mengambil tiga argumen tipe Dua kali lipat (angka floating-point presisi ganda). Akibatnya, prosedur mengembalikan sejumlah tipe lainnya Dua kali lipatsama dengan jumlah dari dua argumen pertama dikurangi argumen ketiga:

Fungsi SumMinus(dNum1 As Double, dNum2 As Double, dNum3 As Double) As Double SumMinus = dNum1 + dNum2 - dNum3 End Function

Prosedur VBA yang sangat sederhana ini fungsi menggambarkan bagaimana data dilewatkan ke prosedur melalui argumen. Anda dapat melihat bahwa tipe data yang dikembalikan oleh prosedur didefinisikan sebagai Dua kali lipat (kata-kata mengatakan Sebagai Ganda setelah daftar argumen). Contoh ini juga menunjukkan bagaimana hasil dari prosedur fungsi disimpan dalam variabel dengan nama yang sama dengan nama prosedur.

Memanggil prosedur VBA "Fungsi"

Jika prosedur sederhana di atas fungsi dimasukkan ke dalam modul di editor Visual Basic, dapat dipanggil dari prosedur VBA lain atau digunakan pada lembar kerja di buku kerja Excel.

Panggil prosedur VBA "Fungsi" dari prosedur lain

Prosedur fungsi dapat dipanggil dari prosedur VBA lain hanya dengan menetapkan prosedur itu ke variabel. Contoh berikut menunjukkan panggilan ke prosedur kesimpulan, yang didefinisikan di atas.

Sub main() Redup total sebagai Double total = SumMinus(5, 4, 3) End Sub

Panggil prosedur VBA "Fungsi" dari lembar kerja

prosedur VBA fungsi dapat dipanggil dari lembar kerja Excel dengan cara yang sama seperti fungsi Excel bawaan lainnya. Oleh karena itu, prosedur yang dibuat pada contoh sebelumnya fungsi - kesimpulan dapat dipanggil dengan memasukkan ekspresi berikut ke dalam sel lembar kerja:

=SumMinus(10, 5, 2)

Prosedur VBA «Sub»

Editor VBA memahami bahwa ada prosedur di depannya Subketika menemukan sekelompok perintah yang diapit antara pernyataan pembuka dan penutup berikut:

Sub... Akhir Sub

Prosedur VBA "Sub": Contoh 1. Perataan tengah dan perubahan ukuran font dalam rentang sel yang dipilih

Pertimbangkan contoh prosedur VBA sederhana Sub, yang tugasnya adalah mengubah pemformatan rentang sel yang dipilih. Sel dipusatkan (baik secara vertikal maupun horizontal) dan ukuran font diubah ke yang ditentukan pengguna:

Sub Format_Centered_And_Sized(Opsional iFontSize As Integer = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub

Prosedur ini Sub melakukan tindakan tetapi tidak mengembalikan hasil.

Contoh ini juga menggunakan argumen Opsional Ukuran huruf. Jika argumen Ukuran huruf tidak lolos ke prosedur Sub, maka nilai defaultnya adalah 10. Namun, jika argumennya Ukuran huruf diteruskan ke prosedur Sub, maka rentang sel yang dipilih akan diatur ke ukuran font yang ditentukan oleh pengguna.

Sub Prosedur VBA: Contoh 2: Rata Tengah dan Font Tebal dalam Rentang Sel yang Dipilih

Prosedur berikut ini mirip dengan yang baru saja dibahas, tetapi kali ini, alih-alih mengubah ukuran, prosedur ini menerapkan gaya font tebal ke rentang sel yang dipilih. Ini adalah contoh prosedur Sub, yang tidak membutuhkan argumen:

Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub

Memanggil Prosedur "Sub" di Excel VBA

Panggil prosedur VBA "Sub" dari prosedur lain

Untuk memanggil prosedur VBA Sub dari prosedur VBA lain, Anda perlu menulis kata kunci Memanggil, nama prosedur Sub dan selanjutnya dalam tanda kurung adalah argumen dari prosedur. Ini ditunjukkan pada contoh di bawah ini:

Sub main() Panggilan Format_Centered_And_Sized(20) End Sub

Jika prosedur Format_Pusat_Dan_Ukuran memiliki lebih dari satu argumen, mereka harus dipisahkan dengan koma. Seperti ini:

Sub main() Panggilan Format_Centered_And_Sized(arg1, arg2, ...) End Sub

Panggil prosedur VBA "Sub" dari lembar kerja

Prosedur Sub tidak dapat dimasukkan langsung ke dalam sel lembar Excel, seperti yang dapat dilakukan dengan prosedur fungsikarena prosedur Sub tidak mengembalikan nilai. Namun, prosedur Sub, yang tidak memiliki argumen dan dideklarasikan sebagai Publik (seperti yang ditunjukkan di bawah) akan tersedia untuk pengguna lembar kerja. Jadi, jika prosedur sederhana yang dibahas di atas Sub dimasukkan ke dalam modul di Editor Visual Basic, prosedur Format_Pusat_Dan_Tebal akan tersedia untuk digunakan dalam lembar kerja Excel, dan prosedurnya Format_Pusat_Dan_Ukuran – tidak akan tersedia karena memiliki argumen.

Berikut adalah cara mudah untuk menjalankan (atau menjalankan) suatu prosedur Sub, dapat diakses dari lembar kerja:

• Tekan Alt + F8 (tekan tombol lain dan sambil menahannya, tekan tombol F8).
• Dalam daftar makro yang muncul, pilih makro yang ingin Anda jalankan.
• Tekan Run (Lari)

Untuk melakukan prosedur Sub dengan cepat dan mudah, Anda dapat menetapkan pintasan keyboard ke sana. Untuk ini:

• Tekan Alt + F8.
• Dalam daftar makro yang muncul, pilih makro yang ingin Anda tetapkan pintasan keyboardnya.
• Tekan parameter (Opsi) dan di kotak dialog yang muncul, masukkan pintasan keyboard.
• Tekan OK dan tutup dialog Makro (makro).

Perhatian: Saat menetapkan pintasan keyboard ke makro, pastikan pintasan tersebut tidak digunakan sebagai standar di Excel (misalnya, Ctrl + C). Jika Anda memilih pintasan keyboard yang sudah ada, pintasan tersebut akan ditetapkan ulang ke makro, dan akibatnya, pengguna dapat memulai makro secara tidak sengaja.

Lingkup Prosedur VBA

Bagian 2 dari tutorial ini membahas ruang lingkup variabel dan konstanta dan peran kata kunci. Publik и Swasta. Kata kunci ini juga dapat digunakan dengan prosedur VBA:

Sub Publik AddToCells(i As Integer) ... End Sub

Private Sub AddToCells(i As Integer) ... End Sub

Ingat bahwa jika sebelum mendeklarasikan prosedur VBA fungsi or Sub kata kunci tidak dimasukkan, properti default diatur untuk prosedur Publik (yaitu, itu akan tersedia di mana-mana dalam proyek VBA ini). Ini berbeda dengan deklarasi variabel, yang secara default adalah Swasta.

Keluar awal dari prosedur VBA "Fungsi" dan "Sub"

Jika Anda perlu menghentikan eksekusi prosedur VBA fungsi or Sub, tanpa menunggu akhir yang alami, maka untuk ini ada operator Keluar Fungsi и Keluar dari Sub. Penggunaan operator ini ditunjukkan di bawah menggunakan prosedur sederhana sebagai contoh. fungsiA yang mengharapkan untuk menerima argumen positif untuk melakukan operasi lebih lanjut. Jika nilai non-positif diteruskan ke prosedur, maka tidak ada operasi lebih lanjut yang dapat dilakukan, sehingga pengguna harus diperlihatkan pesan kesalahan dan prosedur harus segera keluar:

Fungsi VAT_Amount(sVAT_Rate As Single) As Single VAT_Amount = 0 If sVAT_Rate <= 0 Maka MsgBox "Diharapkan Nilai Positif dari sVAT_Rate tapi Diterima" & sVAT_Rate Exit Function End If ... End Function

Harap dicatat bahwa sebelum menyelesaikan prosedur fungsi - PPN_Jumlah, fungsi VBA bawaan dimasukkan ke dalam kode Kotak Pesan, yang menampilkan popup peringatan kepada pengguna.