User defined function not recognized

Creating a macro - Writing a Script - Using the API

User defined function not recognized

Postby BratcheGeige » Tue Oct 15, 2019 2:06 pm

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.
Last edited by MrProgrammer on Wed Oct 16, 2019 10:48 pm, edited 1 time in total.
Reason: Moved topic from Calc forum to Macros and UNO API
OpenOffice 4.1.7
MacOS 10.15 Catalina
User avatar
BratcheGeige
 
Posts: 11
Joined: Thu Oct 10, 2019 6:02 pm

Re: User defined function not recognized

Postby Zizi64 » Tue Oct 15, 2019 2:09 pm

Do you mean a macro cell function written in StarBasic by yourself, or do you use a third party DLL?
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8477
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: User defined function not recognized

Postby Villeroy » Tue Oct 15, 2019 2:09 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27300
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: User defined function not recognized

Postby Zizi64 » Tue Oct 15, 2019 2:26 pm

Can you upload an .ods type sample file here?
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8477
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: User defined function not recognized

Postby BratcheGeige » Tue Oct 15, 2019 6:55 pm

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?
OpenOffice 4.1.7
MacOS 10.15 Catalina
User avatar
BratcheGeige
 
Posts: 11
Joined: Thu Oct 10, 2019 6:02 pm

Re: User defined function not recognized

Postby Zizi64 » Tue Oct 15, 2019 6:58 pm

Please upload a sample file and the code of the launched/called functions.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8477
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: User defined function not recognized

Postby Villeroy » Tue Oct 15, 2019 7:49 pm

There is no way to load any macro code before the application recalculates sheet formulas.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27300
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: User defined function not recognized

Postby BratcheGeige » Tue Oct 15, 2019 8:00 pm

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?
OpenOffice 4.1.7
MacOS 10.15 Catalina
User avatar
BratcheGeige
 
Posts: 11
Joined: Thu Oct 10, 2019 6:02 pm

Re: User defined function not recognized

Postby Zizi64 » Tue Oct 15, 2019 8:02 pm

Please upload sample files, and macro code...
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8477
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: User defined function not recognized

Postby BratcheGeige » Tue Oct 15, 2019 8:35 pm

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"
Attachments
Reading_CutDown.ods
(75.33 KiB) Downloaded 10 times
OpenOffice 4.1.7
MacOS 10.15 Catalina
User avatar
BratcheGeige
 
Posts: 11
Joined: Thu Oct 10, 2019 6:02 pm

Re: User defined function not recognized

Postby Villeroy » Tue Oct 15, 2019 9:42 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27300
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: User defined function not recognized

Postby musikai » Tue Oct 15, 2019 11:42 pm

Villeroy wrote: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. :mrgreen:

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?
Win7 Pro, Lubuntu 15.10, LO 4.4.7, OO 4.1.3
Free Project: LibreOffice Songbook Architect (LOSA)
http://struckkai.blogspot.de/2015/04/libreofficesongbookarchitect.html
musikai
Volunteer
 
Posts: 254
Joined: Wed Nov 11, 2015 12:19 am

Re: User defined function not recognized

Postby BratcheGeige » Wed Oct 16, 2019 6:36 am

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)
OpenOffice 4.1.7
MacOS 10.15 Catalina
User avatar
BratcheGeige
 
Posts: 11
Joined: Thu Oct 10, 2019 6:02 pm

Re: User defined function not recognized

Postby Villeroy » Wed Oct 16, 2019 9:26 am

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?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27300
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: User defined function not recognized

Postby jrkrideau » Wed Oct 16, 2019 1:16 pm

@ 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.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3706
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: User defined function not recognized

Postby Villeroy » Wed Oct 16, 2019 7:54 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27300
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: DavidHMcCracken and 2 guests