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
--
Posted via a free Usenet account from
http://www.teranews.com