Setting Properties Of Upbars And Downbars

tomsoyer · Apr 9th 2007

Hi,

Does anyone know if there is a way to check whether a stock chart has both up bars and down bars?

Here is the problem I have: I have serveral charts that use the xlStockOHLC chart type to display stock data. The Excel default color for down bars is black, and white for up bars. I don't like the default colors, so I tried to change the bars' color via VBA:

Code

with oChart.ChartGroups(1)
.UpBars.Interior.ColorIndex=10
.DownBars.Interior.ColorIndex=3
end with

This code works only when a chart has both up and down bars. In cases where a chart is filled with all down bars (and zero up bars), or vice versa, the code causes an error (cannot set interior colorindex). Apparently, if one tries to access or set any property or access any method of the UpBars object when there are no up bars, Excel throws a runtime error. It seems that Excel still creates the UpBars object even if there are no up bars, but any access to properties and methods of the UpBars object causes an error. This means I need to check if a chart has up bars before setting the interior property of the UpBars object. But I couldn't figure out how to do this without going back and check the original data. Does anyone know if there is a quick way of checking whether a chart has both up bars and down bars?

Thanks,

Tom

**Andy Pope** · Apr 9th 2007

Re: Setting Properties Of Upbars And Downbars

There is a property you can test.
[vba]
if .HasUpDownBars = True then
[/vba]

But this returns true if any point in the series has an Up or Down.
So if the bars are all Up the Down formatting will fail.

I think error trapping is the only way around checking actual data.

Setting Properties Of Upbars And Downbars

Participate now!

Share