Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort Numbers in 2 dimension array (Word 97SR2)

    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.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Numbers in 2 dimension array (Word 97SR2)

    You could do the sort by code- <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=vb&Number=67129&page=&v iew=&sb=&o=&vc=1#Post67129>for a starting point</A>
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort Numbers in 2 dimension array (Word 97SR2)

    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

  4. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Sort Numbers in 2 dimension array (Word 97SR2)

    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

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Numbers in 2 dimension array (Word 97SR2)

    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 <A target="_blank" HREF=http:///www.msofficemag.net/search/results.asp?QU=sort+array&Area=f&Limit=1&Method=K> here </A>. 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.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Numbers in 2 dimension array (Word 97SR2)

    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?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Numbers in 2 dimension array (Word 97SR2)

    Kevin,

    Was there anything which you could adapt in the bubble sort routine I pointed at?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Numbers in 2 dimension array (Word 97SR2)

    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:
    '
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  9. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Numbers in 2 dimension array (Word 97SR2)

    Bubble's too slow (no offense). See recent post to you.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  10. #10
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Sort Numbers in 2 dimension array (Word 97SR2)

    Kevin - you're a gem <img src=/S/salute.gif border=0 alt=salute width=15 height=20>, but I was already on it. The "other sort function" mentioned in my reply to Big Kev <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=vb&Number=104677&page=0 &view=expanded&sb=5&o=0#Post104677>here</A> 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 <img src=/S/doh.gif border=0 alt=doh width=15 height=15> was to go back to Romke's sort class, using the variant array instead of the string array.

    Just ran it and <img src=/S/joy.gif border=0 alt=joy width=23 height=23> 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:

    <pre>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</pre>

    - 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

  11. #11
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort Numbers in 2 dimension array (Word 97SR2)

    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

  12. #12
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Numbers in 2 dimension array (Word 97SR2)

    Now, reach into your code bag and pull me out a nice ini file class module...
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  13. #13
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Sort Numbers in 2 dimension array (Word 97SR2)

    For starters, have a look <A target="_blank" HREF=http://www.msofficemag.net/features/1999/01/vba199901kg_f/vba199901kg_f.asp>here</A>.

  14. #14
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Numbers in 2 dimension array (Word 97SR2)

    Gary,

    Sorry, you'll have to also post your userID and password for us to see it <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    (And your credit card and PIN while you're at it).
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  15. #15
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Numbers in 2 dimension array (Word 97SR2)

    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.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •