Can't get the Add-in Weeknum function to work in macro

[NOYB]

 Dim oService As Object
 oService = createUNOService("com.sun.star.sheet.addin.Analysis")
 WkNum = oService.getWeeknum("2023-03-02", 1)

’ Does not work; “BASIC runtime error.” “Object variable not set.”

’ Called without any variables it says expects 3. Adding a 3rd gives same object variable not set error.
’ Have also tried a numeric date value.
’ Same parameters work in Calc =WEEKNUM(“2023-03-02”,1) function.

Spinning my wheels and not making any progress. Need your help please.

Thank you.

P.S. So glad there is a viable alternative to MSO. New computer and trying real hard to prevent MSO from ever touching it.

[Villeroy]

WEEKNUM is not an add-in function. Simply inspect some cell where you entered a WEEKNUM formula: print ThisComponent.CurrentSelection.getFormula()
You can use weeknum just like this:

srv = createUnoService("com.sun.star.sheet.FunctionAccess")
lngDate = cLng(Date())
wn = srv.callFunction("WEEKNUM", Array(lngDate, 2))
print wn

[mikekaganski]

@Villeroy

WEEKNUM is not an add-in function

Yes it is. But recently @erAck has implemented easier access to these functions, so in newer versions, your code should work even for those functions that earlier required full add-in name.

[erAck]

There are two WEEKNUM() functions, the built-in and the AddIn one that in the (English) UI is displayed as WEEKNUM_EXCEL2003() but is also callable as com.sun.star.sheet.addin.Analysis.getWeeknum().
(actually there’s a third WEEKNUM_OOO() for compatibility with old OOo and hidden in the Function Wizard but let’s not complicate further).

[Villeroy]

I see. ThisComponent.CurrentSelection.setFormula("=COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETWEEKNUM(TODAY();1)" writes =WEEKNUM_EXCEL2003(TODAY();1) into the current cell selection.

[NOYB]

You folks have been a tremendous help with this. Would not have gotten this working without your help. Thank you all very much.

[mikekaganski]

Need for "XPropertySet" object when calling "getDec2Hex"

Platform: LO Basic, v7.3.2.2 (Community), build 49f2b1bff42cfccbd8f788c8dc32c1c309559be0, running on OSX 10.15.7 Am curious about the need for the add-in function getDec2Hex’s first parameter, an “XPropertySet” object. According to a portion of the Libre help site, the function is part of the com.sun.star.sheet.addin.XAnalysis service. In a spreadsheet you’d call “Dec2Hex(VALUE [, PLACES])”, but in a macro it’s “ADD-IN OBJECT.getDec2Hex(XProps Object, VALUE, PLACES)” The add-in service is s…

tdf#148646

Call it like

 Dim oPropBag As Object, oService As Object
 oPropBag = CreateUnoService("com.sun.star.beans.PropertyBag")
 oPropBag.addProperty("NullDate", 0, CDateToUnoDate(Empty))
 oService = CreateUnoService("com.sun.star.sheet.addin.Analysis")
 WkNum = oService.getWeeknum(oPropBag, CDate("2023-03-02"), 1)