Excel 2007, log chart issue

  • Thread starter Thread starter Newsx
  • Start date Start date
N

Newsx

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.

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!
 
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.

- Jon
 
Thanks for that Jon, I tried the application.displayalerts trick and it
kind of worked, much like you mentioned.

The trouble is even if I leave displayalerts off all the time (which
causes its own issues) if I then do anything to the chart at all
(including moving an axis title etc) I get the error.

Since this is for a user base that isn't very technically aware I can't
really afford to have strange messages like that popping up :-(

It's odd in that Excel 2003 handles everything just fine, but Excel 2007
just doesn't want to deal with blank values.



Jon Peltier said:
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.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
Click to expand...

--
======================================================================
To reply by email remove the word master from before the @
Any email sent to reply-to address will be automatically rejected
Any email sent may be quoted in full or in part in relevant newsgroups
======================================================================
Confidence is what you have when you don't understand the situation
 
I was surprised that 2003 didn't give me thee message at all, even in manual
mode. I seem to recall being ticked at getting the message a hundred times
when one plot had lots of negative numbers.

Are the cells really blank, or do they contain formulas? if they are
formulas, maybe returning some value besides a simulated blank might help.
For other purposes, we use NA() to condition the result of a formula for
being left out of a chart. If the cells are or may be blank, link another
range to them, and likewise condition the blanks, then plot from this second
range. And before you say anything about having two ranges, think how nice
it would be if this stopped the messages.

- Jon
 
Thanks for the thoughts Jon.

The cells are definitely blank, and I've managed to kludge to a point
where the errors are suppressed during generating it (the Excel workbook
is created from an external application).

Then I hit a problem where if I modified the chart in any way at all
(including selecting a chart title), I got the same "negative values"
error and Excel crashed. This has to be a bug in Excel, it's inexcusable
:-)

But I also noticed that if I changed a value in the underlying dataset I
got the same error, and then Excel was happy. It even let me move things
in the chart without crashing.

So the best solution to date is to use the Workbook_Open function to
change a value, then change it back. This creates the annoying message
when the workbook is first opened but does prevent Excel from crashing.

I'll see if the NA approach does any better, although since it's failing
to read blanks I'm wondering if we're fundamentally up against Excel
bugs rather than code quirks. Another range is easy enough to produce,
I'll just make it white on white and tuck it out of the way.




Jon Peltier said:
I was surprised that 2003 didn't give me thee message at all, even in manual
mode. I seem to recall being ticked at getting the message a hundred times
when one plot had lots of negative numbers.

Are the cells really blank, or do they contain formulas? if they are
formulas, maybe returning some value besides a simulated blank might help.
For other purposes, we use NA() to condition the result of a formula for
being left out of a chart. If the cells are or may be blank, link another
range to them, and likewise condition the blanks, then plot from this second
range. And before you say anything about having two ranges, think how nice
it would be if this stopped the messages.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
Click to expand...

--
======================================================================
To reply by email remove the word master from before the @
Any email sent to reply-to address will be automatically rejected
Any email sent may be quoted in full or in part in relevant newsgroups
======================================================================
Confidence is what you have when you don't understand the situation
 
Jon Peltier said:
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.
Click to expand...

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.
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).
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,
 
I tried changing the blank values to =NA() and still get the same error.

I must admit I'm starting to wonder whether this is a bug in Excel, 2003
seems to work fine with blank cells but 2007 does not.


Newsx said:
Thanks for the thoughts Jon.

The cells are definitely blank, and I've managed to kludge to a point
where the errors are suppressed during generating it (the Excel
workbook is created from an external application).

Then I hit a problem where if I modified the chart in any way at all
(including selecting a chart title), I got the same "negative values"
error and Excel crashed. This has to be a bug in Excel, it's
inexcusable :-)

But I also noticed that if I changed a value in the underlying dataset
I got the same error, and then Excel was happy. It even let me move
things in the chart without crashing.

So the best solution to date is to use the Workbook_Open function to
change a value, then change it back. This creates the annoying message
when the workbook is first opened but does prevent Excel from crashing.

I'll see if the NA approach does any better, although since it's
failing to read blanks I'm wondering if we're fundamentally up against
Excel bugs rather than code quirks. Another range is easy enough to
produce, I'll just make it white on white and tuck it out of the way.
Click to expand...

--
======================================================================
To reply by email remove the word master from before the @
Any email sent to reply-to address will be automatically rejected
Any email sent may be quoted in full or in part in relevant newsgroups
======================================================================
Confidence is what you have when you don't understand the situation
 
I just played around a bit. Blank cells do not kick off this issue, they are
ignored. Negative cells, zeros, and cells containing text raise the warning,
but only once per chart. Your "blank" cell is probably a formula that
returns "", which is text. What I found more unsettling than this message is
that text cells and zeros are plotted as a marker with a value equal to the
log axis minimum; however, a quick check shows that this display of text and
zero at the log axis minimum been corrected in Excel 2007 SP1.

DisplayBlanksAs only affects truly blank cells, not "".

- Jon
 
Martin Brown said:
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.

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).

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,
Click to expand...

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...
 
Thanks Jon, I also did some fiddling with it and found it got weirder
and weirder. Almost as weird as how I misconfigured my news reader to
lose this thread, hence the delay reposting :-(

I create a series of X-Y points where X went from 1 to about 20 and Y
went 1,10,100,1000,10000, 100,100,10,1,10,100 etc

Then I plotted the data on a chart. A nice series of bumps appeared, as
expected. When I changed the Y axis to be logarithmic I got the warning
message again, despite there being no cells at all with anything other
than perfectly valid data.

It seems to me that MS just put a warning message in to make sure
everybody knows (as if they didn't already) that you can't put negatives
on a log scale. It's a shame they didn't seem to provide a way to tell
it that I already knew that :-(

I've been trying to work this in Excel 2007 SP1.
 
Check your settings on each logarithmic scale. You will get the error message if the minimum, maximum or zero crossing values are zero or negative.

Now, when you get the dialog box, how do know which of several chart objects gave the error?
 
Back
Top