How to use Inline Array Constants while calling librecalc functions from a basic function?

English
, , ,
#1

I recently learned that you can use Inline Array Constants in calcfunctions, e.g.

=DGET(databaseRange;selectioncolumn;{"key";"test"})

I’m now trying to use DGET from within a custom libreoffice-basic macro function, however I can’t figure out how I can write my macro function, so that it also accepts an Inline array constant (e.g. {“key”;“test”})

The function I currently got (this is a test function, that I extracted from all the other code I got):

Function TestF(whatAmI As Object)
	Dim oDoc 'References ThisComponent with a shorter name.
	oDoc = ThisComponent
	
	Dim oRangeDatabase 'DatabaseRange object.
	Dim dataArea As Object
	If NOT oDoc.DatabaseRanges.hasByName("MyDatabase") Then
		Exit Function
	End If
	oRangeDatabase = oDoc.DatabaseRanges.getByName("MySelect")
	dataArea = ScDatabaseRangeObj2ScCellRangeObj(oRangeDatabase)
	
	Dim oRangeSelectionCell 'DatabaseRange object.
	Dim selectorCell As Object
	If NOT oDoc.DatabaseRanges.hasByName("MySelectionCell") Then
		Exit Function
	End If
	oRangeSelectionCell = oDoc.DatabaseRanges.getByName("MySelectionCell")
	selectorCell = ScDatabaseRangeObj2ScCellObj(oRangeSelectionCell)
	
	Dim MyArr(1 to 2, 1 to 1) As Variant
	MyArr(1, 1) = "key"
	MyArr(2, 1) = "test"
	
        Dim oService As Object
        Set oService = createUnoService("com.sun.star.sheet.FunctionAccess")
      
        Dim T
        ' Signature of Array: ScCellRangeObj, ScCellObj, ScCellRangeObj

       REM Now I tried a bunch of ways, but all failed:
       TestF = oService.callFunction("DGET", Array(dataArea, selectorCell, whatAmI))
       TestF = oService.callFunction("DGET", Array(dataArea, selectorCell, {"key";"test"}))
       TestF = oService.callFunction("DGET", Array(dataArea, selectorCell, MyArr))
End Function

I try to call this like so:

=TestF({"key";"test"})

But all ways I tried fail with an InvalidArgumentException. Due to some tests I can say that this is caused by the last parameter, that I’d like to pass as an Inline Array Constant, however I cna’t figure out how to use such an Inline Array Constant (that gets passed to the function as Variant(1 to 2, 1 to 1) ) in the callFunction() call.

I’d be very glad if anyone has any ideas, or could explain to me how libreoffice handles Inline Array Constants within their own functions.
Thank you very much.

#2

I try to call this like so:

=TestF({"key";"test"})

If you want to pass some parameters to your custom function, then you must declate the varabled in the header of the function

Function TestF(Param1 as string, Param2 as string) As string)`

or the relevant type of the input/output parameters. You can pass an array as a Variant type parameter. It will has the values and the array size properties, what you can ask from it by the macro code.

Function TestF(MyArray as Variant) As string)`
#3

Thanks for your reply.
I know I must declare my input parameters in my function header. The issue I’ve got is that I’m not sure how to correctly pass that paramter on to

Dim oService As Object
Set oService = createUnoService("com.sun.star.sheet.FunctionAccess")

REM the DGET call in my function.
oService.callFunction("DGET", Array(dataArea, selectorCell, whatAmI))

I know I can pass a CellRangeObj as the third parameter to DGET, however I’d heavily prefer to be able to use an Inline Array Constant instead.

#4

I’m not sure I can read ScDatabaseRangeObj2ScCellRangeObj without errors - what is this supposed to return?

Also I’m not sure whatAmI As Object will be able to get an array. How about whatAmI As Variant?

#5

Oh, sorry. I forgot I extracted that into other functions:

Function ScDatabaseRangeObj2ScCellRangeObj(databaseRange As Object) As Object
	Dim sheet As Object
	Dim cellRange As Object
	sheet = ThisComponent.Sheets.getByIndex(databaseRange.DataArea.Sheet)
	cellRange = sheet.getCellRangeByPosition(databaseRange.DataArea.StartColumn, databaseRange.DataArea.StartRow, databaseRange.DataArea.EndColumn, databaseRange.DataArea.EndRow)
	ScDatabaseRangeObj2ScCellRangeObj = cellRange
End Function

Function ScDatabaseRangeObj2ScCellObj(selectorRange As Object) As Object
	Dim sheet As Object
	Dim cellRange As Object
	sheet = ThisComponent.Sheets.getByIndex(selectorRange.DataArea.Sheet)
	cell = sheet.getCellByPosition(selectorRange.DataArea.StartColumn, selectorRange.DataArea.StartRow)
	ScDatabaseRangeObj2ScCellObj = cell
End Function

whatAmI already is a Variant(1 to 2, 1 to 1) automatically. I tried whatAmI As Variant, it didn’t change anything.

#6

The “concept” of inline arrays is restricted to the usage in Calc. The conversion to a Basic conformant array must be done (imo) by the FormulaParserr.
What I called a concept here is anyway not at all satisfying.
You can, however, get something like what Calc would pass to a Basic-UDF for a inline-array-input. It is always a 2D-array in Basic style.
I would never try to create a dead thing like an inline-array in Basic.
To the contrary I’m interested in an enhanced concept allowing for calculated elements.
Have a glance on the code contained in the attachment below. It uses
Option VBAsupport 1 to be able to work with input of a variable number of parmeters.
The attched sheet is a primitive demo of how I used (intend to use) the couple of functions.
disask99990demoLivingInlineArrays.ods (17.1 KB)

1 Like
#7

Thanks for the insight.
So if I understood that correctly, there is no way to specify the SearchCriteria for DGET other than passing a Cell Range containing it?
That sadly really isn’t very satisfying.
Well, anyway thanks again to everyone who commented on my question.

#9

I do have a BASIC micro-SQL parser I aptly call Bad Query Language that will let you get SQL-like table results from datatable sheets. An example here might be

{=BQL(“Select Name from Sheet2 Where Age = 7 And Grade = 2”)}

It requires that the database table (with headers) be bottom-right on a sheet, but not necessarily the only information on the sheet, since it has options to start the table at row/column offsets.

Unlike DGET, when used as an array formula BQL can return multiple results. Logically it does not support anything other than AND over OR (no parens allowed). It can do LIKE but cannot do regex like DGET.

If you are interested, let me know.

#8

DGET takes a range for the criteria, but in LO BASIC any range does resolve into a 2D array during execution (as far as I’ve seen practically, at least). So there is nothing stopping us from creating a 2D array for the criteria range based on the headers of the database range and some requesting criteria.
.
Here is a set of functions that do just that. There are three of them.

  1. DGETAccess executes the UNO service for function invocation. It is just what you already had.
  2. GetCriteria2DArray creates a criteria array block based on nested array requests. More on that in a moment.
  3. To2DArray11 converts a UNO nested array expression of a data array to a BASIC 2D array. This seems wasteful, but it seems to go quickly, at least on up-to-mid-sized tables. Example: the element in MyUNOArray(2)(1) becomes the element in MyBASICArray(3,2). You get the idea.

For GetCriteria2DArray we pass in two nested (UNO-style) arrays. The first array is the nested array of the database which we get by pulling the database table somehow, ultimately, by the getDataArray() UNO API call. The second array is a BASIC-created set of request pairs, where the first element is the criterion field name and the second element is the requested filter value. GetCriteria2DArray checks to see if there are repeated field names, and adjust accordingly to create the entire AND/OR criteria block array.

DGETViaBASIC.ods (15.7 KB)

I’ll list GetCriteria2DArray here. See the attached example ODS for other details.

Function GetCriteria2DArray(DbNestedArray As Variant, RequestNestedArray As Variant) As Variant
	Dim ColumnCount As Long
	Dim ColumnIndex As Long
	Dim ColumnHeader As String
	Dim RequestPair As Variant
	Dim InnerRP As Variant
	Dim ORCountAccumulator As Long 'Count of same request pairs
	Dim ORCount As Long 'Max of request pairs with same first value
	Dim ORIndex As Variant
	Dim Result As Variant 'ReDimmed as 1-based 2D array
	Dim ResultColumnIndex As Long
	Dim DbBase As Long 'Database index base
	Dim DbColumnIndex As Long
	
	Rem Get parameters needed from database array
	DbBase = LBound(DbNestedArray) 'Later will assume LBounds same for both nestings (dims)
	ColumnCount = UBound(DbNestedArray(DbBase)) - DbBase + 1
	
	Rem Determine the maximum number of OR lines in criteria
	ORCount = 1 'At least the unary first criterion row
	For Each RequestPair In RequestNestedArray
		For Each InnerRP In RequestNestedArray
			If RequestPair(LBound(RequestPair)) = InnerRP(LBound(InnerRP)) Then
				Rem Each item will match itself so this is a count, not just a repeat count
				ORCountAccumulator = ORCountAccumulator + 1
			EndIf
		Next
		If ORCountAccumulator > ORCount Then
			ORCount = ORCountAccumulator
		EndIf
	Next
	
	Rem Now ReDim the result criteria to match requests
	ReDim Result(1 To 1 + ORCount, 1 To ColumnCount) '1st dim is header + OR lines

	Rem Setup criteria array from Db array header row
	ResultColumnIndex = 1
	For DbColumnIndex = DbBase To (DbBase + ColumnCount - 1)
		Result(1, ResultColumnIndex) = DbNestedArray(DbBase)(DbColumnIndex)
		ResultColumnIndex = ResultColumnIndex + 1
	Next DbColumnIndex
	
	Rem Use requested data pairs to place entries into criteria array
	For ResultColumnIndex = 1 To ColumnCount
		ORIndex = 2 '1 is the criteria header row
		ColumnHeader = Result(1, ResultColumnIndex) '1 is the header row
		For Each RequestPair In RequestNestedArray
			If RequestPair(LBound(RequestPair)) = ColumnHeader Then
				Result(ORIndex, ResultColumnIndex) = RequestPair(UBound(RequestPair))
				ORIndex = ORIndex + 1
			EndIf
		Next
	Next ResultColumnIndex	
	GetCriteria2DArray = Result
	
End Function

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.