Sorting in VBA Arrays

[AAP]

Hi,
I have an two dimensional array and I would like to sort that but couldn't understand how to achieve sorting in array.
I know this can be easily achieved in worksheet but I would like to learn sorting in arrays.
Can anyone help me with this.
Sample file attached.

Thanks a lot!

 

[Marc L]

Hi,

as there is no VBA sort method for an array variable, you need an
algorithm like ComboSort with few data or QuickSort with huge data
(see on Wiki and many samples over the Web in VBA).

Other way is to use a SortedList ActiveX like in the thread
Unique names and their related IDs …
​

 

[AAP]

Sub Demo()
  VA = Sheet1.[B5].CurrentRegion.Value
  Application.ScreenUpdating = False
  Sheet2.Range("B5", Sheet2.Cells(Rows.Count, 3).End(xlUp)).Clear
With CreateObject("System.Collections.SortedList")
  For R& = 2 To UBound(VA)
  .Item(VA(R, 2)) = .Item(VA(R, 2)) & " " & VA(R, 1)
  Next
  R = 5
   
  For L% = 0 To .Count - 1
  Sheet2.Cells(R, 2).Value = .GetKey(L)
  For Each V In Split(LTrim(.GetByIndex(L)))
  R = R
  Sheet2.Cells(R, 3).Value = V
  Next
  R = R + 1
  Next
  .Clear
End With
  Application.Goto Sheet2.Cells(1), True
  Application.ScreenUpdating = True
End Sub

This code works for me but how to sort descending in order.
I guess this line (For L% = 0 To .Count - 1) will do work but couldn't understand L% means.

 

[Marc L]

Descending way : For L% = .Count - 1 To 0 Step -1 …

% means the data type, just check in VBE Locals window !
​

 

[Hui]

L% means a variable named "L" of data type Integer
Refer: http://www.functionx.com/vbaexcel/Lesson03.htm
Scroll down to Integer

 

[Hui]

Data Types, Type Character

Integer, %
Long, @
Single; !
Double, #
String, $
Currency, @

So
Dim myVar as String
can be simplified to
Dim myVar$

Sometimes in VBA you will type 0 and VBA displays 0#
This is because it is declaring that it is a value type Double with a value of 0