Slow bubble sort

**Oppressed1** · 10-03-2012, 02:46 PM

Well I know it's slow, but how would you replace it? Short of a third party ingredient, ideally. "C" language is not available either, so forget that great pointer and qsort help. Looking for a builtin solution. (BTW cboRanges is a combo box - if that matters to anyone)

Please Login or Register  to view this content.

edit: FYI further code to fill combobox follows. This part is not the speed bump; just provided for completeness

Please Login or Register  to view this content.

**Kyle123** · 10-03-2012, 02:55 PM

Not strictly speaking a built in object and it will only work on windows:

Please Login or Register  to view this content.

**shg** · 10-03-2012, 03:25 PM

Everyone has a VBA quicksort routine.

Is the object just to sort a combobox?

**Oppressed1** · 10-03-2012, 03:33 PM

Kyle that looks sweet but .Add gets
Error 458:Variable uses an Automation type not supported in Visual Basic

I call your function with
sMyArray = SortArr(sMyArray)
And this is VBA, not VB, in case anyone trips on that.

I love the dkless Microsoft explanation http://msdn.microsoft.com/en-us/libr...=vs.60%29.aspx which basically says "error 458 is that you made an error." Those D.A.s. Help?

**Kyle123** · 10-03-2012, 03:44 PM

For strings, try:

Please Login or Register  to view this content.

**Oppressed1** · 10-03-2012, 04:19 PM

shg: yes.
Kyle: cstr worked but the function invocation
sMyArray = SortArr(sMyArray)
gives a type mismatch.

This is probably too remedial for Kyle so anyone answer

Note that I redimmed sMyArray yet the function returns a variant (variant, though really a "Collection.ArrayList" where perhaps the incompatibility lies). (I'm unfamiliar with ArrayList. As always, highlighting in code and hitting F1 for Microsoft help doesn't recognize the keyword, as has come to be the case for me on 100% of the commands in VBA help - "Keyword not found")

**Kyle123** · 10-03-2012, 05:26 PM

You can only pass arrays as variants, so in your case sMyArray would need to have been declared as:

Dim sMyArray as variant

You won't get any help from Excel on this one I'm afraid, the Araylist object doesn't belong to Excel it's something that's being borrowed from the .Net framework, so it's actually a VB.NET / C sharp data structure (although it's not really used in those languages since there are better alternatives).

You're atcually passing back a variant array, rather than an array list, the ToArray method sees to that

**Oppressed1** · 10-03-2012, 07:33 PM

Talk about "seeing to things" : my explicitly declared (via redim) "1 To cboRanges.ListCount" array became "0 To cboRanges.ListCount-1" upon the function return value(s) being stuffed into sMyArray (which is now a variant) (though it was still getting redim'd as 1 to cboRanges.ListCount). For those playing along at home, that means all my code indexes into sMyArray drop by 1 now (but only after first calling the sort, and not before). (So to reduce confusion, I modified the redim)

This is a great fast solution, just what I wanted. I might have even learned something new at my annoyingly high age. Further impressive is how you glommed right off of the returned object with With, brazenly avoiding the superfluous time and space of an unneeded variable creation. A+.

BTW, for those who are stuck without the .Net framework, I just discovered a very well done and broad sort analysis (Heap Sorts, Merge Sorts, etc. etc.)
http://www.xtremevbtalk.com/showthread.php?t=78889