I tried create this Basic routine, but can not do that “,” can be replaced with “,” + a “new line character”
Function AddIndentation(formula As String) As String
Dim i As Integer
Dim char As String
Dim result As String
Dim indentLevel As Integer
indentLevel = 0
result = ""
For i = 1 To Len(formula)
char = Mid(formula, i, 1)
Select Case char
Case "("
indentLevel = indentLevel + 1
result = result & char & Chr(10) & String(indentLevel * 4, " ")
Case ")"
indentLevel = indentLevel - 1
result = result & Chr(10) & String(indentLevel * 4, " ") & char
Case "+", "-", "*", "/"
result = result & Chr(10) & String(indentLevel * 4, " ") & char & Chr(10) & String(indentLevel * 4, " ")
Case ","
result = result & Chr(10) & String(indentLevel * 4, " ") & char & Chr(10) & String(indentLevel * 4, " ")
Case Else
result = result & char
End Select
Next i
AddIndentation = result
End Function
Function ConvertToOneLine(formula As String) As String
Dim cleanFormula As String
' Remove all line breaks, tabs, and extra spaces
cleanFormula = Replace(formula, Chr(10), "")
cleanFormula = Replace(cleanFormula, Chr(13), "")
cleanFormula = Replace(cleanFormula, " ", "")
ConvertToOneLine = cleanFormula
End Function
Sub PrettyPrintSelectedCells()
Dim oSheet As Object
Dim oCells As Object
Dim oCell As Object
Dim oRange As Object
Dim sFormula As String
Dim oneLineFormula As String
Dim formattedFormula As String
Dim rStart As Long, cStart As Long, rEnd As Long, cEnd As Long
Dim r As Long, c As Long
' Obtener la hoja activa
oSheet = ThisComponent.CurrentController.ActiveSheet
' Obtener las celdas seleccionadas
oCells = ThisComponent.CurrentSelection
oRange = oCells.getRangeAddress()
' Obtener las coordenadas del rango seleccionado
rStart = oRange.StartRow
cStart = oRange.StartColumn
rEnd = oRange.EndRow
cEnd = oRange.EndColumn
' Iterar a través del rango seleccionado
For r = rStart To rEnd
For c = cStart To cEnd
oCell = oSheet.getCellByPosition(c, r)
' Verificar si la celda es única
If oCell.supportsService("com.sun.star.sheet.SheetCell") Then
' Obtener la fórmula de la celda
sFormula = oCell.Formula
' Convertir la fórmula a una sola línea
oneLineFormula = ConvertToOneLine(sFormula)
' Aplicar formato bonito
formattedFormula = AddIndentation(oneLineFormula)
' Actualizar la celda con la fórmula formateada
oCell.Formula = formattedFormula
' Activar ajuste de texto en la celda
oCell.IsTextWrapped = True
End If
Next c
Next r
End Sub
how to solve it? Is there a method to give a nice print format to the LO calc formulas?