Excel charting tools are quite complex, but one area where a specialist can help is to correctly position a chart on a worksheet. For example, you do not want the chart to overlap your data table or display different pages when printing.
The problem is that the chart is an object in Excel and is not directly related to the position with any rows or columns.
Excel chart position properties
The diagram object in Excel has units, not direct references to cells, and it is here that moving the diagram to the right place is the problem.
You can return some properties of a chart position via VBA code, something like this:
ActiveSheet.ChartObjects (1) .Activate
Set c = ActiveChart.Parent
Return the height, width of the chart, the distance from the top and left of the sheet
chartHeight = c.height
chartTop = c.top
chartLeft = c.left
chartWidth = c.width
Good, but how does this help us position the chart? Well, it depends on what you want to do. Fortunately, the 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 a chart in relation to a dataset
Take the scenario in which you need to place the chart two lines below the data set and one column. The easiest way is to use some simple mathematical calculations to determine the size of the objects and the placement of the diagram, respectively.
First we select the data range and define the height and width of the first cell in the last row.
Set r = Range ("a1"). CurrentRegion
r.Select
cellWidth = Selection.Columns (1) .Width
cellHeight = Selection.Rows (Selection.Count) .Height
Now we will calculate where we want the chart to be positioned - 2 cells below the data set and 1 row in.
fromTop = r.Height + 2 * cellHeight
fromLeft = cellWidth
Finally, we will select a diagram and apply positional modifications.
ActiveSheet.ChartObjects (1) .Activate
Set c = ActiveChart.Parent
c.Left = fromLeft
c.Top = fromTop
There are many other ways in which you can put your chart; for example, to align with a page break or below a summary comment. The concept is similar; define position properties for each object and apply the appropriate changes.
Summary
This article shows how to change the location of a chart, created either by building Excel tools or through VBA. This is a good example of fine-tuning Excel functionality with little knowledge of VBA.
