VBA Macro for trendlines comparing two values

[Melanie_AUT]

Hello all of you!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I'm a complete novice at VBA and macros and quite at a loss at the moment... I hope you can help me with the following: I've been trying to create trendlines comparing two values (recent period and previous period) in a bubble chart.<o></o>
<o></o>
The excel-data available is the following (only part of the data):<o></o>
<o></o>
<TABLE class=MsoNormalTable style="WIDTH: 442pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm" cellSpacing=0 cellPadding=0 width=589 border=0><TBODY><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 0"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; BACKGROUND: gray; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 82pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt" width=109>Names<o></o>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 180pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: windowtext 1.0pt; mso-border-left-alt: windowtext 1.0pt; mso-border-bottom-alt: windowtext .5pt; mso-border-right-alt: black 1.0pt; mso-border-style-alt: solid" width=240 colSpan=3>RECENT period Jan-Apr 08<o></o>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 180pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid black 1.0pt" width=240 colSpan=3>PREVIOUS period Jul-Dez 07<o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt">area<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">x (Stk)<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">y (Eur)<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; BACKGROUND: yellow; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt">z (LZ)<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">x (Stk)<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">y (Eur)<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; BACKGROUND: yellow; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt">z (LZ)<o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt">7401 (W1)<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

5<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

1.797,20 <o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt">

7,00<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

5<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

2.250,40 <o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt">

7,60<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt">7402 (W2)<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

18<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

732,83 <o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt">

6,50<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

104<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

2.914,35 <o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt">

9,39<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt">7403 (W3)<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

16<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

1.831,75 <o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt">

5,56<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

55<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

2.149,75 <o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt">

5,69<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt">7404 (W4)<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

15<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

1.750,00 <o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt">

5,20<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

41<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">

759,20 <o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext 1.0pt">

2,59<o></o>​

</TD></TR></TBODY></TABLE>
<o></o>
The trendlines should compare the values for each area in the periods 2008 and 2007. I.e. area 7401 (W1): start line at previous period x=5/y=2250,40 and end line at recent period x=5/y=1797,20.<o></o>
<o></o>
So far I've succeeded in creating lines through the following macro-script:<o></o>
<o></o>
Sub test()
Dim ThisLine As Shape<o></o>
Set ThisLine = ActiveSheet.Shapes.AddLine(351, 240, 534, 341)<o></o>
With ThisLine.Line
.BeginArrowheadStyle = msoArrowheadOval
.EndArrowheadStyle = msoArrowheadOval
.BeginArrowheadWidth = msoArrowheadWidthMedium
.BeginArrowheadLength = msoArrowheadLengthMedium
.EndArrowheadWidth = msoArrowheadWidthMedium
.EndArrowheadLength = msoArrowheadLengthMedium
.Visible = msoTrue
End With<o></o>
End Sub<o></o>
My problems are the following:<o></o>
<o></o>
1) After entering the x and y values from my excel - AddLine(5, 5, 1797, 2.250) - a line is created but not according to the ranges of the bubble chart. I assumed the macro automatically refers to the x- and y-axis and chooses the starting- and ending-points for the lines from there. Obviously it does not.<o></o>
How can I set correct ranges for the lines?<o></o>
<o></o>
2) As you can see in my data-example above, several lines (for each area) need to be drawn.<o></o>
How would a macro for several lines have to look like?<o></o>
<o></o>
3) I am using a macro for labelling each "bubble" (datapoint) for the same chart. That one is working flawlessly and I'd hate to change anything there. (Especially with excel: “don’t fix what ain’t bust”)<o></o>
Do I need to combine both codes (that is: one for labelling and one for drawing lines) in ONE macro or may I use them seperately?<o></o>
<o></o>
I would be very glad if you could help me. I'm looking forward to your answers and proposals!<o></o>
<o></o>
Thanks in advance,<o></o>
best regards from <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:country-region><st1lace>Austria</st1lace></st1:country-region>, <o></o>
Melanie

 

[pgc01]

Hi Melanie
Welcome to the board

If I understood correctly, you have created an embedded chart in a worksheet and then drew a line on the same worksheet. I don't see, however, any connection between the chart and the line. As far as I can see they are just 2 objects in the same worksheet. They bear no relationship to each other.

As you know, the bubble chart doesn't allow for combinations charts, or else you'd use the usual solution for this type of problem: series type XY scatter to draw the trendlines.


2 possible options to solve your problem:

- to do what you are doing, lines and chart independent objects in a worksheet, just making sure you determine the exact location/lenght of the lines.

- to insert the lines INSIDE the chart. You still have to determine the exact location/lenght of the lines, but now since you are inside the chart you can move it around or copy it and the lines are part of the object. I must say that this is the way I prefer


This is a suggestion of a step by step procedure. The code should:

1 - delete all the lines in the chart. This makes no sense the first time you run the code, but makes all the sense afterwards, when you change the data or the size of the chartobject, or the scales in the axes, etc., and want to redraw the lines.

2 - determine the positions and sizes of the axes. Based on their scales determine conversion factors value to points. This will allow you to determine the values in points corresponding to the values XY

3 - get the XY coordinates of the points (values in your table)

4 - convert the XY coordinates VALUE in XY coordinates CHART POINTS. This will give you the start and end points of the trendlines in chart XY coordinates

5 - add the lines to the chart

6 - format the lines


Answer to your questions:

1 - How can I set correct ranges for the lines?
Already answered

2 - How would a macro for several lines have to look like?
If you store the coordinates of the points in arrays you can just loop through the coordinates of the corresponding pairs of points and you draw all the trendlines in one loop.

3 - Do I need to combine both codes (that is: one for labelling and one for drawing lines) in ONE macro or may I use them seperately?
Both ways are OK.

I think you have explained your problem very well, and so I believe that your difficulties will be related to the vba syntax and the knowledge of the excel object model and not in understanding the concepts involved. If you have problems implementing I can help you (or someone else will), but in that case you have to post some more information, such as:

1 - your excel version
2 - the name of the worksheet
3 - the name of the chart object
4 - how are the points arranged. What makes sense to me is that you have 2 series, the Recent and the previous. Using references relative to the table you posted, asuming it starts in A1, the first has the data in B3:D6, and the second with the data in E3:G6

Hope this helps

 

[Melanie_AUT]

Dear pgc01!

Thanks a lot for your answer!

If I understood correctly, you have created an embedded chart in a worksheet

The chart hasn't been embedded but was created on an extra worksheet. However, it wouldn't be a problem at all to work with embedded charts from now on!

I don't see, however, any connection between the chart and the line. [...] They bear no relationship to each other.

I'm not quite sure if there is any relationship in "computer-terms", but in general understanding they DO relate to each other.
What the trendlines should do: trace the movement/shift of a bubble in a chart when comparing one period with another. That is to say, the line shows where the bubble has been in the last period and where it is now. Therefore the ending point of each trendline is the centre (XY value recent period) of one bubble. The starting point of each trendline has been the centre of a bubble in the previous period (XY value previous period)...
Sounds confusing - I hope I got it across.

- to insert the lines INSIDE the chart.

Yes, THAT's exactly what I'd need...

I believe that your difficulties will be related to the vba syntax and the knowledge of the excel object model and not in understanding the concepts involved. If you have problems implementing I can help you

Thanks for the brilliant explanation of the procedure - it's now perfectly clear how to approach that problem. Still I'm afraid I'm not able at all to actually WRITE that macro script myself. For the time being I'm quite proud of being able to copy-paste the scripts and getting a correct result
So I'd most gladly accept your offer for help and post some more information!

1 - your excel version
MS Excel XP [2002 SR3] EN

2 - the name of the worksheet
depends - but if you'd replace the name in the macro by WORKSHEETNAME I'd manage to adjust that according to my need afterwards.

3 - the name of the chart object
I didn't even know charts had a name that would be relevant for macros... Is this the heading you chose while creating the chart? However, if you'd replace that one by CHARTOBJECTNAME and could explain where this is set, I'm confident that I'll be able to cope with that as well.

4 - how are the points arranged
to simplify matters:


Thanks again for your great help, I'm looking forward a lot to your proposals!
Best regards,
Melanie

PS: in the excel the PREVIOUS period would of course be Jul-Dez 07 (and not 08...).

 

[pgc01]

Hi again

The code I post should be easy to follow, since it sticks to the step by step model I posted before.

This is the test I did:

- opened an empty workbook
- copied the data from your first post
- created a bubble chart with 2 series, one for the Recent period and another for the Previous period
- in the vbe inserted a general module (module1) and pasted the code
- ran the code

It worked OK. Please test it like this the first time.

Remarks:

1 - The code assumes that the chart has 2 series with the exact same number of points

2 - Assigning the Chart variable

There are 2 cases when assigning the chart variable at the beginning of the Sub.

i) the chart is embedded in a worksheet
I used:

Set cht = Worksheets("Sheet1").ChartObjects(1).Chart

This will work if you only have 1 chart embedded in the worksheet or if it's the first one created in that worksheet.
I always prefer to name the chart objects. To do it in xl 2000-2003, ctrl-click the chart and the chart name will appear in the name box (the box to the left of the formula bar, above column A). You should change the chart name to something meaningful and then use:

Set cht = Worksheets("Sheet1").ChartObjects("SomeName").Chart

ii) the chart is a chart sheet
Use instead something like, using the sheet's name:

Set cht = Charts("Jul-Dec 07,  Jan-Apr 08")

3 - The code works for both the Bubble chart and the Scatter (XY) chart

4 - Since you have xl 2003 I tested the code in xl2000 (I don't have access to xl 2003). To use it in xl 2007 you may have to change the code.

5 - I used the values from your first post in this thread. Please do not post images. They are useless if you want to use any information from them as the information cannot be extracted. Also if someone reads this thread in 1 or 2 years the link will be probably dead, which makes this thread crippled.

To post data use a html tool like
Colo's: http://www.mrexcel.com/forum/showthread.php?t=89356
Excel Jeanie: http://www.excel-jeanie-html.de/index.php?f=1
or simply post in text mode, comma separated.

' PGC Jun 08
' TrendlinesTwoSeries - connects each point of the first series with the corresponding point in the
' second series with a straight line
' Assumes both series have the same number of points. For Bubble and Scatter charts.
' Tested in xl 2000. May have to be changed to work in xl 2007.
'
Sub TrendlinesTwoSeries()
Dim cht As Chart, shp As Shape
Dim lXLeft As Long, dXMinimumScale As Double, dXValuePoints As Double                  ' X axis data
Dim lYTop As Long, lYHeight As Long, dYMinimumScale As Double, dYValuePoints As Double ' Y axis data
Dim lLineBeginX As Long, lLineBeginY As Long, lLineEndX As Long, lLineEndY As Long     ' trendline data
Dim vXValues(1 To 2) As Variant, vYValues(1 To 2) As Variant                                 ' temporary coordinates values
Dim lPoint As Long, vPoint As Variant                                                  ' loop variables
 
Set cht = Worksheets("Sheet1").ChartObjects(1).Chart   ' if it's a chart embedded in a worksheet
'Set cht = Charts("Jul-Dec 07,  Jan-Apr 08")           ' if it's a chart sheet
 
With cht
 
    ' delete the Line shapes, in case of a redraw
    For Each shp In .Shapes
        If shp.Type = msoLine Then shp.Delete
    Next shp
 
    ' get the data from the axes
    With .Axes(Type:=xlCategory)
        lXLeft = .Left
        dXMinimumScale = .MinimumScale
        dXValuePoints = .Width / (.MaximumScale - .MinimumScale)
    End With
 
    With .Axes(Type:=xlValue)
        lYTop = .Top
        lYHeight = .Height
        dYMinimumScale = .MinimumScale
        dYValuePoints = .Height / (.MaximumScale - .MinimumScale)
    End With
 
    ' Get the X and Y values
    vXValues(1) = .SeriesCollection(1).XValues
    vYValues(1) = .SeriesCollection(1).Values
    vXValues(2) = .SeriesCollection(2).XValues
    vYValues(2) = .SeriesCollection(2).Values
    
    ' Draw the lines
    For lPoint = 1 To .SeriesCollection(1).Points.Count
    
        ' determine start and end point
        lLineBeginX = lXLeft + (vXValues(1)(lPoint) - dXMinimumScale) * dXValuePoints
        lLineBeginY = lYTop + lYHeight - (vYValues(1)(lPoint) - dYMinimumScale) * dYValuePoints
        lLineEndX = lXLeft + (vXValues(2)(lPoint) - dXMinimumScale) * dXValuePoints
        lLineEndY = lYTop + lYHeight - (vYValues(2)(lPoint) - dYMinimumScale) * dYValuePoints
    
        ' add and format the line
        Set shp = .Shapes.AddLine(lLineBeginX, lLineBeginY, lLineEndX, lLineEndY)
        With shp.Line
            .ForeColor.RGB = RGB(0, 255, 0)
            .Weight = 3
            .BeginArrowheadStyle = msoArrowheadOval
            .EndArrowheadStyle = msoArrowheadOval
            .BeginArrowheadWidth = msoArrowheadWidthMedium
            .BeginArrowheadLength = msoArrowheadLengthMedium
            .EndArrowheadWidth = msoArrowheadWidthMedium
            .EndArrowheadLength = msoArrowheadLengthMedium
            .Visible = msoTrue
        End With
    Next lPoint
End With
End Sub

 

[Melanie_AUT]

Dear PGC!

Thanks to your excellent support my charts eventually include trendlines! THANKS A LOT! Without your help I would have never succeeded in implementing the macro...

Only one question left:

I need to create a new module for each chart - is that correct? Or is there a possibility to insert various lines in the macro as long as I'm referring to each chart/each worksheet?

(A macro for more than one chart would only be the cherry on top - I'm more than content without the cherry )

Thanks again,
best regards,
Melanie

 

[pgc01]

I'm glad this solved your problem (until now, anyway)!

If you have several charts distributed by several worksheets, you can make a list of what charts you want the macro to change, and then loop through the list.

I changed the code to loop through a list in which each chart is specified by the name of the worksheet and the name of the chartobject.

Remarks:

- I only use in the names of the chartobjects: letter, digit, underscore, space

- you can also use the chartobject index, instead of the chartobject name. In that case, remember that the index reflects the order of creation of the chart in the worksheet, ex.:

vWshChtObjs = VBA.Array( _
    VBA.Array("Report 08", 2), _
    VBA.Array("Report 08", 3), _
    VBA.Array("Report 07", 1), _
    VBA.Array("Report 06", 1) _
)

Please try:

' PGC Jun 08
' TrendlinesTwoSeries - connects each point of the first series with the corresponding point in the
' second series with a straight line
' Assumes both series have the same number of points. For Bubble and Scatter charts.
' Tested in xl 2000. May have to be changed to work in xl 2007.
'
Sub TrendlinesTwoSeries()
Dim cht As Chart, shp As Shape
Dim lXLeft As Long, dXMinimumScale As Double, dXValuePoints As Double                  ' X axis data
Dim lYTop As Long, lYHeight As Long, dYMinimumScale As Double, dYValuePoints As Double ' Y axis data
Dim lLineBeginX As Long, lLineBeginY As Long, lLineEndX As Long, lLineEndY As Long     ' trendline data
Dim vXValues(1 To 2) As Variant, vYValues(1 To 2) As Variant                                 ' temporary coordinates values
Dim lPoint As Long, vPoint As Variant                                                  ' loop variables
Dim vWshChtObjs As Variant, vWshChtObj As Variant
 
vWshChtObjs = VBA.Array( _
    VBA.Array("Report 08", "Chart Jul_Dec_07__Jan_Apr_08"), _
    VBA.Array("Report 08", "Chart Jan_Mar 08__Mar_May_08"), _
    VBA.Array("Report 07", "Chart Jan_Jun 07__Jul_Dec_07"), _
    VBA.Array("Report 06", "Chart Jan_Jun 06__Jul_Dec_06") _
)
 
For Each vWshChtObj In vWshChtObjs
 
    Set cht = Worksheets(vWshChtObj(0)).ChartObjects(vWshChtObj(1)).Chart
 
    With cht
 
        ' delete the Line shapes, in case of a redraw
        For Each shp In .Shapes
            If shp.Type = msoLine Then shp.Delete
        Next shp
 
        ' get the data from the axes
        With .Axes(Type:=xlCategory)
            lXLeft = .Left
            dXMinimumScale = .MinimumScale
            dXValuePoints = .Width / (.MaximumScale - .MinimumScale)
        End With
 
        With .Axes(Type:=xlValue)
            lYTop = .Top
            lYHeight = .Height
            dYMinimumScale = .MinimumScale
            dYValuePoints = .Height / (.MaximumScale - .MinimumScale)
        End With
 
        ' Get the X and Y values
        vXValues(1) = .SeriesCollection(1).XValues
        vYValues(1) = .SeriesCollection(1).Values
        vXValues(2) = .SeriesCollection(2).XValues
        vYValues(2) = .SeriesCollection(2).Values
 
 
        ' Draw the lines
        For lPoint = 1 To .SeriesCollection(1).Points.Count
 
            ' determine start and end point
            lLineBeginX = lXLeft + (vXValues(1)(lPoint) - dXMinimumScale) * dXValuePoints
            lLineBeginY = lYTop + lYHeight - (vYValues(1)(lPoint) - dYMinimumScale) * dYValuePoints
            lLineEndX = lXLeft + (vXValues(2)(lPoint) - dXMinimumScale) * dXValuePoints
            lLineEndY = lYTop + lYHeight - (vYValues(2)(lPoint) - dYMinimumScale) * dYValuePoints
 
            ' add and format the line
            Set shp = .Shapes.AddLine(lLineBeginX, lLineBeginY, lLineEndX, lLineEndY)
            With shp.Line
                .ForeColor.RGB = RGB(0, 255, 0)
                .Weight = 3
                .BeginArrowheadStyle = msoArrowheadOval
                .EndArrowheadStyle = msoArrowheadOval
                .BeginArrowheadWidth = msoArrowheadWidthMedium
                .BeginArrowheadLength = msoArrowheadLengthMedium
                .EndArrowheadWidth = msoArrowheadWidthMedium
                .EndArrowheadLength = msoArrowheadLengthMedium
                .Visible = msoTrue
            End With
        Next lPoint
 
    End With
Next vWshChtObj
End Sub