Merging arrays

[Aussiebear]

Would someone please assist me (no baseball bats at this stage) in understanding the following code on merging arrays

Function Merge(ByVal arr1 As Variant, ByVal arr2 As Variant) As Variant
        Dim tmpArr As Variant, upper1 As Long, upper2 As Long
        Dim higherUpper As Long, i As Long, newIndex As Long
        upper1 = UBound(arr1) + 1 : upper2 = UBound(arr2) + 1
        higherUpper = IIf(upper1 >= upper2, upper1, upper2)
        ReDim tmpArr(upper1 + upper2 - 1)
        For i = 0 To higherUpper
            If i < upper1 Then
                tmpArr(newIndex) = arr1(i)
                newIndex = newIndex + 1
            End If
            If i < upper2 Then
                tmpArr(newIndex) = arr2(i)
                newIndex = newIndex + 1
            End If
        Next i
        Merge = tmpArr
End Function

[georgiboy]

my understanding is that this Merge function takes two arrays (arr1 and arr2) as inputs and returns a new array that merges the elements of both arrays in an alternating fashion. If one array is longer than the other, the remaining elements of the longer array are appended at the end.

Function Merge(ByVal arr1 As Variant, ByVal arr2 As Variant) As Variant

Declares a function named Merge that takes two arguments (arr1 and arr2) of type Variant.
The function returns a Variant because arrays in VBA are typically handled as Variant.

    Dim tmpArr As Variant, upper1 As Long, upper2 As Long
    Dim higherUpper As Long, i As Long, newIndex As Long

Declares:
tmpArr: A temporary array that will hold the merged result.
upper1: Stores the upper bound (size) of arr1.
upper2: Stores the upper bound (size) of arr2.
higherUpper: Stores the greater of upper1 and upper2, used to control the loop.
i: Loop counter.

newIndex: Tracks the position where the next element should be inserted in tmpArr.

    upper1 = UBound(arr1) + 1
    upper2 = UBound(arr2) + 1

UBound(arr1) gives the highest index of arr1, so adding 1 gives the total number of elements.
Same logic applies for arr2.

    higherUpper = IIf(upper1 >= upper2, upper1, upper2)

Uses IIf (immediate if) to determine the larger size between upper1 and upper2.

    ReDim tmpArr(upper1 + upper2 - 1)

Allocates space for tmpArr with a size equal to the total number of elements in both arrays.

    For i = 0 To higherUpper

Loops from 0 to higherUpper.
higherUpper ensures we iterate enough times to include all elements of both arrays.

        If i < upper1 Then
            tmpArr(newIndex) = arr1(i)
            newIndex = newIndex + 1
        End If

If i is within the bounds of arr1, insert arr1(i) into tmpArr at newIndex and increment newIndex.

        If i < upper2 Then
            tmpArr(newIndex) = arr2(i)
            newIndex = newIndex + 1
        End If

Similarly, if i is within the bounds of arr2, insert arr2(i) into tmpArr at newIndex and increment newIndex.

    Next i

Moves to the next iteration in the loop.

    Merge = tmpArr

Assigns the merged array (tmpArr) to the function output.

So its like if you had a casino dealer that was perfect at shuffling cards on the table by lifting the edge of the cards and alternating them together but one half of the deck was larger, you would end up with them being alternated but with the remaining cards ending up at the bottom.

[Aussiebear]

Hmmm....... not sure if I can follow all of that.

higherUpper = IIf(upper1 >= upper2, upper1, upper2)
Uses IIf (immediate if) to determine the larger size between upper1 and upper2.

Since we are merging two arrays and if for example Arr(1) had 4 elements, and Arr(2) had 5 elements, haven't we in effect got 9 elements to merge into the tmpArr?

[Aflatoon]

Yes, which is why the output array is sized as:

ReDim tmpArr(upper1 + upper2 - 1)

but the maximum iterations you need to do is determined by the larger of the two input arrays since at each iteration you take a value from both arrays if available. Loop 1 takes item 1 from array 1 to position 1 in output, then item 1 from array 2 to position 2 in output, and repeat to loop 5 which can't take an item from array 1 (only 4 there) but does take the last item from array 2 and puts it at position 9 in the output array.

[georgiboy]

As the code loops through the arrays at the same time (4 & 5) in your example it must loop through the larger of the two arrays to make sure it captures every element in both arrays.

If it looped through the smaller array capturing one element from each of the arrays, you would only end up with eight items in the final array. The IIF statement above determines the largest of the two arrays so that the loop will capture every element from both arrays.

So the higher array (5) captures 1 to 4 from one array and 1 to 5 from the other.

It is the below part of the code that creates the resized array to hold the new alternated array:

ReDim tmpArr(upper1 + upper2 - 1)

[georgiboy]

Aflatoon beat me to it there, now you have it explained from two different brains.

[georgiboy]

Here is one way to follow what is going on, step through the 'RunMe' sub:

Dim arrA As Variant
Dim arrB As Variant


Sub RunMe()
    CreateArrays
    MsgBox Join(Merge(arrA, arrB), ", ")
End Sub


Sub CreateArrays()
    arrA = Array(1, 3, 5, 7)
    arrB = Array(2, 4, 6, 8, 9)
End Sub


Function Merge(ByVal arr1 As Variant, ByVal arr2 As Variant) As Variant
        Dim tmpArr As Variant, upper1 As Long, upper2 As Long
        Dim higherUpper As Long, i As Long, newIndex As Long
        upper1 = UBound(arr1) + 1: upper2 = UBound(arr2) + 1
        higherUpper = IIf(upper1 >= upper2, upper1, upper2)
        ReDim tmpArr(upper1 + upper2 - 1)
        For i = 0 To higherUpper
            If i < upper1 Then
                tmpArr(newIndex) = arr1(i)
                newIndex = newIndex + 1
            End If
            If i < upper2 Then
                tmpArr(newIndex) = arr2(i)
                newIndex = newIndex + 1
            End If
        Next i
        Merge = tmpArr
End Function

[p45cal]

 higherUpper = IIf(upper1 >= upper2, upper1, upper2)

higherUpper = Application.Max(upper2,upper1)

?
Seems a little less convoluted and easier to read.

[Paul_Hossler]

Not enough coffee yet, but by 'Merge' do you mean inserting alternates?

i.e.

ary1 = {1,3,5,7,9,11}
ary2 = {1,2,3,4}

aryOut = {1,1,3,2,5,3,7,4,9,11}

or

aryOut = {1,3,5,7,9,11,1,2,3,4}

[georgiboy]

The code is performing the first one:
aryOut = {1,1,3,2,5,3,7,4,9,11}

[Paul_Hossler]

That's the way I read it also, but sometimes what the code does and what the user wants are diferemt things

I just want a clear requirement

[Aussiebear]

Thank you everyone, I shall have a play with your comments.

[Paul_Hossler]

Late to the game (babysitting grandkids) but since arrays can start and end with any value, e.g. Array1(3 to 12), Array2(-2 to 0), etc., I don't like to make assumptions regardiong bounds

Sub test2()
    Dim a(-2 To 0)


    a(-2) = 1
    a(-1) = 2
    a(0) = 100


End Sub

I often do this if I have to map to (say) columns N:Z. Too me it just makes it more readable

Sub test3()
    Dim A(14 To 26) As Variant
    Dim i As Long
    
    For i = LBound(A) To UBound(A)
        Debug.Print Columns(i).Address
    Next i
End Sub

So my shot was to try and make the Merge wotk without and bounds assumptions. Sometimes I have way TOO much time on my hands

Option Explicit


Sub test()
    Dim ary1 As Variant, ary2 As Variant, aryOut As Variant
    Dim i As Long


    ary1 = Array(100, 300, 500, 700, 900, 1100)
    ary2 = Array(1, 2, 3, 4)


    aryOut = Merge(ary1, ary2)


    For i = LBound(aryOut) To UBound(aryOut)
        Debug.Print i, aryOut(i)
    Next i
End Sub


Function Merge(A1 As Variant, A2 As Variant) As Variant
    Dim n1 As Long, n2 As Long, i As Long, o As Long, m As Long
    Dim i1 As Long, i2 As Long
    Dim A() As Variant
    
    
    n1 = UBound(A1) - LBound(A1) + 1
    n2 = UBound(A2) - LBound(A2) + 1


    ReDim A(1 To n1 + n2)


    m = Application.Min(n1, n2)


    o = 1
    i1 = LBound(A1)
    i2 = LBound(A2)
    
    For i = 1 To m
        A(o) = A1(i1)
        i1 = i1 + 1
        
        A(o + 1) = A2(i2)
        i2 = i2 + 1
        
        o = o + 2
    Next i


    If n1 > n2 Then
        For i = i1 + 1 To n1
            A(o) = A1(i1)
            i1 = i1 + 1
            o = o + 1
        Next i
   
    Else
        For i = i2 + 1 To n2
            A(o) = A2(i2)
            i2 = i2 + 1
            o = o + 1
        Next i
    End If




    Merge = A


End Function

[Aussiebear]

...... here I was thinking i'd have a chance of getting a handle on it, and Paul you go do this to me.

Am I correct in this thinking:
1. We can merge arrays, with smaller arrays being merged into the larger array's, with the resultant array being renamed.

2. That the size of the arrays being of the count of the elements within the array.

upper1 = UBound(arr1) + 1
upper2 = UBound(arr2) + 1

3. The merged array can only occur if the Arrays are of the same data type.

4. That the position of the elements within the merged array is based on "value", or on the "position" of the element within the previous array from which it initially was in?

ary1 = {1,3,5,7,9,11}
ary2 = {1,2,3,4}

aryOut = {1,1,3,2,5,3,7,4,9,11}
or
aryOut = {1,3,5,7,9,11,1,2,3,4}

I didn't get much sleep last night so please keep your baseball bats in their holsters when attempting to edumacate me on this issue.

[Paul_Hossler]

1. Goes back to what you mean by 'Merge'

A1 = {1,2,3,4,5}
A2 = {9.8.7.6}

Amerge = {1,2,3,4,5,9,8,7,6} or Amerge={1,9,2,8,3,7,4,6,5}

2. Since array indecies can start at anything, I think

UBound(a) - LBound(A) +1

is always accurate. A(3 To 10) so 10 - 3 + 1 = 8

3. I don't think so

4. Not sure what you mean. The way I tried has the output arr y just filled up alternating between the two input arrays, and then gets topped off with what's left in the longer one

If you just wanted to stack the two arrays into one, Redim Preserve one to the total and move the other array in

so

A1 = {1,2,3,4,5}
A2 = {9.8.7.6}

Amerge = {1,2,3,4,5,9,8,7,6}

[Aussiebear]

ROFL.... "Amerge" is a headache medicine that narrows blood vessels around the brain. "Merge" to combine or join together.