Quick Jump
Introduction | Example | Tutorial | Applications | Comments

Introduction - Drawing Arrows

This is a fun macro. Use this VBA procedure to draw lines or arrows between two cells in Excel. It’s great for finding matches in data sets or comparing old and new locations in 2D Excel text maps. It may not have practical applications for everyone, but you can still learn a lot from it.


Draw Arrows between Cells

Private Sub DrawArrows(FromRange As Range, ToRange As Range, Optional RGBcolor As Long, Optional LineType As String)
'---------------------------------------------------------------------------------------------------
'---Script: DrawArrows------------------------------------------------------------------------------
'---Created by: Ryan Wells -------------------------------------------------------------------------
'---Date: 10/2015-----------------------------------------------------------------------------------
'---Description: This macro draws arrows or lines from the middle of one cell to the middle --------
'----------------of another. Custom endpoints and shape colors are suppported ----------------------
'---------------------------------------------------------------------------------------------------

Dim dleft1 As Double, dleft2 As Double
Dim dtop1 As Double, dtop2 As Double
Dim dheight1 As Double, dheight2 As Double
Dim dwidth1 As Double, dwidth2 As Double
dleft1 = FromRange.Left
dleft2 = ToRange.Left
dtop1 = FromRange.Top
dtop2 = ToRange.Top
dheight1 = FromRange.Height
dheight2 = ToRange.Height
dwidth1 = FromRange.Width
dwidth2 = ToRange.Width
 
ActiveSheet.Shapes.AddConnector(msoConnectorStraight, dleft1 + dwidth1 / 2, dtop1 + dheight1 / 2, dleft2 + dwidth2 / 2, dtop2 + dheight2 / 2).Select
'format line
With Selection.ShapeRange.Line
    .BeginArrowheadStyle = msoArrowheadNone
    .EndArrowheadStyle = msoArrowheadOpen
    .Weight = 1.75
    .Transparency = 0.5
    If UCase(LineType) = "DOUBLE" Then 'double arrows
        .BeginArrowheadStyle = msoArrowheadOpen
    ElseIf UCase(LineType) = "LINE" Then 'Line (no arows)
        .EndArrowheadStyle = msoArrowheadNone
    Else 'single arrow
        'defaults to an arrow with one head
    End If
    'color arrow
    If RGBcolor <> 0 Then
        .ForeColor.RGB = RGBcolor 'custom color
    Else
        .ForeColor.RGB = RGB(228, 108, 10)   'orange (DEFAULT)
    End If
End With
 
End Sub

Tutorial

Learning the Macro Arguments

Copy and paste the DrawArrows macro into your VBA Editor. The macro accepts 4 arguments, two of which are optional:

  1. FromRange - Starting cell where you want your shape to begin.
    • Example: Range(“B2”)
  2. ToRange - Ending cell where you want your shape to end.
    • Example: Range(“C4”)
  3. RGBcolor - (Optional) Long variable type representing the color you want your shape to be.
    • If you don’t enter a color, the shape will default to orange.
    • Example: RGB(0, 255, 0)
  4. LineType - (Optional) String variable type representing the type of shape you want to draw.
    • If you don’t enter a string, the shape will default to a line with the arrow pointing to the ToRange cell.
    • Accepted arguments:
      • “Double” - draws a line with arrows on both ends.
      • “Line” - draws a line with no arrows.

Each line the macro creates will be drawn from the center of the cell defined in “FromRange” to the center of the cell defined in “ToRange.”

Calling the Macro

The DrawArrows macro will generate lines based on how you call it. Here a couple sample Call statements to get you started.

Example 1:

Call DrawArrows(Range("b1"), Range("c4"))

VBA Draw Shapes Example

Example 2:

Call DrawArrows(Range("b1"), Range("c4"), , "Line")

VBA Draw Shapes Example

Example 3:

Call DrawArrows(Range("b1"), Range("c4"), RGB(0, 0, 255), "Double")

VBA Draw Shapes Example

Customizing the Macro

The default transparency for the lines this macro generates is 0.5. I made this the default so you can still read the text in cells beneath the arrows. You can change the .Transparency = 0.5 line if you want a different transparency.

Likewise, you change the default line thickness by playing with the .Weight = 1.75 line.

One other area of the code you may want to tweak is the default color. It’s currently set to an orange color, but change the integers in RGB(228, 108, 10) if you prefer a different color.

See it in Action

Where does this macro shine? I’ve paired it with my Compare Two Excel Columns for Differences macro to show where items from one column of data were moved to in a second column of data.

Automatically Draw Arrows To Compare Lists with VBA
Compare Columns with Arrows

This is cool and all but, do you want to know where it truly shines? Of course you do. It excels (pardon the pun) when you want to show a visual representation of how the position of an object has changed over time. For example, in the nuclear industry, we may shuffle a control blade from one location to another during a refueling outage. I used this exact macro to draw a map of where control blades came from and where they were moved to during an outage. Here’s an example:

Automatically Draw Arrows To Compare Maps with VBA
Track Location Changes with Arrows

These visuals are great for management and non-technical colleagues because they are easy to digest. You look at them and you know what exactly what they’re trying to say. You just follow the arrows!

Bonus Macros

Hiding Arrows

Are your arrows in the way? Hide them with the following macro:

Sub HideArrows()
    For Each shp In ActiveSheet.Shapes
        If shp.Connector = msoTrue Then
            shp.Line.Transparency = 1
        End If
    Next shp
End Sub

When you’re done hiding them, set shp.Line.Transparency = 0.5.

Deleting Arrows

If you want to start over, run the following macro:

Sub DeleteArrows()
    For Each shp In ActiveSheet.Shapes
        If shp.Connector = msoTrue Then
            shp.Delete
        End If
    Next shp
End Sub

To avoid drawing lines on top of each other, it’s a good idea to call this procedure each time before calling the DrawArrows macro.

Application Ideas

Whether you’re comparing lists or tracking control blades in a nuclear reactor, adding arrows to your Excel spreadsheets can make things look simple. Here are a few ideas to get you started.

  1. Use it to track changes to a seating chart for a wedding.
  2. Got a favorite college football team? Use arrows to trend their performance in the NCAA rankings over time.

If you’re looking to compare columns, this article is a good place to get you started.

Comments

Subscribe to my VBA Tutorials blog or follow me on Google+ for more great VBA content. Share this article with others and send me a message if you have ideas for exciting VBA articles.

Discover how this Nuclear Engineer Mastered Excel VBA

And why you should, too


Your time is valuable. It's time to become a VBA expert.

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.