How to Draw Vectors in Excel Macros

By Samuel Porter

Microsoft Excel is one of the most powerful spreadsheet programs available, and it is useful for a huge variety of activities from financial analysis to science projects. However, most people think of Excel as limited to manipulating grids of formulas, i.e., traditional spreadsheet functionality. But Excel's Visual Basic scripting makes it easy to do even more than that. With VB, you can draw any shape you like on the spreadsheet, and use them to create drawings, charts and other graphics to help visualize your spreadsheet.

Things You'll Need

  • Microsoft Excel 97 or later

Step 1

Open Microsoft Excel. If you have Excel 2007 or later, go to the "View" tab on the ribbon and click "Macros"; otherwise, click the "Tools" menu, and choose "Macros". The Macro dialog box should appear.

Step 2

Type a macro name and click "Create". This should open the Visual Basic applications editor with a new empty subroutine displayed on the screen.

Step 3

Determine the X and Y coordinates you'd like to use for the start and end of your vector. The upper-left-hand corner of the spreadsheet is at (0, 0), and X and Y increase as you go right and down, respectively. Put these values into variables, like this:start_x = 0start_y = 0end_x = 100end_y = 100These values will draw a diagonal line at the upper-lefthand corner of the spreadsheet. You may need to experiment with different numbers to get the line exactly where you want.

Step 4

Add code to draw the line itself:line = Sheet1.Shapes.AddLine(start_x, start_y, end_x, end_y)

Step 5

Add code to add an arrowhead to the end of the line, to make it look like a vector:line.Line.EndArrowheadStyle = MsoArrowheadStyle.msoArrowheadTriangle

Step 6

Close the Visual Basic editor and open the Macro dialog box (see Step 1). Click your macro and click "Run". You should see your line appear.

Tips & Warnings

  • You can use the "line" variable created in Step 4 to change other properties of your vector. For example, to change the line's color to green, you can write "line.Line.ForeColor.RGB = RGB(0, 255, 0)"

References & Resources