In message , Martin Brown
writes
In message , Jon Peltier
writes
In a very simple line chart with one series having one negative value, when
I manually set the scale to logarithmic, I got the error the first time, but
not subsequent times. It's as if Excel remembered it had already bothered me
about this. Despite the warning, the axis was changed. When I created a new
chart (which I had not been warned about) and used the VBA code, I got the
error; after clearing the error, the axis was changed. However, you want to
avoid the error. I tried EnableEvents:

Sub LogScale()
Application.DisplayAlerts = False
ActiveChart.Axes(xlValue).ScaleType = xlLogarithmic
Application.DisplayAlerts = True
End Sub

but it did not prevent the message. I don't know what to suggest, but I'm
waiting to see what others may have to say.

I was using Excel 2007 SP1, as if it matters.

At least with SP1 applied it manages to plot the log axis correctly for
values above 10^7 (it goes wrong in the out of the box version). As far
as I can tell nothing will prevent the first warning about log of
negative or zero any more. In earlier versions blank cells did not
elicit this warning.

Also I have found that under some circumstances with a log axis just
*reading* Axes( ).MaximumScale in VBA can also cause the error message
to occur again. Nothing will mask it any more :(

Seems like a spurious redraw is occurring sometimes (which may explain
why performance is so glacial).

In XL2003 blank cells were tolerated in log plots without error (and
behave numerically as zero in all other respects) which made for a
simple fix for plotting pulse counting data from scientific instruments
in Excel.

It also fails to show all the legends correctly when a large number of
data lines are plotted in the initial graph and you cannot adjust this
safely without adding a huge delay in the VBA code (race condition ?).
Code that steps through OK in the debugger will *NOT* run at full speed.

"Newsx" wrote in message
...

I'm trying to put together a piece of code in Excel, to be called by an
external application to regenerate graphs.

The offending code is this:

ActiveChart.PlotVisibleOnly = True
ActiveChart.DisplayBlanksAs = xlNotPlotted
ActiveChart.Axes(xlValue).ScaleType = xlLogarithmic


The process fails when trying to set the scaletype to logarithmic,
claiming that negative or zero values cannot be plotted.

It doesn't actually fail. The resulting graph line plotted is still
what you want, it is just that there is absolutely no way to get rid of
the stupid error message. They have "tidied" it up so that previous
workarounds now fail.

And as for the Axes dialogue now in XL2007 it is a complete abortion
with every parameter you want to manually customise having a button to
press to ungrey the relevant box. Someone at MickeySoft needs to be
eliminated for inflicting this ugly useless and pathetic offering on
the world.

It is also no longer modal and returns immediately claiming success
even though the user can still alter the settings (although only by
clicking the log button can you cause the screen display to update
again in my copy).

There are no negative or zero values in the data series but there are a
number of blank cells. This works perfectly in Excel 2003 but Excel 2007
throws errors.

This is really doing my head in, so any pointers would be much
appreciated!

Abandon XL2007 for this job and use XL2003 which is faster and produces
graphs that by default do not look like the result of a hamfisted 3
year old playing with a thick wax crayon. I am distinctly unimpressed
with XL2007 YMMV

Regards,
--
Martin Brown


Thanks Martin, unfortunately I don't have a choice over 2007. It's a
royal PITA to deal with this, but I think I've managed to tinker with it
so that it doesn't do any of the hugely unacceptable error-processing
(like crashing when I dared to click an axis title)

Sorry for the delay replying, my news reader lost the thread and it took
me several goes to get it back :-( At least that one was a user
configuration error...


--
Confidence is what you have when you don't understand the situation