If I change the formula like this:
Function SortWithFilter(sFilter As String) As Variant
Dim i As Long, j As Long
Dim aRes As Variant
Dim aData as Variant
aData = Array(51)
oSheet = ThisComponent.Sheets.getByName("myBase")
aData = oSheet.getCellRangebyName("A2:A53")
...
is showing this error:
Array must be dimensionded.
Here’s an example:
Why you don’t want send values of range as param?
This is a test, but I intend to set this range as a parameter or function, At the moment if I increase the number of items on the list I would need to manually change all dropboxes. And by setting this value via basic, I can calculate the amount of total items in the list automatically.
It seems to me that there is an easier solution. Define a name for the original range (Leila Gharani did something similar in her video at 9:53 - she created a Table, and you just give the range name Ctrl+F3). Use this name in all formulas, in all dropdowns. Then, when you change the original range, you will only need to change the address in one place, in the named range.
Ideally, libreoffice would have the option of using # in the cell range, just as it does in excel.
The problem with using named range is that the user would still need to change the named range if he doesn’t want to display an empty item in the list.
Here is a minimal example of how I am using it now.
I used this formula to determine the number of items on the list:
=COUNTA(A2:A1048576)
It is a fragile way as it cannot have any empty lines in the names. However, it is a simple and fully automated way.
1048576? Really? How long list are you going to use? Real length? 1000? 10000 items?
This is really an absurd value, but I only used it because it is the last row in the spreadsheet, so I will never need to update the formula, regardless of the number of items in the list.
I added another variation of the code to my answer - check it out.