Sort Numbers in 2 dimension array (Word 97SR2)

Topic

New Reply

I’m curious to know what routine you use to sort numerical data in VBA.

Wordbasic.sortarray sorts numbers like this:

1,2,3,31,300,3000,4,5, 600, etc.

Reply | Quote

Replies

You could do the sort by code- for a starting point

Reply | Quote

Happy New Year everybody.

Sortarray does that if the array is type String. If you want to sort numeric string data then it must be normalised by adding as many leading zeros as are necessary to make the numbers the same length, ie, in a population where the maximum numeric value is 10000, 1 must be 00001 for things to sort correctly.

If you declare the array as Variant and populate it with Integers or any other kind on numeric data, then Sortarray will sort it correctly.

Kevin Bell

Reply | Quote

Happy new year Big Kev!

Spookily, I came up with the same question as Kevin today and was headed over here to do a search when I saw his post – weird.

Anyway, another question: how do you feed a two-dimensional array to WordBasic.SortArray or maybe more relevant, how do you use the return value? – when I try to Debug.Print the contents of the array, after having run it through WB.SortArray, I get a runtime ‘Subscript out of range’ error (whereas I don’t get this error when I run the array through a different sort function, which however has the same number sorting drawback Kevin is getting). The WB.SortArray seems to go OK but the error is when I try to access the result.
(One other thing to add for troubleshooting: my array was originally 0-based but I also tried changing it to 1-based in case this had any effect on things – but it didn’t…..)

I’ve got a string in one dimension of the array and a number in the other (Long), and have declared the array as type Variant. It doesn’t seem to matter whether I put the Long in the first dimension or second.

(For the other sort function I was using, the array was of type String and I converted the Longs to Strings before adding them to the array.)

Thanks,
Gary

Reply | Quote

It’s that great minds thinking alike thing Gary… I’m sure of it.

My question really should have been phrased a bit more precisely.

Like you, I’ve got one “column” or element of the array taking numeric data and another taking string. The array is declared type Variant. WB.sortarray does sorts the numerica data (though I don’t recall if it sorts correctly), but the big problem is the string column returns with floating point values (singles, doubles?)!!?? Very strange. I guess wb.sortarray can only handle one data type at a time.

I think the bottom line is another sort routine. Romke Soldaat has a sort class available for subscribers here . I’m not a subscriber, but think I’ll become one just to get this sort class. Sounds like the way to go. But not until I’ve exhausted the genius pool here and elsewhere.

Reply | Quote

Kevin,

Was there anything which you could adapt in the bubble sort routine I pointed at?

Reply | Quote

Bubble’s too slow (no offense). See recent post to you.

Reply | Quote

Gary,

I subscribed to officepro mag (or whatever it’s called) to get at the Romke Soldat article that includes the class mod for sorting 2-dim arrays. The code works well but I had to tweak it to replace the enum statements (VBA6) with const statements (VBA5).

Class mods… the only way to fly!

p.s. There’s nothing in the copyright info that indicates you have to be a subscriber to the mag in order to use the code

It says this:
‘

Reply | Quote

Kevin – you’re a gem , but I was already on it. The “other sort function” mentioned in my reply to Big Kev here was in fact Romke Soldaat’s sort class which I’d downloaded the day before (more spooky, huh?).

The problem I was having was that I was declaring the array as a String, and it was returning the numbers “out of sorts” as you were getting as well. When I posted back to BigKev, I was trying to use WordBasic.SortArray instead, and had tried changing my array to a Variant, no success. What I didn’t think of trying ’til I got up today was to go back to Romke’s sort class, using the variant array instead of the string array.

Just ran it and everything’s sorting correctly now.

I’m with you on the simplicity possible when you wrap all the tricky stuff into a class – here’s a snippet of the code I’m using to do the sort:

With Sort
    .DataType = dtNumber
    .Descending = False
    .SortColumn = 1
    .Sort arBmks
End With

For n = 0 To lngBmkCt - 1
    Debug.Print arBmks(n, 0) & " - "; arBmks(n, 1)
Next 'n

– you gotta love this.

BTW another benefit of getting the subscription and thereby access to the archives on that site: almost all the currrent content of the magazine is about 2002-centric features which aren’t much use to anyone who’s working solely on 2000. But all the back issue stuff on their website is a treasure trove of stuff that’ll work on 97/2000.

Thanks to you, Geoff and BigKev for your input.

Gary

Reply | Quote

Interesting,

I must confess to not using the WordBasic.SortArray function. I have a horrible feeling that MS will just say, ‘WordBasic functions are no longer supported’, one day. In cases like yours, I would modify the Quicksort routine from the code librarian to add the additional dimensions, but that’s just me. I will definitely look at Romke Solaat’s site.

As for a bubble sort being too slow, that depends on what you are actually doing. If you are adding another element to an already sorted array and re-sorting, bubble sorts can be faster than other sorts as the new element will float to its correct position and the comparisions only performed once.

As an aside Kevin. Another of your posts asks whether or not you can find out how many dimensions an array has. The sort class from Romke must be able to do that or how can he determine whether or not the .SortColumn property is correct, or indeed know how many columns there are to sort?

Keep the faith guys.

Regards,

Kevin Bell

Reply | Quote

Now, reach into your code bag and pull me out a nice ini file class module…

Reply | Quote

For starters, have a look here.

Reply | Quote

Gary,

Sorry, you’ll have to also post your userID and password for us to see it .

(And your credit card and PIN while you’re at it).

Reply | Quote

That used to be a freebie, but not anymore. But I had no problem pulling it up (and you knew that) ‘cuz I signed up for the trial offer.

I actually got those two articles (Part I and II) back when they were free. I confess, I’ve not studied them, but I remember getting the impression the class mod code was incomplete. I also need i/o that can handle >255 character strings.

Nevertheless, I will read those articles again. Thanks for the reminder.

Reply | Quote

Kev,

Please see my post to Gary (responding to your post) below. I cannot get sortarray to work 2 dimensional arrays with different data types in each element. Have you got another multi-dim array sorter routine?

Reply | Quote

Here’s a Zip file containing a VB project that has a module that sorts 2 dimensional arrays using up to 3 keys. The module contains documentation as to how to call the subroutine.

I have tested this as much as I can but I can’t absolutely guarantee that it is completely bug free. Please make sure you test it against your data before implementing it.

If anyone has any questions, suggestions fit to print, mods or bugs please post to this thread and I will try to respond.

Regards,

Kevin Bell

Reply | Quote

Thanks, Kevin.

Just so you know. That Class mod mentioned above works very well too. If you are interested in seeing it, post back and I’ll post an attachment.

Reply | Quote