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

English
#1

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?

#3

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).

#4

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.

#6

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

#2

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.

1 Like
#5

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.

#7

Hello Mike,
thanks a lot, explains a lot and degrades “a bug” to “a feature”.

But, is there any function, trigger or whatever available to force function resolution (which is done before loading the basic stuff, except standard-lib) for the complete Calc-document after all libraries have been loaded (e.g. in an document event)?
Same as when I modify the formula in the cell content (insert and , then ).

That would really help.

#8

What is the disadvantage of the following scheme (see also the attached file)?
At the beginning of each UDF function from the Standard document library, we call CheckLib().
Document Standard library, any module.

Global IsOpened As Long

' UDF function
Function StdFunc()
  CheckLib() 
  StdFunc=MyLibFunc()
End Function

Sub CheckLib()
  If IsOpened=0 Then
    BasicLibraries.LoadLibrary("MyLib")
    IsOpened=1
  End If
End Sub

Document MyLib library, any module.

' Returns current date and time.
Function MyLibFunc()
  MyLibFunc=CStr(Now())
End Function

TestUDF.ods (11.8 KB)

1 Like
#9

Hi,
yes, this workaround works … but is additional (more or less tiresome) coding and forces you to create a set of new names for the functions in the LIB which you want to use directly as UDFs.
Today this workaround is OK for me (not doing to much with basic).

But maybe someone reading here can check the following idea:
For libraries contained in the document (not global) one could set an option (per library) to be loaded automaticalle together with the standard lib … then one could select which lib to “autoload”.
Or for simplicity just one setting in the documents properties to autoload all libs which stored in the document (not global).

BTW, is your code using the “IsOpend=0…” preferred compared to “BasicLibraries.isLibraryLoaded(…)”?

#10

Hello!
We follow the above restriction that all UDF functions must be in the Standard (VBAProject) library.

The fewer calls to objects (BasicLibraries), the better.

#11

OK, I understood !
Thanx

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.