Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Create Lines With VBA

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.

Contact Form

Name

Email *

Message *

Popular Posts