Excel VBA userform Merging 2 Arrays together and removing duplicates

[JasonEnsor]

Hi Guys,

I am building a form in Excel that has 2 textboxes that hold values separated by ";". What I am wanting is to read each of the textbox values and store them as arrays, then to combine them in to a 3rd array whilst removing duplicates. What I have so far doesn't seem to work for creating the combined array.

So the function I am using is:

Function MergeArrays(vFirstArray As Variant, vSecondArray As Variant) As Variant

    Dim vMergedArray() As Variant
    Dim iFirstArrayLen As Integer
    Dim iSecondArrayLen As Integer
    Dim iMergedArrayLength As Integer
    Dim iCounter As Integer
    
    iFirstArrayLen = UBound(vFirstArray)
    iSecondArrayLen = UBound(vSecondArray)
    iMergedArrayLength = iFirstArrayLen + iSecondArrayLen
    
    ReDim vMergedArray(0 To iMergedArrayLength)
    iCounter = 1

    Do While iCounter <= iFirstArrayLen
        vMergedArray(iCounter) = vFirstArray(iCounter)
        iCounter = iCounter + 1
    Loop
    Do While iCounter <= iMergedArrayLength
        vMergedArray(iCounter) = vSecondArray(iCounter - iFirstArrayLen)
        iCounter = iCounter + 1
    Loop
    
    MergeArrays = vMergedArray
    
End Function

I am then testing this by using the following code

Option Base 1
Private Sub Image1_Click()
    Dim vFirstArray As Variant
    Dim vSecondArray As Variant
    Dim vArray3  As Variant
    
    vFirstArray = Split(selectedClosureReasonsTxt.Text, ";")
    vSecondArray = Split(selectedRecallReasonsTxt.Text, ";")
    
        vArray3 = MergeArrays(vFirstArray, vSecondArray)
    
    Dim iCounter As Integer
    
    For iCounter = 1 To UBound(vArray3)
        Debug.Print iCounter & " " & vArray3(iCounter)
    Next iCounter
    
End Sub

selectedClosureReasonsTxt.Text currently has "ABR;ACS;"
selectedRecallReasonsTxt.Text currently has "ABR;DIS;"

When I run the code my output is:
1 ACS
2
3 DIS
4

Which to me seems as though it is not reading the data in the first element in the Array. Does anyone have any ideas how to resolve this? Or can propose a better solution so that I end up with a final list of unique values from both textboxes in an array

Many Thanks in advance



Regards

J.

 

[SkipVought]

Jason,

1. Why start with Counter = 1? You loose your first element.

2. Why end the strings with ;? this adds an empty element.

Each of your arrays has an upper bound of 2, meaning that there are 3 elements. The lower bound for each is 0. So when combining, UBound + 1 for each array, then finally subtract 1.

And my results...
[pre]
0 ABR
1 ACS
2 ABR
3 DIS
[/pre]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Why do you complicate this so much? Depending on the requirement and meaning of semicolon at the end, you can
- combine textboxes texts first with "&",
- split,
- use collection to generate unique list with filtering empty strings if necessary.

combo

 

[JasonEnsor]

Hi Guys,

The reason for the ";" is that I use a listview with checkboxes for the user to select a choice of codes, this then populates the textbox. The ";" separation means I can display them to the user in a format they are familiar with and can display a vast number of codes in the single textbox without having to scroll (both textboxes are controlled by different listboxes). From the user input I then use the distinct values to generate SQL code.

Skip thanks, that has solved my issue. I can't believe I missed the Ubound + 1...

Combo thanks for also pointing out how I had over complicated the joining of the two inputs. Once again I had fallen victim to the bad habit of jumping in to code without fully planning what I wanted and started running with my first idea.

Thanks again guys, as always I appreciate your wise words of wisdom.

Regards

J.