Alright folks,

I'm trying to write some code that determines whether a chart axis has the
following properties:

minimumscale
maximumscale
majorunit
minorunit
minimumscaleisauto
maximumscaleisauto
minorunitisauto
majorunitisauto

Obviously not all chart types have these properties. The code I'm using to
try and get these values is:

varMinimumScale = ActiveChart.Axes(xlCategory,xlPrimary).MinimumScale

where varMinimumScale is a variant.

Clearly, if the chart is a bar chart, for example, this will crash (a 1004
error).

I've failed in trying to determine this association using If
ActiveChart.ChartType = xlWhatever because apparently my charts are of type
-4111 (they're all scatter plots with two y-axes and lines without markers).
I can't find any mention of numeric chart types in the helpfile or on the
net. Putting a break in the code and progressing step by step shows that
xlXYScatter also produces a number in the -4000 range, whereas other charts
have numbers more like 0-100.

Does anyone have a list of excel's numeric chart types or some code that
would do the job for me (by trapping the 1004 error or something clever)?

Cheers,

Matt

Hi,

You would have more luck with the charttype if you found out the
information from the series rather than the chart.

ActiveChart.SeriesCollection(1).charttype = xlWhatever

The other thing to do is use error handling. If you wrote some wrapper
functions to get each of the required properties you could handle the
error within the routine and return relevant information.

Cheers
Andy


Matt McQueen wrote:
> Alright folks,
>
> I'm trying to write some code that determines whether a chart axis has the
> following properties:
>
> minimumscale
> maximumscale
> majorunit
> minorunit
> minimumscaleisauto
> maximumscaleisauto
> minorunitisauto
> majorunitisauto
>
> Obviously not all chart types have these properties. The code I'm using to
> try and get these values is:
>
> varMinimumScale = ActiveChart.Axes(xlCategory,xlPrimary).MinimumScale
>
> where varMinimumScale is a variant.
>
> Clearly, if the chart is a bar chart, for example, this will crash (a 1004
> error).
>
> I've failed in trying to determine this association using If
> ActiveChart.ChartType = xlWhatever because apparently my charts are of type
> -4111 (they're all scatter plots with two y-axes and lines without markers).
> I can't find any mention of numeric chart types in the helpfile or on the
> net. Putting a break in the code and progressing step by step shows that
> xlXYScatter also produces a number in the -4000 range, whereas other charts
> have numbers more like 0-100.
>
> Does anyone have a list of excel's numeric chart types or some code that
> would do the job for me (by trapping the 1004 error or something clever)?
>
> Cheers,
>
> Matt

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Andy,

SeriesCollection(x).ChartType looks good, but I think there'd be problems
when you've got things like area plots, since they may or may not have the
values I was looking for (i.e. .ChartType may return 76, but this doesn't
mean there's a minimumscaleisauto value). I think this just indicates that
ChartType (for the chart or the series) is not going to provide what I want.

Writing some error handling code looks like the way to go, since it'll
always generate an error. I'd sort of given up on it when I read in the
help-file that a 1004 error was not a trappable one, but I'll read up on it
and give it a bash. Any tips?

Cheers,

Matt

OK, did some error trapping stuff to solve the issue.

Thanks for your help.

Matt