home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> ซอฟต์แวร์ >> Office

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

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

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

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

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

การใช้งาน VBA ใน Excel ที่พบบ่อยที่สุดมีดังนี้:

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

ด้วยตัวอย่าง 3 อย่างนี้ คุณควรเขียนโค้ด 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 จากกล่องดรอปดาวน์อ็อบเจ็กต์และเมธอด เลือก Worksheet และ Activate โค้ดจะรันทุกครั้งที่คุณเปิดสเปรดชีต

จะสร้างฟังก์ชัน Sub Worksheet_Activate() วางโค้ดข้างต้นลงในฟังก์ชันนั้น

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

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

หากคุณต้องการควบคุมโดยตรงมากกว่า การคำนวณเมื่อเปิดชีต คุณสามารถใช้ปุ่มควบคุม (Control Button) แทน

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

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

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

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

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

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

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

Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")

ทำซ้ำกระบวนการอีกครั้ง สร้างปุ่มกดที่สอง ทำให้ Caption เป็น Calculate 7% Tax

ดับเบิลคลิกปุ่มนั้น วางโค้ดเดียวกัน แต่เปลี่ยนตัวคูณภาษีเป็น 0.07

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

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

เพื่อทดสอบ เลือกเมนู Developer เลือก Design Mode จากกลุ่ม Controls บนริบบอน เพื่อปิด Design Mode จะเปิดใช้งานปุ่มกด

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

ส่งอีเมลผลลัพธ์การคำนวณให้ผู้อื่น

ถ้าคุณต้องการส่งผลลัพธ์บนสเปรดชีตให้ใครทางอีเมล?

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

เพื่อเปิดใช้งานฟีเจอร์นี้ ต้องเลือก Tools and References เลื่อนลงไป Microsoft CDO for Windows 2000 Library เปิดใช้งาน คลิก OK

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

ส่วนแรก คือการตั้งตัวแปรเก็บ Subject, To, From, และ 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."

Body ต้องเป็นแบบไดนามิกขึ้นอยู่กับผลลัพธ์ในชีต ที่นี่คุณต้องเพิ่มลูปที่วนผ่านช่วง ดึงข้อมูล และเขียนบรรทัดทีละบรรทัดไปที่ 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

ส่วนถัดไป เกี่ยวกับการตั้งค่า SMTP เพื่อส่งอีเมลผ่าน SMTP เซิร์ฟเวอร์ หากใช้ Gmail นี่คือที่อยู่อีเมล Gmail รหัสผ่าน Gmail และ 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("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@website.com"
    .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@website.com และ password ด้วยรายละเอียดบัญชีของคุณเอง

สุดท้าย เพื่อเริ่มส่งอีเมล ใส่โค้ดต่อไปนี้

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

หมายเหตุ: หากเห็น Transport error เมื่อรันโค้ดนี้ อาจเพราะบัญชี Google บล็อก "less secure apps" ต้องไปที่หน้าตั้งค่า less secure apps และเปิดใช้งาน ON

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

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