Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

Note: I setup a new PC and had errors due to not having Net 3.5 installed, which is NOT default. This unfortunately means I'll be removing this function from my VBA Library.

ArrayList uses an external but standard library and is much better then VBA's built in Array functions.

https://excelmacromastery.com/vba-arraylist/

Use like so:

 Dim ArrListAs Object
 Set ArrList= CreateObject("System.Collections.ArrayList")
 ArrList.Insert 0, "Plum"

Range To ArrayList:

Public Function RangeToArrList(Rng As Range) As Object
 Set RangeToArrList = CreateObject("System.Collections.ArrayList")
 Dim rcell As Range
 For Each rcell In Rng.Cells
  If Not IsError(rcell.Value) Then
   If rcell.Value <> "" Then
    If Not RangeToArrList.contains(rcell.Value) Then
     RangeToArrList.Add rcell.Value
    End If
   End If
  End If
 Next rcell
End Function
Sub TestingSpot_Sub()

 Dim Rng As Range
 Set Rng = Range("F2:F22")
 Dim ArrList As Object
 Set ArrList = RangeToArrList(Rng)
 
 For Each Item In ArrList
  Debug.Print Item
 Next Item

End Sub

Comparing ArrLists:

Public Function NotInSecondArrList(ArrListA As Object, ArrListB As Object) As Object
 Set NotInSecondArrList = CreateObject("System.Collections.ArrayList")
 For Each Item In ArrListA
  If Not ArrListB.contains(Item) Then
   NotInSecondArrList.Add Item
  End If
 Next Item
End Function
Sub TestingSpot_Sub()

 Dim Rng As Range
 
 Set Rng = Range("F2:F22")
 Dim ArrListA As Object
 Set ArrListA = RangeToArrList(Rng)
 
 Set Rng = Range("H2:H22")
 Dim ArrListB As Object
 Set ArrListB = RangeToArrList(Rng)
 
 Dim NotInBArrList As Object
 Set NotInBArrList = NotInSecondArrList(ArrListA, ArrListB)
 
 For Each Item In NotInBArrList
  Debug.Print Item
 Next Item

End Sub
  • No labels