By implementing the MScorlib IComparer Interface, my PropertyComparer Class enables sort Objects added to an ArrayList by the their properties. You can also determine if an Object is in the ArrayList using a BinarySearch.
In order to do a BinarySearch you will need to sort the list with a PropertyComparer and then pass it to the the ArrayList.BinarySearch method.
This requires a MScorlib reference be set
There are a few nuances to consider when implementing MScorlib objects in the VBA.
- Methods and properties of the MScorlib objects are not visible to IntelliSense or the Locals Window
Overloaded Methods are renamed. Generally, the first method in the MSDN documentation would be normal and the subsequent methods would be enumerated with an underscore
In my demo I use
ArrayList.Sort_2 pc and
Implements mscorlib.IComparer Private mArgs As Variant Private mCallType As VbCallType Private mProcName As String Public Function IComparer_Compare(ByVal X As Variant, ByVal Y As Variant) As Long Dim x1 As Variant, y1 As Variant If Len(mProcName) = 0 Then x1 = X y1 = Y Else x1 = CallFunction(X) y1 = CallFunction(Y) End If If TypeName(x1) = "String" Then IComparer_Compare = StrComp(x1, y1, vbTextCompare) Else If x1 > y1 Then IComparer_Compare = 1 ElseIf x1 < y1 Then IComparer_Compare = -1 End If End If End Function Public Sub Init(ProcName As String, CallType As VbCallType, ParamArray Args()) mProcName = ProcName mCallType = CallType mArgs = Args End Sub ' http://www.vbforums.com/showthread.php?405366-RESOLVED-Using-CallByName-with-variable-number-of-arguments ' Author: Joacim Andersson Private Function CallFunction(Object As Variant) Select Case UBound(mArgs) Case -1 CallFunction = CallByName(Object, mProcName, mCallType) Case 0 CallFunction = CallByName(Object, mProcName, mCallType, Val(mArgs(0))) Case 1 CallFunction = CallByName(Object, mProcName, mCallType, Val(mArgs(0)), Val(mArgs(1))) Case 2 CallFunction = CallByName(Object, mProcName, mCallType, Val(mArgs(0)), Val(mArgs(1)), Val(mArgs(2))) Case 3 CallFunction = CallByName(Object, mProcName, mCallType, Val(mArgs(0)), Val(mArgs(1)), Val(mArgs(2)), Val(mArgs(3))) Case 4 CallFunction = CallByName(Object, mProcName, mCallType, Val(mArgs(0)), Val(mArgs(1)), Val(mArgs(2)), Val(mArgs(3)), Val(mArgs(4))) End Select End Function
Public OrdersList As mscorlib.ArrayList Private pc As PropertyComparer Private Sub UserForm_Initialize() Dim cell As Range Set OrdersList = New ArrayList Set pc = New PropertyComparer With Worksheets("Orders") For Each cell In .Range("A2", .Range("A" & .Rows.count).End(xlUp)) OrdersList.Add cell.Resize(1, 8) Next For Each cell In .Range("A1").Resize(1, 8) cboSortBy.AddItem cell.Value Next End With cboSortBy.AddItem "Row" FillOrdersListBox End Sub Private Sub btnFindCarmenSandiego_Click() Dim cell As Range OrdersList.Clear With Worksheets("Orders") For Each cell In .Range("A2", .Range("A" & .Rows.count).End(xlUp)).Resize(, 8) OrdersList.Add cell Next End With pc.Init "Address", VbGet, 0, 0, xlA1, -1 OrdersList.Sort_2 pc FillOrdersListBox lboOrders.ColumnWidths = "" lboOrders.ListIndex = OrdersList.BinarySearch_3(Range("CarmenSandiego"), pc) End Sub Private Sub btnReverse_Click() OrdersList.Reverse FillOrdersListBox End Sub Private Sub cboSortBy_Change() If cboSortBy.ListIndex = -1 Then Exit Sub Select Case cboSortBy.ListIndex Case Is < 8 pc.Init "Cells", VbGet, 1, cboSortBy.ListIndex + 1 Case 8 pc.Init "Row", VbGet End Select OrdersList.Sort_2 pc FillOrdersListBox End Sub Sub FillOrdersListBox() lboOrders.list = WorksheetFunction.Transpose(WorksheetFunction.Transpose(OrdersList.ToArray)) End Sub
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!