คำแนะนำ VBA ขั้นสูงสำหรับ MS Excel

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

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

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

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

ต่อไปนี้เป็นการใช้งาน VBA ใน Excel

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

คำนวณผลลัพธ์จากผู้ใช้กดปุ่ม

ส่งผลการคำนวณทางอีเมลถึงใครบางคน

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

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

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

In_content_1 ทั้งหมด: [300x250] / dfp [640x360]->

ด้วย 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 จากช่องรายการแบบเลื่อนลงวัตถุและวิธีเลือก แผ่นงานและ เปิดใช้งานสิ่งนี้จะเรียกใช้รหัสทุกครั้งที่คุณเปิดสเปรดชีต

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

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

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

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

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

สร้าง คอลัมน์ผลการค้นหาใหม่ที่เรียกว่า tax<<<<<<<<<<<<<<<<

วนรอบคอลัมน์ หน่วยขายแล้วคำนวณภาษีขาย

เขียนผลลัพธ์การคำนวณ ไปยังแถวที่เหมาะสมในแผ่นงาน

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

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 เดียวกันกับ ทำงานโดยอัตโนมัติ แต่ปล่อยให้รหัสการคำนวณภาษีทำงานเมื่อคุณกดปุ่มที่เหมาะสม

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

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

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

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

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

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

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

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

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

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

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

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

ผลลัพธ์การคำนวณอีเมลถึงใครบางคน

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

<รูป class = "ขี้เกียจ wp-block-image ขนาดใหญ่">

คุณสามารถสร้างปุ่มอื่นที่เรียกว่า แผ่นงานอีเมลถึงเจ้านายโดยใช้ขั้นตอนเดียวกันด้านบน รหัสสำหรับปุ่มนี้จะเกี่ยวข้องกับการใช้ออบเจ็กต์ 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("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

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

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

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

สอน Excel VBA: คำสั่ง FOR ... NEXT ... STEP

กระทู้ที่เกี่ยวข้อง: