8 Replies Latest reply on Apr 19, 2016 3:20 PM by Thomas Bryant

    Sorting the last part of a unit vector in Excel VBA

    Thomas Bryant

      I have been working on a VBA macro that exports a unit vector for each selected line in the SolidWorks interface, and I want to sort the value of the unit vector before it is written to Excel. I need to sort the Excel range B2 to D2 (B2 representing part i of the unit vector, C2 representing part j, and D2 representing part k), and the range depends on the number of selected lines. I want to sort them by the k element of the unit vector, which is is array variable vModelSelPt4(2) in the macro code, and I want to sort them with the maximum value in cell D2, the minimum value in cell D3, and have it go ascending from there.

       

      So if there were four selected lines, the D column would look like this:

       

      404.22

      135.13

      182.66

      253.05

       

      Here are the relevant sections of the code; let me know if more is needed. The problem area is the second for statement. The logic I think is sound, but it doesn't print anything out when the Excel file is generated:

       

      For l = 1 To NumberOfSelectedItems

          Set swSketchLine = swSelMgr.GetSelectedObject6(l, -1)

          If Not swSketchLine Is Nothing Then

              Set swSketch = swSketchLine.GetSketch

           If Not oMathVector Is Nothing Then

               Set oUnitVector = oMathVector.Normalise()

               dArrUnit = oUnitVector.ArrayData()

              vModelSelPt4 = GetModelCoordinates(swApp, swSketch, dArrUnit)

               Message = Message & "Unit vector is " & vModelSelPt4(0) & "i + " & vModelSelPt4(1) & "j + " & vModelSelPt4(2) & "k" & vbCrLf

               Set oUnitVector = Nothing

               Set oMathVector = Nothing

      End If

      Next l

       

      For q = 3 To NumberOfSelectedItems

              If vModelSelPt4(2) < Excel.Range("D" & q).Value Then

                   Excel.Range("D" & q).EntireRow.Insert

                   bRes = WriteToExcel((q), vModelSelPt4, "Unit Vector")

              End If

      Next

       

      Private Function WriteToExcel(startRow As Integer, data As Variant, Optional label As String = "") As Boolean

          'get the results into excel

          With xlWorkbook.ActiveSheet

              .Cells(startRow + 2, 2).Value = data(0)

              .Cells(startRow + 2, 3).Value = data(1)

              .Cells(startRow + 2, 4).Value = data(2)  

          End With

      End Function

       

      I see a similar discussion here; should I change it to resemble that?

       

      https://forum.solidworks.com/thread/73048?q=Sorting%20an%20Excel

        • Re: Sorting the last part of a unit vector in Excel VBA
          Amen Allah Jlili

          if your macro has access to Excel, why don't you just sort the range B through D.

           

          Maybe something like follow:

           

           

          Columns("B:D").Sort key1:=Range("D1"), order1:=xlAscending, header:=xlYes

           

           

           

          More information on MSDN.

          PS: I hope you meant 104.22 and not 404.22.

            • Re: Sorting the last part of a unit vector in Excel VBA
              Thomas Bryant

              Hi Amen,

               

              Unfortunately I did mean 404.22; if this were a simple ascending sort it would be easy, haha. The program needs to loop through the data that is created, find the maximum number and put it on cell D2, then put the remaining numbers in ascending order. We don't know how many numbers there will be every time because that depends how many lines the user selects in SolidWorks. or maybe it would be easier to put the data in Excel first, sort it in ascending order, and then put the bottom number in cell D2 and move the others down?

               

              This is one of my previous threads if you need to see more code, or I can paste it here:

               

              Problem with transforming sketch coordinates to model coordinates

                • Re: Sorting the last part of a unit vector in Excel VBA
                  Amen Allah Jlili

                  I don't have access to SolidWorks nor Excel but if I understood correctly, how about copying the selected data to Excel and sorting all the columns descendingly so you have the maximum value at the first line and then simply sorting the second line downwards ascendingly?

                   

                  Something like:

                   

                   

                  Columns("B:D").Sort key1:=Range("D1"), order1:=xlDescending, header:=xlYes

                  Dim L as long: L =  LastColumn = ActiveSheet.Cells("D", ActiveSheet.Columns.Count).End(xlUp).Rows
                  Range("B2:D"&L).Sort key1:=Range("D2"), order1:=xlascending, header:=XlNo

                    • Re: Sorting the last part of a unit vector in Excel VBA
                      Thomas Bryant

                      Hi Amen,

                       

                      Ok, I was able to get that into my code like this:

                       

                      For q = 3 To NumberOfSelectedItems

                      bRes = WriteToExcel((q), vModelSelPt4, "Unit Vector")

                      Columns("B:D").Sort key1:=Range("D2"), order1:=xlDescending, Header:=xlYes

                      'q = LastColumn = ActiveSheet.Cells("D2", ActiveSheet.Columns.Count).End(xlUp).Rows

                      Range("B3:D" & q).Sort key1:=Range("D3"), order1:=xlAscending, Header:=xlNo

                      Next

                       

                      I didn't understand that second line and got a type mismatch when it was in there, so I commented it out, but it seems to work with just those two lines. I'll continue testing it, but this seems to be what I was looking for. Thanks!

                • Re: Sorting the last part of a unit vector in Excel VBA
                  Amen Allah Jlili

                  the commented line is meant to give you the number of the last row of data since you don't know how many rows.

                   

                  Replace it with the following bit (I've tested it):

                   

                   

                  Dim l As Long

                  Dim ws As Worksheet

                  Set ws = ActiveSheet

                  l = ws.Cells(ws.Rows.Count, 4).End(xlUp).Rows
                  Range("B3:D" & L).Sort key1:=Range("D3"), order1:=xlAscending, Header:=xlNo

                    • Re: Sorting the last part of a unit vector in Excel VBA
                      Thomas Bryant

                      I changed it to this:

                       

                      For q = 3 To NumberOfSelectedItems

                                          bRes = WriteToExcel((q), vModelSelPt4, "Unit Vector")

                                          Columns("B:D").Sort key1:=Range("D2"), order1:=xlDescending, Header:=xlYes

                                          Dim r As Long

                                          Dim ws As Worksheet

                                          Set ws = ActiveSheet

                                          r = ws.Cells(ws.Rows.Count, 4).End(xlUp).Rows

                                          'q = LastColumn = ActiveSheet.Cells("D2", ActiveSheet.Columns.Count).End(xlUp).Rows

                                          Range("B3:D" & q).Sort key1:=Range("D3"), order1:=xlAscending, Header:=xlNo

                                          Next

                       

                      But I get an 'Application-defined or object-defined error' on this line when running it initially (I put the declaration statements outside the for loop but that did not help):

                       

                      Columns("B:D").Sort key1:=Range("D2"), order1:=xlDescending, Header:=xlYes

                       

                      Then when subsequently running it, it sorts, but only in descending order.

                       

                      Thanks,

                       

                      Thomas

                        • Re: Sorting the last part of a unit vector in Excel VBA
                          Amen Allah Jlili

                          change the q in the last line to r
                          Range("B3:D" & R).S

                            • Re: Sorting the last part of a unit vector in Excel VBA
                              Thomas Bryant

                              Hi Amen,

                               

                              Ok, after doing that and another minor change, it works the first time but after trying it again it does this:

                               

                              Sort Screenshot.PNG

                               

                              Then, it gives the same error as described in my previous post ('Application-defined or object-defined error') while highlighting that same line:

                               

                              Columns("B:D").Sort key1:=Range("D2"), order1:=xlDescending, Header:=xlYes

                               

                              Here's the whole loop:

                               

                              For q = 3 To NumberOfSelectedItems

                                                  bRes = WriteToExcel((q), vModelSelPt4, "Unit Vector")

                                                  Columns("B:D").Sort key1:=Range("D2"), order1:=xlDescending, Header:=xlYes

                                                  r = ws.Cells(ws.Rows.Count, 4).End(xlUp).Rows

                                                  'q = LastColumn = ActiveSheet.Cells("D2", ActiveSheet.Columns.Count).End(xlUp).Rows

                                                  Range("B3:D2" & r).Sort key1:=Range("D3"), order1:=xlAscending, Header:=xlNo

                                                  'bRes = WriteToExcel(((L - 1) * 1) + 1, vModelSelPt4, "Unit Vector")

                                                  Next

                               

                               

                              Does a try-catch exception need to be put in or something? Thanks.