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?