How To Use VBA To Change The Location Of An Excel Chart

Excel's charting tools are fairly sophisticated but one area where some expertise might help is positioning the chart on your worksheet correctly. For example, you don't want a chart overlapping your data table or covering different pages when printing.

The problem is that a chart is an object in Excel and has no direct relationship in terms of position with any rows or columns.

Position Properties Of An Excel Chart

The chart object in Excel has measurement units rather than direct cell references, and this is where moving the chart to the correct location presents a challenge.

You can return some of the position properties of a chart through VBA code, something like this:

ActiveSheet.ChartObjects(1).Activate
Set c = ActiveChart.Parent

'return the chart height, width, distance from the top and the left of the sheet
chartHeight=c.height
chartTop=c.top
chartLeft=c.left
chartWidth=c.width

Okay, but how does that help us position the chart? Well it depends on want you want to do. Luckily, a cell reference can also return position properties:

set r=range("a1").currentRegion
rangeHeight=r.height
rangeTop=r.top
rangeLeft=r.left
rangeWidth=r.width

Positioning The Chart In Relationship To The Data Set

Let's take the scenario where you'd like to position the chart two rows below the data set and one column in. The easiest way is to use some simple maths to determine the size of the objects and position the chart accordingly.

First, we'll select the data range and determine the height and width of the first cell in the final row.

Set r = Range("a1").CurrentRegion

r.Select

cellWidth = Selection.Columns(1).Width

cellHeight = Selection.Rows(Selection.Count).Height

Now, we'll calculate where we want to chart to be positioned - 2 cells below the data set and 1 row in.

fromTop = r.Height + 2 * cellHeight

fromLeft = cellWidth

And, finally we'll select the chart and apply the positional modifications.

ActiveSheet.ChartObjects(1).Activate

Set c = ActiveChart.Parent

c.Left = fromLeft

c.Top = fromTop

There are many other ways you might want to position your chart; for example to align with a page break, or underneath a summary comment. The concept is similar; determine the position properties for each object and apply the appropriate changes.

Summary

This article has demonstrated how to change the location of a chart produced either by Excel's charting tools or through VBA. It's a good example of fine tuning the functionality of Excel with a little knowledge of VBA.

Andy L Gibson is a former Web Site programmer rediscovering his interest in software applications for small business.

Previously he has used AJAX, XML and ASPX to program Web Sites for restaurants, auction houses and Heath Authorities. He is keen to answer questions and explore business opportunities whereever they might present themselves. You can talk with Andy at his blog at http://solutions4business.wordpress.com/ including links to other articles on VBA issues he has written.

View the original article here