Dynamic Charts

[pl.carry]

I have an array of DEM (Digital Elevation Map) data.
Rows are Latitudes, Columns Longitudes.
I have created a graph (Area) that plots the elevation across Longitudes
(Column) for a given range of Latitude (Lines). Nice graph!
I would like the plot to update automatically when I glide the cursor down
the first column (Latitude), i.e. when I move the cursor down the range.
Cursor movement would be by KBoard not mouse.
Thanks,
Pierre

 

[Jon Peltier]

Pierre -

I did this as a dynamic chart, with a worksheet event procedure to
update the ranges when the activecell changed.

I assumed the data was in a rectangular range (C5:I15), with a row of
category labels just above the data (C4:I4) and a column of series names
to the left (B5:B15). I put the temporary value of 10 in cell A1, then
I set up my range names. Ctrl-F3 opens the dialog.

Here are the names I used, and their definitions:

Name: TheRow
Refers To: =Sheet1!$A$1

Name: datarange
Refers To: =Sheet1!$C$5:$I$15

Name: TheX
Refers To: =Sheet1!$C$4:$I$4

Name: TheY
Refers To: =OFFSET(TheX,TheRow-ROW(TheX),0)

Name: TheName
Refers To: =OFFSET(TheX,TheRow-ROW(TheX),-1,1,1)

I built the chart by selecting a random blank cell and starting the
chart wizard. In Step 2 of the wizard, I clicked on the Series tab, and
clicked Add. In the Name box I entered =Sheet1!TheName, in the Values
box I entered =Sheet1!TheY, and in the Category Labels box I entered
=Sheet1!TheX.

Finally I added the Worksheet Change event that makes it work. I right
clicked the sheet tab and selected View Code, and in the macro window
that appeared I entered this macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim i As Long, j As Long
i = ActiveCell.Row
j = Range("datarange").Row
If i < j Then i = j
j = j + Range("datarange").Rows.Count - 1
If i > j Then i = j
Range("TheRow").Value = i
End Sub

The bits involving j lock the plotted range to the first or last row of
the data range even if the active cell is outside of that range.

Now whenever the active cell moves, the row of data containing the
active cell is plotted in my chart.

- Jon

 

[pl.carry]

Jon,
Thank you. I tried to work it out on my PC2(in French) whilst my PC1(US) is
under repair.
Alas, the word "OFFSET", etc. is naturally different in French and lacking
on the spot a function lexicon I must wait till I get the desktop back.
I knew I was going to be hit by this one day, but is is difficult to source
US software in France if not small enough to be downloadable, and the Soft
here came on the back of a machine purchased in France.
I am going to try and change the display language and revert later.
Pierre

 

[Jon Peltier]

All I can think of, is try l'offset.

- Jon

 

[Pierre]

Jon,
Worked on the first try,
I split the screen in two showing just TheY in Sheet1 (so that I could move
the cursor) and the rest of the screen is Graph1
Suberp, Tx, what a way to explore a map!
Pierre

Last step: I had envisioned to use not a line chart but a surface chart,
with, say, not 1 but 10 datalines taken at a time (5 nautical miles) so that
the plot would not be a cross section of the ground but a slab of ground 5
miles deep looking North (on the chart TheY are Latitudes) and across the
range of Longitudes (TheX). The reason to do that is that in a very chaotic
environment the near-peaks hide the remote plains. By moving a slice at a
time the panorama unfolds in full view. I did it manually and it helps
apprehend the surface very well, but moving the range through the wizard is
a pain.

Is that in the realm of feasability?

I tried to extend that from yr PrivateSub, but I can see that one cannot toy
with VisualBasic as one toyed with interpreted Basic to spiral onto
something which worked in the end.

Thanks again,

Pierre

 

[Jon Peltier]

Pierre -

Different approach, calling for different names. Assuming the data is
in the same place:

Name: datarange
Refers To: =Sheet1!$C$5:$I$15
(or larger, I think)

Name: TheRow
Refers To: =Sheet1!$A$1

Name: TheTop
Refers To: =Sheet1!$B$4:$I$4

Name: TheData
Refers To: =OFFSET(TheTop,TheRow,0,10,8)
(You want 10 datalines, or rows, and B to I is 8 columns)

Name: TheSource
Refers To: =TheTop,TheData

Now the macro updates A1, and also changes the source data for the chart:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim i As Long, j As Long
i = ActiveCell.Row
j = Range("datarange").Row
If i < j Then i = j
j = j + Range("datarange").Rows.Count - 1
If i > j Then i = j
Range("TheRow").Value = i
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=Sheets("Sheet1").Range("theSource"), _
PlotBy:=xlColumns
End Sub

- Jon

 

[Jon Peltier]

Change one line:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim i As Long, j As Long
i = ActiveCell.Row
j = Range("datarange").Row
If i < j Then i = j
j = j + Range("datarange").Rows.Count - 1
If i > j Then i = j
'' change to this:
Range("TheRow").Value = 1 + i - Range("datarange").Row
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=Sheets("Sheet1").Range("theSource"), _
PlotBy:=xlColumns
End Sub

- Jon

 

[Pierre]

Jon,

I get "RunTime 1004; unable to get the ChartObjects property of the
Worksheet class".
The debugging points to : ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=Sheets("Sheet1").Range("theSource"), _
PlotBy:=xlColumns

Also in the previous example I added one series with the names TheName,
TheX, TheY which is what I did again since I suspect these references are
needed, even if only implicitly and if unused will not hamper.

In this instance I need to get 10 series. I am confused on whether I need to
enter only one and the macro will launch the next 9 (but I get the error
msg that for a surface map I need at least 2),or I need to enter each of the
9 others. But when I tried this I got 10 times the same line, rather
naturally, unless I can stick an increment in the Datasource line (but if I
do that I get an error, and I suspect this what the macro does thanks to
OFFSET.)

The line plotted corresponds to that just above where the cursor is on
Sheet1, and it updates OK each time I move the cursor on Sheet1; but there
is still this error msg 1004 and of course I get my two duplicate lines.

I have checked the syntax, and there is nothing amiss I can see.

Where should I look to find the culprit?

Tx,

Pierre

 

[Jon Peltier]

Pierre -

I did test this before posting it (in a workbook that I've since
deleted, wish I'd saved it). Is there a chart on the active sheet? I
used a mixture of ActiveSheet and Sheets("Sheet1") to refer to the same
worksheet. If you have a chart sheet, you need to use

ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").Range("theSource"), _
PlotBy:=xlColumns

if the chart sheet is active, or

ActiveWorkbook.Charts(1).SetSourceData _
Source:=Sheets("Sheet1").Range("theSource"), _
PlotBy:=xlColumns

This technique obviates the need to define ten different ranges for ten
series values, although you could do it this way readily enough. If you
are adding the series one-by-one, you need to start with a different
chart type, and change it to a surface chart after there are at least
two series. But if you're only changing the ranges defined for each
series, it shouldn't matter.

- Jon

 

[Pierre]

I used the >>>>>> and got an immediate result that looks close to OK)
(I got an error msg of "maximum number of data series per chart is 255"
I willl look, at the info displayed on the map to find out where this comes
from. Probably some range misstated.)
Tx,
Pierre