VBAで散布図のプロットが何に対応しているか表示

Excelの散布図で、「１．レコード（行）数が可変でも自動で散布図作成」、「２．特定
（先頭）のレコードのプロットを違う色で表示」、「３．プロットがどのレコードに対応
しているかを表示」を行う。

This article explains methods of [1. automatic build scatter chart independent f
rom record size (# of rows)], [2. display the plot of a specific record (top r
ow) with a different color], [3. Identification and indication about the record
of the selected plot]

data sheet
――――+―――――+―――――-+――――-+――――-+
=COUNTA($A$3:$A$10000) | |
|
――――+―――――+―――――-+――――-+――――-+
record | dat1 | dat2 | dat3 | dat4 |
――――+―――――+―――――-+――――+――――+
id1 | 1.18 | 2.11 | 0.97 | 1.63 |
id2 | 1.26 | 1.41 | 1.27 | 4.71 |
id3 | 1.24 | 2.52 | 1.38 | 2.26 |
id4 | 1.47 | 2.02 | 1.29 | 3.92 |
――――-+―――――+―――――-+―――――+―――+

scatter sheet ( two scatter charts)

１．レコード（行）数が可変でも自動で散布図作成

1. automatic build scatter chart independently from record size

a) register names columns by using CTL+F3 and OFFSET function
e.g. name: val1
ref. range : =OFFSET(data!$B$3,0,0,data!$A$1,1)
name: val2
ref.range : = OFFSET(data!$c$3,0,0,data!$A$1,1)

b) create scatter chart with data range settings like as following e.g.
x series value: =data!val1
y series value: =data!val2

２．特定（先頭）のレコードのプロットを違う色で表示

2. display the plot of top row with a different color

This method is simple.
Just add a data series with top record row , then overwrite the graph with this
graph.
e.g. series name: = “head_object”
x series value: =data!$b$3
y series value: =data!$c$3

３．プロットがどのレコードに対応しているかを表示

3. Identification and indication about the record of the selected plot

I implemented this functionality with VBA.
a) create a class module
e.g. chart_event
b) write code in the chart_event module.
e.g.

Option Explicit
Public WithEvents scatterchart As Chart
Private Sub scatterchart_Select(ByVal ElementID As Long, ByVal x As Long, ByVal
y As Long)
‘ I don’t know ,but getChartElement was not necessary
‘ x is series index, y is plot index
If (ElementID = xlSeries) And (y <> -1) Then MsgBox Worksheets(“Data”).Cells(2 +
y, 1).Value()
End Sub

c) write code in a standard module
e.g. (in this case : enableplot is a macro activated by a button)

Dim scattercharts(1) As New chart_event
‘ don’t forget “new”
Sub enableplot()
‘ Keep the order of “dim” senetense and “sub” sentense
‘ if “sub” ? “dim” order, events are not captured
Set scattercharts(0).scatterchart = Worksheets(“scatter_chart”).ChartObjects(1).
Chart
Set scattercharts(1).scatterchart = Worksheets(“scatter_chart”).ChartObjects(2).
Chart
End Sub

d) put a button as control on scatterchart sheet and bind this button to the “en
ableplot” sub procedure