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:
' X-Y chart, Field10 vs. Field12 Sheets("Raw").Select Names.Add Name:="newRange", RefersTo:="=$K13" & ":" & "$K" & FirstBias _ & ",$M13" & ":" & "$M" & FirstBias, Visible:=True Range("K1").Activate Charts.Add ActiveChart.ChartType = xlXYScatterLines ActiveChart.SetSourceData Source:=Sheets("Raw").Range("newRange"), PlotBy:=xlColumns ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Field10vsField12" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Field10 vs. Field12" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Field10" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Field12" End With ActiveChart.HasLegend = False
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:
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