Using SortedList to sort the items

[YasserKhalil]

Hello everyone
I have some names in column A and their marks in column B and I have used the following code to debug.print the top ten names

Code: Select all

Sub MyTest()
    GetTopTen Range("A2:B100")
End Sub

Sub GetTopTen(r As Range)
    Dim v, t, i As Long
    t = Application.WorksheetFunction.Aggregate(14, 6, r.Columns(2), 10)
    v = r
    For i = 1 To UBound(v, 1)
        If Not IsError(v(i, 1)) Then
            If v(i, 2) >= t Then
                Debug.Print v(i, 1), v(i, 2)
            End If
        End If
    Next i
End Sub

The code is OK but I need to sort the results to make the names with the largest marks at the top of the list. I thought of using SortedList but couldn't implement it in the code.

[HansV]

SortedList is not generally available, so I'd avoid it.

S0667.png

[YasserKhalil]

What may be the alternative solution?

[HansV]

Copy the range to another location, sort it descending on marks, get the top 10 rows, then clear or delete the copy.

[SpeakEasy]

>SortedList is not generally available

Interesting. The CLR (of which the system collections are a part) has been shipped as part of the (desktop) OS since Vista. And I've been successfully using both system collections, and other parts of the CLR in VB, VBA and VBScript for almost 15 years, and in all that time never had anyone suggest they don't have the CLR available to them. You pretty much have to have made a conscious decision to disable or uninstall it ... (And uninstalling .NET in Windows 10 isn't possible; at least not easily)

Back in 2010, Microsoft's analysis indicated that about 90% of Windows PCs had a version of the .NET Framework installed (and thus an instance of the CLR). I've not seen a more recent figure, but I'd be surprised if it were to have decreased. So I'm not sure I'd go along with 'not generally available'

[HansV]

I do have various versions of .NET installed, and I have never uninstalled a version on my fairly recent PC (November 2020).
mscorlib is present, but I still get the error shown above.

[YasserKhalil]

I think this needs a specific version of .NET which is 3.5

[StuartR]

My Windows 10 laptop also generates that Automation error. I have not intentionally deinstalled any Windows components. I seem to have .NET 4.8

[HansV]

These are the versions on my PC. They were either came with Windows 10 or with other software; I haven't explicitly installed or uninstalled any .NET version.

S0668.png

[SpeakEasy]

Yep, not arguing about the error message! To use late binding for the various System.Collections, .NET needs to have registered the underlying assembly in mscoree.dll as COM interops. Sometimes this breaks.

Can you check if you have the following registry key:

HKEY_CLASSES_ROOT\System.Collections.SortedList

if it isn' there, then late binding won't work (if it is there, then there is something else broken about the interop install; try following the CLSID to see where it thinks mscoree is). In either event, should still be able to use early binding,. Add a reference to mscorlib.dll (confusingly, not actually a dll but a tlb)

And then

Dim sl as SortedList
Set SL = New SortedList

should work fine (and has the advantage that there are a bunch of other tricks you can then implement - e.g applying custom sorting function to the aSortedList - that can't be easily be done with late binding)

One possibility is that sometimes the .NET 3.5 feature (which should enable late binding capability) is not enabled by default. So you might want to enable that feature

[HansV]

HKEY_CLASSES_ROOT\System.Collections.SortedList is present. The CLSID points to C:\Windows\System32\mscoree.dll which is present too.

But my point is that this is much too technical for the general user, so I wouldn't want to depend on it.

[SpeakEasy]

And my point is that in 15 years this is the first time I've seen reports of it not working. Perhaps I am exposed to the wrong sort of users ...

[Doc.AElstein]

Hello
I am not sure if this is so relevant, so forgive me if my ignorance and annoying lack of general computer knowledge is showing up once again… _
_...In recent years I have taken some interest in things that some people a lot smarter than me are doing to make Windows do what they thought it should be doing , and not what Microsoft more recently have been decided they want it to do.. you know the “shut up” stuff , “de bloat” stuff etc…
I have seen that people who offer scripts and packages to, in their opinion, “put it right”, often have an extra button or option, as an addition to the main "Putting it right stuff", to install a “.Net thing”, I think it was 3.5 ….
Alan
( Edit P.S. I just rememberd something else.. A couple of years ago I was getting into VPN for all my computers. Most are old, and I had no problems installing different "Client things". A newer one crashed out when trying to install a VPN client software, but suggested I instal some .Net thing, which I did, then all was well.. )
Edit, P.S. 2

... Add a reference to mscorlib.dll ....
And then ...

I did one of these instead a few times, I think it worked - As an alternative to manually Adding the referrence, you can do it with coding. I have found this useful sometimes when supplying macros that need the referrence, - You can arrange that it is done when a workbook opens, for example, ( using good use of error handling , in case the referrence is already added) :-...
_...

Code: Select all

ThisWorkbook.VBProject.References.AddFromFile "C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb"

or by

Code: Select all

ThisWorkbook.VBProject.References.AddFromguid "{BED7F4EA-1A96-11D2-8F08-00A0C9A6186D}", 2, 4

( Ref: https://www.snb-vba.eu/VBA_Sortedlist_en.html )

[YasserKhalil]

@Hans
Can you please give me an example of how to use the ArrayList (how to add keys and values?)

[HansV]

See VBA ArrayList – A Complete Guide

[Doc.AElstein]

See VBA ArrayList – A Complete Guide

That Guy's blogs are, im my opinion, very good and concise, and check this bit out.. VBA ArrayList Automation Error .....

[YasserKhalil]

Thank you very much for all of you.