This is an inquiry about the efficiency of spreadsheet software and consequently advocation for LibreOffice (LO) Calc. While trying to advocate LO Calc to business colleagues, I encountered an irreconcilable efficiency challenge between LO Calc and MS Excel. I appreciate any guidance to identify the plausible culprit. Hopefully a solution of business feasibility may be found (not just technical feasibility). Perhaps the efficiency of LO Calc may be brought on par with that of MS Excel.
A report template in MS xlsm format contained many sumifs() and used parametric URLs to access data in multiple external workbooks. Details are listed in the [tests] section.
A similar efficiency gap was observed in my previous business report a few years ago. Both *.xls and *.ods formats were tested then. Similar construct of external workbooks, parametric URLs, and many sumifs(). The efficiency gap forced me to abandon LO Calc and sticked with MS Excel back then.
Wonder if efficiency gap has been observed by the community on alternative platforms? Like MS Windows 10, LO Calc 18.104.22.168 x64, etc.
Thanks in advance for guidance.
[timing in seconds]
00:02.49 MS Excel, all 3 files open in Excel
01:05.07 LO Calc, all 3 files open in Calc
01:40.25 LO Calc, xlsm open in Calc, xlsx closed
The speed gap is drastic, 30x at least.
2 xlsx files of source data.
1 xlsm file of report template.
All 3 files were in RamDisk.
xlsm file settings:
- manual recalculation mode (F9).
- blank cell if 0.0 value.
The xlsm file contains:
- parametric URLs.
- indirect( parametric URL ).
- many sumifs() using parametric URLs.
The parametric URLs refer to cell ranges in external workbooks. Parametric URLs were built from parameters and stored at run time. The stored parametric URLs were then used by many sumifs(). There were no constant URLs (constant references) in the xlsm file.
The cell region containing sumifs() extended to column S (page width of legible PDF), and down to row 411 at present. The rows of the report will increase as more items are added to the report. An abridged xlsm file is attached after conversion into ods format due to the restriction imposed by ask.libreoffice.org. The VBA code was retained in the ods file.
One plausible candidate capable of such magnitude in efficiency gap could be the file open operation. But I do not know how to test and prove / disprove this hypothesis. Guidance is appreciated.
- When does LO Calc execute file open to the external workbooks?
- Once for each parametric URL? (not likely, too clumsy)
- Once for each sumifs() call?
- Once for each = expression (each cell)?
- Once for each external workbook? (ideal, seeking evidence)
MS Windows XP SP3 x32.
CPU Pentium 3.4 GHz.
RamDisk 500 MB by ImDisk 2.0.10, assigned as B: drive.
MS Excel 2007.
LO 22.214.171.124 (last version for XP SP3).
Java JRE 126.96.36.199 x32 (last version for XP SP3).
Minor primitive code. Not likely culprit for the efficiency gap between MS Excel and LO Calc.
Option Explicit Option Base 0 ' 0 default, 1 optional, for MS VBA arrays rem base 0, arrays, MS VBA rem base 1, ranges, MS Excel rem base 0, arrays, LO Basic rem base 0, ranges, LO Calc Public Function File_Date_Time(ByVal fdir as String, ByVal fname as String) as Date Dim ffile as String ffile = fdir & fname File_Date_Time = FileDateTime(ffile) End Function ' MS syntax => b:/[item 20211022 fbrc.xlsx] Public Function link_ms_path(ByVal fdir as String, ByVal fname as String) as String Dim txt_path as String txt_path = fdir & "[" & fname & "]" link_ms_path = txt_path End Function ' MS syntax = 'fdir[fname]sht'!(col+rhead):(col+rtail) ' 'b:/[item 20211022 fbrc.xlsx]2022'!W2:W170 Public Function link_ms(ByVal fdir as String, ByVal fname as String, ByVal sht as String, ByVal rhead as Integer, ByVal rtail as Integer, ByVal col as String) as String Dim txt_path as String Dim txt_rang as String txt_path = link_ms_path(fdir, fname) txt_rang = "'" & txt_path & sht & "'!" & col & rhead & ":" & col & rtail link_ms = txt_rang End Function
item report.ods (21.7 KB)
[erAck: added ``` lines surrounding code blocks, see Guide]