CODEWITHSUNDEEP
excelchartsscalevba
TYale
TYale

Reputation: 114

Excel VBA Set Chartsheet Axis to Logarithmic

Problem

I am trying to programmatically fill chartsheets with data on both linear and logarithmic scales. For some reason, the Axis.ScaleType property is erroring out.

Attempted Fixes

I have tried forcing the charttype to xyScatter. The enums xlLinear = xlScaleLinear and xlLogarithmic = xlScaleLogarithmic. Placement of the Axis.ScaleType = xlScaleLogarithmic has an effect, but fails when SeriesCollection.Count = 0.

Any suggestions? A bit of googling shows that this is a relatively common problem.

Option Explicit
Private Type xySeries
    Name As String
    X As Range
    Y As Range
    Markers As XlMarkerStyle
    Axis As XlAxisGroup
    Color As XlColorIndex
    Weight As Single
End Type

Private Type xyAxis
    NumberFormat As String
    ScaleType As XlScaleType
    Minimum As Double
    Maximum As Double
End Type

'Pass this function the chartsheet name, and and array of data for the series of custom xySeries type
Private Sub ChartData(sCSName As String, asPlots() As xySeries, XAxis As xyAxis, YAxis() As xyAxis)
    Dim cs As Chart, oSeries As Series, iPlot As Integer
    Dim sPlot As String, sAlias As String, iAlias As Integer
    'Dim rgTime As Range, rgValues As Range
    
    Set cs = ThisWorkbook.Charts(sCSName)
    
    For Each oSeries In cs.SeriesCollection
        oSeries.Delete
    Next
    
    cs.PlotVisibleOnly = False
    cs.ChartType = xlXYScatterLines
    
    
    With ThisWorkbook.Sheets("LimeData")
        
        
        For iPlot = 1 To UBound(asPlots, 1)
            
            cs.SeriesCollection.NewSeries
            cs.SeriesCollection(iPlot).ChartType = xlXYScatterLines
            cs.SeriesCollection(iPlot).Name = asPlots(iPlot).Name
            cs.SeriesCollection(iPlot).XValues = asPlots(iPlot).X
            cs.SeriesCollection(iPlot).values = asPlots(iPlot).Y
            cs.SeriesCollection(iPlot).MarkerStyle = asPlots(iPlot).Markers
            cs.SeriesCollection(iPlot).AxisGroup = asPlots(iPlot).Axis
            cs.SeriesCollection(iPlot).Format.Line.Weight = asPlots(iPlot).Weight
            
        Next iPlot
        
    End With
    
    With cs
        With .Axes(xlCategory)
            .MinimumScale = RangeNominalExtreme(asPlots(1).X, 0)
            .MaximumScale = RangeNominalExtreme(asPlots(1).X, 1)
            .TickLabels.NumberFormat = XAxis.NumberFormat

'Error occurs on the next line
            .ScaleType = XAxis.ScaleType 
'Run-Time Error '-2147467259' (80004002):
'Method 'ScaleType' of 'Axis' failed

        End With
        .Axes(xlValue, xlPrimary).ScaleType = YAxis(1).ScaleType
        .Axes(xlValue, xlSecondary).ScaleType = YAxis(2).ScaleType
    End With
End Sub

Upvotes: 2

Views: 4190

Answers (4)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15551

The problem is most likely due to the presence of a secondary Y-axis (guessed from your line .Axes(xlValue, xlSecondary).ScaleType = YAxis(2).ScaleType). If you can spare it, you could probably go ahead.

See Excel VBA chart axis error: "Method 'ScaleType' of object 'Axis' failed" when reading `.ScaleType`.

Upvotes: 1

user3108276
user3108276

Reputation: 11

Check out:

SetElement(msoElementPrimaryCategoryAxisLogScale)

http://msdn.microsoft.com/en-us/library/office/ff864118.aspx

Upvotes: 1

Jon Peltier
Jon Peltier

Reputation: 6053

When .SeriesCollection.Count = 0, the only chart element there is, is the chart area. No plot area, no series, no axes, nothing. So you can't apply the axis formatting to an axis until there is data in the chart.

Upvotes: 0

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

What I see in ScaleType property help is that it 'Applies only to the value axis.'. But you set your axis to xlCategory instead of xlValue. I can't check it without data you have but this could be possible answer to your problem.

Upvotes: 0

Related Questions