เร่งความเร็วมาโคร Calc ด้วยการอ่านและเขียนข้อมูลเป็นชุด

วิธีที่น่าเชื่อถือที่สุดในการเพิ่มความเร็วให้กับมาโคร Calc ที่ทำงานช้า คือการแทนที่การอ่านและเขียนข้อมูลทีละเซลล์ซ้ำๆ ด้วยการอ่านช่วงข้อมูลเพียงครั้งเดียว การวนลูปในหน่วยความจำ และการเขียนช่วงข้อมูลเพียงครั้งเดียว วิธีนี้จะลดการเรียกใช้ฟังก์ชันระหว่าง Basic และ Calc แต่ไม่รับประกันว่าความเร็วจะเพิ่มขึ้นอย่างแน่นอน เพราะสูตร การจัดรูปแบบ ขนาดไฟล์ และตรรกะของมาโครก็อาจส่งผลต่อเวลาการทำงานได้เช่นกัน ตัวอย่างด้านล่างแสดงการเปลี่ยนแปลงคอลัมน์สถานะและแสดงวิธีการวัดทั้งความเร็วและความถูกต้อง รายละเอียด API ได้รับการตรวจสอบกับเอกสาร LibreOffice 26.8 เมื่อวันที่ 9 ตุลาคม 2026

อะไรที่ควรปรับปรุง และคุณจะวัดผลอย่างไร?

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

ฟังก์ชัน ของ LibreOffice Basic Timerจะรายงานจำนวนวินาทีตั้งแต่เที่ยงคืนLongหากต้องการได้ค่าเป็นตัวเลขวินาที ให้กำหนดค่าให้กับตัวแปร สำหรับการทำงานระยะสั้น ให้บันทึกค่าเริ่มต้นและค่าสิ้นสุด แล้วนำมาลบกัน ถ้าค่าสิ้นสุดต่ำกว่า แสดงว่าการทำงานผ่านเที่ยงคืนไปแล้ว ให้บวก 86,400 วินาทีเข้าไปในผลต่าง ตัวจับเวลาจะรายงานค่าเป็นวินาทีเต็ม หากต้องการScriptForgeความแม่นยำระดับมิลลิวินาที ให้ใช้บริการ Timer ของ ScriptForge ดูเอกสารประกอบของ LibreOffice Basic Timerและเอกสารประกอบของบริการ Timer ของ ScriptForgeควรหลีกเลี่ยงการเปิด บันทึก และตรวจสอบเวิร์กบุ๊กด้วยตนเองในช่วงเวลาที่วัดได้ เว้นแต่ว่ากิจกรรมเหล่านั้นเป็นส่วนหนึ่งของงานหลัก

เหตุใดมาโครขนาดใหญ่ใน Calc จึงทำให้การทำงานช้าลง?

ลูปที่เรียกgetCellByPosition()หรืออ่านและเขียนคุณสมบัติของเซลล์แต่ละรายการซ้ำๆ จะทำให้ Calc ต้องเรียกใช้ API ข้ามอินเทอร์เฟซ Basic-to-Calc สำหรับแต่ละการดำเนินการ ด้วยจำนวนแถวและคอลัมน์จำนวนมาก การเรียกใช้ API เหล่านั้นอาจเพิ่มขึ้นเรื่อยๆ แม้ว่าการแปลงข้อมูลนั้นจะง่ายก็ตาม ลูปที่วนซ้ำในอาร์เรย์ในหน่วยความจำยังคงเข้าถึงแต่ละแถว แต่จะหลีกเลี่ยงการเรียกใช้ API ของเซลล์แยกต่างหากสำหรับทุกค่า

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

คุณประมวลผลช่วงข้อมูลให้เป็นอาร์เรย์ได้อย่างไร?

ใช้ช่วงสี่เหลี่ยมผืนผ้าที่มีเฉพาะค่าที่มาโครของคุณต้องการเปลี่ยนแปลง ตัวอย่างนี้สมมติว่ามีชีตชื่อ `sheet_name` Dataแถวส่วนหัวอยู่ในแถวที่ 1 และค่าสถานะอยู่ในคอลัมน์ C เปลี่ยนชื่อชีตและช่วงให้ตรงกับเวิร์กบุ๊กของคุณ API getDataArray()จะส่งคืนช่วงเป็นแถวที่มีค่า และsetDataArray()เขียนอาร์เรย์ขนาดเดียวกันกลับไปยังช่วงนั้น

Sub UpdateStatuses
    Dim oSheet As Object
    Dim oRange As Object
    Dim aData As Variant
    Dim i As Long

    oSheet = ThisComponent.Sheets.getByName("Data")
    oRange = oSheet.getCellRangeByName("A2:C10001")
    aData = oRange.getDataArray()

    For i = LBound(aData) To UBound(aData)
        If aData(i)(2) = "pending" Then
            aData(i)(2) = "review"
        End If
    Next i

    oRange.setDataArray(aData)
End Sub

อาร์เรย์แถวเริ่มต้นที่ศูนย์: aData(i)(2)โดยอ้างอิงถึงคอลัมน์ที่สามในแต่ละแถว ในที่นี้คือคอลัมน์ C เนื่องจากช่วงที่เลือกเริ่มต้นที่ A ลำดับภายนอกของอาร์เรย์แสดงถึงแถว และลำดับภายในแต่ละลำดับแสดงถึงคอลัมน์ ช่วงที่ส่งผ่านsetDataArray()ต้องมีมิติเท่ากับอาร์เรย์ หากไม่ตรงกันจะทำให้เกิดข้อผิดพลาด โปรดดูข้อมูลอ้างอิง API XCellRangeData ของ LibreOfficeและเอกสารประกอบอาร์เรย์ของ LibreOffice ScriptForge

อย่าอัปเดตเซลล์สูตรด้วยค่าอย่างเดียว

getDataArray()ส่งคืนค่าเซลล์เป็นตัวเลขหรือสตริง ไม่ใช่ข้อความสูตรเดิม หากคุณอ่านช่วงที่มีสูตรและเขียนอาร์เรย์นั้นกลับด้วยsetDataArray()สูตรเหล่านั้นอาจถูกแทนที่ด้วยผลลัพธ์ปัจจุบัน จำกัดวิธีการใช้ค่าอาร์เรย์เฉพาะเซลล์อินพุต/เอาต์พุตที่ควรมีค่าเท่านั้น เมื่อมาโครต้องรักษาหรือแก้ไขสูตร ให้ใช้วิธีการอาร์เรย์สูตรgetFormulaArray()และsetFormulaArray()หรือทำงานเฉพาะกับช่วงที่ไม่มีสูตรเท่านั้นเอกสารอ้างอิง API ของ XCellRangeFormula อธิบาย ส่วนติดต่อที่แยกจากกันนั้น

คุณควรเปลี่ยนการตั้งค่าการคำนวณหรือการแสดงผลเมื่อใด?

ทดสอบตัวเลือกเหล่านี้หลังจากวัดเวอร์ชันของอาร์เรย์แล้วเท่านั้น หากการคำนวณสูตรใหม่ใช้เวลาส่วนใหญ่ในการทำงาน คุณสามารถสอบถามข้อมูลisAutomaticCalculationEnabled()ปิดใช้งานการคำนวณอัตโนมัติชั่วคราวด้วยenableAutomaticCalculation(False)จากนั้นคืนค่าการตั้งค่าเดิมและคำนวณเซลล์ที่เปลี่ยนแปลงใหม่เมื่อจำเป็น ใส่การคืนค่าไว้ในเส้นทางการล้างข้อมูล เพื่อป้องกันไม่ให้ข้อผิดพลาดทำให้เวิร์กบุ๊กอยู่ในโหมดการคำนวณด้วยตนเอง อย่าปิดใช้งานการคำนวณหากมาโครต้องการผลลัพธ์สูตรใหม่ระหว่างขั้นตอน เอกสาร LibreOffice calculate()ระบุว่าการคำนวณเซลล์ที่เปลี่ยนแปลงใหม่เป็นการcalculateAll()บังคับให้คำนวณใหม่ทั้งหมด และอาจมีค่าใช้จ่ายสูง โปรดดูข้อมูลอ้างอิง API ของ XCalculatable

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

คุณจะตรวจสอบการเปลี่ยนแปลงและรู้ได้อย่างไรว่าเมื่อใดควรเปลี่ยนวิธีการ?

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

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