Search code examples
setlibreoffice-calc

How to use LibreOffice Calc 'set' datatype

I have a spreadsheet like this: a1=3, b1=(1..10,15)
How do I check if a1 is in b1? ```=if (a1 in b1, 'tada!', 'Missed it')``? Is this correct?

Solution

  • Ok, try this extension. (This was written many years ago to record numbers on insurance policy forms.)

    The extension contains several functions, the name of each of which begins with LST, in the Function Wizard they are located in the Add-In section.

    Add-In section in the Function Wizard

    The lists of numbers that are passed to the function must be sets of positive integers. Numbers separated by - + = or _ signify an interval of values, other non-digit characters (including period, comma, space) are regarded as separators of separate numbers. Therefore 1..10,15 from your example will be converted to 1, 10, 15, use the sign -

    A few examples will help you understand the purpose of the individual functions. The last line is the answer to your question.

    ListOK examples

    Updated. From Basic all this functions calling like as embedded Calc functions, with service FunctionAccess

    Function IsOnPeak(C2 As Variant, I13 As String, D2  As Variant, I16 As String) As Boolean 
Dim Mth As Integer 
Dim hr As Integer  
Dim svFA As Variant
    Mth =Month(C2)    
    hr = HOUR(D2) 
    svFA = createUnoService( "com.sun.star.sheet.FunctionAccess" )
    IsOnPeak = (svFA.callFunction("LSTOR",Array(Mth,I13)) <> "") And (svFA.callFunction("LSTOR",Array(Hr, I16)) <> "")
End Function