วิธีสั่งให้ VBA ทำงานทวนซ้ำหลายรอบ

การทวนซ้ำตามจำนวนรอบที่เรากำหนด

For...Next Statements เป็นรหัสที่ใช้สั่งให้ทำงานทวนซ้ำตามจำนวนรอบที่เรากำหนด โดยมีโครงสร้างการเขียนดังนี้

For counter = start To end [Step step]
    [statements]
    [Exit For]
    [statements]
Next [counter]

• counter เป็นตัวแปรที่กำหนดขึ้น ส่วนมากจะกำหนดชื่อตัวแปรเป็น i, j, k
• start เป็นเลขเริ่มต้นของรอบแรก
• end เป็นเลขสุดท้ายของรอบสุดท้าย
• step เป็นช่วงของเลขที่ต้องการให้บวกเพิ่มให้กับ counter ในรอบถัดไป ถ้าละส่วนของ step ไม่กำหนดลงไป จะถือว่า step=1
• statements เป็นรหัสคำสั่งที่ต้องการให้ทำงานซ้ำ
• Exit For ใช้กับกรณีที่ต้องการใช้เงื่อนไขให้เลิกทำงานวนซ้ำ

ตัวอย่าง

ต้องการสั่งพิมพ์สลิปเงินเดือนให้กับพนักงานทุกคน

For i = 1 To [Total]
    [Choice] = i
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWindow.SelectedSheets.PrintOut
Next i

1. Total เป็น Range Name ในตารางที่เก็บรายละเอียดเงินเดือนพนักงาน โดยมีสูตร =CountA(ตารางชื่อพนักงานทั้งหมด) เพื่อหายอดจำนวนพนักงานทั้งหมด
2. For i = 1 To [Total]
   ทำหน้าที่สั่งให้เริ่มทำงานทวนซ้ำ ตั้งแต่รอบที่ 1 ถึง รอบสุดท้ายเท่าจำนวนพนักงานทั้งหมด
3. Choice เป็น Range Name ใช้สำหรับรับเลขที่พนักงาน เพื่อใช้เลขที่พนักงานนี้ไปค้นหาข้อมูลของพนักงานเลขที่นั้นๆ นำมาแสดงในใบสลิปเงินเดือน
4. ในตารางที่เตรียมไว้เป็นแบบสลิปเงินเดือน ให้ใช้สูตร Index เพื่อดึงข้อมูลของพนักงานตามเลขที่แสดงไว้ใน Choice เช่น ถ้าต้องการชื่อพนักงานที่เก็บไว้ใน column แรก ให้ค้นหาข้อมูลมาแสดงในสลิปโดยใช้สูตร
   =Index(MyData,Choice,1)
5. [Choice] = i
   ทำหน้าที่ส่งค่า i ไปที่เซลล์ Choice จึงทำให้สูตร Index คำนวณตาม แล้วสูตร Index จะดึงข้อมูลของพนักงานตามเลขที่ของ i มาแสดงในใบสลิป ทั้งนี้ระบบการคำนวณต้องเป็น Automatic อยู่แล้วด้วย (แนะนำให้เพิ่มรหัสว่า Calculate ต่อท้ายบรรทัดนี้ เพื่อสั่งให้แฟ้มคำนวณ โดยไม่ต้องห่วงว่าแฟ้มเป็น Automatic หรือไม่)
6. ActiveWindow.SelectedSheets.PrintPreview
   ActiveWindow.SelectedSheets.PrintOut
   ทำหน้าที่แสดงภาพสลิปบนหน้าจอให้เห็นก่อน รอให้เราคลิกสั่งปิด Print Preview จากนั้นจึงสั่งให้พิมพ์สลิปโดยอัตโนมัติ
   (ถ้าต้องการให้พิมพ์สลิปทันทีโดยไม่ต้องหยุดแสดงภาพที่จะพิมพ์บนจอ ให้ใช้รหัสคำสั่งบรรทัด PrintOut เพียงบรรทัดเดียว)
7. Next i
   ทำหน้าที่สั่งให้วนกลับไปเพิ่ม i = i+1 เป็นรายการถัดไป ขอแนะนำให้เขียนตัว i ต่อท้าย Next ไว้เสมอ เพื่อแสดงว่า Next ตัวนี้เป็นชุดของ i
8. หากต้องการปรับปรุงรหัสให้ดีขึ้น แทนที่จะให้เริ่มจากเลข 1 ไปจนถึงคนสุดท้ายตามค่าที่ส่งมาจากตาราง ควรใช้ InputBox กำหนดค่า i และ Total เพื่อจะได้เลือกสั่งพิมพ์จากรายการใดก็ได้ และเมื่อพิมพ์เสร็จแล้ว ควรย้อนค่าใน Choice กลับเป็นเลขเริ่มต้นเดิม หรือเป็นเลข 1 เพื่อแสดงรายการแรก ดีกว่าปล่อยให้้ตารางแสดงข้อมูลรายการสุดท้ายค้างไว้

การทวนซ้ำตามจำนวนข้อมูล

For Each element In group
    [statements]
    [Exit For]
    [statements]
Next [element]

• element เป็นตัวแปรที่เรากำหนดให้แทนแต่ละส่วนของ group โดยจะตั้งชื่อตัวแปรนี้เป็นคำว่าอะไรก็ได้
• group เป็นกลุ่มของ Object เช่น พื้นที่ตารางที่มีหลายเซลล์ หรือแฟ้มที่มีหลายชีท

ตัวอย่าง

ต้องการปรับค่าในตารางที่ตั้งชื่อว่า Source ถ้าเซลล์ใดในตารางนี้ มีค่าน้อยกว่า 0 ให้ปรับค่าให้เป็น 0

For Each c In [Source]
    c.Select
    If c.Value < 0 Then c.Value = 0
Next c

c เป็นตัวแปรที่เราตั้งขึ้นเพื่อใช้แทน แต่ละเซลล์ในตารางที่มีชื่อว่า Source

การทวนซ้ำจนสมบูรณ์ตามเงื่อนไข

โครงสร้างรหัสซึ่งสามารถนำมาใช้ทำงานทวนซ้ำจนเงื่อนไขสมบูรณ์มีหลายแบบ เช่น

Do [{While | Until} condition]
    [statements]
    [Exit Do]
    [statements]
Loop

Do
    [statements]
    [Exit Do]
    [statements]
Loop [{While | Until} condition]

ตัวอย่าง

ต้องการปรับเพิ่มค่าของเลขที่บันทึกไว้ใน column โดยให้ทำงานเฉพาะพื้นที่เซลล์ที่ที่มีค่าบันทึกไว้ เป็นตารางติดต่อกันไปเริ่มจากเซลล์ A1

Range("A1").Select
Do Until Selection.Value = ""
    Selection.Value = Selection.Value + 1
    Selection.Offset(1, 0).Select
Loop

1. Do Until Selection.Value = ""
   สั่งให้ทวนซ้ำไปจนกว่าจะพบว่าเซลล์เป็นช่องว่าง
2. Selection.Value = Selection.Value + 1
   ปรับค่าในเซลล์ที่เลือกให้มีค่าเพิ่มอีก 1
3. Selection.Offset(1, 0).Select
   เลือกเซลล์ row ถัดไปข้างล่าง ใน column เดิม
4. ถ้าใช้ Do While Selection.Value <> ""
   สั่งให้ทวนซ้ำไปเรื่อยๆ ตราบใดที่เซลล์ที่เลือกไม่เป็นเซลล์ว่าง

นอกจากนี้ยังมีรหัสแบบ While...Wend แต่ไม่แนะนำให้ใช้ เพราะไม่ยืดหยุ่นเท่า Do...Loop

วิธี Exit

หากต้องการออกจากวงจรการทำงานซ้ำให้ใช้รหัส If ช่วยตามแบบต่อไปนี้

• If Selection.Value > 10 Then Exit For
• If Selection.Value > 10 Then Exit Do
• If Selection.Value > 10 Then Exit Sub

เมนูคำสั่งใน Excel ซึ่งทำงานทวนซ้ำโดยไม่ต้องใช้ VBA

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

• F5 > Special หรือ Edit > Goto > Special
  ใช้ในการค้นหาเซลล์ลักษณะต่างๆในตาราง
• Edit > Find, Edit > Replace
  ใช้ค้นหาค่าและเปลี่ยนแปลงค่าในเซลล์
• Tools > Goal Seek
  ใช้คำนวณย้อนกลับ เพื่อหาค่าตัวแปรที่นำไปใช้คำนวณ จนได้ผลลัพธ์ตามต้องการ
• Tools > Scenarios
  ใช้เก็บค่าตัวแปรเพื่อส่งค่าตัวแปรเหล่านั้นไปใช้คำนวณซ้ำ
• Tools > Formula Auditing
  ใช้สร้างแผนที่แสดงลำดับการส่งค่าต่อเนื่องในการคำนวณ
• ปุ่ม F4
  สั่งให้ Excel ทำงานตามคำสั่งล่าสุดซ้ำอีกครั้ง