Excel – Using VBScript to change background color in Excel chart

chartscolorsexcelvbavbscript

I'm using VBScript to create a line scatter plot from columns of data in Excel 2003. It comes out fine, but I want to edit some of the properties of the chart, such as background color and axis labels. I did this manually in Excel and recorded a macro, which gave me the following VBA code:

ActiveChart.PlotArea.Select
With Selection.Border
    .ColorIndex = 16
    .Weight = xlThin
    .LineStyle = xlContinuous
End With
With Selection.Interior
    .ColorIndex = 2
    .PatternColorIndex = 1
    .Pattern = xlSolid
End With
ActiveChart.Axes(xlCategory).Select
With Selection.TickLabels
    .ReadingOrder = xlContext
    .Orientation = 45
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .ReadingOrder = xlContext
    .Orientation = xlHorizontal
End With
ActiveChart.ChartArea.Select
End Sub

This looks fine for VBA, but I'm having trouble converting it to VBScript. How should I get started? This is my code at the moment:

Set objChart = objExcel.Charts.Add()
With objExcel.ActiveChart
    .ChartType = xlXYScatterLinesNoMarkers
    .SeriesCollection(1).Interior.Color = RGB(255, 0, 0)
    .HasTitle = True
    .ChartTitle.Text = "usage"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "units"
    .HasLegend = False
    .SetSourceData objWorksheet.Range("E1","F" & LastRow), xlColumns
    .SetSourceData objWorksheet.Range("E1:F200"), xlColumns
End With

The line .SeriesCollection(1).Interior.Color = RGB(255, 0, 0) causes an error: "Unable to set the color property of the Interior class". I'm guessing I shouldn't call .SeriesCollection right under .Activechart. Any suggestions? At this point I'd just be happy to be able to change the background color of the chart to white, and rotate the x-axis labels 45 degrees.

Thank you in advance.

Best Answer

Order matters. Before you can access a series, you have to add it first. Same goes for other properties, like HasTitle. Also, the lines of an XY diagram don't have an interior color, that's only available in charts that show an interior (like pie charts or column charts). You probably mean the border color here. Change your code to this:

Set objChart = objExcel.Charts.Add
With objChart
  ' define chart type
  .ChartType = xlXYScatterLinesNoMarkers

  ' define data
  .SetSourceData objWorksheet.Range("E1:F200"), xlColumns

  ' format chart
  .SeriesCollection(1).Border.Color = RGB(255, 0, 0)
  .PlotArea.Interior.Color = RGB(255, 255, 255)
  '.PlotArea.Interior.ColorIndex = 2  'alternative
  .HasTitle = True
  .ChartTitle.Text = "usage"
  .Axes(xlCategory, xlPrimary).HasTitle = True
  .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
  .Axes(xlValue, xlPrimary).HasTitle = True
  .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "units"
  .HasLegend = False
End With

and it should work, provided you have defined the symbolic constants.

Related Topic