Thread: Category axis scaling using VBA

Hello all,

I have a rather unusual problem here. After spending about 2 hours googling for anything close to my problem, I thought I'd let you folks give it a whirl.

I have a data plot chart (basic xy chart), that uses a dynamic data range, and automatically adjusts the scaling based on current data.

Everything works perfectly as long as I am adding values to the named range 1 at a time, or deleting multiple values. If, however, I -paste- multiple values into the range, the chart will not assign the new value to the Axes(xlCategory).MaximumScale.

The variable maxCatScale is being calculated correctly according to the watch set, but .MaximumScale is NOT assigned the value. This only happens when pasting the values.

Option Explicit

Sub ScaleDataPlot()
    Dim dataPoints As Integer
    Dim maxCatScale As Integer
    Dim oSeries As Excel.Series
    Dim lowerSpec As Double
    Dim upperSpec As Double
    Dim dataMin As Double
    Dim dataMax As Double
    Dim overallRange As Double
    Dim scaleFactor As Double
    
    With Form.ChartObjects(2).Chart
    
        ' Scale value axis
        lowerSpec = Form.Range("B12").Value
        upperSpec = Form.Range("B13").Value
        dataMin = Form.Range("E11").Value
        dataMax = Form.Range("E12").Value
        
        overallRange = WorksheetFunction.Max(upperSpec, dataMax) - WorksheetFunction.Min(lowerSpec, dataMin)
        scaleFactor = overallRange * 0.1
        
        With .Axes(xlValue)
            .MinimumScale = WorksheetFunction.Min(lowerSpec, dataMin) - scaleFactor
            .MaximumScale = WorksheetFunction.Max(upperSpec, dataMax) + scaleFactor
            .CrossesAt = .MinimumScale
        End With
        
        ' Scale category axis
        dataPoints = WorksheetFunction.CountA(Form.Range("DataRange2"))
        maxCatScale = WorksheetFunction.Max(WorksheetFunction.Ceiling(dataPoints, 10), 10)
    
        If (maxCatScale <> .Axes(xlCategory).MaximumScale) Then
            With .Axes(xlCategory)
                .MinimumScale = 1
                .MaximumScale = maxCatScale
                .MajorUnit = WorksheetFunction.Ceiling((.MaximumScale - .MinimumScale) / 10, 1)
            End With
            
            For Each oSeries In .SeriesCollection
                On Error Resume Next
                If oSeries.HasErrorBars Then
                    oSeries.ErrorBar xlX, xlErrorBarIncludePlusValues, xlErrorBarTypeFixedValue, .Axes(xlCategory).MaximumScale - 1
                End If
            Next oSeries
        End If
    End With
    
End Sub

Any suggestions would be greatly appreciated!
Thanks,

Andy, here's the workbook.

To repeat the problem, try typing values into the table 1 at a time. You'll notice that the "Data Plot" will update as expected. Note, the category axis should only update for every 10 data points (this is by design). In other words, the category axis will always be scaled to the nearest 10 that shows all of the data.

Now, if you add values that cross the 10 boundary (i.e. typing a value for data point #41) the chart will "add" another 10 to the category scale. If you delete any number of values, the chart will -also- rescale as designed.

However, if you have say 28 data points defined, and -paste- 12 more values, bringing the total to 40, the maxCatScale variable correctly calculates the value as 40, but the assignment .MaximumScale = maxCatScale has no effect on the graph whatsoever.

Let me know if you have a problem duplicating this behavior.

Thanks again

Andy,

You're right, there doesn't seem to be any problems with that code now. I can't duplicate the problem either. However, I removed all of the code to scale the other 2 graphs before uploading the workbook (I'm changing the scaling code at the moment and it's mostly broken anyhow).

The only thing I can think of is that somehow the other code was preventing the update somehow. I'll look into it further while adding the scaling code for the normal plot and the histogram.

BTW, the last value missing on the category axis was a minor tradeoff for proper scaling. The X values have to have a whole number MajorUnit for data points to appear lined up properly. I believe there's a way to add arbitrary scaling, but I'll save that exercise for another day, as it's of less importance.

Thanks for your time,