Setting Properties Of Upbars And Downbars

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

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

    with oChart.ChartGroups(1)
    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?



  • Re: Setting Properties Of Upbars And Downbars

    There is a property you can test.
    if .HasUpDownBars = True then

    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!