Fungsi Lembar Kerja VBA | Bagaimana Cara Menggunakan WorksheetFunction di VBA?

Fungsi Lembar Kerja VBA Excel

Fungsi lembar kerja di VBA digunakan ketika kita harus mengacu pada lembar kerja tertentu, biasanya ketika kita membuat modul kode dijalankan di lembar buku kerja yang saat ini aktif tetapi jika kita ingin mengeksekusi kode di lembar kerja tertentu kita menggunakan fungsi lembar kerja, fungsi ini memiliki berbagai kegunaan dan aplikasi di VBA.

Hal terbaik tentang VBA adalah, seperti bagaimana kami menggunakan rumus di lembar kerja, VBA juga memiliki fungsinya sendiri. Jika ini yang terbaik maka itu memiliki hal yang indah juga yaitu "kita dapat menggunakan fungsi lembar kerja di VBA juga".

Iya!!! Anda tidak salah dengar, kami juga dapat mengakses fungsi lembar kerja di VBA. Kami dapat mengakses beberapa fungsi lembar kerja saat menulis kode dan menjadikannya bagian dari kode kami.

Bagaimana cara menggunakan Fungsi Lembar Kerja di VBA?

Anda dapat mengunduh Templat Fungsi Lembar Kerja VBA ini di sini - Templat Fungsi Lembar Kerja VBA

Di lembar kerja, semua rumus dimulai dengan tanda sama dengan (=), demikian pula dalam pengkodean VBA untuk mengakses rumus lembar kerja kita harus menggunakan kata "WorksheetFunction".

Sebelum Anda memasukkan rumus lembar kerja apa pun, Anda perlu menyebutkan nama objek "WorksheetFunction" lalu memberi titik (.) Maka Anda akan mendapatkan daftar semua fungsi yang tersedia di bawah objek ini.

Pada artikel ini, kami secara eksklusif akan berkonsentrasi pada cara menggunakan fungsi lembar kerja dalam pengkodean VBA yang akan menambah nilai lebih pada pengetahuan pengkodean Anda.

# 1 - Fungsi Lembar Kerja SUM Sederhana

Ok, untuk memulai dengan fungsi lembar kerja, terapkan fungsi SUM sederhana di excel untuk menambahkan angka dari lembar kerja.

Asumsikan Anda memiliki data penjualan & biaya bulanan di lembar kerja seperti di bawah ini.

Di B14 dan C14 kita perlu sampai pada jumlah total angka di atas. Ikuti langkah-langkah di bawah ini untuk memulai proses penerapan fungsi "SUM" di Excel VBA.

Langkah 1: Buat nama makro excel sederhana.

Kode:

 Sub Lembar Kerja_Fungsi_Contoh1 () Akhir Sub

Langkah 2: Karena kita membutuhkan hasil di sel B14, mulailah kode sebagai Range ("B14") Value =

Kode:

 Sub Lembar Kerja_Fungsi_Contoh1 () Rentang ("B14") Nilai = Akhir Sub

Langkah 3: Di B14 kita membutuhkan nilai sebagai hasil dari penjumlahan angka. Jadi untuk mengakses fungsi SUM dari worksheet, mulailah kode sebagai "WorksheetFunction".

Kode:

Sub Worksheet_Function_Example1 () Range ("B14") Nilai = Fungsi Lembar Kerja. Akhiri Sub

Langkah 4: Saat Anda meletakkan titik (.) Itu akan mulai menampilkan fungsi yang tersedia. Jadi pilih SUM dari ini.

Kode:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum End Sub

Langkah 5: Sekarang berikan referensi nomor di atas yaitu Range ("B2: B13").

Kode:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum (Range ("B2: B13")) End Sub

Langkah 6: Demikian pula untuk kolom berikutnya, terapkan kode serupa dengan mengubah referensi sel.

Kode:

 Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum (Range ("B2: B13")) Range ("C14"). Value = WorksheetFunction.Sum (Range ("C2: C13")) End Sub

Langkah 7: Sekarang jalankan kode ini secara manual atau menggunakan tombol F5 untuk memiliki total dalam sel B14 & C14.

Wow, kami mendapatkan nilai kami. Satu hal yang perlu Anda perhatikan di sini adalah kami tidak memiliki rumus apa pun di lembar kerja tetapi kami baru saja mendapatkan hasil dari fungsi "SUM" di VBA.

# 2 - Gunakan VLOOKUP sebagai Fungsi Lembar Kerja

Kami akan melihat cara menggunakan VLOOKUP di VBA. Asumsikan di bawah ini adalah data yang Anda miliki di lembar excel Anda.

Di sel E2 Anda telah membuat daftar drop-down dari semua zona.

Berdasarkan pilihan yang Anda buat di sel E2, kami perlu mengambil Kode Pin untuk zona masing-masing. Tapi kali ini melalui VBA VLOOKUP, bukan lembar kerja VLOOKUP. Ikuti langkah-langkah di bawah ini untuk menerapkan VLOOKUP.

Langkah 1: Buat nama makro sederhana di Sub Prosedur.

Kode:

 Sub Lembar Kerja_Fungsi_Contoh2 () Akhir Sub

Langkah 2: Kami membutuhkan hasilnya di sel F2. Jadi mulailah kode sebagai Range ("F2") Nilai =

Kode:

 Sub Lembar Kerja_Fungsi_Contoh2 () Rentang ("F2") Nilai = Akhir Sub

Langkah 3: Untuk mengakses fungsi lembar kerja VLOOKUP memulai kode sebagai "WorksheetFunction.VLOOKUP".

Kode:

 Sub Worksheet_Function_Example2 () Range ("F2"). Value = WorksheetFunction.Vlookup (End Sub

Langkah 4: Salah satu masalah di sini adalah sintaks tidak akan memberi Anda panduan apa pun untuk bekerja dengan VLOOKUP. Anda harus benar-benar yakin tentang sintaks yang sedang Anda kerjakan.

Sintaks pertama VLOOKUP adalah "Nilai Pencarian". Dalam kasus ini, nilai pencarian kita adalah nilai sel E2, jadi tulis kode sebagai Range ("E2"). Nilai

Kode:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value, End Sub

Step 5: Now the second argument is our table array, in this case, our table array range is from A2 to B6. So the code will be Range (“A2:B6”)

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"), End Sub

Step 6: The Third argument will be from which column we need the data from the table array. Here we need the data from the 2nd column, so the argument will be 2.

Code:

 Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"),2, End Sub

Step 7: The final argument is range lookup, we need an exact match so the argument is zero (0).

Code:

 Sub Worksheet_Function_Example2() Range("F2").Value = WorksheetFunction.VLookup(Range("E2").Value, Range("A2:B6"), 2, 0) End Sub

So, we are done with the coding part. Now go to the worksheet and select any of the range.

Now go to your coding module and run the macro Using F5 key or manually to get the pin code of the selected zone.

We cannot go back and run the macro every time, so let’s assign a macro to shapes. Insert one of the shapes in a worksheet.

Add a text value to inserted shape.

Now right click and assign the macro name to this shape.

Click on ok after selecting the macro name.

Now, this shape holds the code of our VLOOKUP formula. So whenever you change the zone name click on the button, it will update the values.

Things to Remember

To access worksheet functions we need to write the word “WorksheetFunction” or “Application.WorksheetFunction”
We don’t have access to all the functions only a few.
We don’t see the actual syntax of worksheet functions, so we need to be absolutely sure of the function we are using.