Featured Discussion: Two Different Ways to Sort Arrays in VBScript

Join the Discussion
Go to this thread


Discussions Homepage

om Gow is working on a Web page in ASP/VBScript. He’s having trouble alphabetically sorting an array with state names. The array in question is loaded with the results of three different queries. The sort is working, but after it’s complete, the data in three of the array columns is out of sync. How does he keep all of the rows of data in sync?

Solution #1
Larry Serflaten says the problem is that every time you move one item, you have to move every item in a whole row. However, this works for only small arrays?and thus, only with short rows. If the arrays are larger, with several items in a single row, it’s undesirable to move that much data around?if it can be avoided.

One way to avoid this is to use a different array as a pointer (indexing) into the data array. This sorts the data, without having to move it, but it needs to be accessed through the indexing array.

Here’s Larry’s example code:

HTHKFSPrivate Sub Form_Load()Dim Data(0 To 1, 0 To 5)Dim I, J, Index  Randomize  Debug.Print "Original list:"  For i = 0 To 5    J = Int(Rnd * 1000)    Data(0, i) = J    Data(1, i) = "ITEM" & Str(J)    Debug.Print Data(0, i), Data(1, i)  Next  Sort Data, Index  Debug.Print "Sorted:"  For i = 0 To 5    Debug.Print Data(0, Index(i)), Data(1, Index(i)), "was: "; Data(0, i)  NextEnd SubSub Sort(ByVal Data, ByRef Index)Dim p1, p2, pp  ReDim Index(0 To 5) ' Hard coded for demo convenience  For p2 = 0 To 5    Index(p2) = p2  Next  p2 = 1  Do While p1 < 5    Select Case Sgn(Data(0, Index(p2)) - Data(0, Index(p1)))    Case -1      pp = Index(p1)      Index(p1) = Index(p2)      Index(p2) = pp      p2 = p1 + 1    Case 0, 1      p2 = p2 + 1      If p2 > 5 Then        p1 = p1 + 1        p2 = p1 + 1      End If    End Select  LoopEnd Sub

Solution #2

Figure 1: The initial (unsorted) display.

Phil Weber suggests an alternative method. Tom could use a user-defined type (UDT) to hold the data and then sort the UDT, rather than sorting with a multi-dimensional array. Simply create an array of the type; when an item is moved, all of its elements will move together. DevX Executive Editor A. Russell Jones has contributed sample code to illustrate Phil’s point.

You can find many different types of sort routines on the Web and in print VB resources. The sort routines for this example are based on a QuickSort function found on the VB2TheMax site. The original version doesn’t work with UDTs, but that’s easy to fix. Unfortunately, it’s also labor-intensive. Sort routines such as this are type specific so if your UDT contains fields of differing types, you’ll need to write one sort routine for each type. In addition, to work with named fields in a UDT, you can’t simply pass in the field name, and there’s no way to access the fields by index number; therefore you have to hard-code the sort routines, one for each field by which you want to sort the UDT.

The example sorts an array of UDT items called state_struct, which contains census population data, by state, for the years 1990 and 2000. Each UDT item contains a state name, abbreviation, and the population figures for the two years. When you consider this data, you can see that it would be convenient to be able to display the items sorted by any field in the UDT.

Public Type state_struct   name As String   abbreviation As String   population2000 As Long   population1990 As LongEnd Type
Figure 2: Here’s the same data sorted by the population2000 field.

To do that, you’ll need four copies of the QuickSort routine, one for each field. In the example code, those routines are in a StateSorter.bas module, and are named QuickSortStatesByName, QuickSorrtStatesByAbbreviation, QuickSortStatesByPop1990 and QuickSortStatesByPop2000. The sample form contains buttons that let you sort the data by any field. Take a look at Figure 1 to see the initial (unsorted) display.

Figure 2 shows the same data sorted by the population2000 field, in reverse order (highest population first).

You can see that using a UDT lets you sort by any one field while still keeping the data for the rows in sync. You could easily adapt this same sorting scheme to sort a class containing properties for the fields.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin


The Latest

technology leadership

Why the World Needs More Technology Leadership

As a fact, technology has touched every single aspect of our lives. And there are some technology giants in today’s world which have been frequently opined to have a strong influence on recent overall technological influence. Moreover, those tech giants have popular technology leaders leading the companies toward achieving greatness.

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job