In response to the suggestions in this thead, http://windowssecrets.com/forums/showthread//138855-VBA-to-set-y-axis-min-and-max-via-values-in-worskheet-cells, I wanted to show others how I adjusted my chart axis min/max value by using a formula function instead of a predefined value.
I edited the code in the workbook posted by zeddy to specify the actual min and max of the values being plotted and added/subtracted from those values as follows:
ChangeAxisScales()
Dim objCht As ChartObject
For Each objCht In ActiveSheet.ChartObjects ‘loop through all charts on sheet
With objCht.Chart.Axes(xlValue) ‘define shortcut
.MaximumScale = [max(C18:C25)] + 0.05 ‘set value from max of a specified range plus a value (in this case 0.05)
[INDENT]‘I used the max function to calculate the maximum of the values being plotted and added .05 to allow for space above that max value[/INDENT]
.MinimumScale = [min(C18:C25)] – 0.05 ‘set value from min of a specified range minus a value (in this case 0.05)
[INDENT]‘I used the min function to calculate the minimum of the values being plotted and subtracted .05 to allow for space below that min value[/INDENT]
‘.MajorUnit = [Unit] ‘set value from named cell (I commented this out because I didn’t use it … but others might find it helpful)
End With ‘end of shortcut
Next objCht ‘process next chart
End Sub
This was tested in Excel 2011 on my MacBook Pro OS X 10.9.5 “Mavericks”
