User defined function not recognized

[BratcheGeige]

Entering a user defined function in a cell runs when first entered. Close and reopen the document and it doesn't run. AutoCalculate is on. ReCalculate doesn't work. Changing a cell referenced by the function does nothing. Even double clicking in the cell and hitting return does nothing. Only thing that makes it work is editing the cell (for instance by erasing and retyping a letter).

How do I get it to run automatically?
Sorry, but I have spent hours looking for an answer already.

[Zizi64]

Do you mean a macro cell function written in StarBasic by yourself, or do you use a third party DLL?

[Villeroy]

A cell function needs to be stored in the special Basic library "Standard" (either in the document or under "My Macros"). This is the only library which is always loaded.

[Zizi64]

Can you upload an .ods type sample file here?

[BratcheGeige]

I have the application automatically run a macro that loads my personal library in the application, and my "Books" document "open event" automatically runs a macro that loads its "Books" library so those libraries are loaded when the document is first opened. It should not be necessary to keep the function in the Standard library. Or am I wrong there? Why is the function not recognized?

[Zizi64]

Please upload a sample file and the code of the launched/called functions.

[Villeroy]

There is no way to load any macro code before the application recalculates sheet formulas.

[BratcheGeige]

OK, I tried putting the function in the document's Standard Library, even though the library it used to be in was already loaded) and now when I open the document I no longer get #NAME? (big improvement?) I just get an empty looking cell. The function has been recognized (or loaded or whatever the terminology is) but it hasn't been run so no value is shown. It doesn't even run when I select the menu item "Recalculate" What am I missing?

[Zizi64]

Please upload sample files, and macro code...

[BratcheGeige]

OK, if you are willing to have a look, here is my document (with most of the 4000 lines of data deleted) but all my code (in case you are curious but don't waste your time). Some things won't work because they reference macros in my application Standard Library. But my document Standard Library Functions Module now contains the function "TOTALBOOKS" (which now seems to work for some reason in this cut down version) and my document "Books" Library Functions Module contains the function "TOTALPAGES" which still produces #NAME? even though the "Books" Library is loaded. Results are on the "Total" sheet in the columns "Total Books" and Total Pages"

[Villeroy]

If people would learn some database fundamentals, all this bullshit would simply go away. Nobody would ever again open a text only spreadsheet.
Function TOTALPAGES is still in library Books.

P.S.: TOTALBOOKS returns zero if the data range does not contain the referenced year value and display of zero is disabled in the view options, thus the cell appears to be blank. TOTALBOOKS is a bogus function. It serves no purpos other than to impress somebodie's big boss. COUNTIF does the same much quicker.
The whole document is a pain in the brain.

[musikai]

If people would learn some database fundamentals, all this bullshit would simply go away. Nobody would ever again open a text only spreadsheet.
.

But you just opened Bratchgeiges ods.

If it just would be easy to understand how to use a database and what you can do with it.
For me: Is there any tutorial for musicians mind to create a database that manages pdfs of music scores?

[BratcheGeige]

Thank you Villeroy for your recommendation. I realized a long time ago (while using Excel) that a database would be more appropriate for my Books but I tried studying Microsoft Access and found it much more difficult to understand than Excel. Would you say that OpenOffice Base is any easier than Access?
As for your recommendation of SUMIF, how could I have each criteria check look at the value in the corresponding row in another column? SUMIF(ColumnToSumRange;Year = corresponding row in CriteriaColumnRange)

[Villeroy]

OpenOffice Base is even more difficult because it is just a tiny addition to this office suite, less advanced than Access. The underlying concepts are the same. However, what could be more intimidating than StarBasic macros?

[jrkrideau]

@ BratcheGeige

It looks like you are trying to reinvent the wheel. I have only had a very quick look at what your spread is trying to do but it looks to me as if it would be much easier to use a bibliographic management system. Have a look at Zotero https://www.zotero.org/ for a good example of what an open source bibliographic management system is like.

My bet is that Zotero will do anything you want and probably quite a bit more. If you don't like the look of Zotero there are several other alternatives available; a quick Google search should bring up some of them.

[Villeroy]

Yes, Zotero is a read-made program for the purpose.

Your spreadsheet does not even try to use the limited features that exist in Excel and Calc. Instead it tries to be clever with stupid Basic code.