Jika Anda baru memulai VBA , maka Anda harus mulai mempelajari panduan VBA kami untuk pemula^{(VBA guide for beginners)} . 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 ^(VBA)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^{(Advanced VBA Guide For Microsoft Excel)}

Tujuan utama penulisan kode VBA di ^(VBA)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^(Import) data dan lakukan perhitungan
• Hitung^(Calculate) hasil dari pengguna menekan tombol
• Email hasil perhitungan ke seseorang

Dengan tiga contoh ini, Anda seharusnya dapat menulis berbagai kode VBA Excel tingkat lanjut Anda sendiri.^{(Excel VBA)}

Mengimpor Data dan Melakukan Perhitungan^{(Importing Data and Performing Calculations)}

Salah satu hal yang paling umum orang menggunakan Excel untuk 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^(Monday) , Anda dapat mengonfigurasi kode VBA agar berjalan saat pertama kali membuka spreadsheet pada Selasa^(Tuesday) pagi.

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

Dim ws As Worksheet, strFile As String

Set 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 ^{(Excel VBA)}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^(With) 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^(taxes) .
2. Ulangi kolom unit terjual^{(units sold)} dan hitung pajak penjualan.
3. Tulis hasil penghitungan ke baris yang sesuai di lembar.

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, 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 , itu 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^{(Calculate Results From Button Press)}

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^(Control) 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^(Developer) , dan pilih Sisipkan^(Insert) dari grup Kontrol^(Controls) di pita. Pilih tombol tekan ^{(push button)} Kontrol ActiveX^{(ActiveX Control)} 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 Calculate 5% Tax .

Anda akan melihat teks ini tercermin pada tombol itu sendiri. Tutup jendela properti^(properties) , 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 Calculate 7% Tax .

Klik dua kali^{(Double-click)} 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^(Developer) , dan pilih Mode Desain^{(Design Mode)} dari grup Kontrol^(Controls) di pita untuk menonaktifkan Mode Desain^{(Design Mode)} . Ini akan mengaktifkan tombol tekan. 

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

Hasil Perhitungan Email ke Seseorang^{(Email Calculation Results to Someone)}

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

Anda dapat 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^{(Excel CDO)} 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^{(Microsoft CDO for 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^(From) , 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 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")

'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 ^(Gmail)SMTP Gmail^{(Gmail SMTP)} (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.

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.Description <> "" Then MsgBox Err.Description

Catatan^(Note) : 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^{(less secure apps settings page)} dan mengaktifkan fitur ini.

Setelah itu diaktifkan, email Anda akan dikirim. Ini adalah 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.^(VBA)

An Advanced VBA Guide For MS Excel

If уou are just getting started with VBA, then you’ll want to start out studying our VBA guide for beginners. But if you’re a seasoned VBA expert and you’re looking for more advanced things you can do with VBA in Excel, then keep reading.

The ability to use VBA coding in Excel opens up a whole world of automation. You can automate calculations in Excel, pushbuttons, and even send email. There are more possibilities to automate your daily work with VBA than you may realize.

Advanced VBA Guide For Microsoft Excel

The main goal of writing VBA code in Excel is so that you can extract information from a spreadsheet, perform a variety of calculations on it, and then write the results back to the spreadsheet

The following are the most common uses of VBA in Excel.

• Import data and perform calculations
• Calculate results from a user pressing a button
• Email calculation results to someone

With these three examples, you should be able to write a variety of your own advanced Excel VBA code.

Importing Data and Performing Calculations

One of the most common things people use Excel for is performing calculations on data that exists outside of Excel. If you don’t use VBA, that means you have to manually import the data, run the calculations and output those values to another sheet or report.

With VBA, you can automate the entire process. For example, if you have a new CSV file downloaded into a directory on your computer every Monday, you can configure your VBA code to run when you first open your spreadsheet on Tuesday morning.

The following import code will run and import the CSV file into your Excel spreadsheet.

Dim ws As Worksheet, strFile As String

Set 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

Open the Excel VBA editing tool and select the Sheet1 object. From the object and method dropdown boxes, choose Worksheet and Activate. This will run the code every time you open the spreadsheet.

This will create a Sub Worksheet_Activate() function. Paste the code above into that function.

This sets the active worksheet to Sheet1, clears the sheet, connects to the file using the file path you defined with the strFile variable, and then the With loop cycles through every line in the file and places the data into the sheet starting at cell A1.

If you run this code, you’ll see that the CSV file data is imported into your blank spreadsheet, in Sheet1.

Importing is only the first step. Next, you want to create a new header for the column that will contain your calculation results. In this example, let’s say you want to calculate the 5% taxes paid on the sale of each item.

The order of actions your code should take is:

1. Create new results column called taxes.
2. Loop through the units sold column and calculate the sales tax.
3. Write the calculate results to the appropriate row in the sheet.

The following code will accomplish all of these steps.

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

This code finds the last row in your sheet of data, and then sets the range of cells (the column with the sales prices) according to the first and last row of data. Then the code loops through each of those cells, performs the tax calculation and writes the results into your new column (column 5).

Paste the above VBA code below the previous code, and run the script. You will see the results show up in column E.

Now, every time you open your Excel worksheet, it’ll automatically go out and get the freshest copy of data from the CSV file. Then, it will perform the calculations and write the results to the sheet. You don’t have to do anything manually anymore!

Calculate Results From Button Press

If you’d rather have more direct control over when calculations run, rather than running automatically when the sheet opens, you can use a control button instead.

Control buttons are useful if you want to control which calculations are used. For example, in this same case as above, what if you want to use a 5% tax rate for one region, and a 7% tax rate for another?

You could allow the same CSV import code to run automatically, but leave the tax calculation code to run when you press the appropriate button.

Using the same spreadsheet as above, select the Developer tab, and select Insert from the Controls group in the ribbon. Select the push button ActiveX Control from the dropdown menu.

Draw the pushbutton onto any part of the sheet away from where any data will go.

Right-click the push button, and select Properties. In the Properties window, change the Caption to what you’d like to display to the user. In this case it might be Calculate 5% Tax.

You’ll see this text reflected on the push button itself. Close the properties window, and double-click the pushbutton itself. This will open the code editor window, and your cursor will be inside the function that will run when the user presses the pushbutton.

Paste the tax calculation code from the section above into this function, keeping the tax rate multiplier at 0.05. Remember to include the following 2 lines to define the active sheet.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Now, repeat the process again, creating a second push button. Make the caption Calculate 7% Tax.

Double-click that button and paste the same code, but make the tax multiplier 0.07.

Now, depending which button you press, the taxes column will be calculated accordingly.

Once you’re done, you’ll have both push buttons on your sheet. Each of them will initiate a different tax calculation and will write different results into the result column. 

To text this, select the Developer menu, and select Design Mode form the Controls group in the ribbon to disable Design Mode. This will activate the push buttons. 

Try selecting each push button to see how the “taxes” result column changes.

Email Calculation Results to Someone

What if you want to send the results on the spreadsheet to someone via email?

You could create another button called Email Sheet to Boss using the same procedure above. The code for this button will involve using the Excel CDO object to configure SMTP email settings, and emailing the results in a user-readable format.

To enable this feature, you need to select Tools and References. Scroll down to Microsoft CDO for Windows 2000 Library, enable it, and select OK.

There are three main sections to the code you need to create to send out an email and embed spreadsheet results.

The first is setting up variables to hold the subject, To and From addresses, and the email body.

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."

Of course, the body needs to be dynamic depending on what results are in the sheet, so here you’ll need to add a loop that goes through the range, extracts the data, and writes a line at a time to the body.

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

The next section involves setting up the SMTP settings so that you can send email through your SMTP server. If you use Gmail, this is typically your Gmail email address, your Gmail password, and the Gmail SMTP server (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

Replace [email protected] and password with your own account details.

Finally, to initiate the email send, insert the following code.

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

Note: If you see a transport error when trying to run this code, it’s likely because your Google account is blocking “less secure apps” from running. You’ll need to visit the less secure apps settings page and turn this feature ON.

After that’s enabled, your email will be sent. This is what it looks like to the person who receives your automatically generated results email.

As you can see there is a lot you can actually automate with Excel VBA. Try playing around with the code snippets you’ve learned about in this article and create your own unique VBA automations.

Related posts

• Panduan VBA Terbaik (Untuk Pemula) yang Anda Butuhkan

• Cara Membuat Makro atau Script VBA Di Excel

• Panduan Untuk Semua Ekstensi File Excel & Apa Artinya

• Cara Menghapus Baris Kosong di Excel

• Perbaiki Pesan Kesalahan "Tidak Dapat Memverifikasi Lisensi" MS Office

• Cara Menghapus Garis Kisi di Excel

• 4 Cara Menggunakan Tanda Centang di Excel

• Cara Menggunakan Pernyataan If dan Nested If di Excel

• Cara Memasukkan CSV atau TSV Ke dalam Lembar Kerja Excel

• Cara Mengurutkan Berdasarkan Tanggal di Excel

• Pusatkan Data Lembar Kerja Anda di Excel untuk Pencetakan

• Gunakan Nama Rentang Dinamis di Excel untuk Dropdown Fleksibel

• Penyortiran Data Satu Kolom dan Multi-Kolom Dasar di Spreadsheet Excel

• Apa itu Array VBA di Excel dan Bagaimana Memprogram Satu

• Cara Menggunakan Fitur Sel Bicara Excel

• Cara Memindahkan Kolom Di Excel

• Cara Menghitung Z-Score di Excel

• 2 Cara Menggunakan Fungsi Transpose Excel

• 3 Cara Memisahkan Sel Di Excel

• Mengapa Anda Harus Menggunakan Rentang Bernama di Excel