LibreOffice CALC macro Hex2Bin and Bin2Hex functions

Question

can anybody help me with solving my problem of Hex2Bin and Bin2Hex functions? First I was trying to make the conversion Hex2Bin. I would like to call the AddIn function from macro so I called createUNOservice:

Function fcHex2Bin(arg as variant, NumberOfBits As integer) as string
   Dim oService as Object
   oService = createUNOService("com.sun.star.sheet.addin.Analysis")
   sArg = cStr(arg)
   fcHex2Bin = oService.getHex2Bin(sArg,NumberOfBits)
End Function

but all the time ends with fault message like "The object variable is not set.". I already don't know why.

My final goal would be to make all functions of Calc running in macros, but at this moment I would be glad to have two functions Hex2Bin and Bin2Hex running - anyhow.

My LibreOffice version: Version: 7.1.3.2 (x64) / LibreOffice Community Build ID: 47f78053abe362b9384784d31a6e56f8511eb1c1 CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: cs-CZ (cs_CZ); UI: cs-CZ Calc: CL

Thank you for your help.

score 0 · Answer 1 · answered Jun 25 '21 at 17:25

This way works.

Function fcHex2Bin(aNum As String, rPlaces As Any) As String
    Dim oFunc As Object
    oFunc = CreateUnoService("com.sun.star.sheet.FunctionAccess")
    Dim aArgs(0 to 1) As Variant
    aArgs(0) = aNum
    aArgs(1) = rPlaces
    fcHex2Bin = oFunc.callFunction("com.sun.star.sheet.addin.Analysis.getHex2Bin", aArgs())
End Function

As for why the other way does not work, many analysis functions require a hidden XPropertySet object as the first argument. The following code produces informative error messages:

REM IllegalArgumentException: expected 3 arguments, got 1
sResult = oService.getHex2Bin(ThisComponent.getPropertySetInfo())
REM IllegalArgumentException: arg no. 0 expected: "com.sun.star.beans.XPropertySet" 
sResult = oService.getHex2Bin(ThisComponent.getPropertySetInfo(), "2", 4)

However I tried passing ThisComponent.getPropertySetInfo().getProperties() from a Calc spreadsheet and it still didn't work, so I'm not exactly sure what is required to do it that way.

The documentation at https://help.libreoffice.org/latest/he/text/sbasic/shared/calc_functions.html does not really explain this. You could file a bug report about missing documentation, perhaps related to https://bugs.documentfoundation.org/show_bug.cgi?id=134032.

LibreOffice CALC macro Hex2Bin and Bin2Hex functions

1 Answers1