How do I sort variables in VBA? 1

[danomaniac]

Question:
I'm trying to load a variable array with numbers, say 100, then sort them, and load a second array with the lowest, next lowest, etc.

something like
load a variable array:

for n = 1 to 100
variable1 = rnd(1000)
next

Then sort them and load another variable so that:

variable2(1) = the lowest
variable2(2) = the next lowest
variable2(3) = the next...etc.

but I don't know how to do that....I could dump them in a range, sort, then reload, but I'd like to do it in code.



"It's more like it is now, than it ever has been."

 

[PHV]

A starting point:
Dim variable1(1 To 100) As Single, N As Integer, M As Integer, tmp As Single
For N = 1 To 100
variable1(N) = Rnd(1000)
Next
For N = 1 To 99
For M = N To 100
If variable1(N) > variable1(M) Then
tmp = variable1(M)
variable1(M) = variable1(N)
variable1(N) = tmp
End If
Next
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CautionMP]

danomaniac,
You might also look at a shell sort.
MSDN Home > MSDN Library > Office Solutions Development >
[tab]A Better Shell Sort: Part I

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[cresbydotcom]

I think that is sometimes called a bubble sort. The last time I use the bubble sort I came to the conclusion that it was better to run ascending array addresses and swap adjacent array elements if (say) greater than. But run descending through the array with the opposite comparison on a second run. Alternating till done.
That way the bubbles moved quicker. It moves the slow moving elements faster on alternative runs.

 

[danomaniac]

PHV,

Thanks - That was exactly what I needed - works great! Thanks again.

"It's more like it is now, than it ever has been."

 

[TonyJollans]

If you're using Word, you can do ...

[blue][tt] WordBasic.Sortarray variable1[/tt][/blue]

If you're in another app, you can instantiate a Wordbasic object to do it ...

[blue][tt] Set WordBasic = CreateObject("Word.Basic")
WordBasic.Sortarray variable1
Set WordBasic = Nothing[/tt][/blue]

... but it probably isn't the best way

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[N1GHTEYES]

Bubble sort is notoriously slow for large sets of numbers.

There are plenty of other sort routines with code examples out there (e.g.

http://www.source-code.biz/snippets/vbasic/6.htm).

 

[mscallisto]

That was probably true (the slowness) way back in the 8086 days of slooow processors but with todays speedy processors and memory it should not be a big deal (although I've really never tested my thesis)

 

[N1GHTEYES]

I have tested it. It depends (obviously) on the size of your data set and the speed of your processor, but, as the speed of machines has increased, so, in general, have our expectations: where we once expected a sort of 1000 items, we now want 1,000,000 & so on. The fact remains that bubble sorting is HORRENDOUSLY slow. The time required grows as something like the square of the numbers sorted.

It also depends on the actual distribution of the numbers in the initial set, but in the vast majority of cases, heapsort is WAAAYYYY better than bubbling. The other nice thing about the code I linked to is that it is sorting a collection - so you can bung pretty much any data you want in there & it will sort it just fine.

Annoyingly, I was going to go on to describe using listboxes to do your sort for you. This is a technique I've used in VB when I was too lazy to code a proper sort routine. You sling the data you want to sort into a listbox, then set the sorted property, & read out the sorted data (the intrinsic sort routines for the listbox are pretty quick). I was thus a bit miffed when I checked and found that the listbox available in Excel does not seem to have this property. Oh well, another good idea does a nosedive...

Tony

 

[mscallisto]

Thanks N1ghteyes...nice post

sam

 

[N1GHTEYES]

Ta.

In the past I've been sorting hundreds of sets of 2-3 million items. Believe me - you don't want to do that by bubbble sort. Unless you've got lots of time to kill...

Tony

 

[HughLerwill]

In Excel psuedo code;

RangeOfCells = VariantArray()

Range.Sort

VariantArray() = RangeOfCells

Apologies for the syntax but its bedtime...

regards Hugh