vba 使用 VBScript 更改 Excel 图表中的背景颜色

Using VBScript to change background color in Excel chart

提问by vicjun

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:

我正在使用 VBScript 从 Excel 2003 中的数据列创建线散点图。结果很好，但我想编辑图表的一些属性，例如背景颜色和轴标签。我在 Excel 中手动执行此操作并记录了一个宏，它给了我以下 VBA 代码：

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:

这对于 VBA 来说看起来不错，但我无法将其转换为 VBScript。我应该如何开始？这是我目前的代码：

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.

行.SeriesCollection（1）.Interior.Color = RGB（255，0，0）导致错误： “无法设置内部类的颜色属性”。我猜我不应该在 .Activechart 下调用 .SeriesCollection。有什么建议？在这一点上，我很高兴能够将图表的背景颜色更改为白色，并将 x 轴标签旋转 45 度。

Thank you in advance.

先感谢您。

采纳答案by Ansgar Wiechers

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:

订单很重要。在访问系列之前，您必须先添加它。其他属性也是如此，例如HasTitle. 此外，XY 图的线条没有内部颜色，仅在显示内部的图表（如饼图或柱状图）中可用。您可能是指此处的边框颜色。将您的代码更改为：

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.

它应该可以工作，前提是您已经定义了符号常量。

回答by George

There are two things:

有两件事：

1. setting the plotting area to a certain color

1. 将绘图区域设置为某种颜色

see example

看例子

'set plotting area to dark gray color
ActiveChart.PlotArea.Select
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorBackground1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = -0.5
    .Solid
End With

mind.ForeColor.ObjectThemeColor = msoThemeColorBackground1

头脑.ForeColor.ObjectThemeColor = msoThemeColorBackground1

1. Setting the general area of the chart to a certain color (I mean the entire area of the chart , except of the plotting area)

   'set the color of the Chart Area to gray With ActiveSheet.Shapes("NameChart").Fill .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorBackground1 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = -0.5 .Solid End With

2. 将图表的一般区域设置为某种颜色（我的意思是图表的整个区域，绘图区域除外）

   '使用 ActiveSheet.Shapes("NameChart").Fill .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorBackground1 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = -0.5 .Solid End With 将图表区域的颜色设置为灰色

Where the name of the Chart has been set as "NameChart" earlier in the VBA code, when generating the Chart

在 VBA 代码中之前已将图表名称设置为“NameChart”的地方，在生成图表时

  'set the name of the Chart to "Cooper" to reference it later
ActiveChart.Parent.Name = "NameChart"

mind.ForeColor.ObjectThemeColor = msoThemeColorBackground1

头脑.ForeColor.ObjectThemeColor = msoThemeColorBackground1

If my answer helped you in any way, please rate it. Thanks.

如果我的回答对您有任何帮助，请评分。谢谢。

回答by Michael Mulvey

The only ways I know are using VBScript or Python. Below is a VBScript that may help you . You would need an if statement to make the Date red or not.

我知道的唯一方法是使用 VBScript 或 Python。下面是一个可以帮助你的 VBScript。您需要一个 if 语句来使日期变为红色。

I would use a formula to do this however and copy this to all the cells in the row you use for the date.

但是，我会使用一个公式来执行此操作，并将其复制到您用于该日期的行中的所有单元格中。

There is a link to the color index in the comments.

评论中有颜色索引的链接。

Save as ExcelDB.vbs

另存为 ExcelDB.vbs

' Validate Variables.
Dim fso, strUserName , oShell

' Create an instance of the Scripting Shell
Set oShell = CreateObject( "WScript.Shell" )

' Get the Username from The Shell enviroment
strUserName = oShell.ExpandEnvironmentStrings( "%USERNAME%" )

' Create a File System Object named "fso".
Set fso = CreateObject("Scripting.FileSystemObject")

' Create Instance of Excel Object
Set objExcel = CreateObject("Excel.Application")

' Hide Alert messages
objExcel.DisplayAlerts = False

' Make Excel File Visible/Hidden (True=Visible , False = Hidden)
objExcel.Visible = True 

' Create a Workbook with 3 sheets named Retired, Styles and Patts
Set objWorkbook = objExcel.Workbooks.Add
objExcel.Activeworkbook.Sheets(1).Name = "Retired"
objExcel.Activeworkbook.Sheets(2).Name = "Styles"
objExcel.Activeworkbook.Sheets(3).Name = "Patts"

' Freeze header row (The first row will remain during sorting)

With objExcel.ActiveWindow
     .SplitColumn = 0
     .SplitRow = 1
End With
objExcel.ActiveWindow.FreezePanes = True


' Define Names for lookup tables.
objExcel.ActiveWorkbook.Names.Add "patterns", "=patts!$A:$b0"
objExcel.ActiveWorkbook.Names.Add "styles", "=Styles!$A:$H0"
objExcel.ActiveWorkbook.Names.Add "widths", "=Styles!$B:$B1"

'Define Values for Cells

objExcel.Cells(1,1).Interior.ColorIndex = 3
objExcel.Cells(1,1).Value = "Red Background"                'Cell A1 Value
objExcel.Cells(2,1).Value = "GreenBorder"                   'Cell A2 Value
objExcel.Cells(2,2).Value = "1"
objExcel.Cells(2,3).Value = "1"
objExcel.Cells(2,4).Value = "1"
objExcel.Cells(2,5).Value = "12"
objExcel.Cells(2,6).Value = "12"
'Format Retired Cells

objExcel.Sheets("Retired").Cells(7, 2).Numberformat = "@"
objExcel.Worksheets("Retired").Cells(2,1).Borders.Color = RGB(0,255,0)
objExcel.Worksheets("Retired").Columns("A:A").Columnwidth = 20
objExcel.Worksheets("Retired").Columns("B:C").Columnwidth = 5
objExcel.Worksheets("Retired").Columns("D:D").Columnwidth = 15
objExcel.Worksheets("Retired").Columns("E:E").Columnwidth = 5
objExcel.Worksheets("Retired").Columns("F:H").columnwidth = 15
objExcel.Worksheets("Retired").Columns("I:I").Columnwidth = 5
objExcel.Worksheets("Retired").Columns("B").NumberFormat = "000"
objExcel.Worksheets("Retired").Columns("C").NumberFormat = "00"

'get a cell value and set it to a variable
row2Cell2 = objExcel.Cells(2,2).Value

'save the new excel file (make sure to change the location) 'xls for 2003 or earlier
objWorkbook.SaveAs "C:\Users\"&strUserName&"\Desktop\CreateExcel.xlsx" 

'close the workbook
'objWorkbook.Close 

'exit the excel program
'objExcel.Quit

The following line is how you change the color of an excel Cell where cell(1,1) = cell A1

以下行是如何更改 excel 单元格的颜色，其中单元格 (1,1) = 单元格 A1

objExcel.Cells(1,1).Interior.ColorIndex = 3