3 Replies Latest reply on Oct 10, 2017 12:29 PM by Josh Brady

    Sorting Sheet Names

    Matt Bieringer

      Hello all,

      I currently have a macro that will create a drawing from a part or assembly. It creates a new sheet for each configuration. My issue comes when I try to reorder the sheets via a bubblesort I get some odd results.

      The code:

      'Sorts Sheets 
      Dim vSheetArr As Variant
      vSheetArr = swDraw.GetSheetNames
      For i = 0 To SheetCount - 1
          sheetName = vSheetArr(i)
          If sheetName Like "*OP*" Then
              sheetName = Right(sheetName, Len(sheetName) - 3)
              vSheetArr(i) = CLng(sheetName)
          End If
      Next i
      Call Sort(vSheetArr)
      For i = 0 To SheetCount - 1
          sheetName = vSheetArr(i)
          If IsNumeric(sheetName) Then
              sheetName = "OP " & sheetName
              vSheetArr(i) = sheetName
          End If
      Next I
      bool = swDraw.ReorderSheets(vSheetArr)
      If bool = False Then
          MsgBox "NO WORKIE"
          Unload Me
      End If
      

       

      Sub Sort(vSheetArr As Variant)      'Sort Sheets
      Dim first           As Long
      Dim last            As Long
      Dim i               As Long
      Dim j               As Long
      Dim Temp            As Long
      Dim list            As Long
          first = LBound(vSheetArr)
          last = UBound(vSheetArr)
          For i = first To last - 1
              For j = i + 1 To last
                  If vSheetArr(i) > vSheetArr(j) Then
                      Temp = vSheetArr(j)
                      vSheetArr(j) = vSheetArr(i)
                      vSheetArr(i) = Temp
                  End If
              Next j
          Next i
      End Sub
      

       

      So if I have configurations OP 1, OP 2, OP 3, OP 10, & OP 100. The sort will output this as: OP 1, OP 10, OP 100, OP 2, & OP 3.

       

      Any help would be appreciated.

        • Re: Sorting Sheet Names
          Josh Brady

          You are sorting strings, not numbers.  Therefore it's just like an alphabetical sort, character by character.  The greater than/less than comparison stops at the first different character and compares them.  Therefore, anything that starts with "OP 1" comes before anything that starts with "OP 2".  If you want to sort the numbers, you'll have to strip off the "OP " portion somehow.