Is EOMONTH an internal function or an Analysis Add-In function?

English
,
#1

Hi everyone,

The following function calls appear to do the exact same thing, and I’m not sure if there are any advantages to using one or the other:

dtResult = oService.callFunction("com.sun.star.sheet.addin.Analysis.getEomonth", Array("2001-09-14", 6))

dtResult = oService.callFunction("EOMONTH", Array("2001-09-14", 6))

A few weeks ago, I needed to find a function to calculate the last day of a given month for use in a macro (N.B. not for use in a spreadsheet) and didn’t have much time to do a deep dive into how it worked, I just needed to get it working quickly.

The code snippet that I found here, which finally did the trick, was this:

Function CalculateEndOfMonth(ByVal date As String, ByVal months as Integer) As Long
	mArgs = Array(date, months)
	fca = createunoservice("com.sun.star.sheet.FunctionAccess")
	lResult = fca.callFunction("com.sun.star.sheet.addin.Analysis.getEomonth", mArgs())
	CalculateEndOfMonth = lResult
End Function

As you can see, this function uses "com.sun.star.sheet.addin.Analysis.getEomonth"

Now that I have some time to clean up my code, I’ve spent the last few days studying the documentation and forum discussions on here, on StackExchange, the OpenOffice forums and so on, but I still have no answer to the following question:

The official documentation, in the section “Calling Internal Calc functions in Basic”, seems to show that it is possible to call internal Calc functions using the syntax:

oService.callFunction("FUNCTIONNAME", Array(param1, param2, ...))

However, if you scroll down on the same page, in the section “UNO Service Names for Analysis Add-In Functions”, EOMONTH is listed as an Analysis Add-In function with the corresponding UNO service name being com.sun.star.sheet.addin.Analysis.getEomonth.

So, is EOMONTH an internal function or an Analysis Add-In function? Which one should I be using in a Basic macro: EOMONTH or com.sun.star.sheet.addin.Analysis.getEomonth?

Thanks! :blush:

#10

DATE(y;m;d) can calculate with all kinds of offsets in years, months and days, even with negative arguments.
=EOMONTH(A1;m) is equivalent to =DATE(YEAR(A1);MONTH(A1)+m+1;0)

1 Like
#11

There is the Function:

=DAYSINMONTH(C7)

C7 is a date.

#16

which is COM.SUN.STAR.SHEET.ADDIN.DATEFUNCTIONS.GETDAYSINMONTH(C7)
equivalent: =DAY(DATE(YEAR(C7);MONTH(C7)+1;0))

#17

@RayCulp , thank you for a topic that we all learned a lot from!


However, in my opinion, it is always better to implement such functions using built-in programming language tools.

' Adds `m` months to date `d` and returns the last day of the month.
Function CalculateEndOfMonth(ByVal d As Date, ByVal m as Long) As Date
    Dim d2 As Date
    d2=DateAdd("m", m, d)
    CalculateEndOfMonth=DateSerial(Year(d2), Month(d2) + 1, 1) - 1
End Function
1 Like
#2

In tdf#150203, @erAck had implemented access to the add-in functions by their short name using FunctionAccess. This didn’t make the EOMONTH an “internal” function (it is still implemented using add-in mechanism); but now (since 7.4.1) you can access the same EOMONTH using both mechanisms.

Note that the mechanism actually suggested in the documentation for calling the add-in functions (as opposed to setting them in formulas) is like

  oPropBag = CreateUnoService("com.sun.star.beans.PropertyBag")
  oPropBag.addProperty("NullDate", 0, CDateToUnoDate(Empty))
  oService = CreateUnoService("com.sun.star.sheet.addin.Analysis")
  lResult = oService.getEomonth(oPropBag, nDay, nMonth)

(see tdf#148646 for explanation of the oPropBag parameter, used because this Analysis function takes an XPropertySet additional argument).

1 Like
#3

Thank you very much for responding so quickly.

Note that the mechanism actually suggested in the documentation for calling the add-in functions (as opposed to setting them in formulas) is like

That’s a bit confusing. Could you please point me to the place in the documentation that recommends this syntax? Because the section of the documentation where I found

oService.callFunction("FUNCTIONNAME", Array(param1, param2, ...))

seems to be specifically about calling these functions in Basic.

#4

Look for this piece:

image
image872×392 49.1 KB

But I had to add additional stuff because of the mentioned documentation issue.

#5

Also, if EOMONTH is still implemented through an add-in, when I click Insert > Function in the menu, shouldn’t i be able to find it under the Category Add-In? I don’t see it there. Or am I misunderstanding something?

image
image567×641 48.3 KB

#6

No idea how the “Add-in” category is implemented in the wizard (and don’t want to look into the code at the moment, because I don’t see a value in that section) - but well, yes, it looks odd.

#7

Ah yes, I see that now. But just out of curiosity, since the syntax

oService.callFunction("FUNCTIONNAME", Array(param1, param2, ...))

seems to work correctly, what would be the advantage of using the more complex syntax with the additional PropertyBag parameter? Is it more reliable?

#8

No, not more reliable. The only real advantage is a way to define your own null date (do people even need that realistically?). But I just wanted to point out the actual recommendation mentioned there (which, without some extra bits of information, won’t even allow people actually use the functions - e.g., how would you know to put the first parameter, if I didn’t tell - because I looked into the code for that). The other difference - that you pass values directly in separate arguments, instead of packing them into an array - is hardly a real advantage.

#9

The only real advantage is a way to define your own null date (do people even need that realistically?).

I don’t even know what a null date is. :blush:

#12

Open a new spreadsheet.
A1: 0
A2: 1
B1: =A1
B2: = A2
Format B1:B2 as date.
Select A1:B2 and drag down some dozens of rows.
Call Tools>Options>Calc>Calculate and change the date setting from 1899-12-30 to something else.

This setting may be relevant when you import some historic spreadsheet formats.
More relevant are the options which affecting text functions (patterns, “match whole cell”).

#13

Ah, so the term “null date” refers to the starting date for calculating subsequent dates, and the more complex syntax that Mike described would allow you to set a custom starting date for calculations.

.

image
image960×703 85.5 KB

#14

It’s a possible adjustment to get the correct dates when the file originates from another application.
If you inspect your FunctionAccess service you find some more properties corresponding to the options on the same configuration tab.

#15

An Add-In can specify the category a function is to be listed under. That is done for all originally Excel-Analysis-ToolPak functions.
See LibreOffice: XAddIn Interface Reference.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.