Basic Libreoffice Macro for components

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


Sub Main

    ColorFirstColumn() 'This editor cursor has an effect on the Run!! Always place it in Main

End Sub


Sub ColorFirstColumn

    Dim nRed As Long: nRed = RGB(255, 181, 197) 'Pink1

    Dim nGreen As Long: nGreen = RGB(144, 238, 144) 'light green

    Dim nBlue As Long: nBlue = RGB(174, 238, 238) 'PaleTurquoise2

    Dim nWhite As Long: nWhite = RGB(255, 255, 255) 'White



    Dim oDoc as Object



    oDoc = ThisComponent

    For rowno=0 To 500

        oCell = oDoc.Sheets(0).getCellByPosition(0,rowno)

        txt = oCell.getString()

        If (InStr(txt, "###")) Then

            oCell.CellBackColor = nRed

        ElseIf (InStr(txt, "##")) Then

            oCell.CellBackColor = nBlue

            oCell.HoriJustify = com.sun.star.table.CellHoriJustify.LEFT

        ElseIf IsCellSubSubHdr(txt) Then

            oCell.CellBackColor = nGreen

        Else

            oCell.CellBackColor = nWhite

        End If

        'Print i, txt

    Next

End Sub


Function IsCellSubSubHdr(Optional txt$) As Boolean 'default False but txt can be Null so we need to test it

    Dim aSearchResult

    Dim oTextSearch: oTextSearch = CreateUnoService("com.sun.star.util.TextSearch")

    Dim aSrcOpt As New com.sun.star.util.SearchOptions

    Dim enLocale As New com.sun.star.lang.Locale



    If IsNull(txt) Then Exit Function



    With aSrcOpt

        .searchFlag = com.sun.star.util.SearchFlags.REG_EXTENDED

        .Locale = enLocale

        .algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP

        .searchString = "^\s*[A-Z][a-z-]+"

    End With

    oTextSearch.setOptions(aSrcOpt)

    aSearchResult = oTextSearch.searchForward(txt, 0, Len(txt) - 1) 'cursor has to be set in

    If aSearchResult.subRegExpressions > 0 Then

        IsCellSubSubHdr = True

    End If

End Function