How to Draw Vectors in Excel Macros

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.

Step

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

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

Video of the Day

Step

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 = 0 start_y = 0 end_x = 100 end_y = 100

These 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

Add code to draw the line itself:

line = Sheet1.Shapes.AddLine(start_x, start_y, end_x, end_y)

Step

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

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.