Sorting an Array in VBA

VBA , SQLserver , Oracle Melbourne, Australia
  • 11 years ago

    Hi, I am new to VBA and want to write VBA code for sorting company details based on a given prioritylevel for each company. Sorted data is then used in other routines. Following are the details of a given company. Name, Address, phone, prioritylevel I am thinking of defining a class module to hold above information, and then define a array of class objects (is this possible?) for storing details of each and every company, and have some mechanism for sorting the array based on priority_level. Is this a reasonable approach? Has someone got otherway of doing this in VBA. Thanks.

  • 11 years ago

    Which program is storing the company details? If you're using Access, you could use a SQL query to create a temporary table and use that. If you're using Excel, then using a global array of variants in a module would be possible, but if there are a lot of companies, it'll chew up lots of RAM.

  • 11 years ago

    Thanks for the reply. There is no database or Excel involved. Data is read by VBA from another system.(eg. from a file).

    Class Module: clsCompany public Name as String public Priority as Integer

    Sub AssignTasks() Dim AllCompany as New Collection Dim Company1 as New clsCompany Dim Company2 as New clsCompany

    'Add as many company to the collection 'Initialise the values Company1.Name = " Name A" Company1.Priority =2 AllCompany.Add Company1

    Company2.Name = " Name B" Company1.Priority =1 AllCompany.Add Company2 ..... 'Now I need to Sort the collection (use bubble sort)

    End Sub

    I have used the standard bubble sort to sort the Colection, but failed when tried to swap items within the Collection based on the Priority. Any idea of sorting a Collection?

  • 11 years ago

    I have investigated this but can't find a way of sorting a Collection. The only way I could do it was to use an array as follows:

    Option Explicit
    Option Base 1
    Sub AssignTasks()
    Dim Companies(3) As Class1
    Dim CompanyTemp As Class1
    Dim i As Integer
    Dim j As Integer
    'Add as many company to the collection
    'Initialise the values
        Set Companies(1) = New Class1
        Companies(1).Name = " Name A"
        Companies(1).Priority = 1
        Set Companies(2) = New Class1
        Companies(2).Name = " Name B"
        Companies(2).Priority = 2
        Set Companies(3) = New Class1
        Companies(3).Name = " Name C"
        Companies(3).Priority = 1
    'check current values
    For i = 1 To 3
        MsgBox Companies(i).Name
        MsgBox Companies(i).Priority
    Next i
    'sort by priority
    Set CompanyTemp = New Class1
    For i = 1 To UBound(Companies) - 1
        For j = i + 1 To UBound(Companies)
            If Companies(i).Priority >= Companies(j).Priority Then
                CompanyTemp.Priority = Companies(j).Priority
                CompanyTemp.Name = Companies(j).Name
                Companies(j).Priority = Companies(i).Priority
                Companies(j).Name = Companies(i).Name
                Companies(i).Priority = CompanyTemp.Priority
                Companies(i).Name = CompanyTemp.Name
            End If
        Next j
    Next i
    'check sort has worked properly
    For i = 1 To 3
        MsgBox Companies(i).Name
        MsgBox Companies(i).Priority
    Next i
    End Sub

    with the following code in the Class module:

    Private strName As String Private intPriority As Integer

    Public Property Get Name() As String Name = strName End Property

    Public Property Let Name(Value As String) strName = Value End Property

    Public Property Get Priority() As Integer Priority = intPriority End Property

    Public Property Let Priority(Value As Integer) intPriority = Value End Property

Post a reply

Enter your message below

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“You can stand on the shoulders of giants OR a big enough pile of dwarfs, works either way.”