VB arrays (excel)

Peter B

I have VB code where I have two arrays and I need to remove all the elements from one array which appear in the second. Both arrays contain only strings.

Anyone know of an easy function to do this?

Seth Brundle

Is this for college? Sounds to me like it is.
Think it through: loop through the first array. Within this loop, loop through the second array and check for the current 1st array item, if it is then remove it from array#1. However, when comparing the two arrays you will need to set a something e.g. a boolean to determine if a match was made.

http://www.ozgrid.com/forum/showthread.php?t=68428

Peter B

No unfortunately not in college anymore.

I have done this in Java using Vectors. With Java I was easily able to add variables to the end of the vector and take them out. With VB you have to decide the size of the array before you add anything to it.

Is there any object type in VB which is better than arrays for this. I have written a function which should do the trick but it seems a long round about way. Is there any shorter way?

By the way I want to return a copy of array1 without any items from array2

Function remove_items(array1, array2)
    count = 0
    
    For i = 1 To UBound(array1)
        
        same = False

        For j = 1 To UBound(array2)
           
            If array1(i) = array2(j) Then
            
                same = True

            End If
        Next j

        If (same) Then
        Else
            count = count + 1
    
        End If
    Next i
    MsgBox count
    count2 = 1
    Dim return_array() As String
    ReDim return_array(count) As String

    For i = 1 To UBound(array1)
        
        same = False

        For j = 1 To UBound(array2)
           
            If array1(i) = array2(j) Then
                    
                

                j = UBound(array2)
                same = True
                

            End If
        Next j
        
        If (same) Then
            'ignore
        Else
            return_array(count2) = array1(i)
            count2 = count2 + 1
        End If

    Next i

    remove_items = return_array

End Function

Peter B

Have edited the code above. I believe that works. The project I am working requires a lot of inserting strings into a "bag" and removing x random ones, or just mixing lists of strings. I am using arrays because they are the only data type I have found which I can do this sort of thing but it is a round about way. Are there any pre-defined functions I can use or better data holders?

Any help would be gladly appreciated.

HarrierJoe

The key is that both lists needs to be sorted alphabetically. The quick solution is to read them both in a listbox-type control and for each item in listbox1 search through listbox2 for any matches. Slow, unefficient, but works if the lists are relatively short and you are in a hurry.

The other more complicated solution but efficient solution is to have incremental search using WHILE...WEND loops of sort.

MOH

Bit late now probably, but you can do it in one loop:

Function remove_items(array1, array2) As String()

Dim return_array() As String
Dim count As Integer
Dim i As Integer
Dim j As Integer
Dim same As Boolean

count = 0

    For i = 1 To UBound(array1)
        same = False

        For j = 1 To UBound(array2)
            If array1(i) = array2(j) Then
                same = True
            End If
        Next j

        If (same) Then
        Else
            count = count + 1
            ReDim [B]Preserve [/B]return_array(count)
            return_array(count) = array1(i)
        End If
    Next i

    MsgBox count
    
    remove_items = return_array
End Function

Redim Preserve maintains the contents after the ReDim.

Note that if you're returning an array, you can only assign it to a variant (although you can still access the element as for a normal array)

You could also just remove the elements from the original array
(for this to work, the array1 will first need to be a variable array)
This may also change the order of the elements in the array - if you need to maintain the order, shuffle all the elements up one when shrinking the array, instead of just swapping with the last one

Sub remove_items2(array1, array2)

Dim count As Integer
Dim i As Integer
Dim j As Integer
Dim same As Boolean
Dim swap As String


    For i = 1 To UBound(array1)
        
        same = False

    'Because we're shrinking our array we may go past the end
        If i > UBound(array1) Then
            Exit For
        End If
        
        For j = 1 To UBound(array2)
            If array1(i) = array2(j) Then
                same = True
            End If
        Next j

        If (same) Then
    'swap current with last element, and delete it
            swap = array1(UBound(array1))
            array1(UBound(array1)) = array1(i)
            array1(i) = swap
            ReDim Preserve array1(UBound(array1) - 1)
    'current element now contains what was the last element, so process it again
            i = i - 1
        End If
    Next i

    MsgBox UBound(array1)
    
End Sub