Merging Curves in One Excel Graph Using VB.NET

I’m opening a new question to clarify as to how two or more curves can be merged in one Excel graph by using VB.NET. It was suggested by @NeilFleming1 in answering a previous question that the following code be used:

Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Worksheets("sheet1").Activate
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.Select

However, Range and Union in the above code cannot work and the program crashes with the error message:

'System.Data.Range' is not accessible in this context because it is 'Private'.

Probably there should be a way to make these public or there’s something else that should be done.

Not being able to solve this merging with a code I resorted to a palliative solution. I made the two columns with data to be plotted adjacent to each other and added a prior empty column. So, we have an empty column H and two columns I and J filled with the data points.  Now the chartRange = ws.Range("H1..J20") renders the two curves properly overlaid. Unfortunately, the x-axis values are not the ones in column A but are just the consecutive numbers of the data points and instead of the x-axis a thick line (made of the zero points in H) appears. Don't know how to place the actual x-axis numbers correctly but that's as much as a palliative solution can do, I guess. So, how can this problem be solved using VB.NET code to automate Excel graph plotting? Thanks in advance for your help.
judicoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

judicoAuthor Commented:
Thanks a lot. I'm looking forward to hearing from the experts.
0
13598Commented:
You can change the scaling and also where to cross through the Format Axis menu:
The Scale tab provides different options for a category (x) axis.

To change the number at which the value axis starts or ends, type a different number in the Minimum box or the Maximum box.
To change the interval of tick marks (tick marks and tick-mark labels: Tick marks are small lines of measurement, similar to divisions on a ruler, that intersect an axis. Tick-mark labels identify the categories, values, or series in the chart.) and chart gridlines (gridlines in charts: Lines you can add to a chart that make it easier to view and evaluate data. Gridlines extend from the tick marks on an axis across the plot area.), type a different number in the Major unit box or Minor unit box.
To change the units displayed on the value axis, select the units that you want in the Display units list.
If you want to show a label that describes the units, select the Show display units label on chart check box.

Tip  If the chart values are large numbers, you can make the axis text shorter and more readable by changing the display unit of the axis. For example, you can display chart values ranging from 1,000,000 to 50,000,000 as 1 to 50 on the axis and show a label that indicates that the units are expressed in millions.


To change the value axis to logarithmic, select the Logarithmic scale check box.
Tip  This type of scale is useful when the values that are plotted in the chart cover a very large range.

To reverse the order of the values on the selected axis, select the Values in reverse order check box.
Note  When you change the order of the values on the value (y) axis from bottom to top, the category labels on the category (x) axis will flip from the bottom to the top of the chart. Likewise, when you change the order of the categories from left to right, the value labels will flip from the left side to the right side of the chart.

To specify the value at which the category (x) axis crosses the value (y) axis, type that value in the Category (X) axis crosses at box.
To force the x-axis, cross the value axis at the highest value, and select the Category (X) axis crosses at maximum value check box. This effectively moves the category labels to the opposite side of the chart.
Note  This option overrides the Category X crosses at value.
Tip  XY (scatter) charts show values on both the category (x) axis and the value (y) axis, while line charts show values on only the value (y) axis. This difference is an important factor in deciding which chart type to use. Because the scale of the line chart's category axis can't be changed as much as the scale of the value axis that is used in the xy (scatter) chart, you might consider using an xy (scatter) chart instead of a line chart if you need to change the scaling of that axis or display it as a logarithmic scale.



The sample I gave you on the other post has them crossing at 0:
With ActiveChart.Axes(xlCategory)
        .MinimumScale = 8
        .MaximumScale = 11
        .MinorUnit = 0.04
        .MajorUnit = 0.2
        .Crosses = -4114  ' xlCustom
        .CrossesAt = 0
        .ReversePlotOrder = False
        .ScaleType = -4132    ' xlLinear
        .DisplayUnit = -4142    ' xlNone
    End With

Determine what values you need and I can help you establish them in vb.net.
0
judicoAuthor Commented:
Thanks. I can see how you can change these options but first I'd like to understand how you choose the data in columns I and J to be plotted as a function of column A.

Also, where do you get the values of these codes -4132, -4142 and -4114. How do you know what each one means?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

13598Commented:
I get those values by doing what I suggested earlier. I am doing all this in Excel recording a macro. That is how I get those values. =}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
13598Commented:
I would suggest you do the same. Wouldn't it be easier to first get it like you want it directly in Excel and then code it?
0
judicoAuthor Commented:
See, what I'd like the program to do is just by clicking on the compiled VB.NET file data from two excel files to be extracted and added into a third file where the crunching of the numbers and plotting should occur automatically. Most of this is already done except for merging the two graphs from columns I and J and plotting them as a function of the data in column A. At this moment, not knowing how to do that programmatically, I resorted to a palliative solution whereby I place the data to be plotted in adjacent columns (as I said I and J) and leave an earlier column H empty. Then I choose:

chartRange = objectWorkBook.ActiveSheet.Range("H1..J10000")

and this indeed plots data from columns I and J together but messes up the x-axis because now the x-axis is the empty column H rather than the needed data in column A. So, that's the problem I'm having now and that will probably wrap up that part of the project.
       
0
judicoAuthor Commented:
I see your suggestion:

"I would suggest you do the same. Wouldn't it be easier to first get it like you want it directly in Excel and then code it?"

but I'll have to figure out how to do ti.
0
13598Commented:
Have you tried hiding the emply column(H)?
0
13598Commented:
What fails when you try to merge the two excel files?
0
13598Commented:
Couldn't you just copy column A values into H?
0
judicoAuthor Commented:
Maybe, as you've suggested, I have to understand first how one finds out what the code of a recorded macro is and then try to implement it in VB.NET. That would be the radical solution of all potential questions that would arise. So, when one records a macro how does one see what the actual recorded code is?
0
13598Commented:
This is the code to change your X axis in vb.net.
Could you just use this to chane the X axis to be column A?
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2A3:R4A3"

0
13598Commented:
After you are done recording: Tools/Macro/ Stop Recording..
Then Tools/Macro/Macros.
Select Macro and click the Edit button.  Voilá.
0
13598Commented:
For change your X axis to column A it needs to be:
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R1C1:R12C1"

not what I gave you earlier.  1 means the first column which is A
C means column so C1 is column A.
R means rows in my example is rows 1 through 12.
Try this to change your x axis to A.
0
judicoAuthor Commented:
OK. Thanks. I got that. It's a little messy though and I'd like to do it with my current code. For some reason your above suggested line (in my case):

chartObj.Chart..SeriesCollection(1).XValues = "=Sheet1!R2A3:R4A3"

gives me the error message:

'Public member 'XYValues' on type 'SeriesCollection' not found


0
judicoAuthor Commented:
So, now I've come up with this:

Dim chartRange As office.Range
        Dim charts As office.ChartObjects
        Dim chartObj As office.ChartObject
        Dim seriesCollection As office.SeriesCollection
        Dim series As office.Series
        charts = objectWorkBook.ActiveSheet.ChartObjects()
        chartObj = charts.Add(480, 20, 250, 250)
   
        Dim xlValue As Integer
        xlValue = 2
        Dim xlCategory As Integer
        xlCategory = 1
        Dim xlColumns As Integer
        xlColumns = 0

        With chartObj.Chart    
            .ChartType = office.XlChartType.xlLineMarkers
            chartRange = objectWorkBook.ActiveSheet.Range("A1:A10003, I1:J10003")
            .SetSourceData(chartRange)

            .SeriesCollection(1).XValues = "=Sheet1!R2C1:R10003C1"
            .SeriesCollection(2).XValues = "=Sheet1!R2C1:R10003C1"
            .SeriesCollection(1).Delete()
                 
            .Axes(xlValue).hastitle = True
            .Axes(xlValue).axistitle.caption = "Energy, j"
            .Axes(xlValue).axistitle.font.name = "times new roman"
            .Axes(xlValue).axistitle.font.bold = True
            .Axes(xlValue).axistitle.font.size = 10
            .Axes(xlValue).axistitle.font.color = RGB(1, 2, 3)
            .Axes(xlValue).ticklabels.numberformat = "0.000"
            .Axes(xlValue).hasmajorgridlines = False
            '.axes(xlvalue).hasmajorgridlines = 10
            '.axes(xlvalue).ticklabels.font.color = rgb(11, 12, 13)
            '.axes(xlvalue).ticklabels.orientation = 22  slanted axis label 22 is the degrees of slanting
            .Axes(xlValue).majortickmark = 2   ' (xlinside)
       
            .Axes(xlCategory).hastitle = True
            .Axes(xlCategory).axistitle.caption = "Time, s"
            .Axes(xlCategory).axistitle.font.name = "times new roman"
            .Axes(xlCategory).axistitle.font.bold = True
            .Axes(xlCategory).axistitle.font.size = 10
            .Axes(xlCategory).ticklabels.numberformat = "0.000"
         
            .Axes(xlCategory).ticklabels.font.size = 8
            .Axes(xlCategory).TickLabels.Orientation = 0
            .Axes(xlCategory).majortickmark = 2   ' (xlinside)
   
            .HasLegend = True
            .Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop
        End With

Now, it appears that the data in the two columns I and J are plotted together as a function of the values in column A. Don't know whether any two columns, not adjacent to each other can be plotted as a function of A. First, however, I have to get a handle of the x-axis (y-axis is almost done save the length of the tick marks, that's something I don't know hoe to change). The above code only gives a thicker abscissa (its line thicker than the line of the ordinate) without any tick marks. The tick labels are positioned well but I cannot control the number of the tick labels (they are six in this case but are too crowded). Also, I'd like to have them rounded to even numbers but probably that's too much to ask.

I'm following your advice to learn the code from the macro but something still escapes me.
0
judicoAuthor Commented:
Now, that's really weird. The line

.ChartType = office.XlChartType.xlLineMarkers

in the above code causes the plotting of a graph with overlaid columns I and J as a function of A, however, there are no x-axis tick marks and the abscissa is thick.

If the above line is replaced by

.ChartType = office.XlChartType.xlXYScatterLines

then everything with the tick marks and tick labels is rendered fine but now there's only one one colum, namely J, displayed in the graph against column A as the x-axis values. Like I said, I'm missing something.
0
judicoAuthor Commented:
OK, now that did it. The line to be used is

 .ChartType = office.XlChartType.xlXYScatterLines

and this line is to be removed (I don't know why it was there to begin with):

.SeriesCollection(1).Delete()

Obviously, I should use as type of graph xlXYScatterLines instead of xlLineMarkers. For some reason the latter causes problems, at least with what I'm trying to do. I'd be happy to hear some more words of wisdom on this if not I can award you the points with a big 'thank you' for the help in bringing this problem to an acceptable solution.
0
judicoAuthor Commented:
Thank you very much for your help. You did a very good job and I'm increasing the points to be awarded to 200. Wish you all the best.
0
judicoAuthor Commented:
You advice to explore the code of the Excel macro when trying to automate Excel from VB.NET was the crucial step. Of course, the VB.NET syntax is peculiar and the macro doesn't give the exact code needed in VB.NET but that can be figured out in most cases. Thanks again and all the best.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.