Setting Properties Of Upbars And Downbars

  • 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

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!