选择和检索数据点 - 嵌入图表-VBA [英] Selecting and Retrieving data point - Embedded Charts -VBA

问题描述

我有一张表（Table1），我用来在同一张表 - 嵌入式图表中创建一个散点图（图表标题）。我想选择一个数据点，并检索是值。我发现以下在线。

  Dim myClassModule（）As New EventClassModule 
 
 Sub InitializeChart（）
如果ActiveSheet。 ChartObjects.Count> 0然后
 ReDim myClassModule（1到ActiveSheet.ChartObjects.Count）
 
 Dim chtObj As ChartObject 
 Dim chtnum As Integer 
 
对于每个chtObj在ActiveSheet .ChartObjects 
 chtnum = chtnum + 1 
设置myClassModule（chtnum）.myChartClass = chtObj.Chart 
下一个
如果
结束Sub 
 
 Sub ResetCharts（）
 Dim chtnum As Integer 
 
对于chtnum = 1到UBound（myClassModule）
设置myClassModule（chtnum）.myChartClass = Nothing 
下一个
 End Sub 
  

这在课程模块中

$ b $公开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

如果ElementID = xlSeries或ElementID = xlDataLabel Then
如果Arg2> 0然后
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

但以下行：

 code> Dim myClassModule（）As New EventClassModule 
  

导致错误：

已使用的定义类型未定义&

解决方案

/ div>

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

在正常模块（任何名称）：

  Option Explicit 
 
 Dim myClassModule（）As New EventClassModule 
 
 Sub InitializeChart（）
如果ActiveSheet.ChartObjects。计数> 0然后
 ReDim myClassModule（1到ActiveSheet.ChartObjects.Count）
 
 Dim chtObj As ChartObject 
 Dim chtnum As Long'Integer 
 
对于每个chtObj在ActiveSheet.ChartObjects 
 chtnum = chtnum + 1 
设置myClassModule（chtnum）.myChartClass = chtObj.Chart 
下一个
如果
结束Sub 
 
 Sub ResetCharts（）
'Dim chtnum As Long'Integer 
 
如果不是myClassModule没有，那么
'对于chtnum = 1 To ubound（myClassModule）
 'Set myClassModule（chtnum）.myChartClass = Nothing 
'Next 
删除myClassModule 
如果
 
 End Sub 
 
 
 
子条目（）
 Dim i& 
 Dim j& 
对于i = 1至10 
对于j = 1至10 
单元格（i，j）= 100 * Rnd 
下一步j 
下一步i 
 
 ActiveSheet.Shapes.AddChart2（286，xl3DColumn）.Select 
 ActiveChart.SetSourceData Source：= Range（Sheet1！$ A $ 1：$ J $ 10）
 
 End Sub 
  

和一个类模块（名为EventClassModule）：$ /

  Option Explicit 
 
 
公开WithEvents myChartClass As Chart 
 
 
 
私人子Class_Terminate（）
设置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 
 
如果ElementID = xlSeries或ElementID = xlDataLabel Then 
如果Arg2> 0然后
 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 
  

I have a table(Table1) which I have used to create a Scatter diagram("Chart Title") in the same sheet - Embedded chart. I want to select a data point and retrieve is values. I found the following online.

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

This goes in the class module

  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 

But the following line:

Dim myClassModule() As New EventClassModule

is causing an error:

Used - defined type not defined & Cannot perform requested operation

Any ideas why?

解决方案

using your code , i got this:

in the normal module (any name):

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

and in a class module (named 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

这篇关于选择和检索数据点 - 嵌入图表-VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！