Excel 2007, log chart issue

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!


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

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
_______



"Newsx" <newsmaster@maelstrom.demon.co.uk> wrote in message
news:n5hCCZDtCZ2HFAwe@maelstrom.demon.co.uk...
>
> 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!
>
>
> --
> Confidence is what you have when you don't understand the situation

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.



In message <#SV1tnXhIHA.5368@TK2MSFTNGP04.phx.gbl>, Jon Peltier
<jonxlmvpNO@SPAMpeltiertech.com> 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.
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>Tutorials and Custom Solutions
>Peltier Technical Services, Inc. - http://PeltierTech.com
>_______
>
>
>
>"Newsx" <newsmaster@maelstrom.demon.co.uk> wrote in message
>news:n5hCCZDtCZ2HFAwe@maelstrom.demon.co.uk...
>>
>> 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!
>>
>>
>> --
>> Confidence is what you have when you don't understand the situation
>
>

--
======================================================================
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Newsx" <newsmaster@maelstrom.demon.co.uk> wrote in message
news:iTwjYtGoai2HFACj@maelstrom.demon.co.uk...
>
> 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.
>
>
>
> In message <#SV1tnXhIHA.5368@TK2MSFTNGP04.phx.gbl>, Jon Peltier
> <jonxlmvpNO@SPAMpeltiertech.com> 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.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Tutorials and Custom Solutions
>>Peltier Technical Services, Inc. - http://PeltierTech.com
>>_______
>>
>>
>>
>>"Newsx" <newsmaster@maelstrom.demon.co.uk> wrote in message
>>news:n5hCCZDtCZ2HFAwe@maelstrom.demon.co.uk...
>>>
>>> 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!
>>>
>>>
>>> --
>>> Confidence is what you have when you don't understand the situation
>>
>>
>
> --
> ======================================================================
> 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

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.




In message <#QwuupchIHA.4076@TK2MSFTNGP05.phx.gbl>, Jon Peltier
<jonxlmvpNO@SPAMpeltiertech.com> writes
>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
>_______
>
>
>"Newsx" <newsmaster@maelstrom.demon.co.uk> wrote in message
>news:iTwjYtGoai2HFACj@maelstrom.demon.co.uk...
>>
>> 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.
>>
>>
>>
>> In message <#SV1tnXhIHA.5368@TK2MSFTNGP04.phx.gbl>, Jon Peltier
>> <jonxlmvpNO@SPAMpeltiertech.com> 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.
>>>
>>>- Jon
>>>-------
>>>Jon Peltier, Microsoft Excel MVP
>>>Tutorials and Custom Solutions
>>>Peltier Technical Services, Inc. - http://PeltierTech.com
>>>_______
>>>
>>>
>>>
>>>"Newsx" <newsmaster@maelstrom.demon.co.uk> wrote in message
>>>news:n5hCCZDtCZ2HFAwe@maelstrom.demon.co.uk...
>>>>
>>>> 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!
>>>>
>>>>
>>>> --
>>>> Confidence is what you have when you don't understand the situation
>>>
>>>
>>
>> --
>> ======================================================================
>> 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
>
>

--
======================================================================
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

In message <#SV1tnXhIHA.5368@TK2MSFTNGP04.phx.gbl>, Jon Peltier
<jonxlmvpNO@SPAMpeltiertech.com> 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" <newsmaster@maelstrom.demon.co.uk> wrote in message
>news:n5hCCZDtCZ2HFAwe@maelstrom.demon.co.uk...
>>
>> 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

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.


In message <eD3ssYBzbD3HFA8a@maelstrom.demon.co.uk>, Newsx
<newsmaster@maelstrom.demon.co.uk> writes
>
>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.
>
>
>
>
>In message <#QwuupchIHA.4076@TK2MSFTNGP05.phx.gbl>, Jon Peltier
><jonxlmvpNO@SPAMpeltiertech.com> writes
>>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
>>_______
>>
>>
>>"Newsx" <newsmaster@maelstrom.demon.co.uk> wrote in message
>>news:iTwjYtGoai2HFACj@maelstrom.demon.co.uk...
>>>
>>> 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.
>>>
>>>
>>>
>>> In message <#SV1tnXhIHA.5368@TK2MSFTNGP04.phx.gbl>, Jon Peltier
>>> <jonxlmvpNO@SPAMpeltiertech.com> 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.
>>>>
>>>>- Jon
>>>>-------
>>>>Jon Peltier, Microsoft Excel MVP
>>>>Tutorials and Custom Solutions
>>>>Peltier Technical Services, Inc. - http://PeltierTech.com
>>>>_______
>>>>
>>>>
>>>>
>>>>"Newsx" <newsmaster@maelstrom.demon.co.uk> wrote in message
>>>>news:n5hCCZDtCZ2HFAwe@maelstrom.demon.co.uk...
>>>>>
>>>>> 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!
>>>>>
>>>>>
>>>>> --
>>>>> Confidence is what you have when you don't understand the situation
>>>>
>>>>
>>>
>>> --
>>> ======================================================================
>>> 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
>>
>>
>

--
======================================================================
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"Newsx" <newsmaster@maelstrom.demon.co.uk> wrote in message
news:n5hCCZDtCZ2HFAwe@maelstrom.demon.co.uk...
>
> 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!
>
>
> --
> Confidence is what you have when you don't understand the situation

In message <QUfK3DCiWP3HFwtc@nospam.demon.co.uk>, Martin Brown
<|||newspam|||@nezumi.demon.co.uk> writes
>In message <#SV1tnXhIHA.5368@TK2MSFTNGP04.phx.gbl>, Jon Peltier
><jonxlmvpNO@SPAMpeltiertech.com> 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" <newsmaster@maelstrom.demon.co.uk> wrote in message
>>news:n5hCCZDtCZ2HFAwe@maelstrom.demon.co.uk...
>>>
>>> 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

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.

In message <uGJdPDKiIHA.4844@TK2MSFTNGP06.phx.gbl>, Jon Peltier
<jonxlmvpNO@SPAMpeltiertech.com> writes
>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
>-------
>Jon Peltier, Microsoft Excel MVP
>Tutorials and Custom Solutions
>Peltier Technical Services, Inc. - http://PeltierTech.com
>_______
>
>
>
>"Newsx" <newsmaster@maelstrom.demon.co.uk> wrote in message
>news:n5hCCZDtCZ2HFAwe@maelstrom.demon.co.uk...
>>
>> 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!
>>
>>
>> --
>> Confidence is what you have when you don't understand the situation
>
>

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