Macro security: How to allow my own macros without allowing all

[urbanian]

I wrote my own macros (quite a learning experience) to do a moderately complicated calculation. How do I convince calc to allow them without allowing everything, since “everything” is a security issue?

[sokol92]

One way is to use Trusted file locations.

[urbanian]

More detail required, I guess. And if it isn’t obvious, I’m really new to creating macros.

When I first started I was putting my macro in the “standard” place. For some reason I couldn’t access it from my spreadsheet. I don’t know what I was doing wrong, but I just tried a simple case and it seems to work.

But, to get it to work, I followed the instructions to copy the file into my spreadsheet. That worked; the macro is only in the spreadsheets that need it, and I just duplicate the original sheet when I need another.

So, when you say trusted file locations, presumably I would add the calc document itself as a trusted file location?

Ah, but trusted file location is a directory, not a file. That means any macro in the directory with my file will be trusted. Perhaps a bit too open, but better than nothing.

I suppose I could sign my own macros, but that requires a certificate manager, more trouble than it’s worth.

[erAck]

@urbanian

That means any macro in the directory with my file will be trusted. Perhaps a bit too open, but better than nothing.

It’s under your control what files with macros you put there, no?

[urbanian]

It’s under your control what files with macros you put there, no?

True, but it still seems odd that all of those functions in other libraries can’t be accessed in a calc document, at least as a function in a cell.

And if we’re making that assumption, wouldn’t it make sense for there to be an explicit setting to allow macros in Standard?

[sokol92]

If we are talking about application library Standard (in the My Macros section of the tree), then the functions of this library can be used in cell formulas. Permissions are not required for this. At the same time, we understand that when opening such a document in another instance of LibreOffice, the formula with a reference to the library function will return error value (#NAME?).

[Wanderer]

@sokol92

If we are talking about application library Standard

@urbanian

functions in other libraries

So I think his point are other libraries, see

Must all Basic calc macros be in "Standard?"

I’m creating a macro. I’d like it to be in a separate library than “Standard,” But my calc document can’t seem to find it. I create a simple macro in the Standard Module1: Public Function ATest() ATest = "Success" End Function And put =ATEST() in my calc sheet, I get, as expected, “Success”. If, however, if I create a new library and put the identical code into a module there, I just get #VALUE!. But that error implies that calc at least knows that function exists. If I change the cell in …

[Villeroy]

Moderately complicated calculations should be done directly on spreadsheets. This is why spreadsheets exist. Spreadsheet calculations with built-in functions will be executed in much more efficient ways and the creators of these functions are better programmers than you and me.
Call Tools>Options… Security, button [Macro Security…]
Choose the highest security level and declare one or more directories as trusted. Do NOT declare your “Downloads” folder, but your “Documents” folder may be a good choice. Subfolders are included.