Xy Chart In Vba, Order Of X And Y

  • I need to create many XY charts in a VBA macro. For each chart, I need to select a particular column for the X dimension, and a particular column for the Y dimension. When I use a named range to define the X and Y columns, my macro works fine for those instances when the X comes first. However, if my desired Y dimension comes first, I cannot find a way to correctly select these.


    The context is such that my spreadsheet always has the same data fields in the same columns, but the numbers of rows vary. In addition, I need to dynamically select which rows to use, based on the values in the columns to be plotted. I’ve figured out how to do this within the macro, except for those instances when the Y column comes before the X.


    An example when this works:



    In the above example, variable ‘FirstBias’ is computed previously in the macro.


    I realize that I can rearrange columns in my spreadsheet to avoid this, but that is much less convenient, and less elegant, and I think there must be a way for a macro to do this automatically. I can do it interactively by cutting and pasting the data ranges in the chart wizard dialog, but I need this to be automated. I also figured out how to automate it in the case where the X and Y columns have fixed rows, by adding the commands below:


    Code
    Set xRange = Range("=$M13:$M50 ")
        Set yRange = Range("=$K13:$K50")
        ActiveChart.SeriesCollection(1).XValues = "=Raw!xRange"
        ActiveChart.SeriesCollection(1).Values = "=Raw!yRange"


    When I try to put the dynamic range definitions into the above, I get my prescribed order overruled. Is there some way I can keep dynamic dimensioning of the ranges and switch the order of X & Y?


    Thanks in advance

  • Re: Xy Chart In Vba, Order Of X And Y


    DennisN,


    I didn't see anyone else answer so I'll just put a question out there for you. When you say the order of x and y columns do you mean that the y column is defined before the x column in the macro or do you mean that the letter of the y column is alphabetically prior to the x column?


    Jim

  • Re: Xy Chart In Vba, Order Of X And Y


    The column to be used as the Y (vertical) axis exists to the left of the one to be used for the X (horizontal), e.g., column B is for Y, column D is X. I've found that XY charts make an assumption that the X comes first, and I have to override that in some way.

  • Re: Xy Chart In Vba, Order Of X And Y


    Thanks much. This works, but I don't think I will use it, because it adds several functions just for the one purpose of swapping axes. It's a matter of how much code I want in my macro's -- for me, it's past the trade-off point of function vs. complexity. Unless someone has a simpler way to do this, I think I will just transform the spreadsheet to allow me to use the XY-charts in a straightforward way.


    I think it's too bad that VBA does not yet have a simpler approach to doing this. IMHO, this is a leftover legacy of Excel's business-application-only beginnings, as with the x-axis being assumed to be a category, and the y-axis as a measurement/value. As Excel came to be used more for engineering and scientific problems, functionality was added to make general XY-charts and other very useful charts. Much of that is done very well, with lots of powerful techniques, but it seems that there are still a few holes. Just my opinion.

  • Re: Xy Chart In Vba, Order Of X And Y


    Sorry I missed this thread.


    Does this not work for you?
    [vba]Sub ChartXY()


    Dim objCht As ChartObject
    Dim xRange As Range
    Dim yRange As Range

    Set objCht = ActiveSheet.ChartObjects(1)

    Set xRange = Range("$M13:$M50")
    Set yRange = Range("$K13:$K50")
    With objCht.Chart
    .SeriesCollection(1).XValues = xRange
    .SeriesCollection(1).Values = yRange

    End With
    End Sub
    [/vba]


    If not can you post an example workbook

    [h4]Cheers
    Andy
    [/h4]

  • Re: Xy Chart In Vba, Order Of X And Y


    Aha!! Thanks very much. That works, simply and elegantly.


    Actually I need to define the ranges dynamically, but I do that as follows:


    Code
    Set xRange = Range("=$M13" & ":" & "$M" & FirstBias - 5)
        Set yRange = Range("=$K13" & ":" & "$K" & FirstBias - 5)


    and then the seriesCollection assignments can be made in the 'with' block.

  • Re: Xy Chart In Vba, Order Of X And Y


    Hi,


    You might also want to look into dynamic named ranges -- they grow as more rows of data are populated. This would allow you to use the code to switch the ranges using named ranges (cleaner looking and more extensible in the end).


    Hope this helps.


    Jon

Participate now!

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