Macro for Open Document event in Calc seems to be invoked too late

[weez]

I am using two libraries in my .ods file: Standard and Custom. I have a LoadLibs macro in a module in the standard library that does an explicit load of the Custom library. The LoadLibs macro is set as the assigned action for the Open Document event on the .ods file.

The sheet in the .ods file has a cell that invokes a function macro (FM) that is defined in the Standard library. FM then invokes another function defined in the Custom library. When the Custom library is already loaded, all is fine and FM behaves as expected.

However, when opening the file, there is an error: “Basic runtime error. Sub-procedure or function procedure not defined”. When I click OK, the spreadsheet opens and I can see that the cells that called FM have incorrect values. When I press F9 to recalculate, they are not updated. When I press Ctrl-Shift-F9 to recalculate hard, they are updated with the expected values.

I tried the same setting the LoadLibs macro as the assigned action for the Document Loading Finished and Activate Document events as well, with the same [unexpected] result.

It seems that the Custom library is getting loaded (which I can verify in the Basic IDE after getting past the initial runtime error), but only after the cells in the sheet are calculated when the file is loaded.

How can I get the Custom library to be loaded before any calculations are done so I can avoid the error and the sheet has the proper values without needing to do a recalculate hard?

[jfn]

Hi,

in your FM macro you could check whether the Custom library is loaded. If it isn’t, do it.

    With GlobalScope.BasicLibraries
       If Not .IsLibraryLoaded("Custom") Then .LoadLibrary("Custom")
    End With

Of course, adapt the above to your context (GlobalScope vs document scope).

[mikekaganski]

Sigh - why answer, when it’s wrong? @weez wrote that it doesn’t work on open. I provided a link to the explanation - during the loading of the document, checking existing macros in the loaded libraries, and marking some formula tokens as errors happens before any macro is ever run, so no running code could change this.

[jfn]

MMM… Seems like I didn’t fully understand the whole picture.
Sorry for the noise.

[mikekaganski]

User-defined functions must be defined only in Standard (or, if you use MS formats, in VBAProject) library. This is current functional limitation. You can have a minimal “stub” in Standard, that would actually load the other library, and call the code from there.

See 79588 – Macros called from within formulas doesn't survive save, close and reopen of the calc-document.

[weez]

Thanks, Mike. Using a stub was my best work-around so far – I defined a function in Standard that returns the empty string which also loads the Custom library. I then concatenate that result to the contents that I want in cell A1. That seems to get around the issue. That seems to be OK for the first sheet, but I’m getting other related issues on the second sheet. I think I’m going to have to give up and either put everything in Standard (which I’d rather not do) or find an alternative.