Did you know that you can do graphic related things using vba. Yes you can develop codes to create or modify shapes available in Microsoft Excel. So let's see how we can create a straight line. What you need to do is you need to give x and y coordinates of the start and end of the line. Consider that the upper left corner of the document as (0,0) point. And you need to give the locations in points.
|
Sub DrawLine_Example1() ActiveSheet.Shapes.AddLine 0, 0, 250, 250 End Sub |
If you run above macro you will get following result.
So the first two values are x and y coordinates of the starting point respectively. and next two values are x and y coordinates of end point. And here is another example
|
Sub DrawLine_Example2() ActiveSheet.Shapes.AddLine 20, 100, 100, 45 End Sub |
It will create line like this.
Now let's look at little advanced scenario. Assume you need to create a line between following two points.
So how we do that because we don't know exact x,y coordinates of those two points. Fortunately we can give start and end points from cell locations as well. So we can create the line using following code.
|
Sub DrawLine_Example3() Dim BeginX As Long Dim BeginY As Long Dim EndX As Long Dim EndY As Long With Range("B5") BeginX = .Left + .Width BeginY = .Top + .Height / 2 End With With Range("G9") EndX = .Left EndY = .Top + .Height / 2 End With ActiveSheet.Shapes.AddLine BeginX, BeginY, EndX, EndY End Sub |
In the above code starting point is given by following part.
|
With Range("B5") BeginX = .Left + .Width BeginY = .Top + .Height / 2 End With |
And end point is given by these 4 lines.
|
With Range("G9") EndX = .Left EndY = .Top + .Height / 2 End With |
Also you can replace
|
With Range("B5") BeginX = .Left + .Width BeginY = .Top + .Height / 2 End With |
With following code. Then you don’t need width for the calculations.
|
With Range("C5") BeginX = .Left BeginY = .Top + .Height / 2 End With |
With VBA you can not only create lines but also create any other shapes you need.