How to disable macros on document load?

[sashkinaaa]

Hello,

I have written a simple macro(you can see it below) for getting the background color of a cell and it works fine. But when I save the document and open it I get error for every single line using the macro. The error says: “Basic Runtime Error. Property or method not found: getSheets”. I think that when the document loads something happens and I want to stop the macro when the document loads. Please help me running the macro only when I want/need.

P.S. In Tools > Customize > Events there isn’t anything.

Function getColor(c,r)
  Dim oDoc  As Object   ' define variables
  Dim oSheet As Object
  Dim oCell As Object
  Dim bgcolor As String
  oDoc  = ThisComponent
  oSheet= ThisComponent.getCurrentController.getActiveSheet
  oCell = oSheet.getCellByPosition(c-1,r-1)
  bgcolor = oCell.CellBackColor
  getColor = Switch(bgcolor = "16776960", "yellow", _
             bgcolor = "43315", "green", bgcolor = "16711680", "red")
End Function

(Edited slightly for better readability of the code by @Lupp )

[Lupp]

(I do not know a reasonable way how to disable macros first, and enable them later. You might try to use a global variable fed by a Sub called on a suitable document event. Every sensitive user function would then test for that variable and return a preliminary and invalid result on the first run. I did not test this idea. Below I will try to avoid the problem from the beginning.)

First of all: Your code cannot work as expected because you try to compare the numeric values you get for .CellBackColor with strings.

Secondly: Even the working variant

getColor = Switch(bgcolor = 16776960, "yellow", _
             bgcolor = 43315, "green", bgcolor = 16711680, "red")

is not recommendable insofar as you compare in every case with one of hundreds of colorcodes representing colors looking very closely like the palette color you want to identify. Palettes may change with time and from app to app. The LibO standard-palette Green e.g. has currently (R,G,B)=(0,169,51) (your 43315) while html uses (R,G,B)=(0,128,0) (decimal 32768) for Green.

Finally: Concerning the error message you mentioned I can only guess you are working with a buggy version. I think to remember a time when newly loaded Calc documents tried to run macros using not yet sufficiently initialized objects. This could lead to errors of that kind. Please edit your question and add information about the used version of LibO and your OperatingSystem (including the version).

Sorry. I was wrong. My current V 6.3.3 also raises an error when recalculating your function “on load”. I obviously hadn’t used macros of the king for some time. Will come back if there is a new idea.

It shouldn’t be too surprising that the ActiveSheet which is the viewed sheet in fact not is initialized before the recalculation “on load” is finished. Anyway the user code posted above seems to be based on the misunderstanding during calculations the ActiveSheet was the sheet for which calculation as running. You need to choose the sheet containing the cell for which the .CellBackColor explicitly.

Start with

Function getColor(s, c, r)
Dim oDoc  As Object   ' define variables
Dim oSheet As Object
Dim oCell As Object
Dim bgcolor As String
oDoc  = ThisComponent
oSheet= ThisComponent.getSheets(s-1)
oCell = oSheet.getCellByPosition(c-1,r-1)
bgcolor = oCell.CellBackColor
getColor = Switch(bgcolor = 16776960, "yellow", _
             bgcolor = 43315, "green", bgcolor = 16711680, "red")
End Function

and find a better way to assign a color name to the RGB value. A raw suggestion you find here.

[sashkinaaa]

Thank you very much @Lupp. I am using Win 10 Pro and LibreOffice 6.3.2.2.
I tried your solution and it works very well. I think the problem is because I put the function in My macros. When I put it on every document it works very well. The RGB values are very cool and your solution helped me a lot.

[Lupp]

If you want to place the code in a module of the local Standard library, here is a crude workaround:

Function getColor(s, c, r)
getColor = "Needing recalculation (Ctrl+Shift+F9)!"
On Local Error Goto fail
    ...Code as posted / included earlier
    ....
getColor = h
fail:
End Function  

It will not meet the needs of direct printing from a file manager or similar.

[sashkinaaa]

Again, thank you very much @Lupp… This is all I neded.