วิธีส่งข้อมูลกลับไปแก้รายการเดิมที่มีอยู่แล้ว

แทนที่จะส่งข้อมูลใหม่ไปต่อท้ายรายการทั้งหมดที่มีอยู่เดิม คราวนี้จะหาทางส่งข้อมูลไปแก้รายการเก่า (ถ้ามี)

ชุดคำสั่ง : SendData

สิ่งที่ต้องการ

ส่งข้อมูลที่กรอกลงในเซลล์ B3:D3 กลับไปแก้ไขรายการเก่า โดยใช้ค่าที่กรอกนั้น ส่งไปทับรายการเดิมที่มีอยู่แล้วในตารางด้านขวา เช่น จากภาพ ให้ส่งรายการ a002 ที่ใช้ชื่อใหม่ว่า boy ไปแก้ไขรายการ a002 เดิมซึ่งใช้ชื่อว่า b ให้เป็น boy

ขั้นตอนการสร้างงาน

1. สร้าง Range Name ชื่อ Source ให้กับเซลล์ B3:D3
2. สร้าง Range Name ชื่อ Ref ให้กับเซลล์ G3
3. สร้าง Range Name ชื่อ Id ให้กับเซลล์ G3:G10
4. สร้าง Formula Name ชื่อ Target ให้เป็นสูตร
   =OFFSET( Ref, MATCH( $B$3, Id, 0)-1, 0, 1, 3)

วิเคราะห์สูตร

Target
=OFFSET( Ref, MATCH( $B$3, Id, 0)-1, 0, 1, 3)

ขอให้สังเกตเลข 0, 1, 3 ที่อยู่ด้านท้ายของสูตร Offset ก่อน

• เลข 0 กำหนดตำแหน่งของข้อมูล ให้เริ่มในแนว column เดียวกันกับ Ref
• เลข 1 คือ ความสูงของรายการข้อมูล ซึ่งต้องมีความสูงคงที่ 1 row เสมอ
• เลข 3 คือ ความกว้างของรายการ ซึ่งประกอบด้วย Id, Name, Amount จึงกำหนดให้กว้าง 3 column คงที่

ส่วนของสูตร MATCH( $B$3, Id, 0)-1 เป็นตัวช่วยกำหนดตำแหน่งรายการเดิมว่า Id ที่กรอกในเซลล์ B3 เป็นตำแหน่งลำดับที่เท่าใดใน Range ชื่อ Id สาเหตุที่ต้องลบผลที่ได้จากสูตร Match ออกเสีย 1 ตำแหน่ง ทั้งนี้เพื่อให้ตรงกับจำนวน row ที่ต้องนับถัดไปจากตำแหน่งของเซลล์ Ref

ดังนั้นสูตร Offset นี้ จึงทำหน้าที่ส่งรายการข้อมูลที่กรอกไว้ในตารางที่มีความสูง 1 rowและกว้าง 3 column จาก Range Name ชื่อ Source ไปยัง Target ซึ่งมีขนาดความสูงและความกว้างเดียวกัน เพียงแต่ Target จะขยับไปหาตำแหน่งรายการเดิมซึ่งอยู่ถัดไปจาก Ref ตามที่นับได้ด้วยสูตร Match-1

ตัวอย่างนี้จะเกิด error ขึ้นทันที ถ้ารหัสที่กรอกลงไปในเซลล์ B3 ไม่ใช่รหัส Id ที่บันทึกเก็บไว้แล้วในตารางด้านขวา ดังนั้นถ้าต้องการป้องกันไม่ให้ชุดคำสั่ง SendData หยุดทำงานกลางคัน ให้แก้ชุดคำสั่งเล็กน้อยเป็นดังนี้

Sub SendData()
    On Error Resume Next
    MyVar = [Source]
    [Target] = MyVar
End Sub

On Error Resume Next ที่ใส่เพิ่มเป็นบรรทัดแรก จะทำหน้าที่ตรงกับความหมาย ซึ่งแปลว่า "เมื่อเกิด error ขึ้น ให้กลับไปทำงานต่อ" ทำให้ชุดคำสั่ง SendData จะไม่เตือน error ให้เห็นอีกเลยแม้รหัสบรรทัดต่อๆไปจะไม่ทำงานก็ตาม

แทนที่จะใช้ On Error Resume Next ซึ่งไม่ได้ช่วยแก้ไขข้อมูลและไม่ได้เตือนให้ผู้ใช้ทราบว่าเกิดปัญหาแต่อย่างใด เราสามารถใช้ On Error Goto แทนดังนี้

Sub SendData()
    On Error GoTo ResetInput
    MyVar = [Source]
    [Target] = MyVar
    End
ResetInput:
    [Source] = "Invalid"
End Sub

On Error Goto ทำหน้าที่ตรงกับคำแปล คือ "เมื่อเกิด error ขึ้นให้ไปทำงานที่อื่นต่อ" ซึ่งในตัวอย่างนี้ กำหนดให้ข้ามไปทำงานต่อที่บรรทัด ResetInput โดยทำหน้าที่เปลี่ยนข้อมูลใน Source เป็นคำว่า Invalid เพื่อเตือนให้ทราบว่า ข้อมูลที่กรอกนั้นไม่ถูกต้อง

สังเกตว่าบรรทัด ResetInput: ต้องมีเครื่องหมาย colon : ต่อท้าย และนิยมเขียนโดยไม่จัดย่อหน้า

นอกจากนั้น ในกรณีที่ไม่เกิด error เราต้องป้องกันไม่ให้ชุดคำสั่งนี้ ทำงานในส่วนตั้งแต่บรรทัด ResetInput: จึงต้องใช้คำสั่ง End ไว้ก่อน เพื่อทำให้รหัสคำสั่งทำงานตามปกติ และจบไปเลย โดยไม่สนใจกับรหัสคำสั่งบรรทัดที่เหลือ