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

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

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

คู่มือ VBA ขั้นสูงสำหรับ Microsoft Excel(Advanced VBA Guide For Microsoft Excel)

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

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

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

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

การนำเข้าข้อมูลและการคำนวณ(Importing Data and Performing Calculations)

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

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

รหัสนำเข้าต่อไปนี้จะเรียกใช้และนำเข้าไฟล์ CSV(CSV)ลงในสเปรดชีตExcel ของคุณ(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 จากกล่องดรอปดาวน์ของอ็อบเจ็กต์และเมธอด ให้เลือกเวิ(Worksheet) ร์กชีต และเปิดใช้(Activate)งาน ซึ่งจะเรียกใช้รหัสทุกครั้งที่คุณเปิดสเปรดชีต

สิ่งนี้จะสร้างฟังก์ชันย่อย Worksheet_Activate() (Sub Worksheet_Activate() )วางโค้ดด้านบนลงในฟังก์ชันนั้น

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

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

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

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

  1. สร้างคอลัมน์ผลลัพธ์ใหม่ที่เรียกว่าภาษี(taxes)
  2. วนรอบ คอลัมน์ หน่วยขาย(units sold)และคำนวณภาษีขาย
  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(CSV)โดยอัตโนมัติ จากนั้นจะทำการคำนวณและเขียนผลลัพธ์ลงในแผ่นงาน คุณไม่จำเป็นต้องดำเนินการใดๆ ด้วยตนเองอีกต่อไป!

คำนวณผลลัพธ์จากการกดปุ่ม(Calculate Results From Button Press)

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

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

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

ใช้สเปรดชีตเดียวกันกับด้านบน เลือก แท็บ นักพัฒนา(Developer)แล้วเลือกแทรก(Insert)จากกลุ่ม ตัวควบคุม(Controls)ใน Ribbon เลือกปุ่มกด(push button) ActiveX Controlจากเมนูดรอปดาวน์

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

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

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

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

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

ตอนนี้ ทำซ้ำขั้นตอนอีกครั้ง สร้างปุ่มกดที่สอง ทำให้คำบรรยายCalculate 7% Tax %

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

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

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

เมื่อต้องการส่งข้อความนี้ ให้เลือก เมนู นักพัฒนา(Developer)และเลือกโหมดการออกแบบ(Design Mode)จากกลุ่มตัวควบคุม(Controls)ใน Ribbon เพื่อปิดใช้งานโหมดการ(Design Mode)ออกแบบ สิ่งนี้จะเปิดใช้งานปุ่มกด 

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

ส่งอีเมลผลการคำนวณถึงใครบางคน(Email Calculation Results to Someone)

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

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

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

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

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

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(SMTP) ของคุณ ได้ หากคุณใช้Gmailโดยทั่วไปจะเป็นที่อยู่อีเมล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("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

แทนที่[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

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

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

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



ธงชัย สันตติวงศ์

About the author

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


Related posts