Jika Anda baru memulai dengan VBA, maka Anda ingin memulai mempelajari Panduan VBA untuk pemula kami. Tetapi jika Anda adalah seorang ahli VBA berpengalaman dan Anda sedang mencari hal-hal yang lebih maju 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 perhitungan di Excel, tombol, dan bahkan mengirim email. Ada lebih banyak kemungkinan untuk mengotomatiskan pekerjaan harian Anda dengan VBA daripada yang Anda sadari.
Panduan VBA Lanjutan Untuk Microsoft Excel
Tujuan utama penulisan kode VBA di Excel adalah agar Anda dapat mengekstrak informasi dari spreadsheet, lakukan beragam perhitungan di atasnya, lalu tulis hasilnya kembali ke spreadsheet
Berikut ini adalah penggunaan VBA yang paling umum di Excel.
Dengan tiga contoh ini, Anda harus dapat menulis berbagai kode Excel VBA canggih Anda sendiri.
Mengimpor Data dan Melakukan Perhitungan
Salah satu hal paling umum yang digunakan orang untuk menggunakan Excel adalah sedang melakukan perhitungan pada data yang ada di luar Excel. Jika Anda tidak menggunakan VBA, itu artinya Anda harus mengimpor data secara manual, menjalankan perhitungan dan mengeluarkan nilai-nilai itu ke lembar atau laporan lain.
In_content_1 all: [300x250] / dfp : [640x360]->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 Anda untuk dijalankan ketika Anda pertama kali membuka spreadsheet pada hari Selasa pagi.
Kode impor berikut akan jalankan dan impor file CSV ke dalam spreadsheet Excel Anda.
Dim ws As Worksheet, strFile As StringSet ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = “c:\temp\purchases.csv” With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")) .TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Buka alat pengeditan Excel VBA dan pilih objek Sheet1. Dari kotak tarik turun objek dan metode, pilih Lembar Kerjadan Aktifkan. Ini akan menjalankan kode setiap kali Anda membuka spreadsheet.
Ini akan membuat fungsi Sub Lembar Kerja_Aktifkan (). Rekatkan kode di atas ke dalam fungsi itu.
Ini mengatur lembar kerja aktif menjadi Sheet1, menghapus lembar, menghubungkan ke file menggunakan jalur file yang Anda tentukan dengan variabel strFile, dan kemudian Denganlingkaran melalui setiap baris dalam file dan menempatkan data ke dalam sheet 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 tajuk baru untuk kolom yang akan berisi hasil perhitungan Anda. Dalam contoh ini, katakanlah Anda ingin menghitung pajak 5% yang dibayarkan atas penjualan setiap item.
Urutan tindakan yang harus diambil kode Anda adalah:
Kode berikut akan menyelesaikan semua langkah ini.
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double
Set StartCell = Range("A1")
'Find Last Row and Column
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
Cells(1, 5) = "taxes"
For Each cell In rng
fltTax = cell.Value * 0.05
Cells(rowCounter, 5) = fltTax
rowCounter = rowCounter + 1
Next cell
Kode ini menemukan baris terakhir di lembar data Anda, dan lalu tentukan rentang sel (kolom dengan harga penjualan) sesuai dengan baris data pertama dan terakhir. Kemudian, kode akan melewati setiap sel tersebut, melakukan penghitungan pajak, dan menulis hasilnya ke kolom baru Anda (kolom 5).
Tempelkan kode VBA di atas di bawah kode sebelumnya, dan jalankan skrip. Anda akan melihat hasilnya muncul di kolom E.
Sekarang, setiap kali Anda membuka lembar kerja Excel Anda, ia akan secara 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 Tombol Tekan
Jika Anda lebih suka memiliki kontrol langsung lebih saat penghitungan berjalan , alih-alih berjalan secara otomatis saat sheet terbuka, Anda dapat menggunakan tombol kontrol saja.
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 yang lain?
Anda dapat mengizinkan kode impor CSV yang sama untuk jalankan secara otomatis, tetapi biarkan kode perhitungan pajak tetap berjalan ketika Anda menekan tombol yang sesuai.
Menggunakan spreadsheet yang sama seperti di atas, pilih tab Pengembang, dan pilih Sisipkandari grup Kontroldi pita. Pilih tombol pushKontrol ActiveX dari menu dropdown.
Tarik tombol tekan ke bagian mana pun dari lembar dari mana pun data akan pergi.
Klik kanan tombol push, dan pilih Properti. Di jendela Properti, ubah Caption ke apa yang ingin Anda tampilkan kepada pengguna. Dalam hal ini mungkin Hitung Pajak 5%.
Anda akan melihat teks ini tercermin pada tombol push 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 ketika pengguna menekan tombol.
Tempel kode perhitungan pajak dari bagian di atas ke fungsi ini, 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 judulnya Hitung Pajak 7%.
Klik dua kali tombol itu dan tempel kode yang sama, tetapi buat pengganda pajak 0,07.
Sekarang, tergantung tombol mana yang Anda tekan, kolom pajak akan dihitung sesuai.
Setelah selesai, Anda akan memiliki kedua tombol push di lembar Anda. Masing-masing dari mereka akan memulai perhitungan pajak yang berbeda dan akan menulis hasil yang berbeda ke dalam kolom hasil.
Untuk mengirimi pesan ini, pilih menu Pengembang, dan pilih Mode Desaindari grup Kontrol di pita untuk menonaktifkan Mode Desainkuat. Ini akan mengaktifkan tombol push.
Coba pilih setiap tombol untuk melihat bagaimana kolom hasil "pajak" berubah.
Hasil Perhitungan Email ke Seseorang
Apa jika Anda ingin mengirim hasil pada spreadsheet kepada seseorang melalui email?
Anda dapat membuat tombol lain yang disebut Lembar Email ke Bosmenggunakan prosedur yang sama di atas. Kode untuk tombol ini akan melibatkan penggunaan objek CDO Excel untuk mengonfigurasi pengaturan email SMTP, dan mengirim hasilnya melalui format yang dapat dibaca pengguna.
Untuk mengaktifkan fitur ini, Anda harus memilih Alat dan Referensi. Gulir ke bawah ke Microsoft CDO untuk Windows 2000 Library, aktifkan, dan pilih Oke.
Ada tiga bagian utama pada kode yang perlu Anda buat untuk mengirim email dan menyematkan hasil spreadsheet.
Yang pertama adalah mengatur variabel untuk disimpan subjek, alamat Ke dan Dari, dan badan email.
Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double
Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."
Tentu saja, tubuh harus dinamis tergantung pada hasil apa yang dihasilkan di lembar, jadi di sini Anda harus menambahkan lingkaran yang melewati rentang, mengekstrak data, dan menulis garis pada waktu ke tubuh.
Set StartCell = Range("A1")
'Find Last Row and Column
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
strBody = strBody & vbCrLf
For Each cell In rng
strBody = strBody & vbCrLf
strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _
& " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "."
rowCounter = rowCounter + 1
Next cell
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).
Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With 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 protected]"
.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
End With
With CDO_Mail
Set .Configuration = CDO_Config
End With
Ganti [email protected] dan kata sandi dengan detail akun Anda sendiri.
Akhirnya, 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.Description <> "" Then MsgBox Err.Description
Catatan: Jika Anda melihat kesalahan pengangkutan saat mencoba menjalankan kode ini, kemungkinan karena akun Google Anda memblokir "aplikasi yang kurang aman" dari berjalan. Anda harus mengunjungi halaman pengaturan aplikasi kurang aman dan mengaktifkan fitur ini ON.
Setelah itu diaktifkan, email Anda akan dikirim. Seperti inilah penampilan orang yang menerima email hasil yang dihasilkan secara otomatis.
Seperti yang Anda lihat ada banyak hal yang sebenarnya dapat Anda lakukan secara otomatis dengan Excel VBA. Cobalah bermain-main dengan cuplikan kode yang telah Anda pelajari di artikel ini dan buat otomasi VBA unik Anda sendiri.