vba - 基于数据标签文本的颜色数据点 - Excel VBA

Question

我有下面的代码，它根据数据标签文本为气泡图数据点着色。我不确定为什么会出现“无效参数错误”

为更清晰起见进行了编辑。

代码循环遍历一个电子表格，我在其中存储了数据标签过滤器标准(见附图)。它将复制预制的气泡图并为其着色。变量 f 在变量 a 和 c 之间循环，并且基于这两个变量之间的值，如果匹配，气泡图将着色。如果没有，它会越过它。气泡着色后，它会继续进行下一个着色变体。

Sub Slide31()
Dim rngx As Range
Dim rngy As Range
Dim rngz As Range
Dim ws3 As Worksheet
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim icnt As Long
Dim lastrow As Long
Dim k As Long
Dim icounter As Long
Dim a As Long
Dim c As Long
Dim b As Long
Dim d As Variant
Dim Chart As ChartObject
Dim PPapp As Object
Dim PPTDoc As PowerPoint.Presentation
Dim PPT As PowerPoint.Application
Dim PPpres As Object
Dim pptSlide As PowerPoint.Slide
Dim ppslide As Object
Dim e As Long
Dim f As Long
Dim filename As String
Dim filename2 As String
Dim x As Variant
Dim y As Variant
Dim z As Variant

Dim ch As Chart
Dim s As Series
Dim iPoint As Long
Dim nPoint As Long


Set ws = Worksheets("Reference")
Set ws1 = Worksheets("Bubbles")
Set ws2 = Worksheets("Slide 31")
Set ws3 = Worksheets("Bubble Reference")

ws2.Activate

'ws2.Range("h:h").NumberFormat = "0.00%"

lastrow = ws2.Cells(Rows.Count, "b").End(xlUp).Row
For icounter = 1 To lastrow
For icnt = 51 To 79
If ws2.Cells(icounter, 2) = ws.Cells(icnt, 3) Then
d = ws.Cells(icnt, 3)
a = icounter + 2
b = icounter + 2
c = icounter + 11

filename = ""
filename2 = ""

ws3.ChartObjects(1).Copy
ws2.Paste

    Set ch = ActiveChart
Set s = ch.SeriesCollection(1)

For f = a To c
nPoint = s.Points.Count
For iPoint = 1 To nPoint



    If ws2.Cells(f, 8) = s.Points(iPoint).DataLabel.Text Then
        s.Points(iPoint).Format.Fill.ForeColor.RGB = RGB(192, 0, 0)

    End If
  Next iPoint

 Next f

End If
Next icnt
Next icounter

Answer 1

Point 对象没有 Interior 属性。 (编辑:是的，即使 Dox 和 Intellisense 似乎没有暴露它，它实际上也是如此)。

( Point object reference | Excel Reference )

您收到的特定错误(1004，“无效参数错误”)类似于索引越界，您以某种方式尝试以无效方式索引 Points 集合，但我不确定这是怎么回事是可能的。例如，如果您尝试 s.Points(0) 或 s.Points(s.Points.Count+1)，则很容易出现此错误。

您可以尝试这种替代方法:

Dim pt as Point

For Each pt in s.Points
    If ws2.Cells(f, 8) = pt.DataLabel.Text Then
        pt.Format.Fill.ForeColor.RGB = RGB(192, 0, 0)
    End If
Next