Fit Chart Widths Automatically in VBA

[KC305]

Hi,

I can automate chart creation in VBA, but is there any way to also have it such that the x-axis of the chart begins with the minimum x-value and ends with the maximum x-value in the data set? Thanks!

 

[mole999]

have a look at CEILING function, and use that to look at your range, and place a value on your spread sheet. I know there is a way to use that ceiling value when you build the chart in VBA, just can't think of it off the top of my head and the file I have it in is at work

 

[Jon Peltier]

You probably don't want to use exactly the min and max of the data (unless the X value shows dates), but I'll get to that in a minute.

Figure out the min and max of the X values, then apply to the X axis:

Dim dXMin as Double, dXMax as Double
Dim vXVals as Variant
vXVals = ActiveChart.SeriesCollection(1).XValues
dXMin = WorksheetFunction.Min(vXVals)
dXMax = WorksheetFunction.Max(vXVals)

With ActiveChart.Axes(xlCategory)
    .MinimumScale = dxMin
    .MaximumScale = dXMax
End With

To get nicer axis scale parameters than simply using the min and max, check out my tutorial Calculate Nice Axis Scales in Excel VBA.