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

 Dim oService As Object
 oService = createUNOService("")
 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.

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("")
lngDate = cLng(Date())
wn = srv.callFunction("WEEKNUM", Array(lngDate, 2))
print wn

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.

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
(actually there’s a third WEEKNUM_OOO() for compatibility with old OOo and hidden in the Function Wizard but let’s not complicate further).

1 Like

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

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


Call it like

 Dim oPropBag As Object, oService As Object
 oPropBag = CreateUnoService("")
 oPropBag.addProperty("NullDate", 0, CDateToUnoDate(Empty))
 oService = CreateUnoService("")
 WkNum = oService.getWeeknum(oPropBag, CDate("2023-03-02"), 1)