VBA ส่งตัวเลขไปตำแหน่งที่ต้องการอัตโนมัติ

จากโจทย์นี้ …. ต้องการกรอกข้อมูลในคอลัมน์ B ยาวลงมาเรื่อยๆ แล้วต้องการให้ ตัวเลขใดก็ตาม ที่กรอกครั้งที่ 1 ให้มาเรียงในคอลัมน์ D และต่อไปตัวเลขใดก็ตาม ที่กรอกครั้งที่ 2 ให้มาเรียงในคอลัมน์ E และต่อไปตัวเลขใดก็ตาม ที่กรอกครั้งที่ 3 ให้มาเรียงในคอลัมน์ F แบบนี้ไปเรื่อย ๆ

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

งานลักษณะนี้เราต้องใช้ Feature ของ VBA ที่เรียกว่า Event procedure แล้ว Event Procedure อะไร…

โดยปกติการจะสั่งให้ Code VBA ที่เขียนเสร็จแล้วทำงาน เราจะกด Short key ที่ตั้งไว้ หรือคลิ้กที่ object ที่เราได้ assign macro เอาไว้…แต่ ในกรณีนี้ เราต้องการให้ VBA ส่งตัวเลขไปตำแหน่งที่ต้องการอัตโนมัติ เราจึงต้องใช้ Event Procedure

Event Procedure คือการกำหนดให้ Code เกิดการ Run อัตโนมัติภายให้เหตุการณ์ที่เราต้องการ (event แปลว่าเหตุการณ์) ซึ่ง Microsoft ได้เตรียม Event ต่าง ให้เราหลาย Event มาก ๆ เพื่อให้เราเลือกใช้ มีทั้ง Event ที่อยู่ในระดับ Workbook และ Worksheet รวมถึง Control ต่าง ๆ ใน user form ด้วย

ในงานนี้เราจะใช้ Event Procedure ที่ชื่อว่า “Worksheet_Change”

โดยผมออกแบบ Code เป็น 2 ส่วน คือ…
1) ใน module ทำหน้าที่ส่งค่าที่คีย์สุดท้ายไปที่ ตำแหน่งของตารางตามจำนวนครั้งที่ถูกคีย์
2) ใน worksheet (event procedure) ทำหน้าที่เป็น Trigger ในการเรียก Code ที่อยู่ใน module ให้ทำงาน

**โดยปกติ สามารถเขึยน โค้ดที่ Event procedure ได้โดยตรง แต่เราจะไม่สามารถทดสอบการทำงานของโค้ดได้ เพราะโค้ดจะทำงานเฉพาะเมื่อเกิด event ฉะนั้นผมจึงแนะนำให้เขียน Code หลักที่ module แล้วจึงเขียนโค้ดเพื่อสั่งให้ Code ใน module ทำงานเป็นเหมือน Trigger เรียกให้ Code หลักทำงาน **

ขั้นตอนการออกแบบ Code

• 1) เขียน Code ที่ module โดย…
  • ประกาศตัวแปร 3 ค่า เพื่อรับค่า คือ…
    • myTargetRow As Long เพื่อรับค่าบรรทัดที่จะเอาตัวเลขที่กรอกในคอลัมน์ B ไปวาง
    • myTargetCol As Long เพื่อรับค่าคอลัมน์ที่จะเอาตัวเลขที่กรอกในคอลัมน์ B ไปวาง
    • myNumber As Long เพื่อรับค่าตัวเลขที่กรอกเอาไปวาง ณ บรรทัด myTargetRow,myTargetCol
  • กำหนดค่าให้กับตัวแปรทั้ง 3 โดย
    • myTargetCol สัมพันธ์กับจำนวนครั้งที่คีย์ และจะเพิ่มขึ้นเรื่อย ๆ ตามจะนวนครั้งที่คีย์ค่านั้น ๆ
    • myTargetRow เมื่อได้ตำแหน่งคอลัมน์แล้ว ก็ใช้ตำแหน่งคอลัมน์นั้นเพื่อหาบรรทัดสุดท้ายเพื่อเอาจำนวนเลขที่คีย์ ไปวาง
    • myNumber คือค่าสุดท้ายที่คีย์นั้น ๆ
  • หลังจากนั้นจึงวางค่าตัวเลข ตามตัวแปรที่ได้ทั้ง 3
  • ทดสอบการทำงานของ Code ให้เรียบร้อย
• 2) เขียน Code ใน Even Procedure ของ worksheet
  • เลือกใช้ Event prodedure ที่ชื่อ Worksheet_Change (นั่นคือถ้ามีการเปลี่ยนแปลงใด ๆ ในชีทนี้ Code ก็จะทำงานอัตโนมัติ ทันที)
  • สร้างเงื่อนไขให้ Code เกิดการทำงานเพิ่มเติมด้วย If Statement เพื่อให้ Code ทำงานเฉพาะเมื่อการเปลี่ยนแปลงเกิดที่คอลัมน์ 2 (หรือ คอลัมน์ B นั่นเอง)
  • ถ้าเงื่อนไขครบ ก็ให้ เรียก Code ที่อยู่ใน module ที่เตรียมไว้ทำงาน

อธิบายการทำงานของ Code

• เราคีย์ เลขเข้าไปในคอลัมน์ B
• ส่วนที่เป็น Event procedure ก็จะไป trigger เรียก module
• โดยกำหนดเงื่อนไขคือ ถ้าไม่ใช่คอลัมน์ B ไม่ต้อง Trigger
• แต่ถ้าตรงเงื่อนไข ก็ให้เรียก module
• code ก็จะเก็บค่าตัวตัวเลขเข้าตัวแปรไว้
• แล้วนับจำนวนครั้งที่เราคีย์ แล้วใช้ไปกำหนดตำแหน่งคอลัมน์
• จากนั้นก็ไปหาตำแหน่งบรรทัดสุดท้ายของคอลัมน์นั้น ๆ
• แล้วสุดท้ายก็เอาค่าตัวเลขที่เก็บไว้ไปวางที ตำแหน่งนั้น ๆ

ก็จะได้โค้ด หน้าตาแบบนี้…

—- Code ที่ module เป็นแบบนี้—-

Sub Allocate_Number()
Dim myTargetRow As Long, myTargetCol As Long, mySourceRow As Long
Dim myNumber As Long
myNumber = ActiveCell.Offset(-1, 0).Value
myTargetCol = WorksheetFunction.CountIfs(Range(“B:B”), myNumber) + 3
myTargetRow = Cells(Rows.Count, myTargetCol).End(xlUp).Row + 1
Cells(myTargetRow, myTargetCol).Value = myNumber
End Sub

—-Code ที worksheet เป็นแบบนี้ —-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Call Allocate_Number
End If
End Sub