why the sorting Doesn't Happen? (1 Viewer)

[prabha_friend]

Option Compare Database

Private Sub MailFolder_Browse_Click()
Dim MailFolderDiag As FileDialog
Set MailFolderDiag = Application.FileDialog(msoFileDialogFolderPicker)
With MailFolderDiag
    .AllowMultiSelect = False
    .Show
End With
Me.MailFolder.Value = MailFolderDiag.SelectedItems(1)
Dim CurrentFile As Variant
CurrentFile = Dir(Me.MailFolder.Value & "\", vbNormal)
Do While CurrentFile <> ""
    'MsgBox CurrentFile
   Dim myXLapp As New Excel.Application
   With myXLapp
        .Visible = True
        With .Workbooks.Open(FileName:=Me.MailFolder.Value & "\" & CurrentFile, ReadOnly:=True)
            'MsgBox .Name
            Dim VisibleIndex As Integer
            VisibleIndex = 0
            Dim SheetNamesWithIndex() As Variant
            For Each Sheet In .Sheets
                With Sheet
                    If .Visible = True Then
                        If VisibleIndex = 0 Then
                            ReDim Preserve SheetNamesWithIndex(0, 1)
                        Else
                            ReDim Preserve SheetNamesWithIndex(0, UBound(SheetNamesWithIndex, 2) + 2)
                        End If
                        SheetNamesWithIndex(0, UBound(SheetNamesWithIndex, 2) - 1) = Sheet.Index
                        SheetNamesWithIndex(0, UBound(SheetNamesWithIndex, 2)) = CDate(Split(.Name, "-")(UBound(Split(.Name, "-"))) & "/01")
                        VisibleIndex = VisibleIndex + 1
                    End If
                End With
            Next Sheet
            Dim SortedSheetNames As Variant
            SortedSheetNames = myXLapp.WorksheetFunction.Sort(SheetNamesWithIndex, 2, -1)
            MsgBox SortedSheetNames(1)
            .Close
        End With
        .Quit
    End With
    CurrentFile = Dir()
Loop
End Sub

Exactly Here SortedSheetNames = myXLapp.WorksheetFunction.Sort(SheetNamesWithIndex, 2, -1)

 

[cheekybuddha]

First, always use Option Explicit in your code (above or below Option Compare Database)

This will highlight undeclared variables like Sheet:

' ...
            For Each Sheet In .Sheets
                With Sheet
'...

why the sorting Doesn't Happen?

Not sure, but can the Excel Sort function sort multi-dimensional arrays?

Also what is the purpose of using a multi-dimensional array here?

 

[prabha_friend]

If not... What would be the shortest possible way. VBA doesn't have a sort function of its own I think. There must be some way right. What is that?

 

[prabha_friend]

How about using the higher entities like Collections, Dictionaries, structs and Types. I think it's the time for me to grow above just 'Arrays'

 

[MarkK]

If you set a reference to mscorlib, you can use a class called SortedList, which sorts objects by key. Here is a code sample, and output...

Sub Test()
    Dim csl As New mscorlib.SortedList
    Dim i As Integer
    
    csl.Add "Key", "Value"
    csl.Add "Zebra", "Has Stripes"
    csl.Add "Test", "This is a test"
    csl.Add "Angler", "This is a fish"
    csl.Add "Chocolate", "This is sweet"
    
    For i = 0 To csl.Count - 1
        Debug.Print i, csl.GetKey(i), csl.GetByIndex(i)
    Next
End Sub

The output of that code is...

 0            Angler        This is a fish
 1            Chocolate     This is sweet
 2            Key           Value
 3            Test          This is a test
 4            Zebra         Has Stripes

I more commonly use the ArrayList class, which you can sort based on values in the objects it contains, but there is also a Queue class and a Stack class. One problem with doing this is that these object do not expose intellisense in VBA, so you have to google what members they expose.