VBA中堆叠的条形图

Question

我是VBA的新手，几乎没有编码经验，因此，任何帮助都将不胜感激。

我正在尝试在VBA创建一个堆叠的条形图。本质上，我的C列中有M/DD/Yyyy格式的发现日期，并且我的H列中有错误类型（即人类，制造等）。我需要在X轴上使用一个月和Y轴上的错误数创建一个堆叠的条形图。然后，带有每种类型的错误的条形杆将其分解为颜色。

我对如何在VBA中实现这一目标感到非常迷失。我的总体想法是在循环中进行某种反击。我不确定语法，只是如何实现这一图表。任何帮助将不胜感激。太感谢了。

编辑： 这是我到目前为止的代码。试图计算特定错误的实例，但我不知道我将如何计算每月某些错误的实例。

Sub Interim_Tracker()

    'Declares discovery date as range
    Dim DiscoveryDate As Range
    Dim RootCause As Range
    
    'Declares errors as singles
    Dim HumanError As Single
    Dim EquipmentError As Single
    Dim MaterialError As Single
    Dim Method_ProcedureError As Single
    Dim EnvironmentError As Single
    Dim UnknownError As Single
    
    'Declares criteria as strings
    Dim CriteriaValueHumanError As String
    Dim CriteriaValueEquipmentError As String
    Dim CriteriaValueMaterialError As String
    Dim CriteriaValueMethod_ProcedureError As String
    Dim CriteriaValueEnvironmentError As String
    Dim CriteriaValueUnknownError As String
    
    'Sets discovery date range to appropriate column
    Set DiscoveryDate = Worksheets("Macros Test Sheet").Range("C2:C1000")
    Set RootCause = Worksheets("Macros Test Sheet").Range("H2:H1000")
    
    
    'Sets criteria values
    CriteriaValueHumanError = "Human"
    CriteriaValueEquipmentError = "Equipment"
    CriteriaValueMaterialError = "Material"
    CriteriaValueMethod_ProcedureError = "Method/Procedure"
    CriteriaValueEnvironmentError = "Environment"
    CriteriaValueUnknownError = "Unknown"
    
    'Counts occurences of errors in column
    HumanError = WorksheetFunction.CountIf(RootCause, CriteriaValueHumanError)
    EquipmentError = WorksheetFunction.CountIf(RootCause, CriteriaValueEquipmentError)
    MaterialError = WorksheetFunction.CountIf(RootCause, CriteriaValueMaterialError)
    Method_ProcedureError = WorksheetFunction.CountIf(RootCause, CriteriaValueMethod_ProcedureError)
    EnvironementError = WorksheetFunction.CountIf(RootCause, CriteriaValueEnvironmentError)
    UnknownError = WorksheetFunction.CountIf(RootCause, CriteriaValueUnknownError)
    
    
End Sub

我知道这是什么接近完成的，但是当我运行它时，当我将范围设置为C或H列时，我会从范围错误中获得下标。

这是我试图产生此输出的图表：

Answer 1

我建议将所有数据放在隐藏的工作表/刮擦页面上，以形成一个具有第一列类别的表，而第一个名称现在是第一个。

然后按以下方式调用代码，

Option Explicit

Sub Macro1()

    Dim n As Long, m As Long
    ' n = num of categories
    ' m = num of series
    n = 4
    m = 3

    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    Dim r As Range
    Set r = ws.Range("A3")
    Set r = r.Resize(n + 1, m + 1)
    
    Dim s As Shape
    Set s = ws.Shapes.AddChart2(-1, xlColumnStacked)
    s.Chart.SetSourceData Source:=r
End Sub

中列出数据的示例

这是有关如何从a3