Passing page names and name ranges into a function

melvin7020 · July 25, 2023, 10:44am

Sorry, it is very basic and probably really really obvious question. How do you pass a named range and page name into a function.

I want to tidy up this code, by splitting it into two, and just having a sub that takes the names and another that just passes the name to the first function, instead of repeating the whole code nine times. Hope that makes sense.

Sub AdvancedRangeFilterV5()
  Dim oSheet     'A sheet from the Calc document.
  Dim oRanges    'The NamedRanges property.
  Dim oCritRange 'Range that contains the filter criteria.
  Dim oDataRange 'Range that contains the data to filter.
  Dim oFiltDesc  'Filter descriptor.

  REM Range that contains the filter criteria

  oSheet = ThisComponent.Sheets.getByName("filters")
  oCritRange = oSheet.getCellRangeByName("aaLarge")

  REM The data that you want to filter
  oSheet = ThisComponent.Sheets.getByName("Large")
  oDataRange = oSheet.getCellRangeByName("Large")
 
  oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
  oFiltDesc.UseRegularExpressions = True
  oDataRange.filter(oFiltDesc)


rem   first repeat
  oSheet = ThisComponent.Sheets.getByName("filters")
  oCritRange = oSheet.getCellRangeByName("aaLargeSaying")
  oSheet = ThisComponent.Sheets.getByName("largeNsaying")
  oDataRange = oSheet.getCellRangeByName("LargeNsaying")
 
rem passed to this 
  oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
  oFiltDesc.UseRegularExpressions = True
  oDataRange.filter(oFiltDesc)   

rem   second repeat 
  oSheet = ThisComponent.Sheets.getByName("filters")
  oCritRange = oSheet.getCellRangeByName("aaLargeType")
  oSheet = ThisComponent.Sheets.getByName("largeNtype")
  oDataRange = oSheet.getCellRangeByName("LargeNtype")
 
 
rem passed to this again
  oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
  oFiltDesc.UseRegularExpressions = True
  oDataRange.filter(oFiltDesc)
  
  
rem   Third repeat 
  oSheet = ThisComponent.Sheets.getByName("filters")
  oCritRange = oSheet.getCellRangeByName("bbMedium")
  oSheet = ThisComponent.Sheets.getByName("medium")
  oDataRange = oSheet.getCellRangeByName("medium")

rem passed to this again 
  oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
  oFiltDesc.UseRegularExpressions = True
  oDataRange.filter(oFiltDesc)
  
rem   fourth repeat
   oSheet = ThisComponent.Sheets.getByName("filters")
  oCritRange = oSheet.getCellRangeByName("bbMediumSaying")
  oSheet = ThisComponent.Sheets.getByName("mediumNsaying")
  oDataRange = oSheet.getCellRangeByName("mediumNsaying")
 
rem again passed to this again 
  oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
  oFiltDesc.UseRegularExpressions = True
  oDataRange.filter(oFiltDesc)
  
rem   fifth repeat
  oSheet = ThisComponent.Sheets.getByName("filters")
  oCritRange = oSheet.getCellRangeByName("bcMediumType")
  oSheet = ThisComponent.Sheets.getByName("mediumNtype")
  oDataRange = oSheet.getCellRangeByName("mediumNtype")
 
rem yet again passed to this again
  oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
  oFiltDesc.UseRegularExpressions = True
  oDataRange.filter(oFiltDesc)  
  
  
rem   Sixth repeat
  oSheet = ThisComponent.Sheets.getByName("filters")
  oCritRange = oSheet.getCellRangeByName("ccOneSmall")
  oSheet = ThisComponent.Sheets.getByName("small")
  oDataRange = oSheet.getCellRangeByName("small")
 
rem yet again passed to this
  oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
  oFiltDesc.UseRegularExpressions = True
  oDataRange.filter(oFiltDesc)
  
rem   seventh repeat 
  oSheet = ThisComponent.Sheets.getByName("filters")
  oCritRange = oSheet.getCellRangeByName("ccSmallSaying")
  oSheet = ThisComponent.Sheets.getByName("smallNsaying")
  oDataRange = oSheet.getCellRangeByName("smallNsaying")
 
rem yet again passed to this
  oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
  oFiltDesc.UseRegularExpressions = True
  oDataRange.filter(oFiltDesc)
  
rem   final repeat 
  oSheet = ThisComponent.Sheets.getByName("filters")
  oCritRange = oSheet.getCellRangeByName("cdSmallType")
  oSheet = ThisComponent.Sheets.getByName("smallNtype")
  oDataRange = oSheet.getCellRangeByName("smallNtype")

rem yet again passed to this 
  oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
  oFiltDesc.UseRegularExpressions = True
  oDataRange.filter(oFiltDesc)
  
  End Sub```

Zizi64 · July 25, 2023, 11:29am

Your code snippet is a Subroutine, but not a Function.
Where you want to pass the names from?

Lupp · July 25, 2023, 11:51am

There are no page objects in Calc. Do you talk of sheet names or of page style names?
Concerning the “name ranges” I suppose you mean “named ranges”.

Concerning all the rest:
I won’t study your code, but only answer the question as I understand it (on a first level) generally for FUNCTION calls.

You can pass a name of any kind as a string.
You can pass a SheetCellRange as a quintuple of numbers giving indices (0-based or 1-based) and/or the numbers of rows/columns. (See example below.)
Using a not too old LibreOffice you can set Option VBAsupport 1 at the beginning of a module. Passig a range reference to a UDF you will then get an object of service type ooo.vba.excel.Range giving access to its property .CellRange bridging to Calc’s SheetCellRange. Handle this in a restrictive way. There are issues.

Function doingSomething(pSheetIndex1Based As Long,  _
                        pRowIndex1Based As Long, _
                        pColIndex1Based As Long, _
                        pNumRows As Long, _
                        pNumCols As Long)
cDoc    = ThisComponent
wSheet  = cDoc.Sheets(pSheetIndex1Based - 1)
wEndRow = pRowIndex1Based + pNumRows - 1
wEndCol = pColIndex1Based + pNumCols - 1
wRange  = wSheet.getCellRangeByPosition(pColIndex1Based - 1, _
                                        pRowIndex1Based - 1, _
                                        wEndCol, _
                                        wEndRow)
SubstantialStatrement1
SubstantialStatrement2
REM and so on
doingSomething = outVariable
End Function

f you actually want to pass SheetCellRanges to subroutines NOT called from formulas, The unavoidable question in return is “In what way do you intend to call the Sub?”
The answers then do not fit into a post, but require a tutorial.

melvin7020 · August 13, 2023, 10:27am

deleted replied to wrong response

melvin7020 · August 13, 2023, 10:30am

I think I am mistaken. I think it will be passing from one sub to another. Something along the lines of

Sub PassingThis
Dim Variable as Integer
Variable = 1
WasPassedToMe(Variable1, "booladgo")
End Sub


Sub WasPassedToMe(Variable1 as Integer, SecondVariable as string)
Dim oSheet as Object
Dim oCell as Object
oSheet = ThisComponent.CurrentController.getActiveSheet()


oCell = oSheet.getCellRangeByName("b2")
oCell.setString(SecondVariable)


oCell = oSheet.getCellRangeByName("c3")
oCell.setString(Variable1)

End Sub

Lupp · August 13, 2023, 11:22am

Don’t understand.
Any Sub can call any different Sub and even itself (“recursively” then) with arbitrary arguments that will replace the parameters of the called Sub during action.
There only must be consistent typing, and variables used in argument positions must exist (Your Variable1 doesn’t in the calling position!) and lead to consistent results.
In case of recursive calls to a Sub its semanitics need to guarantee a break in the recursion.

karolus · August 13, 2023, 11:30am

Variable <> Variable1

melvin7020 · August 13, 2023, 2:25pm

awesomeness. This worked. Thank you.

Sub PassingThis
WasPassedToMe(“filters”, “aaLarge”, “Large”, “Large”)
WasPassedToMe(“filters”, “aaLargeSaying”,“largeNsaying”,“LargeNsaying”)
WasPassedToMe(“filters”, “aaLargeType”, “largeNtype”,“LargeNtype”)

End Sub

Sub WasPassedToMe(FirstVariable as string, SecondVariable as string, ThirdVariable as string, FourthVariable as string)
REM Range that contains the filter criteria

oSheet = ThisComponent.Sheets.getByName(FirstVariable)
oCritRange = oSheet.getCellRangeByName(SecondVariable)

REM The data that you want to filter
oSheet = ThisComponent.Sheets.getByName(ThirdVariable)
oDataRange = oSheet.getCellRangeByName(FourthVariable)

oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
oFiltDesc.UseRegularExpressions = True
oDataRange.filter(oFiltDesc)

End Sub