How to Draw Vectors in Excel Macros

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.

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.

Advertisement

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.

Video of the Day

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.

Advertisement

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

Advertisement

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.

Advertisement

Step 4

Add code to draw the line itself:

Advertisement

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

Advertisement

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.

Advertisement

Advertisement

references

Report an Issue

screenshot of the current page

Screenshot loading...