Keep zero baseline in charts

Factotum

Board Regular
Joined
May 14, 2015
Messages
118
I've done a lot with VBA, but never anything relating to charts. I have a macro, courtesy of www.TheSpreadsheetGuru.com, which adjusts the Min/Max of the Y-Axis of a chart (see below). I would like to make it so that a zero baseline is always visible. If the data points go below 0, then the zero baseline would be somewhere in the middle of the graph. If the data points do not go below zero, then the bottom of the Y-Axis would begin at zero, regardless what the max is. Any suggestions?

As a bonus: any suggestions on how to always round the max up to the nearest 100?

Here's the code I have:
Code:
Dim cht As ChartObject
Dim srs As Series
Dim FirstTime  As Boolean
Dim MaxNumber As Double
Dim MinNumber As Double
Dim MaxChartNumber As Double
Dim MinChartNumber As Double


With Worksheets("KPMs 2018").ChartObjects("Chart 1")
    .Activate


    'Determine Chart's Overall Max/Min From Connected Data Source
      For Each srs In ActiveChart.SeriesCollection
        'Determine Maximum value in Series
          MaxNumber = Application.WorksheetFunction.Max(srs.Values)
        
        'Store value if currently the overall Maximum Value
          If FirstTime = True Then
            MaxChartNumber = MaxNumber
          ElseIf MaxNumber > MaxChartNumber Then
            MaxChartNumber = MaxNumber
          End If
        
        'Determine Minimum value in Series (exclude zeroes)
          MinNumber = Application.WorksheetFunction.Min(srs.Values)
          
        'Store value if currently the overall Minimum Value
          If FirstTime = True Then
            MinChartNumber = MinNumber
          ElseIf MinNumber < MinChartNumber Or MinChartNumber = 0 Then
            MinChartNumber = MinNumber
          End If
End With


    'Rescale Y-Axis
    ActiveChart.Axes(xlValue).MinimumScale = Round(MinChartNumber / 100) * 100
    ActiveChart.Axes(xlValue).MaximumScale = Round(MaxChartNumber / 100) * 100
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Factotum

Board Regular
Joined
May 14, 2015
Messages
118
Looks like I should do my homework before agreeing to write a macro to do what the user wants. Turns out that what I was trying to do, and eventually succeeded in, is already an available function in Excel. All that is needed is to right click on the axis, select Format Axis from the drop down, and set the Minimum and Maximum to 'Auto'. In case anyone is interested in how I did this with VBA, I just added an IF/THEN to the original code (see below). I did not find out how to always round up, so I just rounded to the nearest hundred and added an extra 500 to the axis to make sure the rounding did not take the graph off the chart.

Code:
Sub Chart_Update()


Dim cht As ChartObject
Dim srs As Series
Dim FirstTime  As Boolean
Dim MaxNumber As Double
Dim MinNumber As Double
Dim MaxChartNumber As Double
Dim MinChartNumber As Double


With Worksheets("KPMs 2018").ChartObjects("Chart 1")
    .Activate


    'Determine Chart's Overall Max/Min From Connected Data Source
      For Each srs In ActiveChart.SeriesCollection
        'Determine Maximum value in Series
          MaxNumber = Application.WorksheetFunction.Max(srs.Values)
        
        'Store value if currently the overall Maximum Value
          If FirstTime = True Then
            MaxChartNumber = MaxNumber
          ElseIf MaxNumber > MaxChartNumber Then
            MaxChartNumber = MaxNumber + 500
          End If
        
        'Determine Minimum value in Series (exclude zeroes)
          MinNumber = Application.WorksheetFunction.Min(srs.Values)
          
        'Store value if currently the overall Minimum Value
          If FirstTime = True Then
            MinChartNumber = MinNumber
          ElseIf MinNumber < MinChartNumber Or MinChartNumber = 0 Then
            MinChartNumber = MinNumber
          End If
        
      Next srs
End With


    'Rescale Y-Axis
    If MinChartNumber > 0 Or MinChartNumber = 0 Then
        ActiveChart.Axes(xlValue).MinimumScale = 0
    Else
        ActiveChart.Axes(xlValue).MinimumScale = Round(MinChartNumber / 100) * 100 - 500
    End If
    
    ActiveChart.Axes(xlValue).MaximumScale = Round(MaxChartNumber / 100) * 100


End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,177
Messages
5,640,618
Members
417,158
Latest member
jimmy1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top