Panduan VBA Tingkat Lanjut Untuk MS Excel

Jika Anda baru memulai dengan VBA, maka Anda harus mulai mempelajari panduan VBA kita untuk pemula. Tetapi jika Anda seorang ahli VBA berpengalaman dan Anda sedang mencari hal-hal yang lebih canggih yang dapat Anda lakukan dengan VBA di Excel, maka teruslah membaca.

Kemampuan untuk menggunakan pengkodean VBA di Excel membuka seluruh dunia otomatisasi. Anda dapat mengotomatiskan penghitungan di Excel, tombol tekan, dan bahkan mengirim email. Ada lebih banyak kemungkinan untuk mengotomatisasi pekerjaan sehari-hari Anda dengan VBA daripada yang mungkin Anda sadari.

Panduan VBA Lanjutan Untuk Microsoft Excel

Tujuan utama penulisan kode VBA di Excel adalah agar Anda dapat mengekstrak informasi dari spreadsheet, melakukan berbagai perhitungan di atasnya, dan kemudian menulis hasilnya kembali ke spreadsheet

Berikut ini adalah penggunaan VBA yang paling umum di Excel.

  • Impor data dan lakukan perhitungan
  • Hitung hasil dari pengguna yang menekan tombol
  • Email hasil perhitungan ke seseorang

Dengan tiga contoh ini, Anda seharusnya dapat menulis berbagai kode VBA Excel tingkat lanjut Anda sendiri.

Mengimpor Data dan Melakukan Perhitungan

Salah satu hal paling umum yang digunakan orang untuk menggunakan Excel adalah melakukan perhitungan pada data yang ada di luar Excel. Jika Anda tidak menggunakan VBA, itu berarti Anda harus mengimpor data secara manual, menjalankan penghitungan, dan mengeluarkan nilai tersebut ke lembar atau laporan lain.

Dengan VBA, Anda dapat mengotomatiskan seluruh proses. Misalnya, jika Anda memiliki file CSV baru yang diunduh ke direktori di komputer Anda setiap hari Senin, Anda dapat mengonfigurasi kode VBA untuk dijalankan saat pertama kali membuka spreadsheet pada Selasa pagi.

Kode impor berikut akan menjalankan dan mengimpor file CSV ke dalam spreadsheet Excel Anda.

Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets(“Sheet1″) Cells.ClearContents strFile = “c:temppurchases.csv” Dengan ws.QueryTables.Add(Connection:=”TEXT;” & strFile, Destination:=ws.Range(“A1”)).TextFileParseType = xlDelimited.TextFileCommaDelimiter = True.Refresh Akhiri Dengan

Buka alat pengeditan Excel VBA dan pilih objek Sheet1. Dari kotak dropdown objek dan metode, pilih Worksheet dan Activate. Ini akan menjalankan kode setiap kali Anda membuka spreadsheet.

Ini akan membuat fungsi Sub Worksheet_Activate(). Rekatkan kode di atas ke dalam fungsi itu.

Ini menetapkan lembar kerja aktif ke Sheet1, membersihkan lembar, menghubungkan ke file menggunakan jalur file yang Anda tetapkan dengan variabel strFile, dan kemudian loop Dengan siklus melalui setiap baris dalam file dan menempatkan data ke dalam lembar mulai dari sel A1.

Jika Anda menjalankan kode ini, Anda akan melihat bahwa data file CSV diimpor ke spreadsheet kosong Anda, di Sheet1.

Mengimpor hanyalah langkah pertama. Selanjutnya, Anda ingin membuat header baru untuk kolom yang akan berisi hasil perhitungan Anda. Dalam contoh ini, katakanlah Anda ingin menghitung pajak 5% yang dibayarkan atas penjualan setiap barang.

Urutan tindakan yang harus dilakukan kode Anda adalah:

  1. Buat kolom hasil baru bernama pajak.
  2. Ulangi kolom unit terjual dan hitung pajak penjualan.
  3. Tulis hasil penghitungan ke baris yang sesuai di lembar.

Kode berikut akan menyelesaikan semua langkah ini.

Redupkan LastRow As Long Redup StartCell As Range Redupkan rowCounter As Integer Dim rng As Range, cell As Range Dim fltTax As Double Set StartCell = Range(“A1”) ‘Temukan Baris Terakhir dan Kolom LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4)) rowCounter = 2 Sel(1, 5) = “pajak” Untuk Setiap sel Dalam rng fltTax = sel.Nilai * 0,05 Sel(rowCounter, 5) = fltTax rowCounter = rowCounter + 1 Sel berikutnya

Kode ini menemukan baris terakhir di lembar data Anda, lalu menetapkan rentang sel (kolom dengan harga jual) menurut baris data pertama dan terakhir. Kemudian kode tersebut melewati setiap sel tersebut, melakukan penghitungan pajak dan menuliskan hasilnya ke dalam kolom baru Anda (kolom 5).

Rekatkan kode VBA di atas di bawah kode sebelumnya, dan jalankan skripnya. Anda akan melihat hasilnya muncul di kolom E.

Sekarang, setiap kali Anda membuka lembar kerja Excel Anda, lembar kerja tersebut akan otomatis keluar dan mendapatkan salinan data terbaru dari file CSV. Kemudian, ia akan melakukan perhitungan dan menulis hasilnya ke lembar. Anda tidak perlu melakukan apa pun secara manual lagi!

Hitung Hasil Dari Tekan Tombol

Jika Anda lebih suka memiliki kontrol lebih langsung saat penghitungan berjalan, daripada berjalan secara otomatis saat lembar terbuka, Anda bisa menggunakan tombol kontrol sebagai gantinya.

Tombol kontrol berguna jika Anda ingin mengontrol perhitungan mana yang digunakan. Misalnya, dalam kasus yang sama seperti di atas, bagaimana jika Anda ingin menggunakan tarif pajak 5% untuk satu wilayah, dan tarif pajak 7% untuk wilayah lain?

Anda dapat mengizinkan kode impor CSV yang sama berjalan secara otomatis, tetapi biarkan kode penghitungan pajak berjalan saat Anda menekan tombol yang sesuai.

Menggunakan spreadsheet yang sama seperti di atas, pilih tab Pengembang, dan pilih Sisipkan dari grup Kontrol di pita. Pilih tombol tekan Kontrol ActiveX dari menu tarik-turun.

Gambar tombol tekan ke bagian mana pun dari lembar jauh dari tempat data apa pun akan pergi.

Klik kanan tombol tekan, dan pilih Properties. Di jendela Properties, ubah Caption menjadi apa yang ingin Anda tampilkan kepada pengguna. Dalam hal ini mungkin Hitung Pajak 5%.

Anda akan melihat teks ini tercermin pada tombol itu sendiri. Tutup jendela properti, dan klik dua kali tombol itu sendiri. Ini akan membuka jendela editor kode, dan kursor Anda akan berada di dalam fungsi yang akan berjalan saat pengguna menekan tombol.

Tempelkan kode penghitungan pajak dari bagian di atas ke dalam fungsi ini, dengan menjaga pengganda tarif pajak pada 0,05. Ingatlah untuk menyertakan 2 baris berikut untuk menentukan lembar aktif.

Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets(“Sheet1”)

Sekarang, ulangi prosesnya lagi, buat tombol tekan kedua. Buat caption Hitung Pajak 7%.

Klik dua kali tombol itu dan rekatkan kode yang sama, tetapi buat pengganda pajak 0,07.

Sekarang, tergantung tombol mana yang Anda tekan, kolom pajak akan dihitung sesuai dengan itu.

Setelah selesai, Anda akan memiliki kedua tombol tekan di lembar Anda. Masing-masing akan memulai penghitungan pajak yang berbeda dan akan menuliskan hasil yang berbeda ke dalam
kolom hasil.

Untuk mengirim teks ini, pilih menu Pengembang, dan pilih Mode Desain dari grup Kontrol di pita untuk menonaktifkan Mode Desain. Ini akan mengaktifkan tombol tekan.

Coba pilih setiap tombol tekan untuk melihat bagaimana kolom hasil “pajak” berubah.

Hasil Perhitungan Email ke Seseorang

Bagaimana jika Anda ingin mengirimkan hasil pada spreadsheet kepada seseorang melalui email?

Anda bisa membuat tombol lain yang disebut Email Sheet to Boss menggunakan prosedur yang sama di atas. Kode untuk tombol ini akan melibatkan penggunaan objek CDO Excel untuk mengonfigurasi pengaturan email SMTP, dan mengirimkan hasilnya melalui email dalam format yang dapat dibaca pengguna.

Untuk mengaktifkan fitur ini, Anda harus memilih Tools and References. Gulir ke bawah ke Microsoft CDO untuk Windows 2000 Library, aktifkan, dan pilih OK.

Ada tiga bagian utama pada kode yang perlu Anda buat untuk mengirim email dan menyematkan hasil spreadsheet.

Yang pertama adalah menyiapkan variabel untuk menampung subjek, alamat Ke dan Dari, dan badan email.

Redup CDO_Mail Sebagai Obyek Redup CDO_Config As Object Redup SMTP_Config Sebagai Varian Redup strSubject As String Redup strDari As String Redup strKe As String Redup strCc As String Redup strBcc As String Redup strBody As String Redup LastRow Selama Redup StartCell As Range Redup rowCounter As Inng Sebagai Rentang, sel Sebagai Rentang Redup fltTax Sebagai Ganda

Tetapkan ws = ActiveWorkbook.Sheets(“Sheet1”) strSubject = “Pajak Dibayar Kuartal Ini” strFrom = “MyEmail@gmail.com” strTo = “BossEmail@gmail.com” strCc = “” strBcc = “” strBody = “Berikut ini adalah rincian pajak yang dibayarkan atas penjualan kuartal ini.”

Tentu saja, tubuh harus dinamis tergantung pada hasil apa yang ada di lembar, jadi di sini Anda perlu menambahkan loop yang melewati rentang, mengekstrak data, dan menulis baris pada satu waktu ke tubuh.

Set StartCell = Range(“A1”) ‘Temukan Baris Terakhir dan Kolom LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(Baris Terakhir, 4)) rowCounter = 2 strBody = strBody & vbCrLf Untuk Setiap sel Dalam rng strBody = strBody & vbCrLf strBody = strBody & “We sold ” & Cells(rowCounter, 3).Value & ” dari ” & Sel(Penghitung baris, 1).Nilai _ & ” untuk ” & Sel(Penghitung baris, 4).Nilai & ” dan pajak yang dibayarkan untuk ” & Sel(Penghitung baris, 5).Nilai & “.” rowCounter = rowCounter + 1 Sel berikutnya

Bagian selanjutnya melibatkan pengaturan pengaturan SMTP sehingga Anda dapat mengirim email melalui server SMTP Anda. Jika Anda menggunakan Gmail, ini biasanya alamat email Gmail Anda, kata sandi Gmail Anda, dan server SMTP Gmail (smtp.gmail.com).

Setel CDO_Mail = CreateObject(“CDO.Message”) Pada Kesalahan GoTo Error_Handling Setel CDO_Config = CreateObject(“CDO.Configuration”) CDO_Config.Load -1 Setel SMTP_Config = CDO_Config.Fields Dengan SMTP_Config.Item(“http://schemas.microsoft..com/cdo/configuration/sendusing”) = 2.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”.Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “email@website.com”.Item(“http:/ /schemas.microsoft.com/cdo/configuration/sendpassword”) = “password”.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 465.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True.Update Diakhiri Dengan CDO_Mail Set.Configuration = CDO_Config Diakhiri Dengan

Ganti email@website.com dan kata sandi dengan detail akun Anda sendiri.

Terakhir, untuk memulai pengiriman email, masukkan kode berikut.

CDO_Mail.Subject = strSubject CDO_Mail.From = strFrom CDO_Mail.To = strTo CDO_Mail.TextBody = strBody CDO_Mail.CC = strCc CDO_Mail.BCC = strBcc CDO_Mail.Send Error_Handling: If Err.DescriptionBox “

Catatan : Jika Anda melihat kesalahan transportasi saat mencoba menjalankan kode ini, kemungkinan karena akun Google Anda memblokir “aplikasi yang kurang aman” agar tidak berjalan. Anda harus mengunjungi halaman pengaturan aplikasi yang kurang aman dan mengaktifkan fitur ini.

Setelah itu diaktifkan, email Anda akan dikirim. Seperti inilah tampilan orang yang menerima email hasil yang Anda buat secara otomatis.

Seperti yang Anda lihat, ada banyak hal yang sebenarnya dapat Anda otomatisasi dengan Excel VBA. Cobalah bermain-main dengan cuplikan kode yang telah Anda pelajari di artikel ini dan buat otomatisasi VBA unik Anda sendiri.

Baca Juga

© 2022 Sridianti.com