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

0
votes

I have an assignment in an Excel VBA macro

axscty = ax.ScaleType

which gives the error: "Method 'ScaleType' of object 'Axis' failed". I found sources stating that it is a bug in Excel 2007. Others found the error when setting .ScaleType, e.g.,

.ScaleType = xlLinear

I have found the cause of the error: I have a secondary Y-axis with data series. If I assign those series to the primary Y-axis and remove the secondary axis, the error disappears.

Anyone knows how to avoid the error when there is a secondary Y-axis?

1
vbaexcelcharts
How have you defined your variable "ax"? – Jon Peltier
@JonPeltier - I do not have the sample workbook with chart and code at this point (it's an old post, although still of interest to me). Most likely I did something like Dim ax as Axis, Set ax = myChart.Axes(xlValue). The only alternatives I see are: 1) Using xlCategory instead of xlValue, 2) Adding the second optional parameter AxisGroup, as xlPrimary or xlSecondary. Can you point out the effect this can have on an answer? – sancho.s ReinstateMonicaCellio
Maybe I used to know about this. It's obviously a bug, in 2007 through 2013. Apparently the ScaleType property is only recognized for Value (Y) axes, not Category (X) axes, even if that axis is a numerical value axis in an XY scatter chart. – Jon Peltier
@JonPeltier - the ScaleType property (both for getting and setting) is recognized for Category (X) axes, as long as there is no secondary Y axis (and I do not know what happens if there is a secondary X axis). – sancho.s ReinstateMonicaCellio
I just did a few tests in Excel 2016. ScaleType works for the primary X axis if there is no series on the secondary axis, even if the secondary axes have been deleted. Presumably this bug occurs in Excel 2007 through 2016. – Jon Peltier

1 Answers

1
votes

It looks like the following works (if you have any secondary axis or not):

ActiveChart.SetElement (msoElementPrimaryCategoryAxisLogScale)
ActiveChart.SetElement (msoElementPrimaryValueAxisLogScale)
ActiveChart.SetElement (msoElementSecondaryCategoryAxisLogScale)
ActiveChart.SetElement (msoElementSecondaryValueAxisLogScale)