[Solved] Macro available but not from CELL

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
bxdobs
Posts: 9
Joined: Fri Oct 10, 2014 4:56 am

[Solved] Macro available but not from CELL

Post by bxdobs »

Running Apache OO Calc 4.1.5 under Win 7Pro 64b

Just copied a few macros from Excel (with mods compatible with OO) ... manually added the macro listed below via the editor ... while the macro menu shows the macro name, a cell function is giving me a #NAME error when attempting to call it from a CELL

= gfnRd(123.45678,3) should return 123.457
= gfnRd(123.45678) should return 123.46

Any ideas as to what is causing this issue?

Needing to add a Round() function that emulates the Excel Round() ... Trunc() is apparently unavailable to the Macro

Code: Select all


Public Function gfnRd( D as Double, Optional Dxc as Integer) as Double
   dim z as double
   dim dc as integer
   dim i as integer

   dc = 2

   if NOT isMissing(Dxc) then dc = Dxc

   z = D

   if dc >= 0 then 
   
   for i = 1 to (dc + 1)

      z = z * 10
   next      
   
    z = int(z)
   
   for i = 1 to (dc + 1)
      z = z / 10
      if i = 1 then z = int(z - int(z) + .5) + Int(z)
   next       

   end if

   gfnRd = z

End Function

Last edited by Hagar Delest on Fri Mar 01, 2019 11:14 am, edited 1 time in total.
Reason: tagged solved
bxdobs
Posts: 9
Joined: Fri Oct 10, 2014 4:56 am

Re: Marco showing in Macro selection but not available from

Post by bxdobs »

Update

After adding and manually calling another macro function that directly called the gfnRd function, the gfnRd function now works from a Cell function ... expecting there must be a bug within the OO macro registry logic.
OpenOffice 4.1.1 on Windows XP PRO SP3
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Marco showing in Macro selection but not available from

Post by Zizi64 »

After adding and manually calling another macro function that directly called the gfnRd function, the gfnRd function now works from a Cell function ... expecting there must be a bug within the OO macro registry logic.
Where is the code of function gfnRd located/stored?
Your macro function works for me if it is located in MyMacros - Standard library - Module1 of my AOO4.1.6 portable version. There is not any bug here. Update your 4.1.1 version displayed in your signature.
I have tried it with only one User defined macro function - not other macros located in the only one module named Module1.

Note:
Only the "Standad" library will be loaded into the memory automatically at starting of the Calc. Other user defined libraries will be loaded only when you call a function or sub from them. Or you can force the loading the other libraries from your macro or manually.


Needing to add a Round() function that emulates the Excel Round() ... Trunc() is apparently unavailable to the Macro
Otherwise, there are such existing built-in functions in the AOO and LO Calc named ROUND() and TRUNC). Why they are not appropriate for you? The function name is depended on the locale settings in the AOO, but you can set the language of the function names permanently to "English" in the LibreOffice independently from other locale settings.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
bxdobs
Posts: 9
Joined: Fri Oct 10, 2014 4:56 am

Re: Marco showing in Macro selection but not available from

Post by bxdobs »

The Macro (typo in the title) is stored under My Macros, Standard, Module1

As to the Round() and Trunc() Functions, they don't appear to be available to the MACRO script language? Tried both and the Macros fail with what amounts to syntax errors.

I don't need this Rounding Function for Cell calculations it is required for a Macro copied from Excel ... was just kind of taken back when the function wasn't available from a cell ... still thinking this may be a bug ... I physically moved the function in the module and it failed until it was called by the test function first.
OpenOffice 4.1.1 on Windows XP PRO SP3
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Marco showing in Macro selection but not available from

Post by Zizi64 »

As to the Round() and Trunc() Functions, they don't appear to be available to the MACRO script language? Tried both and the Macros fail with what amounts to syntax errors.
You can call the Cell Functions from your macro (there are code snippets in this forum).
viewtopic.php?f=45&t=75288

And you can round with StarBasic commands too by usage the "compatibility mode": viewtopic.php?f=9&t=65419

Code: Select all

Function myRound(D as Double, Optional Dxc as Integer) as Double
CompatibilityMode( true )
myRound = round(D, Dxc)
End function
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
bxdobs
Posts: 9
Joined: Fri Oct 10, 2014 4:56 am

Re: Marco showing in Macro selection but not available from

Post by bxdobs »

Thanks for the link and CompatibilityMode (true) feature
OpenOffice 4.1.1 on Windows XP PRO SP3
Post Reply