vba - Excel VBA xlMarkerStyle 不绘图

Question

以下 Excel VBA 模块旨在根据条件更改各种线段的颜色。它工作得很好，只是它不会绘制单独的线标记。该问题显然存在于 xlMarkerStyleCircle、MarketSize、MarkerBackgroundColor 和 MarkerForegroundColor 行中。我不确定问题是否与对象命名不当或对象引用排序不当有关。任何帮助或建议将不胜感激。同样，如果有人看到更有效的方法来编码相同的目标，请随时分享。

谢谢...

干杯，约翰

<小时/>

  Sub tropical_cyclone_track_format()

  With ActiveSheet
Set r = .Range("E24:E31")
For i = 1 To .Shapes("CHART 3").Chart.SeriesCollection(1).Points.Count - 1

  If r(i) = "1" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerStyle = xlMarkerStyleCircle
  If r(i) = "1" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerSize = 2
  If r(i) = "1" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerBackgroundColor = RGB(0, 0, 0)
  If r(i) = "1" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerForegroundColor = RGB(0, 0, 0)
  If r(i) = "1" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Border.LineStyle = xlContinuous
  If r(i) = "1" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Border.Color = RGB(255, 255, 64)
  If r(i) = "1" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Format.Line.Transparency = 0
  If r(i) = "1" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Format.Line.Weight = 1

  If r(i) = "2" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerStyle = xlMarkerStyleCircle
  If r(i) = "2" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerSize = 2
  If r(i) = "2" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerBackgroundColor = RGB(0, 0, 0)
  If r(i) = "2" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerForegroundColor = RGB(0, 0, 0)
  If r(i) = "2" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Border.LineStyle = xlContinuous
  If r(i) = "2" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Border.Color = RGB(255, 153, 16)
  If r(i) = "2" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Format.Line.Transparency = 0
  If r(i) = "2" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Format.Line.Weight = 1

  If r(i) = "3" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerStyle = xlMarkerStyleCircle
  If r(i) = "3" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerSize = 2
  If r(i) = "3" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerBackgroundColor = RGB(0, 0, 0)
  If r(i) = "3" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerForegroundColor = RGB(0, 0, 0)
  If r(i) = "3" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Border.LineStyle = xlContinuous
  If r(i) = "3" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Border.Color = RGB(255, 3, 0)
  If r(i) = "3" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Format.Line.Transparency = 0
  If r(i) = "3" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Format.Line.Weight = 1

  If r(i) = "4" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerStyle = xlMarkerStyleCircle
  If r(i) = "4" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerSize = 2
  If r(i) = "4" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerBackgroundColor = RGB(0, 0, 0)
  If r(i) = "4" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).MarkerForegroundColor = RGB(0, 0, 0)
  If r(i) = "4" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Border.LineStyle = xlContinuous
  If r(i) = "4" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Border.Color = RGB(80, 0, 0)
  If r(i) = "4" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Format.Line.Transparency = 0
  If r(i) = "4" Then .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Format.Line.Weight = 1

  Next i
  End With

End Sub

Answer 1

这是一个答案的一半，因为它解释了为什么您的代码不绘制单独的行标记，但未能为您提供正确的方法。如果您希望我仔细研究您的问题，您必须提供您的工作表，以便我可以评估您图表的详细信息。

另一方面，您的代码非常重复，因此效率低下。

我现在讨论这两个问题。

1. Point 对象没有 Border 方法。这就是原因为什么你的代码不起作用。这意味着您不能编写如下代码:

   .Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1).Border
   
   如果您想知道Point对象有哪些方法，请在VBE上按F2。如果你想了解更多关于对象和方法的信息可以尝试 analysistabs.com或dummies.com然后excel-spreadsheet.com 。再次强调，如果您分享您的工作表，我将能够为您提供帮助。

2. 您重复该代码 32 次
   
   .Shapes("CHART3").Chart.SeriesCollection(1).Points(i + 1)
   
   你可以通过声明 Point 类型的变量 p 并赋值来避免这种情况它的值就在 for 循环之后。我给你一个想法，你如何才能实现这一点，但为了让它正常工作，你应该分享你的工作表。
   
   

   Sub tropical_cyclone_track_format()
   
   Dim r As Range
   Set r = ActiveSheet.Range("E24:E31")
   
   Dim i As Integer
   For i = 1 To ActiveSheet.Shapes("CHART 3").Chart.SeriesCollection(1).Points.Count - 1
   
   Dim p As Point
   Set p = ActiveSheet.Shapes("CHART 3").Chart.SeriesCollection(1).Points(i + 1)
   
   If r(i) = "1" Then
       With p
   
           p.MarkerSize = xlMarkerStyleCircle
           p.MarkerStyle = 2
           p.MarkerBackgroundColor = RGB(0, 0, 0)
           p.MarkerForegroundColor = RGB(0, 0, 0)
           p.Format.Line.Transparency = 0
           p.Format.Line.Weight = 1
   
       End With
   End If
   
   If r(i) = "2" Then
       With p
   
           p.MarkerSize = xlMarkerStyleCircle
           p.MarkerStyle = 2
           p.MarkerBackgroundColor = RGB(0, 0, 0)
           p.MarkerForegroundColor = RGB(0, 0, 0)
           p.Format.Line.Transparency = 0
           p.Format.Line.Weight = 1
   
       End With
   End If
   
   If r(i) = "3" Then
       With p
   
           p.MarkerSize = xlMarkerStyleCircle
           p.MarkerStyle = 2
           p.MarkerBackgroundColor = RGB(0, 0, 0)
           p.MarkerForegroundColor = RGB(0, 0, 0)
           p.Format.Line.Transparency = 0
           p.Format.Line.Weight = 1
   
       End With
   End If
   
   If r(i) = "4" Then
       With p
   
           p.MarkerSize = xlMarkerStyleCircle
           p.MarkerStyle = 2
           p.MarkerBackgroundColor = RGB(0, 0, 0)
           p.MarkerForegroundColor = RGB(0, 0, 0)
           p.Format.Line.Transparency = 0
           p.Format.Line.Weight = 1
   
       End With
   End If
   Next i
   
   End Sub