Evaluate vs Worksheet Function

โดย คุณอรวีร์

ปกติเราจะเรียกใช้ Function ของ Excel ที่ไม่มีใน VBA โดยวิธีแบบนี้ . . .

I = Application.WorksheetFunction.Ceiling(Sheets("Sheet1").Cells(1, 1).Value, Sheets("Sheet1").Cells(1, 2).Value)

เป็นการกำหนดให้ I เท่ากับ Ceiling( A1 ของ Sheet ชื่อ Sheet1, B1 ของ Sheet ชื่อ Sheet1 ) เช่นถ้า A1=12 B1=5 จะได้เป็น I=15

การกำหนดชื่อ Sheet ลงไปด้วยเพื่อให้สามารถ Run ได้ถูกต้องไม่ว่าขณะนั้น ActiveSheet จะเป็น Sheet ไหน สามารถเขียนแบบ Evaluate ได้เป็น . . .

I = [ Ceiling(Sheet1!A1,Sheet1!B1) ]

เมื่อทดลองจับเวลาในการ Run แล้ว(วน Loop หลายรอบ) พบว่าใช้เวลาเท่าๆกัน

แล้วแตกต่างกันตรงไหน ?

สมมุติเราต้องการค้นหาบรรทัดที่ Column A มีคำว่า "ERASE" เพื่อลบบรรทัดเหล่านั้นทิ้งทั้งบรรทัด
จะเขียนโปรแกรมโดยวิธีใช้ Function Match() ของ Excel ค้นหาใน Column A ได้ดังนี้

I=Application.WorksheetFunction.Match("ERASE", Columns(1), 0)

ซึ่งเทียบกับแบบ Evaluate ได้เป็น

I=[Match("ERASE", A:A, 0)]

ทั้ง 2 คำสั่งข้างบนนี้จะเกิด Error เมื่อค้นหาไม่พบ

แต่เมื่อใช้ TypeName( ) มาคร่อมเพื่อตรวจสอบ Error จะใช้แบบ Evaluate ได้ดังนี้

Do While TypeName([MATCH("ERASE", A:A, 0)]) <> "Error"
    Rows(([MATCH("ERASE", A:A, 0)])).Delete Shift:=xlUp
Loop

โดยไม่เกิด Error แต่จะเกิด Error ถ้าใช้แบบนี้ . . .

Do While TypeName(Application.WorksheetFunction.Match("ERASE", Columns(1), 0)) <> "Error"
    Rows(([MATCH("ERASE", A:A, 0)])).Delete Shift:=xlUp
Loop

เช่นเดียวกับ Find( ) จะเกิด Error เช่นกันเมื่อค้นไม่พบ

ทดลอง Run 4 คำสั่งนี้

Msgbox Typename([1/2])
Msgbox Typename(1/2)
Msgbox Typename([1/0])
Msgbox Typename(1/0)

จะพบว่า คำสั่งที่ 4 เกิด Error โดยคำสั่งที่ 3 ไม่เกิด Error

สรุปคือ การใช้ Evaluate() น่าจะสามารถใช้แทน WorksheetFunction ได้ทั้งหมด(อรวีร์ใช้คำว่า "น่าจะ" เพราะไม่แน่ใจเหมือนกัน อรวีร์ก็เพิ่งรู้จัก Eval จากการบอกของอาจารย์สมเกียรติเมื่อไม่น่ามานี้)