วิธีทำให้ VBA Run คำสั่ง ณ เวลาที่ต้องการ

จากที่ผมสอนวิธีการส่ง line/email ด้วย excel ไป ทำให้มีหลายคนถามมาหลังไมค์ว่า ทำยังไงถึงจะให้ VBA มันแจ้งเตือน ณ เวลาใดเวลาหนึ่งที่ต้องการได้? เช่น บางคนอาจเอาไปส่งเตือนลูกค้า บางคนเอาไว้ส่งเรื่องหุ้น อะไรแบบนี้

การที่จะทำแบบนั้นได้ แปลว่ามันต้องมี Event/Trigger บางอย่างเกิดขึ้น ดังนั้นใบบทความนี้จะสอนว่าทำยังไงถึงจะรัน VBA ในเวลาที่กำหนดได้ครับ

สร้าง Action ขึ้นมาก่อน

สมมติว่าผมสร้าง sub เพื่อแสดง msgbox ธรรมดาๆ ไว้ใน Module 1 ดังนี้ (ให้แสดง msgbox นี่แหละ ง่ายสุดในสามโลกแล้ว)

Sub TestMacro1()

MsgBox ("Test Run Macro Number 1")

End Sub

Sub TestMacro2()

MsgBox ("Test Run Macro Number 2")

End Sub

ทั้งนี้ในชีวิตจริงคุณต้องเปลี่ยนจาก MsgBox ไป call sub ที่เตรียมไว้ เช่น Call LineNotify อะไรแบบนี้นะ

Application.Ontime คำสั่งที่ใช้กำหนดเรื่องรันตาม schedule เวลา

การกำหนด schedule เวลา จะใช้ Code แบบง่ายสุดในรูปแบบนี้

Application.OnTime เวลา, "ชื่อsub" 

ระวัง! อย่างไรก็ตาม เวลาที่ใส่ลงไปเป็นเวลาอย่างเร็วสุดที่จะรันคำสั่ง (จะไม่รันก่อนหน้านั้นแน่นอน) แต่มันอาจไม่รันตอนเวลานั้นเป๊ะ (ถ้า Excel ยังยุ่งกับเรื่องอื่นอยู่)

ดังนั้นผมสามารถสร้าง Sub ไว้ Test เรื่องเวลาดังนี้

เวลาอาจจะใช้เป็นตัวแปร เช่น MyTime แล้ว MyTime สามารถกำหนดได้หลายแบบเช่น

ถ้าใส่เวลาลงไปเลย เช่น

Sub TestTime1()
MyTime = TimeValue("19:43:00")   'รันตอน 19:43
Application.OnTime MyTime, "TestMacro2"
End Sub

ถ้าใส่แบบนับจากปัจจุบันด้วยวิธี
Now + TimeSerial(ชั่วโมง, นาที, วินาที) หรือ
Now + TimeValue(” ชั่วโมง : นาที : วินาที “) ก็ได้

Sub TestTime2()
MyTime = Now + TimeSerial(0, 0, 5)   'อีก 5 วินาทีจะรัน
MyTime2 = Now + TimeValue("00:00:05")  'อีก 5 วินาทีจะรัน แบบนี้ก็ได้
Application.OnTime MyTime, "TestMacro1"
End Sub

พอกด F5 ที่ Sub TestTime ก็จะพบว่าอีก 5 วินาทีถึงจะมี MsgBox เก้งขึ้นมาว่า Test Run Macro Number 1

มีคำสั่งอื่นแทรกก็ได้

แต่ถ้าใส่อีกคำสั่งต่อท้ายไปเลยแบบไม่หน่วงเวลา คำสั่ง Test แทรกที่ต่อท้ายมันจะถูกรันขึ้นมาก่อน (เพราะคำสั่งแรกมีการ schedule เอาไว้ว่าอีก 5 วินาทีจากตอนแรกถึงจะสั่ง TestMacro1)

Sub TestTime3()
MyTime = Now + TimeSerial(0, 0, 5)   'อีก 5 วินาทีจะรัน
Application.OnTime MyTime, "TestMacro1" 'อันนี้สั่งตาม schedule ล่วงหน้าไว้
MsgBox ("Test แทรก") 'นี่คือคำสั่งแทรก
End Sub

ถ้าจะยกเลิกการ Schedule สามารถทำได้ดังนี้

Application.OnTime เวลาเดิมที่สั่ง, "ชื่อsubเดิมที่สั่ง", , False
'False คือสั่ง cancel

เช่น

Sub TestTime4()
Mytime = Now + TimeSerial(0, 0, 5)   'อีก 5 วินาทีจะรัน
Application.OnTime Mytime, "TestMacro1"
MsgBox ("Test แทรก")
Application.OnTime Mytime, "TestMacro1", , False    'นี่จะ cancel การ schedule
End Sub

การสั่งรันหลายๆ ครั้งต่อเนื่อง ทุกๆ xx นาที

ถ้าเราสั่งให้ Sub 2 ตัวมันเรียกกันเอง มันก็จะรันต่อเนื่องไปเรื่อยๆ ได้ เช่น

Sub TestMacroRecur()

MsgBox ("Test Run Macro Number 2")

StartTimeRecur   'เรียกตัว schedule อีกรอบ

End Sub

Sub StartTimeRecur()
StartTime = Now + TimeSerial(0, 0, 5)   'อีก 5 วินาทีจะรัน
Application.OnTime StartTime, "TestMacroRecur", , True
End Sub

แต่ที่นี้มันจะรันไปเรื่อยๆ ไม่หยุดเลย เราก็ต้องสร้างปุ่มมาสั่งหยุดการเตือนอีกที เช่น สร้าง sub นี้แล้ว Assign Macro ให้กับปุ่มเอาไว้

Sub CancelTimeRecur()
' เอาอันนี้ไป assign กับปุ่มก็ได้
Application.OnTime StartTime, "TestMacroRecur", , False
End Sub

และนี่ก็คือ Code ให้รันคำสั่งที่ต้องการขณะที่เปิดไฟล์ Excel ไว้อยู่

แต่จะทำไงดี ถ้าไม่อยากเปิด Excel ทิ้งไว้ แต่อยากให้มันเปิดขึ้นมาเองเมื่อจะรัน??

วิธีแก้ไขก็คือ ให้ Windows เป็นคนเปิด Excel ขึ้นมา แล้วรัน Macro ที่เราต้องการ ซึ่งวิธีทำก็คือใช้เครื่องมือที่ชื่อว่า Windows Task Scheduler นั่นเอง (ดังนั้นเงื่อนไขเดียวคือ Computer ต้องเปิดอยู่ ตอนถึงเวลารัน)

วิธีการใช้ Task Scheduler

ซึ่งถ้าเราใช้คำสั่งนี้แล้ว เราสามารถรัน VBA ได้ในเวลาที่ต้องการ และสั่งให้มันปิดโปรแกรมไป แล้วก็รันใหม่ในอีกเวลาก็ได้ โดยไม่ต้องยุ่งกับคำสั่ง Application.OnTime ที่เราเรียนกันข้างบนด้วยซ้ำ 555

ผมไปเจอบทความภาษาอังกฤษที่เขียนไว้ครบถ้วนมาก คือ บทความนี้ ดังนั้นผมจะพยายามสรุปจากบทความเลยนะครับ

Step1 : เอา code นี้ไปใส่ Notepad

'Input Excel File's Full Path
'เปลี่ยน path เป็นไฟล์ของตัวเองที่จะรัน
  ExcelFilePath = "D:\ThepExcel\web\VBA on time\VBAonTime.xlsm"

'Input Module/Macro name within the Excel File
'ใส่ชื่อ macro และ module ให้ถูกต้อง
  MacroPath = "Module1.TestMacro1"

'Create an instance of Excel
  Set ExcelApp = CreateObject("Excel.Application")

'Do you want this Excel instance to be visible?
  ExcelApp.Visible = True  'or "False"

'Prevent any App Launch Alerts (ie Update External Links)
  ExcelApp.DisplayAlerts = False

'Open Excel File
  Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)

'Execute Macro Code
  ExcelApp.Run MacroPath

'Save Excel File (if applicable)
  wb.Save

'Reset Display Alerts Before Closing
  ExcelApp.DisplayAlerts = True

'Close Excel File
  wb.Close

'End instance of Excel
  ExcelApp.Quit

'Leaves an onscreen message!
  MsgBox "Your Automated Task successfully ran at " & TimeValue(Now), vbInformation

save as ไฟล์ เลือกแบบ all files แล้วตั้งชื่อให้เป็น MacroTask.vbs

Step 2 : ตั้งค่าในโปรแกรม Task Scheduler

หาโปรแกรม Task Scheduler แล้วเปิดขึ้นมา

• ไปที่ Action -> Create Task
• ใน Tab General : ตั้งชื่อ Taskอะไรก็ได้
• ใน Tab Triggers : กดปุ่ม New แล้วตั้งเวลาได้ สามารถเลือกรันแบบ one time หรือจะ repeat ก็ได้ เช่น สามารถสั่งรัน ทุกๆ วัน ทุกอาทิตย์ ทุกเดือนได้
• ใน Tab Action : กด New แล้วใส่ว่า
  
  • ใน Program/Script ให้ browse หรือใส่คำว่า C:\Windows\System32\cscript.exe
  • ใน Argument (ต้องมีเครื่องหมายคำพูด)
    • ใส่ path ของ MacroTask.vbs เช่น
      “D:\ThepExcel\MacroTask.vbs”
• ใน Tab Condition : กำหนดได้ว่าให้รันก็ต่อเมื่ออะไร? ถ้าไม่อยากให้มีเงื่อนไขเยอะก็ติ๊กออก
• ใน Setting : ให้เป็นเงื่อนไขเพิ่มเติมอื่นๆ ไม่ต้อง Set ก็ได้

พอ ok แล้วรอเวลา เจ้า Excel ก็จะถูกรันเองโดยอัตโนมัติเลย!!

ตัวอย่างการเอาไปใช้งาน

ผมอาจจะใช้สั่ง LoopTable ในไฟล์การแจ้งเตือน Line ที่ผมทำเอาไว้ก็ได้

มันก็จะเปิด Excel ขึ้นมาเอง ส่ง Line เอง ปิด Excel เอง จบเลย 555