Panduan VBA Tingkat Lanjut Untuk MS Excel

Panduan VBA Tingkat Lanjut Untuk MS Excel

Panduan VBA Tingkat Lanjut Untuk MS Excel.

 Panduan VBA Tingkat Lanjut Untuk MS Excel



Jika Anda baru memulai dengan VBA, maka Anda harus mulai mempelajari panduan VBA kami 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 perhitungan 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.

Daftar isi


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 perhitungan, 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.

Redupkan Sebagai Lembar Kerja, strFile Sebagai String

Setel ws = ActiveWorkbook.Sheets("Sheet1")

Sel.Hapus Isi


strFile = “c:\temp\pembelian.csv”


Dengan ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))

     .TextFileParseType = xlDelimited

     .TextFileCommaDelimiter = Benar

     .Menyegarkan

Berakhir 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, mengosongkan 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 yang disebut pajak.

 2. Ulangi kolom unit terjual dan hitung pajak penjualan.

 3. Tulis hasil hitung ke baris yang sesuai di lembar.


Kode berikut akan menyelesaikan semua langkah ini.

Redup Baris Terakhir Selamanya

Redup StartCell Sebagai Rentang

Redup rowCounter Sebagai 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 = "MyEmail@gmail.com"

strTo = "BossEmail@gmail.com"

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@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

End With


With CDO_Mail

     Set .Configuration = CDO_Config

End With


Replace email@website.com 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.



Sekian artikel tentang Panduan VBA Tingkat Lanjut Untuk MS Excel. Baca juga artikel mengenai Penyortiran Data Satu Kolom dan Multi-Kolom Dasar di Spreadsheet Excel yang mungkin perlu Sobat ketahui.

Lebih baru Lebih lama