Adding a Line Shape of a Given Slope and Length

David would like to add a line at a particular slope and for a particular length in his workbook. This should be a line shape, not a chart. he would like to specify a slope (such as 34 degrees) and a length (such as 3.5 inches) and have that line appear within the workbook. He wonders if there is a way to get that precise with line shapes and, if so, how he can do it.

You can create the desired line either manually or with a macro. To do it manually, follow these steps:

1. Display the Insert tab of the ribbon.
2. In the Illustrations group, click the Shapes tool. Excel displays a drop-down palette of many different shapes.
3. Click on the Line shape. (This shape is in the Lines section. Make sure you choose the leftmost line, the one without any arrowheads on it.) Excel changes the mouse pointer to a small plus sign.
4. Hold down the Shift key as you click and drag the mouse to draw your line. The length doesn't really matter at this point, but it should be horizontal because you are holding down the Shift key.
5. When you release the mouse button, the line appears.
6. Hover the mouse pointer over the line. The mouse pointer should change to a four-headed arrow.
7. Right-click on the line and choose Size and Position from the resulting Context menu. Excel displays the Format Shape pane at the right side of the screen.
8. Set the Width to be 3.5 and the Rotation to be 34.

The other settings in the pane don't really matter that much. It is the Width and Rotation settings that are the important ones.

If you want to use a macro to create the desired line, here is an easy way to do it:

Sub DrawLine()
    Dim dLength As Double
    Dim dAngle As Double
    Dim x1 As Single
    Dim y1 As Single
    Dim x2 As Single
    Dim y2 As Single

    ' Use upper-left corner of active cell as starting point
    x1 = ActiveCell.Left
    y1 = ActiveCell.Top

    ' Grab length and angle from noted cells
    dLength = Range("B4").Value
    dAngle = Range("B5").Value
    dLength = Application.InchesToPoints(dLength)
    dAngle = WorksheetFunction.Radians(dAngle)   ' Convert degrees to radians

    x2 = x1 + dLength * Cos(dAngle)
    y2 = y1 - dLength * Sin(dAngle)

    ' Insert a line object of 4 points thickness
    With ActiveSheet.Shapes.AddLine(x1, y1, x2, y2).Line
        .Weight = 4
    End With
End Sub

This macro reads the contents of cells B4 and B5 each time it is run to determine, respectively, the length and angle of the desired line. Each time you run it, it also adds a new line. Because of this, you'll want to select a different cell each time you run it because the line starts at the upper-left corner of the selected cell.

Also, note that the manual steps described earlier start with a line that is horizontal. The macro, however, creates a line that is at the desired angle and length—it doesn't start from a horizontal position. This means that if you select the macro-created line and display the Format Shape pane, the Width and Rotation settings won't match what you might expect. The angle and length of the macro-created line are correct, however.

Now, one more thing to keep in mind: Shapes in Excel are often approximate, not exact. This is particularly true if you print out the worksheet that includes the line. You might get out your protractor and find that the line isn't exactly at 34 degrees or find that your ruler indicates it isn't exactly 3.5 inches. This has to do with lots of variables relative to display and printing of graphics, and there is very little that can be done about it. The best bet is to either accept the approximation or play around with the lines until you get them to appear (or print) exactly as you expect.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11796) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.