
Public Class ThisWorkbook
    Private Sub ThisWorkbook_Startup() Handles Me.Startup
        getItems(1)
        getItems(2)
    End Sub
    Private Sub ThisWorkbook_AfterSave(Success As Boolean) Handles Me.AfterSave
        insertItems()
    End Sub
    Private Sub getItems(sheet As Byte)
        If sheet = 1 Then
            Dim IdRange As Excel.Range = Globals.Sheet1.Range("A2")
            Dim NameRange As Excel.Range = Globals.Sheet1.Range("B2")
            Dim LastNameRange As Excel.Range = Globals.Sheet1.Range("C2")
            Do While (IdRange.Value2 <> vbNullString)
                Dim node1 As LinkedListNode(Of Person) = New LinkedListNode(Of Person)(New Person)
                node1.Value.ID = IdRange.Value2
                node1.Value.IdRange = IdRange
                node1.Value.NameRane = NameRange
                node1.Value.LastNameRane = LastNameRange
                PersonsLinkedList1.AddLast(node1)
                IdRange = IdRange.Offset(1, 0)
                NameRange = NameRange.Offset(1, 0)
                LastNameRange = LastNameRange.Offset(1, 0)
            Loop
        ElseIf sheet = 2 Then
            Dim IdRange As Excel.Range = Globals.Sheet2.Range("A2")
            Dim NameRange As Excel.Range = Globals.Sheet2.Range("B2")
            Dim LastNameRange As Excel.Range = Globals.Sheet2.Range("C2")
            Do While (IdRange.Value2 <> vbNullString)
                Dim node2 As LinkedListNode(Of Person) = New LinkedListNode(Of Person)(New Person)
                node2.Value.ID = IdRange.Value2
                node2.Value.IdRange = IdRange
                node2.Value.NameRane = NameRange
                node2.Value.LastNameRane = LastNameRange
                PersonsLinkedList2.AddLast(node2)
                IdRange = IdRange.Offset(1, 0)
                NameRange = NameRange.Offset(1, 0)
                LastNameRange = LastNameRange.Offset(1, 0)
            Loop
        End If
    End Sub
    Private Sub insertItems()
        Dim node1 As LinkedListNode(Of Person) = PersonsLinkedList1.First
        Dim node2 As LinkedListNode(Of Person)
        Dim Person1 As Person
        Dim Person2 As Person
        Do
            node2 = PersonsLinkedList2.First
            Person1 = node1.Value
            Person2 = node2.Value
            If Person1.ID < Person2.ID Then
                addBefore(Person1, node2)
            ElseIf Person1.ID = Person2.ID Then
                Update(Person1, node2)
            ElseIf Person1.ID > Person2.ID Then
                Do
                    node2 = node2.Next

                    If node2 Is Nothing Then
                        Person2 = Nothing
                        Exit Do
                    Else
                        Person2 = node2.Value
                    End If
                Loop Until Person1.ID < Person2.ID
                If Person2 Is Nothing Then
                    node2 = PersonsLinkedList2.Last
                    addAfter(Person1, node2)
                ElseIf Person1.ID < Person2.ID Then
                    addBefore(Person1, node2)
                End If
            End If
            node1 = node1.Next
        Loop Until node1 Is Nothing
    End Sub
    Private Sub addBefore(Person1 As Person, node As LinkedListNode(Of Person))
        node.Value.IdRange.EntireRow.Insert(Shift:=Excel.XlDirection.xlDown, CopyOrigin:=Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow)
        Dim person2 As Person = New Person
        person2.ID = Person1.ID
        person2.IdRange = node.Value.IdRange.Offset(-1, 0)
        person2.NameRane = node.Value.NameRane.Offset(-1, 0)
        person2.LastNameRane = node.Value.LastNameRane.Offset(-1, 0)
        PersonsLinkedList2.AddBefore(node, person2)
        person2.IdRange.Value2 = Person1.IdRange.Value2
        person2.NameRane.Value2 = Person1.NameRane.Value2
        person2.LastNameRane.Value2 = Person1.LastNameRane.Value2
    End Sub

    Private Sub addAfter(Person1 As Person, node As LinkedListNode(Of Person))
        node.Value.IdRange.Offset(1, 0).EntireRow.Insert(
            Shift:=Excel.XlDirection.xlDown,
            CopyOrigin:=Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow)
        Dim person2 As Person = New Person
        person2.ID = Person1.ID
        person2.IdRange = node.Value.IdRange.Offset(1, 0)
        person2.NameRane = node.Value.NameRane.Offset(1, 0)
        person2.LastNameRane = node.Value.LastNameRane.Offset(1, 0)
        PersonsLinkedList2.AddAfter(node, person2)
        person2.IdRange.Value2 = Person1.IdRange.Value2
        person2.NameRane.Value2 = Person1.NameRane.Value2
        person2.LastNameRane.Value2 = Person1.LastNameRane.Value2
    End Sub
    Private Sub Update(Person1 As Person, node As LinkedListNode(Of Person))
        node.Value.ID = Person1.ID
        node.Value.IdRange.Value2 = Person1.IdRange.Value2
        node.Value.NameRane.Value2 = Person1.NameRane.Value2
        node.Value.LastNameRane.Value2 = Person1.LastNameRane.Value2
    End Sub


    Private Sub ThisWorkbook_Shutdown() Handles Me.Shutdown

    End Sub
End Class
