Must all Basic calc macros be in "Standard?"

[urbanian]

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 my calc csheet to =TESTX(), instead I get #NAME!.

So obviously calc recognizes the name, just for some reason won’t run the macro.

I’ve set macro security to allow everything, and that doesn’t help. So that isn’t the issue.

[Wanderer]

Have you loaded the library ?

Compare with Tools:
https://help.libreoffice.org/latest/ro/text/sbasic/shared/03/lib_tools.html

Or Wiki on loading libraries (chapter 4.2.2)
https://wiki.documentfoundation.org/Documentation/DevGuide/LibreOffice_Basic#Loading_Libraries

[Villeroy]

User defined Basic functions for spreadsheet formulas need to be stored in the “Standard” library of the document or the global one.
Real spreadsheet functions with documentation and localized parameter names appearing in the function wizard need to be written in a real programming language and wrapped into an extension package.

Extension with a very useful function and sample spreadsheet: Apache OpenOffice Community Forum - [Calc][oxt] A function for all python string methods - (View topic)

[urbanian]

As near as I can tell, loadLibrary() only works when calling another library from the standard library. I tried a function in Standard that loaded my other library then called a function in it. That worked. But even though the other library is now (presumably) loaded, I still can’t reference it directly from my document.

Looks like I’m stuck putting everything into the Standard library.

[urbanian]

I would have preferred using a “real” language like Python, but the contortions needed to do that presented more of a learning curve than I was willing to climb.

[urbanian]

I still find it odd that LO appears to know that the function exists, just can’t figure out how to load it.

[Zizi64]

Sub LoadMyLibrary

	If (Not GlobalScope.BasicLibraries.isLibraryLoaded("NameOfYourLibrary")) Then
		GlobalScope.BasicLibraries.LoadLibrary("NameOfYourLibrary")
	End If
end sub

Put this macro into the Standard Library (that will be loaded automatically), and then assign the macro to some Event what will be raised after launching the of the Calc application.

Otherwise you can load a library by the UI, manually: Just click on the icon near od the Library name in the StarBasic IDE. The yellow ones are loaded. And use one of the excellent Object Inspection Tools: XrayTool or MRI.

[Villeroy]

@Zizi64

Otherwise you can load a library by the UI,

Function ANSWER()
ANSWER = 42
End Function

Put this in any library other than “Standard”, enter =ANSWER() into a sheet cell and the answer will be #NAME!
Move the function into “Standard” and the answer will be 42.

[Zizi64]

OOopsss!

Yes, the result is #NAME? before I have loaded the library manually, and the result is #VALUE! after I have loaded the user defined Library. (It must retype/reedit the formula.)
The LO Calc can recognise the name of the function in the user Library (the name becomes uppercase), but there is not a valid result…

LO6.1.6.

(The manual load works for me with the XRAYTOOL extension. Sometimes I load it by code, and sometimes manually. But: that uses a Sub instead of a Function.)

[mikekaganski]

This is a bug. The respective code is wrong, assuming that pObject->GetParent() necessarily means “document BASIC”, and taking the parent’s name as the document, which will be passed to SfxObjectShell::CallBasic. But when the library is not “Standard”, the parent of that “non-standard” library is the “Standard” library … and the call fails.

You may file a bug report.