https://bugs.documentfoundation.org/show_bug.cgi?id=173411
Bug ID: 173411
Summary: FORMATTING: Range.ClearFormats (VBA compatibility
mode) does not remove cell merge state
Product: LibreOffice
Version: 26.2.5.2 release
Hardware: x86-64 (AMD64)
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Created attachment 208369
--> https://bugs.documentfoundation.org/attachment.cgi?id=208369&action=edit
CompetitionScoreFile_LibreCalcCrashWhenUsedMacro
Slightly relating to bug ID 89951 and 123334
Root Cause Explanation: LibreOffice Calc Crash on Programmatic Sort
Symptom: LibreOffice Calc crash when a Basic macro programmatically sorts a
range that unexpectedly contains merged cells.
Underlying bug: Range.ClearFormats, when called under VBA compatibility mode
(Option VBASupport 1), does not remove cell merge state.
In real Microsoft Excel VBA, calling Range.ClearFormats on a range containing
merged cells also un-merges them — merge state is treated as part of cell
formatting. In LibreOffice Basic's VBA-compatibility layer, ClearFormats clears
fonts, colors, and number formats, but the underlying merge (a structural
row/column-span property) survives untouched.
How this manifests in a real workbook:
The macro in question builds a results sheet every week. On each run it:
Clears the working area with Range.ClearContents + Range.ClearFormats.
Re-merges a header row at a position calculated dynamically from that week's
data (e.g. based on how many players are in a given class — this position is
not fixed and shifts week to week).
Writes fresh data and sorts it via dispatcher.executeDispatch(frame,
".uno:DataSort", "", 0, args).
Because step 1 never actually un-merges anything, a header merge created in a
previous run at row N survives every subsequent "clear." When a later run's
data grows and happens to overlap row N, the sheet ends up with stray, empty,
still-merged cells sitting silently inside what should be a plain data range.
When the .uno:DataSort dispatch then targets a range that includes those
leftover merged cells, Calc needs to show a modal warning dialog ("Ranges
containing merged cells can only be sorted without formats") before it can
proceed. Because the sort was triggered programmatically via executeDispatch()
— with no code path to click "OK" on that dialog — the application stalls
waiting on user input that will never arrive. This presents to the end user as
a hang/freeze rather than a clean error, and often requires force-quitting the
process.
Minimal reproduction (isolates the formatting bug only, without the full
macro):
vb
Sub TestClearFormats()
' Assumes C1:D1 is already merged on the active sheet
ActiveSheet.Range("A1:F5").ClearFormats
' Click cell C1 afterward - it will still select the whole C1:D1
' block, proving the merge survived ClearFormats.
End Sub
Full reproduction (triggers the actual hang), for context: the same pattern,
but followed by a dispatcher.executeDispatch(..., ".uno:DataSort", ...) call on
a range that still contains the surviving merge. That combination is what
causes the freeze; the workbook I'm attaching separately reproduces the
formatting bug cleanly, but not the hang itself — the user's own file (uploaded
separately) reproduces the full hang and should be used for that part of the
report.
Suggested fix on the LibreOffice side: Range.ClearFormats under VBA
compatibility should also clear merge state, matching real Excel VBA behavior —
or at minimum, .uno:DataSort should degrade gracefully (skip/report an error)
rather than block on an un-dismissable modal dialog when invoked via
executeDispatch().
Workaround on the macro side:
vb
ws.Range("A1:L200").UnMerge
ws.Range("A1:L200").ClearContents
ws.Range("A1:L200").ClearFormats
To test file macro press the button, warning comes up, only choice is OK, then
later as macros progress it actually crashes librecalc totally.
Hope this helps.
Great software by the way.
/Robert
--
You are receiving this mail because:
You are the assignee for the bug.
