Daftar Isi:
- Contoh fungsi lembar kerja
- Memasuki fungsi lembar kerja
- Lebih lanjut tentang menggunakan fungsi lembar kerja
Video: Cara Membuat Filter Data dengan VBA/Macro Excel 2024
Meskipun VBA menawarkan berbagai fungsi built-in yang layak, Anda mungkin tidak selalu menemukan apa yang Anda butuhkan. Untungnya, Anda juga dapat menggunakan sebagian besar fungsi lembar kerja Excel dalam prosedur VBA Anda. Satu-satunya fungsi lembar kerja yang tidak dapat Anda gunakan adalah fungsi VBA yang setara. Misalnya, Anda tidak dapat menggunakan fungsi RAND Excel (yang menghasilkan nomor acak) karena VBA memiliki fungsi setara: Rnd.
VBA membuat fungsi lembar kerja Excel tersedia melalui objek WorksheetFunction, yang terdapat dalam objek Aplikasi. Berikut adalah contoh bagaimana Anda dapat menggunakan fungsi SUM Excel dalam sebuah pernyataan VBA:
Total = Application. Lembar kerjaFungsi SUM (Range ("A1: A12"))
Anda dapat menghilangkan bagian Application atau WorksheetFunction bagian dari ekspresi. Bagaimanapun, VBA tahu apa yang sedang Anda lakukan. Dengan kata lain, ketiga ungkapan ini semua sama persisnya:
Total = Aplikasi Lembar kerjaFungsi SUM (Range ("A1: A12")) Total = WorksheetFunction. SUM (Range ("A1: A12")) Total = Aplikasi. SUM (Range ("A1: A12"))
Preferensi pribadi saya adalah menggunakan bagian WorksheetFunction hanya untuk membuatnya sangat jelas bahwa kode tersebut menggunakan fungsi Excel.
Contoh fungsi lembar kerja
Di sini, Anda menemukan cara menggunakan fungsi lembar kerja dalam ekspresi VBA Anda.
Menemukan nilai maksimum dalam kisaran
Berikut adalah contoh yang menunjukkan bagaimana menggunakan fungsi lembar kerja MAX Excel dalam prosedur VBA. Prosedur ini menampilkan nilai maksimum pada kolom A dari worksheet aktif:
Sub ShowMax () Dim TheMax Sebagai Double TheMax = WorksheetFunction. MAX (Range ("A: A")) MsgBox TheMax End Sub
Anda dapat menggunakan fungsi MIN untuk mendapatkan nilai terkecil dalam kisaran tertentu. Dan seperti yang Anda duga, Anda dapat menggunakan fungsi lembar kerja lainnya dengan cara yang sama. Misalnya, Anda dapat menggunakan fungsi LARGE untuk menentukan nilai k yang terbesar di kisaran. Ekspresi berikut menunjukkan hal ini:
SecondHighest = WorksheetFunction. BESAR (Rentang ("A: A"), 2)
Perhatikan bahwa fungsi LARGE menggunakan dua argumen. Argumen kedua mewakili bagian k - 2, dalam kasus ini (nilai kedua terbesar).
Menghitung pembayaran hipotek
Contoh berikut menggunakan fungsi lembar kerja PMT untuk menghitung pembayaran hipotek. Tiga variabel digunakan untuk menyimpan data yang dilewatkan ke fungsi Pmt sebagai argumen.Kotak pesan menampilkan pembayaran yang dihitung.
Sub PmtCalc () Dim IntRate Sebagai Double Dim LoanAmt Sebagai Periode Double Dim As Long IntRate = 0. 0625/12 Periods = 30 * 12 LoanAmt = 150000 MsgBox WorksheetFunction. PMT (IntRate, Periods, -LoanAmt) End Sub
Seperti yang ditunjukkan oleh pernyataan berikut, Anda juga dapat memasukkan nilai secara langsung sebagai argumen fungsi:
Lembar Kerja Lembar Kerja MsgBox. PMT (0 0625/12, 360, -150000)
Namun, dengan menggunakan variabel untuk menyimpan parameter membuat kode lebih mudah dibaca dan dimodifikasi, jika perlu.
Menggunakan fungsi pencarian
Contoh berikut menggunakan fungsi VBA's InputBox dan MsgBox, ditambah fungsi Excel VLOOKUP. Ini meminta nomor bagian dan kemudian mendapatkan harga dari tabel pencarian. Di bawah, kisaran A1: B13 diberi nama PriceList.
Sub GetPrice () Dim PartNum Sebagai Variant Dim Price Sebagai Double PartNum = InputBox ("Enter the Part Number") Sheets ("Prices"). Aktifkan Harga = Lembar kerjaFungsi. VLOOKUP (PartNum, Range ("PriceList"), 2, False) MsgBox PartNum & "costs" & Price End Sub
Inilah cara kerja GetPrice:
-
Fungsi VBA's InputBox meminta pengguna untuk nomor bagian.
-
Nomor komponen yang dimasukkan pengguna diberikan ke variabel PartNum.
-
Pernyataan selanjutnya mengaktifkan lembar kerja Price, kalau-kalau itu bukan lembar aktif.
-
Kode menggunakan fungsi VLOOKUP untuk menemukan nomor bagian dalam tabel.
-
Perhatikan bahwa argumen yang Anda gunakan dalam pernyataan ini sama dengan yang akan Anda gunakan dengan fungsi dalam formula lembar kerja. Pernyataan ini memberikan hasil fungsi pada variabel Price.
-
Kode menampilkan harga untuk bagian tersebut melalui fungsi MsgBox.
Prosedur ini tidak memiliki penanganan kesalahan, dan gagal total jika Anda memasukkan nomor komponen yang tidak ada. (Coba saja.) Jika ini adalah aplikasi aktual yang digunakan dalam bisnis yang sebenarnya, Anda ingin menambahkan beberapa pernyataan yang menangani kesalahan dengan lebih anggun.
Memasuki fungsi lembar kerja
Anda tidak dapat menggunakan kotak dialog Excel Paste Function untuk memasukkan fungsi lembar kerja ke modul VBA. Sebagai gantinya, masukkan fungsi seperti itu dengan cara kuno: dengan tangan. Namun, Anda dapat menggunakan kotak dialog Paste Function untuk mengidentifikasi fungsi yang ingin Anda gunakan dan mencari tahu tentang argumennya.
Anda juga dapat memanfaatkan opsi Daftar Anggota Auto VBE, yang menampilkan daftar drop-down dari semua fungsi lembar kerja. Ketik saja Aplikasi. WorksheetFunction , diikuti oleh sebuah periode. Kemudian Anda melihat daftar fungsi yang bisa Anda gunakan. Jika fitur ini tidak berfungsi, pilih perintah VBE's Tools → Options, klik tab Editor, dan beri tanda centang di sebelah Auto List Members.
Lebih lanjut tentang menggunakan fungsi lembar kerja
Pendatang baru ke VBA sering membingungkan fungsi built-in VBA dan fungsi buku kerja Excel. Aturan yang baik untuk diingat adalah bahwa VBA tidak mencoba menemukan kembali kemudi.Untuk sebagian besar, VBA tidak menduplikat fungsi worksheet Excel.
Untuk sebagian besar fungsi lembar kerja yang tidak tersedia sebagai metode dari objek WorksheetFunction, Anda dapat menggunakan operator atau fungsi built-in VBA yang setara. Sebagai contoh, fungsi lembar kerja MOD tidak tersedia di objek WorksheetFunction karena VBA memiliki persamaan: operator Mod built-in-nya.
Intinya? Jika Anda perlu menggunakan sebuah fungsi, tentukan dulu apakah VBA memiliki sesuatu yang sesuai dengan kebutuhan Anda. Jika tidak, periksa fungsi lembar kerja. Jika semuanya gagal, Anda mungkin bisa menulis fungsi kustom dengan menggunakan VBA.
