选择和检索数据点 - 嵌入式图表 - VBA

Question

我有一个表格(Table1(，我用它来在同一张纸中创建散点图("图表标题"( - 嵌入式图表。我想选择一个数据点并检索值。我在网上找到了以下内容。

Dim myClassModule() As New EventClassModule
Sub InitializeChart()
    If ActiveSheet.ChartObjects.Count > 0 Then
    ReDim myClassModule(1 To ActiveSheet.ChartObjects.Count)
    Dim chtObj As ChartObject
    Dim chtnum As Integer
    For Each chtObj In ActiveSheet.ChartObjects
        chtnum = chtnum + 1
        Set myClassModule(chtnum).myChartClass = chtObj.Chart
    Next
  End If
 End Sub
 Sub ResetCharts()
 Dim chtnum As Integer
 For chtnum = 1 To UBound(myClassModule)
    Set myClassModule(chtnum).myChartClass = Nothing
 Next
 End Sub

这在类模块中

  Public WithEvents myChartClass As Chart
  Private Sub myChartClass_Mousedown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
  Dim ElementID As Long, Arg1 As Long, Arg2 As Long
  Dim myX As Variant, myY As Double
  With ActiveChart
   .GetChartElement x, y, ElementID, Arg1, Arg2
  If ElementID = xlSeries Or ElementID = xlDataLabel Then
   If Arg2 > 0 Then
      myX = WorksheetFunction.Index _
   (.SeriesCollection(Arg1).XValues, Arg2)
   myY = WorksheetFunction.Index _
   (.SeriesCollection(Arg1).Values, Arg2)
   MsgBox (Arg1 & Chr(10) & Arg2)
   End If
  End If
 End With
 End Sub 

但以下行：

Dim myClassModule() As New EventClassModule

导致错误：

已使用 - 未定义类型且无法执行请求的操作

知道为什么吗？

Answer 1

使用您的代码，我得到了这个：

在普通模块(任何名称(中：

Option Explicit
Dim myClassModule() As New EventClassModule
Sub InitializeChart()
    If ActiveSheet.ChartObjects.Count > 0 Then
        ReDim myClassModule(1 To ActiveSheet.ChartObjects.Count)
        Dim chtObj As ChartObject
        Dim chtnum As Long 'Integer
        For Each chtObj In ActiveSheet.ChartObjects
            chtnum = chtnum + 1
            Set myClassModule(chtnum).myChartClass = chtObj.Chart
        Next
    End If
End Sub
 Sub ResetCharts()
 'Dim chtnum As Long 'Integer
 If Not myClassModule Is Nothing Then
 '   For chtnum = 1 To ubound(myClassModule)
 '       Set myClassModule(chtnum).myChartClass = Nothing
 '   Next  
 Erase myClassModule
 End If
 End Sub

Sub entry()
Dim i&
Dim j&
For i = 1 To 10
    For j = 1 To 10
        Cells(i, j) = 100 * Rnd
    Next j
Next i
ActiveSheet.Shapes.AddChart2(286, xl3DColumn).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$J$10")
End Sub

在类模块(名为 EventClassModule(中：

Option Explicit

Public WithEvents myChartClass As Chart

Private Sub Class_Terminate()
Set myChartClass = Nothing
End Sub

Private Sub myChartClass_Mousedown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Variant, myY As Double
With myChartClass 'ActiveChart
    .GetChartElement x, y, ElementID, Arg1, Arg2
    If ElementID = xlSeries Or ElementID = xlDataLabel Then
        If Arg2 > 0 Then
            myX = WorksheetFunction.Index(.SeriesCollection(Arg1).XValues, Arg2)
            myY = WorksheetFunction.Index(.SeriesCollection(Arg1).Values, Arg2)
            MsgBox (Arg1 & Chr(10) & Arg2)
        End If
    End If
End With
End Sub