Safe computational macros

[ratukima]

Hey there!

I’m trying to find a way to make my spreadsheets a bit more readable. Right now I have cells which contain lots of computational complexity and which are really unreadable and ugly. I would like to do something very simple and completely safe as a function that I can reference from my spreadsheet. Image something like this:

Function Foo(x)
Foo = (12 * x) + 9
End Function

I.e., inherently safe, nothing possibly malicious about it. However, as soon as I embed any macro, obviously I’ll run into the macro warnings of LibreOffice (and Excel for that matter) which will make users not want to turn them on (rightfully so) and then the document not to work as intended. Is there a way where I can access a limited subset of macros (e.g., nothing unsafe like file access, etc) and then have those embedded macros (or user-defined functions) always work for any security setting?

Thanks!

[karolus]

@ratukima

always work for any security setting?

No, otherwise these security settings would be completely useless

[ratukima]

They would not if it would allow a safe subset of macros. Which apparently doens’t exist in LibreOffice as I take it? In Excel, this is possible using LAMBDA().

[karolus]

maybe have a look at ⇒ Tools ⇒ Options ⇒ Security ⇒ Macro Security ⇒ Trusted sources ?

[EarnestAl]

See tdf#138749 for LAMBDA() Corrected, thanks @Lupp
See tdf#137543 for LET() Resolved for 24.8 and can be tested in daily builds

[Lupp]

@ratukima

Right now I have cells which contain lots of computational complexity and which are really unreadable and ugly.

An attached .ods containing realistic examples might help a contributor to suggest an applicabel "real-world workaround".
Implementing additional MS-Excel-features may help with compatibility.
If those features are actually useful and implemented reasonably in some Excel versions may still be doubtable. (In case of the mentioned LET() I actually doubt seriously. You may help me considering this by showiing me some convincing real-world examples.)

[Lupp]

@EarnestAl

See tdf#38749 for LAMBDA() …

Actually tdf#138749

[keme1]

assumption

If your users want to read your formulas, then they will perhaps also want to read your macros. For the regular user, navigating the macro interface will be perceived as added complexity.
By “regular user” I mean those who will retreat at every warning, instead of actually read the warning and act accordingly. From your request, I assume that this is the type of user you are targeting.

Suggested solutions

Not what you asked for, but perhaps a viable path anyway:

To make your formulas easier to read/maintain:

• Divide your single cell complex formulas across multiple cells with simpler subformulas.
• Name your cells/ranges, and use the names in your formulas.
• Insert explanation as labels/prompts in adjoining cells, or as comments to cells.

To allow macros:
Direct your users on how to allow/enable macros. Make sure to also tell them that they should not enable any macro from any source, but be conscious about who they trust. For the aforementioned “regular user”, such direction may still be TL;DR. Alas, my list of viable options is then exhausted.

I appreciate that macros are sometimes necessary. However, in addition to the security issue they also reduce portability in many cases. Cell formulas are more “universal”.