4 ข้อผิดพลาดที่ควรหลีกเลี่ยงเมื่อเขียนโปรแกรม Excel Macros ด้วย VBA

4 ข้อผิดพลาดที่ควรหลีกเลี่ยงเมื่อเขียนโปรแกรม Excel Macros ด้วย VBA

Microsoft Excel เป็นเครื่องมือวิเคราะห์ข้อมูลที่มีความสามารถอยู่แล้ว แต่ด้วยความสามารถในการทำงานซ้ำๆ โดยอัตโนมัติด้วยมาโครโดยการเขียนโค้ดอย่างง่ายใน Visual Basic for Applications (VBA) จึงมีประสิทธิภาพมากกว่านั้นมาก ใช้ไม่ถูกต้อง อย่างไรก็ตาม VBA อาจทำให้เกิดปัญหาได้





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



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





เริ่มต้นใช้งาน VBA

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

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



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

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



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

1. ชื่อตัวแปรที่น่ากลัว

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



วิธีการขอเงินคืนใน Steam

ตลอดหลายทศวรรษของการเขียนโค้ด ฉันได้พบโรงเรียนแห่งความคิดมากมายเมื่อพูดถึงแบบแผนการตั้งชื่อตัวแปรและเรียนรู้กฎสองสามข้ออย่างยากลำบาก ต่อไปนี้คือเคล็ดลับสั้นๆ ในการสร้างชื่อตัวแปร:

  • ทำให้สั้นที่สุด
  • ทำให้เป็นคำอธิบายมากที่สุด
  • นำหน้าด้วยประเภทตัวแปร (บูลีน จำนวนเต็ม ฯลฯ...)

นี่คือภาพหน้าจอตัวอย่างจากโปรแกรมที่ฉันใช้บ่อยเพื่อโทร WMIC Windows จาก Excel เพื่อรวบรวมข้อมูลพีซี

เมื่อคุณต้องการใช้ตัวแปรภายในฟังก์ชันภายในโมดูลหรือวัตถุ (ฉันจะอธิบายด้านล่างนี้) คุณต้องประกาศให้เป็นตัวแปร 'สาธารณะ' โดยนำหน้าการประกาศด้วย สาธารณะ . มิฉะนั้น ตัวแปรจะได้รับการประกาศโดยนำหน้าด้วยคำว่า ไม่มี .

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

นอกจากนี้ อย่าลืมตั้งชื่อแผ่นงานในสมุดงาน Excel ของคุณ

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

2. ทำลายแทนการวนซ้ำ

หนึ่งในปัญหาที่พบบ่อยที่สุดที่โปรแกรมเมอร์ VBA รุ่นใหม่มีเมื่อพวกเขาเริ่มเขียนโค้ดคือการจัดการกับลูปอย่างเหมาะสม

การวนซ้ำเป็นเรื่องปกติมากใน Excel เนื่องจากคุณมักจะประมวลผลค่าข้อมูลทั้งแถวหรือคอลัมน์ ดังนั้นคุณต้องวนซ้ำเพื่อประมวลผลค่าข้อมูลทั้งหมด

โปรแกรมเมอร์ใหม่มักต้องการแยกออกจากลูป (VBA For loops หรือ VBA Do While loops ) ทันทีเมื่อเงื่อนไขบางอย่างเป็นจริง

ต่อไปนี้คือตัวอย่างของวิธีนี้ที่ใช้เพื่อแยกลูป VBA

For x = 1 To 20
If x = 6 Then Exit For
y = x + intRoomTemp
Next i

โปรแกรมเมอร์ใหม่ใช้วิธีนี้เพราะมันง่าย พยายามหลีกเลี่ยงการทำให้วงแตกอย่างชัดเจน

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

While (x>=1 AND x<=20 AND x6)
For x = 1 To 20
y = x + intRoomTemp
Next i
Wend

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

3. ไม่ใช้อาร์เรย์

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

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

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

คุณสามารถกำหนดอาร์เรย์ได้ง่ายๆ เพียงพิมพ์ Dim arrMyArray(12) เป็น Integer .

ทำไมตอนนี้ gpus ถึงแพงจัง

สิ่งนี้จะสร้าง 'ถาด' ที่มี 12 ช่องให้คุณเติม

โค้ดการวนซ้ำแถวที่ไม่มีอาร์เรย์อาจมีลักษณะดังนี้:

Sub Test1()
Dim x As Integer
intNumRows = Range('A2', Range('A2').End(xldown)).Rows.Count
Range('A2').Select
For x = 1 To intNumRows
If Range('A' & str(x)).value <100 then
intTemp = (Range('A' & str(x)).value) * 32 - 100
End If
ActiveCell.Offset(1, 0).Select
Next
End Sub

ในตัวอย่างนี้ โค้ดกำลังประมวลผลผ่านทุกเซลล์ในช่วงและทำการคำนวณอุณหภูมิ

หากคุณต้องการทำการคำนวณอื่นๆ เกี่ยวกับค่าเดียวกันเหล่านี้ กระบวนการจะยุ่งยาก คุณต้องทำซ้ำโค้ดนี้ ประมวลผลผ่านเซลล์เหล่านี้ทั้งหมด และทำการคำนวณใหม่ ทั้งหมดนี้เพื่อการเปลี่ยนแปลงเพียงครั้งเดียว!

นี่เป็นตัวอย่างที่ดีกว่า โดยใช้อาร์เรย์ ขั้นแรก มาสร้างอาร์เรย์กันก่อน

Sub Test1()
Dim x As Integer
intNumRows = Range('A2', Range('A2').End(xldown)).Rows.Count
Range('A2').Select
For x = 1 To intNumRows
arrMyArray(x-1) = Range('A' & str(x)).value)
ActiveCell.Offset(1, 0).Select
Next
End Sub

NS x-1 สำหรับการชี้ไปที่องค์ประกอบอาร์เรย์นั้นจำเป็นเท่านั้นเพราะ For loop เริ่มต้นที่ 1 องค์ประกอบอาร์เรย์ต้องเริ่มต้นที่ 0

ตอนนี้คุณมีอาร์เรย์แล้ว การประมวลผลเนื้อหาก็ง่ายมาก

Sub TempCalc()
For x = 0 To UBound(arrMyArray)
arrMyTemps(y) = arrMyArray(x) * 32 - 100
Next
End Sub

ตัวอย่างนี้ผ่านอาร์เรย์แถวทั้งหมด ( UBound ให้จำนวนค่าข้อมูลในอาร์เรย์) ทำการคำนวณอุณหภูมิ แล้วใส่ลงในอาร์เรย์อื่นที่เรียกว่า arrMyTemps .

4. ใช้การอ้างอิงมากเกินไป

ไม่ว่าคุณจะเขียนโปรแกรมใน Visual Basic หรือ VBA เต็มรูปแบบ คุณจะต้องรวม 'ข้อมูลอ้างอิง' เพื่อเข้าถึงคุณลักษณะบางอย่าง

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

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

คุณควรตรวจสอบรายการนี้เนื่องจากการอ้างอิงที่ไม่จำเป็นอาจทำให้ทรัพยากรระบบสิ้นเปลือง หากคุณไม่ได้ใช้การจัดการไฟล์ XML เหตุใดจึงเลือก Microsoft XML ไว้ หากคุณไม่ได้สื่อสารกับฐานข้อมูล ให้ลบ Microsoft DAO ฯลฯ

หากคุณไม่แน่ใจว่าข้อมูลอ้างอิงที่เลือกเหล่านี้ทำอะไร ให้กด F2 และคุณจะเห็น Object Explorer ที่ด้านบนของหน้าต่างนี้ คุณสามารถเลือกไลบรารีอ้างอิงเพื่อเรียกดูได้

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

ตัวอย่างเช่น เมื่อฉันคลิกที่ไลบรารี DAO จะเห็นได้ชัดว่านี่คือทั้งหมดที่เกี่ยวกับการเชื่อมต่อและสื่อสารกับฐานข้อมูล

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

วิธีถ่ายโอนไฟล์จาก Android ไปยังพีซีแบบไร้สาย

การเขียนโปรแกรมใน Excel VBA

ความคิดทั้งหมดในการเขียนโค้ดใน Excel จริงๆ ทำให้หลายคนกลัว แต่ความกลัวนี้ไม่จำเป็นจริงๆ Visual Basic for Applications เป็นภาษาที่เรียนรู้ได้ง่ายมาก และหากคุณปฏิบัติตามหลักปฏิบัติทั่วไปที่กล่าวถึงข้างต้น คุณจะมั่นใจได้ว่าโค้ดของคุณสะอาด มีประสิทธิภาพ และเข้าใจง่าย

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

แบ่งปัน แบ่งปัน ทวีต อีเมล การอัพเกรดเป็น Windows 11 คุ้มค่าหรือไม่?

Windows ได้รับการออกแบบใหม่ แต่นั่นเพียงพอที่จะโน้มน้าวให้คุณเปลี่ยนจาก Windows 10 เป็น Windows 11 หรือไม่

อ่านต่อไป หัวข้อที่เกี่ยวข้อง
  • ผลผลิต
  • การเขียนโปรแกรม
  • การเขียนโปรแกรม
  • การเขียนโปรแกรม Visual Basic
  • Microsoft Excel
เกี่ยวกับผู้เขียน แอนโธนี่ แกรนท์(40 บทความที่ตีพิมพ์)

Anthony Grant เป็นนักเขียนอิสระที่ครอบคลุมการเขียนโปรแกรมและซอฟต์แวร์ เขาเป็นวิทยาการคอมพิวเตอร์ที่สำคัญในการเขียนโปรแกรม Excel ซอฟต์แวร์และเทคโนโลยี

เพิ่มเติมจาก Anthony Grant

สมัครรับจดหมายข่าวของเรา

เข้าร่วมจดหมายข่าวของเราสำหรับเคล็ดลับทางเทคนิค บทวิจารณ์ eBook ฟรี และดีลพิเศษ!

คลิกที่นี่เพื่อสมัครสมาชิก