Special x-y graph with labels

[Jan Rennel]

Hi,

I try to develop a special x-y graph with identical x-y-axes and straight lines representing constant values.

I try to add constant text D/E=0.25, D/E=0.5 etc. to the lines but I am not successful. Can some one help me?

Kind regards,

Jan

[bsalv]

see attachment
goto sheet4
run macro "AddLines2Graph"
the other group of lines is similar

small adjustment

Please Login or Register  to view this content.

[Jan Rennel]

Hi,

Thanks for your suggestions. However, this is a very special graph where x- and y-axes are of quite the same length and you have lines in various directions as you can see from Sheet1. All the lines are already calculated and drawn, except that in a final step of the program, all the lines will be "black" and "thin". The lines form a special pattern which will be used later on.

What I needed was an explanation of the meaning of the lines taken from the Range("D45") in Sheet1. I took that part from another program I have, but got an Error Message. The question is why the module I have added for getting the text D/E=0.25, D/E=0.5, D/E=0.75 etc. is not accepted?

Kind regards,

Jan

[MrShorty]

What I needed was an explanation of the meaning of the lines taken from the Range("D45") in Sheet1. I took that part from another program I have, but got an Error Message. The question is why the module I have added for getting the text D/E=0.25, D/E=0.5, D/E=0.75 etc. is not accepted?

I'm not sure I'm following your problem. Is this a problem in Sub Makechart() in Module 1? What error message are you getting? When it gives you an error message, and you select the "debug" button, what line is highlighted?

I stepped through the makechart subroutine. when I got to the Set myRange = ActiveSheet.Range("D45").CurrentRegion line, it gave me a "property or method not supported" error. I observed that, at that point in the code, the active sheet was a chart sheet (not a worksheet or a chart embedded in a worksheet). A chart sheet does not contain cells or ranges, so, at that statement, activesheet.range(... was invalid. The active chart sheet appears to have been added in the 5th/6th statement, after the set statement that adds the embedded chart referred to as "mychart." I'm wondering if you are intending to add both of those charts, or if the "chart.add" statement is an error.

Is that the problem you are having?

[bsalv]

integrate my way of working in your macro and you have everthing you want.
But my macro calculates everything in memory, so that part you have to adapt.
I use points(2) to write a label, so that point must be within the grapf.

[Andy Pope]

First remove the line Chart.Add which creates a default chart sheet on data from 'Sheet1'!$A$1:$AJ$36. Your code does not affect the newly inserted chart sheet and fails due to Activesheet reference later in the code.

The reason the data labels are not showing is your range reference for data points for those additional series is wrong. Use the Select Data dialog to see where they are getting there values from.

Please Login or Register  to view this content.

[Jan Rennel]

Hi,

I have now a program that creates the special x-y-diagram I am looking for which can be seen in the enclosed file.

The next step is to include xy-line curves without markers in myChart, where the data is from in and around "Range ("I50").

The curves AA, BB and CC shall have different colors. The labels AA, BB and CC should have the same color as the curves they represent.

In addition the year figures 2000, 2004, 2008, 2011 and 2012 should be typed in the graph using very small, black text.

Can someone help me with this macro?

Kind regards,

Jan

[bsalv]

Please Login or Register  to view this content.

[Jan Rennel]

Hi,

Thank you for your help with the xy-macro.

The macro functions with the data supplied, except that the label text "AAA", "BBB" and "CCC" in the same color as the curves is missing.

I had also hoped to get a more general macro, i.e. not for only 5 data points but for a number determined by the number of years in each group determined by the labels "AAA", "BBB" and "CCC". The number of groups should also be flexible, i.e. more than 3 should be a possibilty.

To complicate it somewhat further I would also like to add the text "Total capital" parallel with for instance the straight line between 0/6000 and 6000/0, i.e. the text should have a slope of 45 degrees.

Kind regards,

Jan

[bsalv]

your more general macro (more then your 5 data points) isn't that difficult, if you're able to write the other macros, you just have to work with areas !

Please Login or Register  to view this content.

[Jan Rennel]

Hi,

I have tried to use your macro but I got an Error message saying that "the item with the specified name was not found".

When you debug, it is the statement "With sh.ChartObjects("Chart 5").Chart" which is not accepted. I do not know how to handle this.

Kind regards,

Jan

[bsalv]

"chart5" was the chart in the attachment of reaction 7.
Noqw i used 1, as that's the indexnumber of the 1st chart of that sheet.

[Jan Rennel]

Hi,

Thank you very much for your help. We are coming closer to how it should work and look like.

However, I don't want to have any legends, but instead the label
name "AA", "BB" etc. in the graph close to the respective curve with the same color as the curve.

It is enough to have the text "Total capital" only once in the middle between the xy-axes. Not twice as now.

I tried to add another series of data "DD" but then the program failed. If I understood it right it was because the color scheme was not enough.

What do you say?

Kind regards,

Jan

[bsalv]

it's difficult, i use the label of a point to label your serie in the graph. So that point won't have its year-value
Your new series get to colorindex 3,4,5, ....I didn't know what colors you had in mind.

For "total capital" i should add a 3rd point somewhere in the middle and give it a label, but i'm too tired.

[Jan Rennel]

Hi again,

I know it is not easy. I called it special x-y graph with labels. I suppose you understand - why.

It is fine that you have taken legends away. Otherwise the x and y axes will not be of the same length which they should be to be meaningful.

How many series can be included?

And how many individual data in a series? Are there any limitations?

Kind regards,

Jan

[MrShorty]

According to http://office.microsoft.com/en-us/ex...849.aspx?CTT=1 you can have up to 255 data series, 32000 points for each data series up to a total of 256000 data points.

[bsalv]

that "Total capital" must be done in your macro "makechart", but there you made it a little bit difficult.
In an previous reaction I proposed something different.
I repeat that proposal in the macro "makechart2".
So you can make your basic graph and afterwise you can add the new series with that macro of yesterday "MyNewSeries" .

[Jan Rennel]

Hi,

Thank you very much for all your help. It looks now rather fine I think.

A small question. You mentioned that you used the first position of a series for label "AA", "BB" etc. What about using the second or third one? If so, it would be easier to see the starting year, in this case "2000", which could be of certain interest.

For the text "Line_6000" etc. I only need one. But, it is of course easy to take the other way manually.

Kind regards,


Jan

[bsalv]

see attachment

[Jan Rennel]

Hi,

I have been away but have anyway had an opportunity to test the macro. It is excellent!

Perhaps, you should do it somewhat more general by adding a cell where you type the maximum figure on the axis. In our case it has been 12000 but other data may require another scale. The relationship in the graph shall, of course, be the same.

I also prefer the labels "AA", "BB" etc. in Arial. Could you, please, add that too.


Kind regards,

Jan

[bsalv]

I'll think about it.

Please Login or Register  to view this content.

[Jan Rennel]

Hi,

Thank you for your Arail statement.

Would il be complicated to change the figures for years now in black to the the respective colors of the curves? The actual data are very close and it would be easier to read if the year data had different colors, i.e. the same as the curves.

Have you come to any conclusion to add "maximum value" as a variable in the program?

Kind regards,

Jan

[bsalv]

see attachment

[Jan Rennel]

Hi,

Fantastic!
You can really see that the programming is done by a professional.

I have only one viewpoint. Now, you have what you call options in the beginning of the VBA-program. If you instead moved it to Sheet1 close to where you have the rest of your data, I think it would improve. Very few people can read VBA-code, but if you had a cell saying "Max axis-value" and another "Dividing" in for instace columns cells "E, F, G" at the top it would be better. You can then immediately see the result and adjust accordingly.

The label "Total capital", i.e. the sum of Debt and Equity or what you call "Line_6000" requires that you really have that line in the graph to be included. I suppose it can be difficult to have the label to include any line of that type in the graph. Of course, the label "Total capital" should be shown only once in the graph.

Kind regards,

Jan

[Jan Rennel]

Hi again,

I have problems with the label "line_6000" in the program. The real text should be "Total capital employed, MUSD", but then I get a line break which is not desired.

How to avoid the line break?

Kind regards,

Jan

[bsalv]

I don't know. There is no width on a datalabel.

[Andy Pope]

prior to xl2013 you can not control the width of a data label via Width property.

Best you can do is reduce font size or increase size of chart area. The wrapping is based on internal algorithm based on those things.

[Jan Rennel]

Hi,

About a month ago I got help with the enclosed macro.

Now, when I will try the macro again I get an error message.

What is the reason?

The data are enclosed as well as the graph as it should look like.

Kind regards,

Jan

[Andy Pope]

The table name has changed.

Please Login or Register  to view this content.

[Jan Rennel]

Hi Andy,

Thank you for your suggestion.

However, I am afraid I still don't know how to solve it.

I have the data but I don't talk about "Table1" anywhere.

In principle I want something like:
Set Table1=ActiveCell.CurrentRegion
to include the data in Sheet1.

The curious thing is that the macro has functioned previously.

Kind regards,

Jan

[Andy Pope]

I have the data but I don't talk about "Table1" anywhere.

The code in MyNewSeries routine says different. As I posted you have code that set a reference to table1.

Please Login or Register  to view this content.

If you change it to Tabel122 then the code does not error.

[bsalv]

As Andy Pope said, that "tabel1" is now "tabel122", so the name in the macro must be modified.
Now, as there is only 1 table in that sheet, you also can use the indexnumber 1 instead of the name.

Please Login or Register  to view this content.

[Jan Rennel]

Hi bsalv,

Thank you for your advice! The macro functions now properly!

If I would like to change so instead of having "sheet1", I would like to have more general ActiveSheet.

How would the code then be changed in MyNewSeries?


Kind regards,

Jan

[bsalv]

condition : there is 1 table in your activesheet

Please Login or Register  to view this content.

[Jan Rennel]

Hi bsalv,

The macro does not work well. I get the message "Subscript out of range".

It is perhaps because of the meaning of "table".

For me the meaning is that I have an ActiveSheet with a number of labels/companies each with a set of debt, equity values as can be seen from the enclosed Excel sheet.

Can you please advice how the macro should be changed to work in that way?


Kind regards,

Jan

[Jan Rennel]

Hi again!

I wonder if my problems with the macro are due to that I have an old Office version (2007)?

For instance the code:

Set c = ActiveSheet.ListObjects(1).DataBodyRange

is that OK in Office 2007?


Kind regards,

Jan

[bsalv]

of course, listobjects were new in 2007 and didn't exist in older versions,

Please Login or Register  to view this content.

[Jan Rennel]

Hi,

Thank you very much! The macro now functions properly.

However, I have still a question.

I deleted the graph heading which meant that the y-axis became somewhat longer than the x-axis.

What is the code to control the length of the x-y-axes, respectively?

Kind regards,

Jan

[bsalv]

modify the numbers

Please Login or Register  to view this content.

[Jan Rennel]

Hi bsalv,

Thank you very much for all your help with the x-y-macro.

I have really appreciated your build up of this strong tool for economic analysis.


Kind regards,

Jan