[Solved] Custom Function does NOTHING!

Creating a macro - Writing a Script - Using the API

[Solved] Custom Function does NOTHING!

Postby moriturimax » Sat Sep 15, 2018 9:16 pm

Just started in Open Office Calc and am trying to translate my Excel spreadsheets into Open Office. My bugaboo is that I use custom VBA functions and want to also bring them over in OOBasic form. I decided to try a very simple example as shown below.. I just want to make sure I understand the structure of a function, however when I put "=DiceRoll()" without quotes in a cell on the spreadsheet, it returns 0 after hitting F9. The spreadsheet name is Untitled2.ods

I am baffled since I seem to be missing something fundamental.
ps, is the editor supposed to automatically format DiceRoll the same as the function name? When I made the Function name "DiceRoll" and typed it in the function body and hit enter it didn't change it from "diceroll=6" to "DiceRoll=6".. I am used to VBA in Excel automatically doing it and wondering here if it is something I am doing wrong.

[Untitled 2.ods]Standard
-----------------------------------
REM ***** BASIC *****
option explicit
sub main
end sub

Function DiceRoll()
DiceRoll=6
End Function
-----------------------------------
Last edited by Hagar Delest on Sun Sep 16, 2018 12:11 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.5
Windows 10 Pro 64-bit
moriturimax
 
Posts: 14
Joined: Sat Sep 15, 2018 9:05 pm

Re: Custom Function does NOTHING!

Postby Zizi64 » Sat Sep 15, 2018 10:24 pm

I am used to VBA in Excel automatically doing it and wondering here if it is something I am doing wrong.

The interpreter will notify you - based on the Option Explicit - if you mispell a variable name. Tha caps generally are not relevant in StarBasic - but there are some cases (in the names and variables of the API functions) when you must use exact caps.

Where you stored the macro? Into the document? Or into the MyMacros?
What file type are you using?

Please upload a real, ODF type spreadsheet file here.
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.7 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.0 and AOO4.1.5
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: 7162
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Custom Function does NOTHING!

Postby moriturimax » Sun Sep 16, 2018 12:37 am

Here is the basic sheet I was commenting on... it saved as ods, is that okay?
Attachments
testing2.ods
trying to get a function to work
(9.71 KiB) Downloaded 5 times
OpenOffice 4.1.5
Windows 10 Pro 64-bit
moriturimax
 
Posts: 14
Joined: Sat Sep 15, 2018 9:05 pm

Re: Custom Function does NOTHING!

Postby FJCC » Sun Sep 16, 2018 12:53 am

The fucntion recalculates for me when I press CTRL+Shift+F9. It also recalculates if I edit the function to take a parameter x, which isn't used in the function body, I write
=FICE(A1)
in a cell and change the value of A1.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6700
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Custom Function does NOTHING!

Postby moriturimax » Sun Sep 16, 2018 12:53 am

Really? Playing with keys... SHIFT-CTRL-F9 seems to get it to work... using TOOLS>CELL CONTENTS>RECALCULATE has no effect, F9 has no effect, but SHIIFT-CTRL-F9 does? Really?
OpenOffice 4.1.5
Windows 10 Pro 64-bit
moriturimax
 
Posts: 14
Joined: Sat Sep 15, 2018 9:05 pm

Re: Custom Function does NOTHING!

Postby moriturimax » Sun Sep 16, 2018 12:56 am

FJCC wrote:The fucntion recalculates for me when I press CTRL+Shift+F9. It also recalculates if I edit the function to take a parameter x, which isn't used in the function body, I write
=FICE(A1)
in a cell and change the value of A1.


Arg, saw your reply right after I entered mine, you are fast... thanks for the parameter suggestion.. I vaguely remember in the past I had the same workaround in an Excel sheet.
OpenOffice 4.1.5
Windows 10 Pro 64-bit
moriturimax
 
Posts: 14
Joined: Sat Sep 15, 2018 9:05 pm

Re: Custom Function does NOTHING!

Postby moriturimax » Sun Sep 16, 2018 12:59 am

adding a parameter still doesn't do anything when I F9 recalc, but as long as SHIFT-CTRL-F9 works I'll count myself happy.
OpenOffice 4.1.5
Windows 10 Pro 64-bit
moriturimax
 
Posts: 14
Joined: Sat Sep 15, 2018 9:05 pm

Re: Custom Function does NOTHING!

Postby FJCC » Sun Sep 16, 2018 1:32 am

Yes, really. I think F9 only triggers recalculation for functions whose parameters have changed. Since your function does not take any parameters, F9 has no effect. CTRL+Shift+F9 is a hard recalculate, i.e. recalculate even if you don't think you have to.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6700
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Custom Function does NOTHING!

Postby moriturimax » Sun Sep 16, 2018 4:13 am

Ha! Got it, again thanks to the parameter suggestion, I had put an incorrect function in the function call, changed it to =FICE(RAND()) and now F9 updates.. I stuck RND in there by mistake.. excel habits.

Many thanks, it just bugged me.

PS on a related note, is the IDE supposed to properly upper case keywords and such? When I enter a macro as something like "function fice()" and so on, it doesn't properly format it to "Function Fice()".. I figured it would do it so it would stand out as a quick visual aid.. I see it that way in online tutorials, everything in the IDE is properly annotated and colored and such...
OpenOffice 4.1.5
Windows 10 Pro 64-bit
moriturimax
 
Posts: 14
Joined: Sat Sep 15, 2018 9:05 pm

Re: Custom Function does NOTHING!

Postby Zizi64 » Sun Sep 16, 2018 9:23 am

it saved as ods, is that okay?

The StarBasic+API functions will work in the .ods format only. The .ods is one of the ODF formats. ODF: Open Document format. It is an International Standard:
ISO/IEC 26300-1:2015
ISO/IEC 26300-2:2015
ISO/IEC 26300-3:2015




PS on a related note, is the IDE supposed to properly upper case keywords and such? When I enter a macro as something like "function fice()" and so on, it doesn't properly format it to "Function Fice()".. I figured it would do it so it would stand out as a quick visual aid.. I see it that way in online tutorials, everything in the IDE is properly annotated and colored and such...



The names of the functions always will be appeared in ALLCAPS format in the cells where you call them. As I remember, it worked similarly in the Excel too.
But
- you can use (maually) "CamelCaps" for your function names;
- or you can write them without caps;
- or you can use the ALLCAPS format
in your macro code. The IDE will not modify them, but it can accept them.

There is an extension for AOO (maybe it work with LibreOffice too...) to format the macro code:
https://extensions.openoffice.org/en/pr ... -colorizer
but I never tried it...
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.7 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.0 and AOO4.1.5
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: 7162
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests