How do I add new chart series automatically in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I may have several dozen or more (even hundreds or thousands) series to add
to a microsoft Excel x-y scatter chart. Each series only consists of two
data points and represent a straight line connected to pre-defined nodes in a
complex 3-D geometric figure. When plotted, the geometric 3-D figure is
revealed, and can be rotated to different viewing angles. Even though Excel
Chart is not specifically intended for this type display, it works very well.
The only problem is automating the new series as the coordinates for each
new line are input. Presently I have to do add each new series individually
- or pre-define the series with dummy data. However, doing hundreds or
thousands of lines this way is daunting.
 
Hi, I have not understood your task very well.
Anyway, I'm trying to answer it.
Did you try a named range (say, DATA) for series, like
DATA = (OFFSET...)...?
Is it what you want???

If yes - give me your mail and I'll send you a file ;
 
Yes, I use named ranges to input some of the data (the node coordinates) as a
single series (no connecting lines), and then an add-in labeler to for their
label assignments. But each line segment must be an independent series as
its assignment to particular nodes is arbitrary. Jon Peltier also has a VBA
example on how to input a group of series all at once. But what I would like
to do is add each new line series to the chart as it was defined. This is
because input error is more easily discovered and corrected this way.
 
I think the way I'd approach this is to have four columns, X1, X2, Y1, Y2. The user
enters the next row of data, and presses a button to add the next series with these
values. Instead of a button, you could use the Worksheet_Change event to detect when
a new row is populated and add a new series. If these points need labels, then you
could have two more columns, Name1, Name2. The event procedure would add these
labels to the points.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon,
I was hoping you would respond. I have visited your site and find it
extensive and most helpful. I have the input data (x1,x2, and y1,y2) in
columns already, as well as the label for the line, so all the input data for
a series in on the same row. Where I falter is in generalizing the recorded
macro so it is not specific to the recorded row. I can create a macro by
recording my keystrokes, but then my mind goes blank, and I can't seem to
then make it general. It seems I would need someway for the macro to use the
current row number, and I'm not sure how to proceed from there in changing
the specific absolute cell locations to one where the cell reference is a set
column, but a variable or current row . I am new to the Visual Basic Code
and although you and others have many excellant examples, I am still
struggling to generalize the syntax. Any specific coding would be greatly
appeciated.
Regards,
Cymonm
 
Write a procedure to take a row of data and process it into the chart. Don't specify
a row number, but use a variable lRow to signify the row number. Finally, insert
this variable as an argument to the procedure:

Sub AddRowToChart(lRow As Long)
'...
' your code goes here
'...
End Sub

Now switch to the sheet that contains the data. Right click on the sheet tab, and
select View Code. This opens a special code module that is attached specifically to
the sheet.

There are two pull downs at the top of the module. In the left one, select
Worksheet. This puts the frame of an event procedure in the module, for
Worksheet_SelectionChange. We don't want this one, so you can delete it or ignore
it. In the right dropdown, select Change. Now you get the event procedure that we need:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

What this is, is a procedure that will run every time the sheet changes (almost
every time, but definitely every time a user enters a new value). When it runs, the
procedure is passed Target, the range that experienced the change.

Suppose your five columns of data are in columns A:E. You want to detect a change in
these columns, and send the row of the change to the first procedure. You also might
not want to add a row to the sheet if it's already been added. For this purpose,
I'll use the easy approach of putting a 1 into column F. So we will not process a
row that has 1 in column F. If you construct your added series and labels
appropriately, they'll update without needing the event procedure.

Pseudo code for the event procedure:

bail out if changed cell isn't in A:E
bail out if column F in the row of the changed cell contains 1
bail out if A:D don't all have data (too soon to process)

Here's a cut at the procedure (watch for newsreader line wraps):

Private Sub Worksheet_Change(ByVal Target As Range)
'bail out if changed cell isn't in A:E
If Not Intersect(Target, Range("A:E")) Is Nothing Then
'bail out if column F in the row of the changed cell contains 1
If Cells(Target.Row, "F") <> 1 Then
'bail out if A:D don't all have data (too soon to process)
If Application.CountA(Cells(Target.Row, "A").Resize(1, 5)) = 5 Then
AddRowToChart Target.Row
Cells(Target.Row, "F").Value = 1
End If
End If
End If
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon,
Wow, that's a lot of code. OK, thanks. I'll be working on implementing
your guidance - probably for the next couple of weeks. Surely I'm not the
only simpleton wanting this solution. Seems like Microsoft, or somebidy
really clever, like yourself, would have created an "add nerw series" plot
function that could be used in an if statement. Oh, well, who am I to
complain. Thanks again for pointing me in the right direction and providing
the code to get me there.
Best regards,
Cymon
 
Back
Top