Hide Zero Errors in Log Graph

[dungyc]

Hi,

I am a regular visitor to your forums and have enjoyed and benefitted from all of your shared knowledge.

However, I have a problem that I cannot track down the solution to on previous threads. It involves a Log-Log graph pre-formatted, prior to the data table being populated, which when empty displays a zero value error message. How can I stop this?

Any help or insight would be great,

thanks[/img]

 

[vane0326]

This code will work for you.


Sub Clear_Zeros()

Range("B:B").Replace What:="0", Replacement:="", LookAt:=xlWhole


End Sub

 

[dungyc]

I apologise I didn't get my full meaning across.

The data used to plot the graph is the result of some quite complicated UDF's (in my simple Excel VBA experience). And therefore cannot be changed to "", as when they are initially zero or null, as it is used as an argument for another process.

All I want to do is suppress the following warning message flashing up:

"Negative or zero values cannot be plotted correctly on log charts. Only positive values can be interpreted on a logarithmic scale. To correct the problem, do one of the following:
i) Enter only positive values (greater than zero) in the cells to create the chart.
ii) In the chart, click the axis you want to change. On the format menu, click Selected Axis. Click the scale tab, and then clear the Logarithmic Scale check box."

I'm aware of both facts, but just need to suppress that message until the user has entered in the data to plot the chart.

 

[PA HS Teacher]

It sounds to me like somewhere in the UDF

There is a line along the lines of

If Value < 0 Then
msgbox(...."Negative or zero values cannot be plotted ...")
End If



If you look at the code for the UDF and comment that If statemen out by putting a '
in front of that If statement, perhaps the code will still run. (just make sure you put a ' in front of all lines from If to End IF.


But not seeing the code, I don't know if that will affect the functioning of your code in other ways.

 

[tusharm]

Have the UDF return #n/a! as in

Function myUDF()
    myUDF = CVErr(xlErrNA)
    End Function

I apologise I didn't get my full meaning across.

The data used to plot the graph is the result of some quite complicated UDF's (in my simple Excel VBA experience). And therefore cannot be changed to "", as when they are initially zero or null, as it is used as an argument for another process.

All I want to do is suppress the following warning message flashing up:

"Negative or zero values cannot be plotted correctly on log charts. Only positive values can be interpreted on a logarithmic scale. To correct the problem, do one of the following:
i) Enter only positive values (greater than zero) in the cells to create the chart.
ii) In the chart, click the axis you want to change. On the format menu, click Selected Axis. Click the scale tab, and then clear the Logarithmic Scale check box."

I'm aware of both facts, but just need to suppress that message until the user has entered in the data to plot the chart.

 

[dungyc]

Thanks All,

Just for information:

I used a combination from the comments made. I changed the output to #NA. however that still not stop the message appearing. I experimented and changed the chart x axis to linear in order to see if there was a specific problem associated to one axis. I changed it back and tried a log log scale with none of the aforementioned problems! who knows!