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]
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
step1: connect VPN step2: connect to your VPN server by SSH with a teraterm teminal software. step3: select setup and SSH Forwarding. step4: click add and set forward local port(e.g. 13389), to remote machne(I address) and port(3389), then OK step5:remote desktop connection wtth localhost:13389