' LibreOffice Macro
' Split column "Voornaam, tussenvoegsel, Achternaam" in 3 aparte kolomen.
'------------------------------------------------------------------------------
'
' Deze macro doorloopt elke rij in kolom A van je LibreOffice Calc-werkblad,
' splitst de volledige naam in voornaam, tussenvoegsel en achternaam,
' en schrijft de resultaten in kolommen B, C en D.
'
' 1 - Open je LibreOffice Calc-bestand.
' 2 - Druk op Alt + F11 om de Macro-editor te openen.
' 3 - Klik op Organizer en dan op Modules.
' 4 - Selecteer My Macros en klik op New Module. Geef het een naam, bijvoorbeeld SplitNamesModule.
' 5 - Selecteer de nieuwe module en klik op Edit om de editor te openen.
' 6 - Kopieer en plak de onderstaande code in de editor:
' 7 - Sla de macro op en sluit de editor.
' 8 - Ga terug naar je LibreOffice Calc-werkblad.
' 9 - Druk op Alt + F11 om het macro-venster te openen, selecteer SplitNamesModule.SplitNames en klik op Run.
'------------------------------------------------------------------------------

Sub SplitNames
    Dim oSheet As Object
    Dim oCell As Object
    Dim oRange As Object
    Dim oData As Object
    Dim LastRow As Long
    Dim i As Long
    Dim FullName As String
    Dim Parts() As String
    Dim FirstName As String
    Dim Infix As String
    Dim LastName As String

    ' Get the active sheet
    oSheet = ThisComponent.CurrentController.ActiveSheet

    ' Get the last row with data in column A
    oRange = oSheet.Columns(0).QueryEmptyCells
    LastRow = oRange.getByIndex(0).RangeAddress.StartRow - 1

    ' Loop through each row in column A
    For i = 0 To LastRow
        ' Get the full name from column A
        oCell = oSheet.getCellByPosition(0, i)
        FullName = oCell.String

        ' Split the name into parts
        Parts = Split(FullName, " ")

        ' Initialize variables
        FirstName = Parts(0)
        Infix = ""

        ' If there's only one part, set the LastName to the FirstName
        If UBound(Parts) = 0 Then
            LastName = FirstName
            FirstName = ""
        Else
            LastName = Parts(UBound(Parts))
            ' Check if there are Infixs
            If UBound(Parts) > 1 Then
                Dim j As Integer
                For j = 1 To UBound(Parts) - 1
                    If j = 1 Then
                        Infix = Parts(j)
                    Else
                        Infix = Infix & " " & Parts(j)
                    End If
                Next j
            End If
        End If

        ' Write the results to columns B, C, and D
        oSheet.getCellByPosition(1, i).String = FirstName
        oSheet.getCellByPosition(2, i).String = Infix
        oSheet.getCellByPosition(3, i).String = LastName
    Next i
End Sub