วิธีใช้รหัส VBA แบบ Evaluate ในการรับส่งข้อมูล

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

เนื้อหาในบทนี้จะหาทางใช้ VBA ช่วยในการรับส่งข้อมูลแทนการใช้สูตร จะได้ไม่ต้องเสียแรงเสียเวลาวุ่นวายกลับมาสร้างสูตร link และไม่ต้องสร้างสูตร link เตรียมไว้ก่อนเต็มตาราง ซึ่งจะทำให้แฟ้มที่มีตารางสูตรหลายๆเซลล์ กลายเป็นแฟ้มที่มีขนาดแฟ้มใหญ่โดยไม่จำเป็น

ชุดคำสั่งที่ใช้รับส่งข้อมูล มีเพียง 2 บรรทัด

Sub SendData()
    MyVar = [Source]
    [Target] = MyVar
End Sub

ข้อกำหนดในการใช้งาน

1. Source และ Target เป็น Range Name หรือ Formula Name ก็ได้
2. Source คือ ตารางต้นทางที่ส่งข้อมูล
3. Target คือ ตารางปลายทางที่รับข้อมูล
4. ขนาดตารางของทั้ง Source และ Target ต้องมีขนาดเท่ากัน เช่น ถ้า Source เป็นเซลล์เดียว ดังนั้น Target ก็ต้องเป็นเซลล์เดียวด้วย แต่ถ้า Source เป็นตารางที่มีขนาดความสูง 2 row ความกว้าง 3 column ดังนั้น Target ต้องเป็นตารางที่มีขนาดความสูง 2 row ความกว้าง 3 column เช่นกัน
5. ขณะที่สั่งให้คำสั่งชุดนี้ทำงาน แฟ้มที่มีตารางที่ตั้งชื่อว่า Source และ Target ต้องเปิดและกำลังใช้งานอยู่
6. Source หรือ Target ไม่จำเป็นต้องเป็นตารางในชีทเดียวกัน โดยจะเป็นตารางที่อยู่ต่างชีทหรือต่างแฟ้มกันก็ได้ เช่น Target เป็นชื่อ Range Name ที่ตั้งไว้ในแฟ้ม Book1.xls แต่อาจกำหนดให้ Refers to พื้นที่ตารางใน Book2.xls (ในกรณีนี้ ขณะที่สั่งให้ชุดคำสั่งนี้ทำงาน ต้องเปิดแฟ้ม Book1.xls และ Book2.xls และต้องเลือกอยู่ในแฟ้ม Book1.xls เพราะเป็นแฟ้มที่มีชื่อ Range Name Source และ Target)
7. สามารถตั้งชื่อตารางเป็นชื่ออื่นได้ตามต้องการ โดยแก้ชื่อที่ใช้ในรหัสให้ถูกต้องตามชื่อที่ตั้งไว้ ต้องสะกดชื่อตามให้ถูกต้องตรงกันทุกตัวอักษร ชื่อในรหัสไม่จำเป็นต้องใช้อักษรตัวใหญ่ตัวเล็กตามชื่อใน Excel แต่ถ้าตรงกันได้ทุกตัวจะดีกว่า

คำอธิบายขั้นตอนการทำงานของชุดคำสั่ง SendData

1. MyVar เป็นชื่อตัวแปรที่ตั้งขึ้น (Array Variable) ซึ่งจะเปลี่ยนเป็นชื่ออื่นใดก็ได้ สำหรับรับข้อมูลที่มาจาก Range Name ชื่อ Source
2. จากนั้น Range Name ชื่อ Target จะรับข้อมูลที่เก็บไว้ใน MyVar
3. คำว่า Source และ Target ที่พิมพ์ไว้ในระหว่างเครื่องหมาย [ ] เป็นการใช้แบบ Evaluate ทำให้ VBE ค้นหาต่อเองว่าทั้ง Source และ Target เป็น Range Name
4. เราใช้ MyVar เป็นสื่อกลางในการรับส่งข้อมูลระหว่าง Source และ Target ซึ่งเฉพาะตัวค่าของข้อมูลเท่านั้นที่จะถูกส่งไปเก็บไว้ที่ Target ถ้าใน Source มีเซลล์สูตร =1+2 จะส่งผลลัพธ์เป็นเลข 3 ไปเก็บไว้ที่ Target สิ่งอื่นนอกจากค่าผลลัพธ์ เช่น Format จะไม่ถูกส่งออกไป
5. ถ้าตัด MyVar ออกแล้วใช้คำสั่ง [Target] = [Source] บรรทัดเดียว จะใช้รับส่งข้อมูลได้เฉพาะเซลล์เดียวเท่านั้น
6. กรณีต้องการสั่งให้รหัสชุดนี้ทำงาน และในขณะนั้นมีแฟ้มหลายแฟ้มกำลังเปิดใช้งานร่วมกันอยู่ ควรเพิ่มรหัสบรรทัดแรก ให้ทำหน้าที่ย้ายไปดูแฟ้มที่มี Range Name ชื่อ Source และ Target ตั้งชื่อไว้ เช่น ถ้าเป็น Range Name ที่อยู่ในแฟ้มชื่อ Book1.xls ให้แก้ไขรหัสเป็น

Sub SendData()
    Windows("Book1.xls").Activate   ‘รหัสไม่ดีเพราะ ชื่อ Book1.xls
    MyVar = [Source]
    [Target] = MyVar
End Sub

หรือ

Sub SendData()
    ThisWorkbook.Activate              ‘รหัสนี้ดี เพราะไม่ระบุชื่อแฟ้ม
    MyVar = [Source]
    [Target] = MyVar
End Sub

หมายเหตุ ThisWorkbook หมายถึง ตัวแฟ้มที่มี Module เก็บชุดคำสั่งที่กำลังทำงาน ดังนั้นเมื่อชุดคำสั่ง SendData ทำงาน แล้วพบคำสั่ง ThisWorkbook.Activate จะทำให้แฟ้มที่เก็บชุดคำสั่ง SendData ถูกเลือกขึ้น ซึ่งย่อมเป็นแฟ้มเดียวกันกับแฟ้มที่มี Range Name ชื่อ Source และ Target นั่นเอง