Insert value of formula with macro

[ste]

Hello,
i’ve this macro and its work but insert the formula.
How can i insert only the result of the formula so as to lighten the file since I would have more than a thousand formulas otherwise?

REM  *****  BASIC  *****

Sub agg_disp 

ThisComponent.Sheets(0).getCellByPosition(11,2).setFormula("=CONCATENATE(""Disponibilita' Fornitore (arrivo in 5/7 giorni lavorativi dall ordine)"";"" "";""Pezzi"";;"" "";IFERROR(INDEX(Innova.B:B;MATCH(1;(Innova.A:A=C2)*(Innova.C:C=N3);0));""""")

end Sub  

(Slightly edited by @Lupp for better readability.)

[Lupp]

(Regard the amendment by editing below!)
There is no EVALUATEFORMULA() function in Calc, and in many cases like yours the formula contains references (partly?) relative, which only can be evaluated if the base cell is known.
If you are sure to never again need a recalculation, you can try it this way:

targetCell = ThisComponent.Sheets(0).getCellByPosition(theCol, theRow)
targetCell.setFormula(("=...(whatever you have)...")  REM Be careful with references!!
Select Case targetCell.FormulaResultType2
REM Overwriting the properties .Value or .String will dispose the formula again.
  Case 1 REM Number type Double
    targetCell.Value = targetCell.Value
  Case 2 REM text type String
    targetCell.String = targetCell.String
End Select

By the way “more than a thousand formulas” will rarely be a problem if not very complicated and inefficient.
Also: There may be good reasons to work with the range properties .DataArray, .FormulaArray, and …

Edit 2021-03-15 about 21:50 UTC
As reported by the questioner the above code didn’t work correctly though it’s correct. (A wee little bug.)
The following replacement is tested now (V 7.0.4.2) and worked as expected:

targetCell = ThisComponent.Sheets(0).getCellByPosition(theCol, theRow)
targetCell.setFormula(("=...(whatever you have)...")  REM Be careful with references!!
Select Case targetCell.FormulaResultType2
REM Overwriting the properties .Value or .String will dispose the formula again.
  Case 1 REM Number type Double
    v = targetCell.Value
    targetCell.Value = v
  Case 2 REM text type String
    s = targetCell.String
    targetCell.String = s
End Select

[ste]

sorry i am not expert but if i insert

Sub agg_disp 

targetCell = ThisComponent.Sheets(0).getCellByPosition(11, 2)
targetCell.setFormula("=(1+1)")  REM Be careful with references!!
Select Case targetCell.FormulaResultType2
REM Overwriting the properties .Value or .String will dispose the formula again.
  Case 1 REM Number type Double
    targetCell.Value = targetCell.Value
  Case 2 REM text type String
    targetCell.String = targetCell.String
End Select

End Sub

the result is 2 but in the field there is the formula (=1+1) and not only the result

[Lupp]

Please use the tool Preformatted Text for Basic code.

[Lupp]

Strange! I didn’t test the code, and I regard the bahaviour you experienced a bug.
However: Use the following replacement, and it will work. (Now tested with V 7.0.4.2)

Select Case targetCell.FormulaResultType2
REM Overwriting the properties .Value or .String will dispose the formula again.
The helper variable v (s respectively) makes the difference.
  Case 1 REM Number type Double
    v = targetCell.Value
    targetCell.Value = v
  Case 2 REM text type String
    s = targetCell.String
    targetCell.String = s
End Select

[Lupp]

By the way: If you have contiguous ranges where the formula results should replace the formulas, you best use the poperty .DataArray of every such range.

da = thisRange.getDataArray()
thisRange.setDataArray(da)

will do the trick without any need of branching.
Thousands of formulas vanish within millisdeconds and leve the former results as constant content…

[ste]

hello, thank’s the edit work perfectly. This last trick i am sorry but you know i didn’t understand how i should use it. Can you do an exemple?

[JohnSUN]

To demonstrate the idea of our distinguished colleague @Lupp in terms of using DataArray, I wrote the following code. I hope that analyzing it will help you solve many problems similar to those described in your question.

Sub agg_disp
Dim oSheets As Variant, oSheet As Variant, oCursor As Variant, oRange As Variant
Dim aRangeAddress As New com.sun.star.table.CellRangeAddress
Dim oDataC As Variant, oDataN As Variant, oDataL As Variant
Dim oDataInnova As Variant
Dim i As Long, j As Long, nEndRow As Long
	oSheets = ThisComponent.getSheets()	' All sheets of this spreadsheet
	oSheet = oSheets.getByIndex(0)		' First sheet
Rem No need to give extra work to Calc by specifying a full column like A:A=C2
Rem Use only the filled-in part of the sheet:
	oCursor = oSheet.createCursor(): oCursor.gotoEndOfUsedArea(True)
	aRangeAddress = oCursor.getRangeAddress()
	If aRangeAddress.EndColumn < 13 Then Exit Sub	' There is no column N in the table '
	
	nEndRow = aRangeAddress.EndRow
Rem Get data of columns C, N an L. Also you can get all data of used range:
	oRange = oSheet.getCellRangeByPosition(2, 1, 2, nEndRow)
	oDataC = oRange.getDataArray()	' Values from column C
	
	oRange = oSheet.getCellRangeByPosition(13, 1, 13, nEndRow)
	oDataN = oRange.getDataArray()	' Values from column N
	
	oRange = oSheet.getCellRangeByPosition(11, 1, 11, nEndRow)
	oDataL = oRange.getDataArray()	' Cells of column L
Rem Get data from Innova sheet
	oSheet = oSheets.getByName("Innova")
	oCursor = oSheet.createCursor(): oCursor.gotoEndOfUsedArea(True)
	oDataInnova = oCursor.getDataArray()
Rem Fill array with result of calculation (without formula)
	For i = LBound(oDataL) To UBound(oDataL)
		oDataL(i)(0) = "Disponibilita' Fornitore (arrivo in 5/7 giorni lavorativi dall ordine) Pezzi "
		For j = LBound(oDataInnova) To UBound(oDataInnova)
			If (oDataInnova(j)(0) = oDataC(i)(0)) And (oDataInnova(j)(2) = oDataN(i)(0)) Then
				oDataL(i)(0) = oDataL(i)(0) & oDataInnova(j)(1)
				Exit For
			EndIf
		Next j
	Next i
Rem Put result to colunm L 
	oRange.setDataArray(oDataL)
End Sub

[ste]

i think that i worng something. If i use your help does not give me nothing. I have to set something?