CODEWITHSUNDEEP
setlibreoffice-calc
GreatDayDan
GreatDayDan

Reputation: 169

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?

Upvotes: 0

Views: 147

Answers (1)

JohnSUN
JohnSUN

Reputation: 2529

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

Upvotes: 1

Related Questions