การรีเฟรชผลลัพธ์ฟังก์ชัน VBA ใน Excel: คู่มือที่สมบูรณ์

เมื่อทำงานกับ Excel ผู้ใช้หลายคนพึ่งพาพลังของ ฟังก์ชันที่ผู้ใช้กำหนด (UDFs) เพื่อทำการคำนวณเฉพาะทาง อย่างไรก็ตาม คุณอาจพบปัญหาทั่วไป: UDF ของคุณไม่ประเมินผลใหม่โดยอัตโนมัติหลังจากที่เปลี่ยนข้อมูลที่เกี่ยวข้องในสเปรดชีตของคุณ ซึ่งอาจทำให้คุณรู้สึกหงุดหงิด โดยเฉพาะอย่างยิ่งหากคุณใช้เวลาในการสร้างฟังก์ชันที่ซับซ้อน

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

ทำความเข้าใจกับปัญหา

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

ทำไม UDF ของฉันถึงไม่รีเฟรชโดยอัตโนมัติ?

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

วิธีแก้ปัญหา: ทำให้ UDF ของคุณเป็นแบบไม่คงที่

ในการแก้ไขปัญหานี้ คุณสามารถทำให้ฟังก์ชันที่ผู้ใช้กำหนดของคุณ ไม่คงที่ การทำให้ฟังก์ชันไม่คงที่จะทำให้มันคำนวณใหม่ทุกครั้งที่ Excel คำนวณใหม่ ซึ่งรวมถึงการเปลี่ยนแปลงในเซลล์อื่น ๆ ทั่วทั้งสมุดงาน

วิธีการใช้ Application.Volatile

นี่คือขั้นตอนง่าย ๆ ในการปรับปรุง UDF ของคุณเพื่อรวมวิธี Application.Volatile

1. เปิดตัวแก้ไข VBA ของคุณ: กด ALT + F11 เพื่อเปิดตัวแก้ไข VBA ใน Excel

2. ค้นหาฟังก์ชันของคุณ: ค้นหาฟังก์ชันที่เกี่ยวข้องในโปรเจ็กต์ของคุณ ตัวอย่างเช่น หากคุณมีฟังก์ชันที่ทำให้ตัวเลขสองเท่า คุณจะต้องมองหามันในโมดูล VBA ของคุณ

3. แทรก Application.Volatile: ที่จุดเริ่มต้นของฟังก์ชันของคุณ ให้ใส่บรรทัด Application.Volatile

   นี่คือตัวอย่างง่าย ๆ ของวิธีที่ฟังก์ชันของคุณควรมีลักษณะหลังการปรับปรุง:

   Function doubleMe(d)
       Application.Volatile
       doubleMe = d * 2
   End Function
   

คำอธิบายของโค้ด

• คำสั่ง Application.Volatile จะทำให้ฟังก์ชันนี้ไม่คงที่
• การทำเช่นนี้จะทำให้ฟังก์ชันทำงานทุกครั้งที่ Excel คำนวณใหม่ (เมื่อมีการเปลี่ยนแปลงในเซลล์อื่น)
• ผลที่ได้คือผลลัพธ์ของคุณจะสะท้อนสถานะล่าสุดของข้อมูลเสมอ

การตั้งค่าการคำนวณอัตโนมัติใน Excel

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

1. ไปที่แท็บ Formulas: คลิกที่แท็บ “ฟังก์ชัน” (Formulas) ใน Ribbon
2. เลือกตัวเลือกการคำนวณ: ในกลุ่มการคำนวณ (Calculation) ให้ตรวจสอบว่าตัวเลือก “อัตโนมัติ” (Automatic) ได้ถูกเลือก

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

สรุป

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

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