XL2007 - Axis Scaling Dialog problem

[Martin Brown]

I have a spreadsheet that uses the following sequence to scale the y
axis of an X-Y scatter graph with lines drawn between points from VBA
and it works perfectly in all previous versions of Excel. It fails
with XL2007 rather catastrophically. The main spreadsheet was imported
from XL2003.

I have looked in the KB and archives here and not found any other
reports of this problem so I don't know if it is something peculiar
about this particular installation or what. It is reproducible in a
toy chart in pure XL2007.

The failing lines are;

ActiveChart.Axes(xlSecondary).Select
ok = Application.Dialogs(xlDialogScale).Show

The initial error is 1004 - Application defined or object defined
error

Running it a second time gives Runtime Error "-2147467259"
Method 'select' of object 'Axis' failed
(this may be because the Axis is already selected)

Attempts to save the original file then result in XL falling over in a
big heap.

I have retested it with the canonical toy program with a 4 point X-Y
scatter chart and it fails the same way on the xlDialogScale, but it
does not give the weird secondary error for selecting the axis or
crash out soon after.

xlDialogScale has value 87 according to the XL2007 help system.

I can't think what else to try. Thanks for any enlightenment,
suggestions or work arounds. It seems a bit gross to have to define
your own dialogue for axis scaling because the built in one has become
horribly broken

Thanks,
Martin Brown

 

[Andy Pope]

Hi,

With a bit of trial and error seems the code should be

ActiveChart.Axes(xlSecondary).Select
ok = Application.Dialogs(504).Show

Although the help says 504 is xlDialogSeriesShape, Series Shape dialog box.

Cheers
Andy

 

[Martin Brown]

Hi,

With a bit of trial and error seems the code should be

ActiveChart.Axes(xlSecondary).Select
ok = Application.Dialogs(504).Show

Although the help says 504 is xlDialogSeriesShape, Series Shape dialog box.

Thanks Andy for this. Did you really find it by trial and error? Or
were you trying to display a Series Shape dialogue?

It doesn't bode well for the future though. Sooner or later they have
to either make the help match the actuality (breaking existing
historical code) or change their code to match the specification and
help.

XL2007 is still pretty flakey. It is glacially slow charting moderate
amounts of data and the VBA implementation is buggy and inclined to
fall over if you breathe on it. It hasn't been this bad since early
release XL2k. I can't remember when XL2003 last crashed on me, whereas
2007 fails almost hourly.

The default settings in 2007 for scientific X-Y scatter graphs look
like they were drawn with a barge pole dipped in tar using non-
descript colours, and customising them in any way at all makes it run
even slower

And it is necessary to ring up and beg for the "hotfixes" that only
slightly ameliorate these various graphics problems with bulk data.
Has anyone come up with some effective go faster tricks?

(it is quite possible that doing certain things in exactly the right
order might help)

Regards,
Martin Brown

 

[Andy Pope]

Trial and error. I noticed that most of the chart related dialogs had
new codes, which is to be expected as the chart dialogs are new modaless
ones. So I did a quick loop to Show dialogs. Narrowed it down until I
got the one you wanted.

Documentation, charting and macro recorder have certainly suffered with
the apparent need to get 2007 out on time rather than late and working.

Cheers
Andy

 

[Jon Peltier]

Martin -

I have Excel 2007 installed on a backup laptop, and I use it more as a toy
than anything else, to help me answer questions here, mostly. I have but a
single client using 2007. For my own work, I saw no reason to upgrade beyond
2003, and the testing I've done in 2007 has convinced me that it's not a
stable, productive environment. Many of my clients have not even upgraded to
2003 yet. I've advised the ones who have asked to stop at 2003, at least
until they can have everything important tested in 2007.

- Jon