Trig Calculation Error in VBA Excel Macro

[Machinist R1]

I will start off by apologizing for the length of this post. The macro is supposed to take the data from an excel file and convert it into a text file script macro for Draftsight. Everything works as intended except for the trig calculation and I believe it is due to a programmatic error. I would appreciate any input that helps me make this macro work properly.

The output from the FormattedStringRadiusLine variable is correct and works as intended. The output from FormattedStringEllipseLine variable is incorrect and while the lines that are created are interesting, I cannot program the correct toolpath from them (I am a CNC programmer btw)

Example data would be 2 columns with the first column containing the line angle from 0 to 90 and the second column containing line length in the 2.5-3.5 range generating a non-standard profile

I am posting here because I believe that the macro has a simple error that a second set of eyes will catch

Here is the entire macro:

Option ExplicitPrivate Sub CreateTextFile001_Click()
'
' Create all Variables
'
Dim StockPlateFile As Variant, DataRange As Range, CellValue As Variant, RowPosition As Integer, ColumnPosition As Integer, LineAngle As Double, LineLength As Double
Dim FormattedStringRadiusLine As String, FormattedStringEllipseLine As String, LastX As Double, LastY As Double, CurrentX As Double, CurrentY As Double
Dim LineAngleString As String, LineLengthString As String, LastXString As String, LastYString As String, CurrentXString As String, CurrentYString As String
'
' Set DataRange to highlighted cells
'
Set DataRange = Selection
'
' Prompt user to select file destination for formatted data and set StockPlateFile to the selected file name
'
StockPlateFile = Application.GetSaveAsFilename(filefilter:="SCR Files,*.scr", Title:="Ellipse File", Buttontext:="Select")
'
' Determine if the user selected a file
'
If StockPlateFile <> False Then
    '
    ' Open the selected file
    '
    Open StockPlateFile For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    '
    ' Gather the data from the highlighted cells
    '
    ' Loop for rows
    For RowPosition = 1 To DataRange.Rows.Count
        '
        ' Store LastX and LastY
        '
        LastX = CurrentX
        LastY = CurrentY
        CurrentX = 0
        CurrentY = 0
        LineLength = 0
        LineAngle = 0
        ' Loop for columns
        For ColumnPosition = 1 To DataRange.Columns.Count
            ' Grab the value in the cell for each iteration of each loop
            CellValue = DataRange.Cells(RowPosition, ColumnPosition).Value
            ' Sort the cell data based upon which column it is in
            If ColumnPosition = 1 Then
                ' First column is Angles
                LineAngle = CellValue
            End If
            If ColumnPosition = 2 Then
                ' Second column is lengths
                LineLength = CellValue
            End If
        Next ColumnPosition
        '
        ' Calculate X and Y coordinates of line end point
        '
        CurrentX = (LineLength * Cos(LineAngle)) - 25
        CurrentY = LineLength * Sin(LineAngle)
        '
        ' Format the data to work correctly in Draftsight
        ' A formatted string is required to get the correct number of spaces for the LINE command
        '
        '
        ' Round the numbers to 6 places
        LastX = Round(LastX, 6)
        LastY = Round(LastY, 6)
        CurrentX = Round(CurrentX, 6)
        CurrentY = Round(CurrentY, 6)
        ' Convert numbers to strings
        LineLengthString = Str(LineLength)
        LineAngleString = Str(LineAngle)
        LastXString = Str(LastX)
        LastYString = Str(LastY)
        CurrentXString = Str(CurrentX)
        CurrentYString = Str(CurrentY)
        '
        ' Radius Line from -25,0 Format
        '
        FormattedStringRadiusLine = "LINE -25,0 @" & Format(LineLengthString, "00.000000") & "<" & Format(LineAngleString, "00.000000")
        '
        ' Line from end of last Radius Line to end of current Radius Line Format
        '
        FormattedStringEllipseLine = "LINE " & Format(LastX, "00.000000") & "," & Format(LastY, "00.000000") & " " & Format(CurrentX, "00.000000") & "," & Format(CurrentY, "00.000000")
        '
        ' Write the Angle Line String to the file
        '
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , FormattedStringRadiusLine
        '
        ' Write the End Point String to the file only if this is not the first Row
        '
        If RowPosition > 1 Then
            ' Write the String to the file
            Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , FormattedStringEllipseLine
        Else
        End If
    Next RowPosition
    ' Close the file
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
Else
'
' If user selects cancel
'
End If


End Sub

 

[Machinist R1]

I figured out what the problem was. Cos() and Sin() functions use radians in VBA not degrees. I would assume that all other angle related trig. functions use radians as well.

To get radians from degrees, multiply degrees * ( PI / 180 ). I hope this helps someone in the future.

 

[joeu2004]

Or simply WorksheetFunction.Radians(degrees).

If you prefer degrees*pi/180, you might consider saving WorksheetFunction.Pi in a type Double variable. Its binary representation is closer to the actual value of pi (according to the wikipage), and it is infinitesimally more accurate than the binary representation of the constant 3.14159265358979.

Unrelated observation.... Generally, it is better to use type Long instead of type Integer, especially for the type of RowPosition, which might exceed 32767 in theory, albeit perhaps not in your case.

In modern CPUs, there is no performance benefit to using type Integer. And usually, the storage benefit does not matter, especially in your example.

 

[Machinist R1]

Awesome! Thank you for your reply, knowing these kinds of tips is very helpful.