https://bugs.documentfoundation.org/show_bug.cgi?id=173023
Bug ID: 173023
Summary: Report of Unexpected Behavior: User-Defined Macros in
Calc Formulas Not Recognized Until Case Change
(Case-Sensitivity Issue) and Proposal for Enhanced
Macro Portability/Backup
Product: LibreOffice
Version: unspecified
Hardware: x86-64 (AMD64)
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Subject: Additional findings and reproducible steps for this issue (macro
recognition and case-sensitivity)
Hello,
I believe this issue is closely related to this bug (40203) and also to 79588
regarding macro portability, but I decided to open a new report because these
issues not only continue to this day 08 - 05 - 2026, but the last day any
comments on this appeared in bug 40203 were from 2020
I would like to share my experience and a specific workaround that revealed a
deeper caching problem. I am using Windows 11 and LibreOffice [Insert your
LibreOffice version here].
Steps to reproduce the exact behavior I encountered:
1. Create a custom Basic function in "My Macros & Dialogs > Standard" (e.g.,
`Function test() as string: test = "OK": End Function`).
2. In a spreadsheet saved as `.ods`, use the formula `=test()` (in lowercase).
The formula works normally on the original computer.
3. Copy the `.ods` file to a second computer (or, alternatively, copy the macro
module into the document's own Standard library using the Organizer, mimicking
the portability scenario).
4. Open the file on the second computer. The cell displays `#NAME?`, even
though the macro is now correctly placed inside the document's library.
5. Use "Find & Replace" (Ctrl+H), set the scope to "Formulas", and replace
`=test` with `=TEST` (changing from lowercase to UPPERCASE).
6. The value is immediately recalculated and works correctly.
Expected behavior:
Since the documentation states that LibreOffice Basic is case-insensitive for
function names, changing the case should not be necessary. The formula engine
should clear its namespace cache whenever a new module is inserted into the
document's library, without requiring manual cell editing.
Suggestion for improvement:
To prevent less attentive users from losing their work entirely (I only
recovered because I had saved my macro code in an external `.txt` file as a
precaution), I would like to suggest two complementary solutions:
A) A visible warning when a user saves a document that references macros not
embedded in its own `Standard` library (e.g., "This spreadsheet relies on
macros stored in your user profile. Do you want to embed them into the document
to ensure portability?").
B) A built-in wizard or option to easily import/export macro modules directly
from/to external `.txt` or `.bas` files, specifically into the document's
library. This would act as a safety net and save users from starting over from
scratch.
I hope these details help narrow down the root cause. I am available to provide
sample files, screenshots, or any additional information if needed.
Thank you for your time and for maintaining such an essential tool.
Best regards,
Cristian Nicolas
--
You are receiving this mail because:
You are the assignee for the bug.
