คู่มือ VBA ขั้นสูงสำหรับ MS Excel

หากคุณเพิ่งเริ่มต้นใช้งาน VBA คุณจะต้องเริ่มศึกษาคู่มือ VBA สำหรับผู้เริ่มต้น แต่ถ้าคุณเป็นผู้เชี่ยวชาญ VBA มากประสบการณ์ และกำลังมองหาสิ่งที่ล้ำหน้ากว่าที่คุณสามารถทำได้ด้วย VBA ใน Excel ให้อ่านต่อไป

ความสามารถในการใช้การเข้ารหัส VBA ใน Excel เปิดโลกทั้งใบของระบบอัตโนมัติ คุณสามารถคำนวณอัตโนมัติใน Excel ปุ่มกด หรือแม้แต่ส่งอีเมล มีความเป็นไปได้มากขึ้นที่จะทำให้งานประจำวันของคุณเป็นอัตโนมัติด้วย VBA มากกว่าที่คุณจะคิดได้

คู่มือ VBA ขั้นสูงสำหรับ Microsoft Excel

เป้าหมายหลักของการเขียนโค้ด VBA ใน Excel คือเพื่อให้คุณสามารถดึงข้อมูลจากสเปรดชีต ทำการคำนวณต่างๆ จากนั้นเขียนผลลัพธ์กลับไปที่สเปรดชีต

ต่อไปนี้คือการใช้ VBA ที่พบบ่อยที่สุดใน Excel

• นำเข้าข้อมูลและทำการคำนวณ
• คำนวณผลลัพธ์จากการที่ผู้ใช้กดปุ่ม
• ส่งอีเมลผลการคำนวณถึงผู้อื่น

ด้วยตัวอย่างทั้งสามนี้ คุณจะสามารถเขียนโค้ด Excel VBA ขั้นสูงได้หลากหลายรูปแบบ

การนำเข้าข้อมูลและดำเนินการคำนวณ

สิ่งหนึ่งที่คนทั่วไปใช้ Excel เพื่อทำการคำนวณกับข้อมูลที่มีอยู่ภายนอก Excel ถ้าคุณไม่ได้ใช้ VBA แสดงว่าคุณต้องนำเข้าข้อมูล เรียกใช้การคำนวณ และส่งออกค่าเหล่านั้นไปยังแผ่นงานหรือรายงานอื่น

ด้วย VBA คุณสามารถทำให้กระบวนการทั้งหมดเป็นแบบอัตโนมัติได้ ตัวอย่างเช่น หากคุณมีไฟล์ CSV ใหม่ดาวน์โหลดลงในไดเร็กทอรีบนคอมพิวเตอร์ของคุณทุกวันจันทร์ คุณสามารถกำหนดค่าโค้ด VBA ให้ทำงานเมื่อคุณเปิดสเปรดชีตครั้งแรกในเช้าวันอังคาร

รหัสนำเข้าต่อไปนี้จะเรียกใช้และนำเข้าไฟล์ CSV ลงในสเปรดชีต Excel ของคุณ

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

เปิดเครื่องมือแก้ไข Excel VBA และเลือกวัตถุ Sheet1 จากกล่องดรอปดาวน์ของออบเจ็กต์และเมธอด ให้เลือก เวิร์กชีต และ เปิดใช้งาน . ซึ่งจะเรียกใช้รหัสทุกครั้งที่คุณเปิดสเปรดชีต

สิ่งนี้จะสร้าง แผ่นงานย่อย_Activate() การทำงาน. วางโค้ดด้านบนลงในฟังก์ชันนั้น

ซึ่งจะตั้งค่าเวิร์กชีตที่ใช้งานอยู่เป็น Sheet1 ล้างชีต เชื่อมต่อกับไฟล์โดยใช้เส้นทางของไฟล์ที่คุณกำหนดด้วย strFile ตัวแปรแล้ว ด้วย วนรอบทุกบรรทัดในไฟล์และวางข้อมูลลงในแผ่นงานโดยเริ่มจากเซลล์ A1

หากคุณเรียกใช้โค้ดนี้ คุณจะเห็นว่าข้อมูลไฟล์ CSV ถูกนำเข้าไปยังสเปรดชีตเปล่าของคุณใน Sheet1 .

การนำเข้าเป็นเพียงขั้นตอนแรก ถัดไป คุณต้องการสร้างส่วนหัวใหม่สำหรับคอลัมน์ที่จะมีผลการคำนวณของคุณ ในตัวอย่างนี้ สมมติว่าคุณต้องการคำนวณภาษี 5% ที่จ่ายจากการขายสินค้าแต่ละรายการ

ลำดับของการกระทำที่โค้ดของคุณควรทำคือ:

1. สร้างคอลัมน์ผลลัพธ์ใหม่ชื่อ ภาษี .
2. วนรอบ หน่วยที่ขาย และคำนวณภาษีขาย
3. เขียนผลการคำนวณไปยังแถวที่เหมาะสมในชีต

รหัสต่อไปนี้จะทำตามขั้นตอนเหล่านี้ทั้งหมดให้สำเร็จ

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

รหัสนี้จะค้นหาแถวสุดท้ายในแผ่นข้อมูลของคุณ แล้วตั้งค่าช่วงของเซลล์ (คอลัมน์ที่มีราคาขาย) ตามแถวข้อมูลแรกและแถวสุดท้าย จากนั้นโค้ดจะวนซ้ำในแต่ละเซลล์ คำนวณภาษี และเขียนผลลัพธ์ลงในคอลัมน์ใหม่ของคุณ (คอลัมน์ 5)

วางโค้ด VBA ด้านบนด้านล่างโค้ดก่อนหน้า และเรียกใช้สคริปต์ คุณจะเห็นผลลัพธ์ปรากฏขึ้นในคอลัมน์ E

ตอนนี้ ทุกครั้งที่คุณเปิดเวิร์กชีต Excel เวิร์กชีตจะออกไปโดยอัตโนมัติและรับสำเนาข้อมูลล่าสุดจากไฟล์ CSV จากนั้นจะทำการคำนวณและเขียนผลลัพธ์ลงในแผ่นงาน คุณไม่จำเป็นต้องดำเนินการใดๆ อีกต่อไป!

คำนวณผลลัพธ์จากการกดปุ่ม

หากคุณต้องการควบคุมโดยตรงมากกว่าเมื่อทำการคำนวณ แทนที่จะเรียกใช้โดยอัตโนมัติเมื่อเปิดแผ่นงาน คุณสามารถใช้ปุ่มควบคุมแทนได้

ปุ่มควบคุมมีประโยชน์หากคุณต้องการควบคุมว่าจะใช้การคำนวณใด ตัวอย่างเช่น ในกรณีเดียวกันกับข้างต้น ถ้าคุณต้องการใช้อัตราภาษี 5% สำหรับภูมิภาคหนึ่ง และอัตราภาษี 7% สำหรับอีกภูมิภาคหนึ่งจะเป็นอย่างไร

คุณสามารถอนุญาตให้รหัสนำเข้า CSV เดียวกันทำงานโดยอัตโนมัติ แต่ปล่อยให้รหัสการคำนวณภาษีทำงานเมื่อคุณกดปุ่มที่เหมาะสม

ใช้สเปรดชีตเดียวกันกับด้านบน เลือก นักพัฒนา และเลือก แทรก จาก การควบคุม กลุ่มในริบบิ้น เลือกปุ่มกด ActiveX Control จากเมนูแบบเลื่อนลง

วาดปุ่มกดบนส่วนใดก็ได้ของชีตให้ห่างจากตำแหน่งที่ข้อมูลจะไป

คลิกขวาที่ปุ่มกด และเลือก คุณสมบัติ . ในหน้าต่างคุณสมบัติ เปลี่ยนคำบรรยายเป็นสิ่งที่คุณต้องการแสดงให้ผู้ใช้เห็น ในกรณีนี้อาจเป็น คำนวณภาษี 5% .

คุณจะเห็นข้อความนี้สะท้อนให้เห็นในตัวปุ่มกด ปิด คุณสมบัติ หน้าต่าง และดับเบิลคลิกที่ปุ่มเอง ซึ่งจะเป็นการเปิดหน้าต่างตัวแก้ไขโค้ด และเคอร์เซอร์ของคุณจะอยู่ภายในฟังก์ชันที่จะทำงานเมื่อผู้ใช้กดปุ่ม

วางรหัสการคำนวณภาษีจากส่วนด้านบนลงในฟังก์ชันนี้ โดยคงตัวคูณอัตราภาษีไว้ที่ 0.05 อย่าลืมใส่ 2 บรรทัดต่อไปนี้เพื่อกำหนดชีตที่ใช้งานอยู่

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

ตอนนี้ ทำซ้ำขั้นตอนอีกครั้ง สร้างปุ่มกดที่สอง ทำคำบรรยาย คำนวณภาษี 7% .

ดับเบิลคลิกที่ปุ่มนั้นแล้ววางรหัสเดียวกัน แต่ให้เพิ่มตัวคูณภาษี 0.07

ตอนนี้ ขึ้นอยู่กับปุ่มที่คุณกด คอลัมน์ภาษีจะถูกคำนวณตามนั้น

เมื่อเสร็จแล้ว คุณจะมีปุ่มกดทั้งสองบนแผ่นงานของคุณ แต่ละคนจะเริ่มการคำนวณภาษีที่แตกต่างกัน และจะเขียนผลลัพธ์ที่แตกต่างกันลงในคอลัมน์ผลลัพธ์

หากต้องการส่งข้อความนี้ ให้เลือก นักพัฒนา เมนู แล้วเลือก โหมดออกแบบ สร้างกลุ่มการควบคุมใน ribbon เพื่อปิดใช้งานโหมดการออกแบบ . สิ่งนี้จะเปิดใช้งานปุ่มกด

ลองเลือกแต่ละปุ่มกดเพื่อดูว่าคอลัมน์ผลลัพธ์ "ภาษี" เปลี่ยนไปอย่างไร

ส่งอีเมลผลการคำนวณถึงผู้อื่น

จะทำอย่างไรถ้าคุณต้องการส่งผลในสเปรดชีตให้ผู้อื่นทางอีเมล

คุณสามารถสร้างปุ่มอื่นที่เรียกว่า ส่งแผ่นงานอีเมลถึงหัวหน้า โดยใช้ขั้นตอนเดียวกันข้างต้น รหัสสำหรับปุ่มนี้จะเกี่ยวข้องกับการใช้วัตถุ Excel CDO เพื่อกำหนดการตั้งค่าอีเมล SMTP และส่งอีเมลผลลัพธ์ในรูปแบบที่ผู้ใช้อ่านได้

ในการเปิดใช้งานคุณลักษณะนี้ คุณต้องเลือก เครื่องมือและข้อมูลอ้างอิง . เลื่อนลงไปที่ Microsoft CDO สำหรับไลบรารี Windows 2000 เปิดใช้งาน และเลือก ตกลง .

โค้ดที่คุณต้องสร้างมีสามส่วนหลักเพื่อส่งอีเมลและฝังผลลัพธ์ของสเปรดชีต

อย่างแรกคือการตั้งค่าตัวแปรเพื่อเก็บหัวเรื่อง ที่อยู่ถึงและจาก และเนื้อหาของอีเมล

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

แน่นอน เนื้อหาต้องเป็นไดนามิกขึ้นอยู่กับผลลัพธ์ที่อยู่ในชีต ดังนั้นที่นี่คุณจะต้องเพิ่มการวนซ้ำที่ผ่านช่วง ดึงข้อมูล และเขียนทีละบรรทัดไปยังเนื้อหา

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

ส่วนถัดไปเกี่ยวข้องกับการตั้งค่าการตั้งค่า SMTP เพื่อให้คุณสามารถส่งอีเมลผ่านเซิร์ฟเวอร์ SMTP ของคุณได้ หากคุณใช้ Gmail โดยทั่วไปจะเป็นที่อยู่อีเมล Gmail รหัสผ่าน 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("https://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("https://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("https://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("https://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
.Item("https://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("https://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("https://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With

With CDO_Mail
     Set .Configuration = CDO_Config
End With

แทนที่ [email protected] และรหัสผ่านด้วยรายละเอียดบัญชีของคุณเอง

สุดท้าย ในการเริ่มส่งอีเมล ให้ใส่รหัสต่อไปนี้

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

หมายเหตุ :หากคุณเห็นข้อผิดพลาดในการขนส่งเมื่อพยายามเรียกใช้โค้ดนี้ อาจเป็นเพราะบัญชี Google ของคุณบล็อกไม่ให้ "แอปที่มีความปลอดภัยน้อย" ทำงาน คุณจะต้องไปที่หน้าการตั้งค่าแอปที่มีความปลอดภัยน้อยและเปิดฟีเจอร์นี้

หลังจากเปิดใช้งานแล้ว อีเมลของคุณจะถูกส่งไป นี่คือสิ่งที่ดูเหมือนกับผู้ที่ได้รับอีเมลผลลัพธ์ที่สร้างขึ้นโดยอัตโนมัติของคุณ

อย่างที่คุณเห็นมีหลายสิ่งหลายอย่างที่คุณสามารถทำให้เป็นอัตโนมัติด้วย Excel VBA ลองลองใช้ข้อมูลโค้ดที่คุณได้เรียนรู้ในบทความนี้ และสร้างการทำงานอัตโนมัติของ VBA ที่ไม่เหมือนใคร