Sorting an Array in VBA

• • Reply

  11 years ago

  by hdeepika

  Dee Pika

  Melbourne, Australia

  Joined 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.

  Report abuse
• • Reply

  11 years ago

  by Uncle

  Uncle

  England, United Kingdom

  Joined 19 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.

  Report abuse
• • Reply

  11 years ago

  by hdeepika

  Dee Pika

  Melbourne, Australia

  Joined 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?

  Report abuse
• • Reply

  11 years ago

  by Uncle

  Uncle

  England, United Kingdom

  Joined 19 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

  Report abuse