AnsweredAssumed Answered

Sorting the last part of a unit vector in Excel VBA

Question asked by Thomas Bryant on Apr 13, 2016
Latest reply on Apr 19, 2016 by 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

Outcomes