How to nest Calc Functions in a only one Macro Function?

FelipeAle · January 12, 2022, 4:01am

There is some way to nest various calc functions creating the Service (?)

com.sun.star.sheet.FunctionAccess

The code below is what I’m trying to do.

 Sub Test1
    Dim oDoc as Object, SpreadSheet as Object, targetCell as Object
    Dim nRow as Long
    oDoc = ThisComponent
    SpreadSheet = oDoc.getSheets().getByIndex(0)
    'For nRow = 2 To fnLastRow
    'targetCell = SpreadSheet.getCellByPosition(2, nRow-1)
    'targetCell.setFormula("=RIGHT(A" & nRow & ";" & _
'				"LEN(A" & nRow & ")-" & _
'					"SEARCH(" & CHR$(34) & "Residência" & CHR$(34) & _
'						";" & "A" & nRow & ")-11)" )
'	Next nRow
	oRange = SpreadSheet.getCellByPosition(1, 1)
	oRange.setString ( fnNestFormulas( SpreadSheet.getCellRangeByName("A2"), "Residência") )
End Sub
rem------------------------------------------------------------------------------
Function fnNestFormulas (oCell$, sWord$)
    Dim oDoc as Object : oDoc = ThisComponent
    Dim oSheet as Object, oService as Object
    Dim Personal_Address
    oSheet = oDoc.getCurrentController.getActiveSheet()
    oService = CreateUnoService("com.sun.star.sheet.FunctionAccess")
    Personal_Address = oService.callFunction("RIGHT", _
						array(oSheet.getCellRangeByName(oCell), _
						"LEN", oSheet.getCellRangeByName(oCell) & - _
						 "SEARCH", array(sWord, oSheet.getCellRangeByName(oCell) ) _
							& -11 ) )
	fnNestFormulas = Personal_Address
End Function
rem------------------------------------------------------------------------------
Function fnLastRow
    Dim oDoc as Object : oDoc = ThisComponent
    Dim oSheet as Object, oService as Object, i as Variant
    oSheet = oDoc.getCurrentController.getActiveSheet()
    oService = CreateUnoService("com.sun.star.sheet.FunctionAccess")
    i = oService.callFunction("COUNTA" , _
				array(oSheet.getCellRangeByName("A:A")))
    fnLastRow = i
End Function	'fnLastRow

File Example:
FileExample.ods (26,4,KB)

I would appreciate if someone tells me what I’m doing wrong on the code, or if what I’m asking for is impossible.

JohnSUN · January 12, 2022, 8:31am

Yes, you can use nested calls to com.sun.star.sheet.FunctionAccess.
But now I propose to discuss another issue. Don’t you think that you have chosen not the easiest way to solve the problem? Why not
=REGEX(A2;".*Residência: ";"")

FelipeAle · January 12, 2022, 11:49am

Actually, I’m still learning how to use oOoBasic and calc functions, and I didn’t knew about this formula.
I still don’t understand the regular expressions well, and now I see that I need to study too much more.

Thank you so much for your help, and of course I’ll study this calc function to understand its operation.

JohnSUN · January 12, 2022, 11:54am

When you’re done with that, you can use code like this:

Sub ExtractAddresses
Dim oSheet As Variant
Dim oDataArray As Variant
Dim oService As Variant
Dim nEndRow As Long, nRow As Long
	oSheet = ThisComponent.getSheets().getByIndex(0)

	GlobalScope.BasicLibraries.loadLibrary("Tools")
	nEndRow = GetLastUsedRow(oSheet)

	oDataArray = oSheet.getCellRangeByPosition(0, 0, 0, nEndRow).getDataArray()

	oService = CreateUnoService("com.sun.star.sheet.FunctionAccess")

	For nRow = LBound(oDataArray) To UBound(oDataArray)
		oDataArray(nRow)(0) = oService.callFunction("REGEX",Array(oDataArray(nRow)(0), ".*Residência: ", ""))
	Next nRow
	
	oSheet.getCellRangeByPosition(2, 0, 2, nEndRow).setDataArray(oDataArray)
End Sub