Charting question

[mortgageman]

In the following type of line, can the "source" be a VBA variable? In other words, if I calculate in VBA, can I go directly to the chart, or do I have to write to a range and then chart?


ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B1:C2"), PlotBy:= _
xlRows

Gene, "The Mortgage Man", Klein

 

[Jon Peltier]

You can generate an array and use it as the source data:

Dim v As Variant
v = Array(1, 2, 3, 4)

ActiveChart.SeriesCollection(1).Values = v

or simply

ActiveChart.SeriesCollection(1).Values = Array(1, 2, 3, 4)

However, there is a limit to how much data can be inserted into the chart in this way. The limit is the number of characters it takes to write the array in the form

={1,2,3,4}

The limit is just under 255 characters. Note that greater precision requires many more characters per value and therefore fewer values per series. I've written a bit about this here:

http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html

In general I've found that it's better to put the data into a worksheet, in terms of reliability and transparency of the solution.

 

[mortgageman]

Since I am only dealing with straight lines (which need only 2 points or a total of 4 numbers in the array) this seems perfect. Thank you.

Gene, "The Mortgage Man", Klein

 

[mortgageman]

Not working yet. I have copied my current code below. The problems I am experiencing are these:

1) I'm pretty sure that the graph the this code is generating is adding to an existing graph, not starting a new one. It is also generating curves, not lines - but I'm pretty sure that this is connected.

2) I'm almost positive that when I write the graph to a file, (near the end of the code) it is not overwriting the previous file and not giving me a message about it.

Any and all ideas would be most helpful. I am pretty sure I have reached the limits of my ignorance. Note that I have left in (but commented out) the lines that were generated by the macro recorder but I am not using.

Gene, "The Mortgage Man", Klein

'Generate the Graph

'First create the 2 arrays, each holding 4 values per each of the 2 lines:
'Since we have y intercept, 0 is one x value and just use 10 for second x value
yval = 10 * slope1 + bint1
line1array = Array(0, 10, bint1, yval)
yval = 10 * slope2 + bint2
line2array = Array(0, 10, bint2, yval)
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
'ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B1:C2"), PlotBy:= _
' xlRows
'replacing above line with this line:
ActiveChart.SeriesCollection(1).Values = line1array

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""line1"""
'ActiveChart.SeriesCollection(2).XValues = "=Sheet1!R5C2:R5C3"
'ActiveChart.SeriesCollection(2).Values = "=Sheet1!R6C2:R6C3"
ActiveChart.SeriesCollection(2).Values = line2array
ActiveChart.SeriesCollection(2).Name = "=""line2"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
'The following three lines taken from JW's Excel 2002 Power Programming with VBA page 464
Set CurrentChart = Sheets("Sheet1").ChartObjects(1).Chart
Fname = ThisWorkbook.Path & "\temp.gif"
CurrentChart.Export Filename:=Fname, Filtername:="GIF"
Image1.Picture = LoadPicture(Fname)
End Sub

 

[Jon Peltier]

Here are a few points to ponder:

1. Your command

line1array = Array(0, 10, bint1, yval)

puts X and Y values into a single array, so your chart is using your X and Y values fior its Y values, and using 1, 2, 3, and 4 for its X values (or whatever had been the values in the default series in the chart).

You need two different commands to add X and Y values. It should look something like this:

  line1Xarray = array(0,10)
  line1Yarray = array(bint1, yval)

  With ActiveChart.SeriesCollection(1)
    .XValues = line1Xarray
    .Values = line1Yarray
  End With

2. You are in fact adding a new chart (with Charts.Add).

3. Chart.Export overwrites an existing file with the same name, without giving you any notice. If you keep using Sheets("Sheet1").ChartObjects(1).Chart without deleting the first chart, then you will keep exporting the first chart, because the subsequent charts have higher index numbers. Try:

Set CurrentChart = Sheets("Sheet1").ChartObjects(Sheets("Sheet1").ChartObjects.Count).Chart

 

[mortgageman]

Wow Jon - This works a lot better. I really appreciate the help. I still have one small problem however. The legend, intead of having just line1 and line2 also has something called "series 3" which also shows up as a triangle in the actual chart. I don't see series 3 anywhere in the following code. I'm sure I'm missing something dumb, but I just don't see it.

Edit - Spoke to soon. It is bombing out on this line:

With ActiveChart.SeriesCollection(1) <== debug points here
.XValues = line1xarray
.Values = line1yarray
End With








Gene, "The Mortgage Man", Klein

'Generate the Graph

'First create the 2 arrays, each holding 4 values per each of the 2 lines:
'Since we have y intercept, 0 is one x value for both
'Also use Tusharms good idea including intersection point in data
xint = (bint2 - bint1) / (slope1 - slope2)
yint = xint * slope1 + bint1
line1xarray = Array(0, xint)
line1yarray = Array(bint1, yint)
line2xarray = Array(0, xint)
line2yarray = Array(bint2, yint)
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
With ActiveChart.SeriesCollection(1)
.XValues = line1xarray
.Values = line1yarray
End With

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""line1"""
With ActiveChart.SeriesCollection(2)
.XValues = line2xarray
.Values = line2yarray
End With
ActiveChart.SeriesCollection(2).Name = "=""line2"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
'Write graph to a file and then read it into the UF image
Set CurrentChart = Sheets("Sheet1").ChartObjects(Sheets("Sheet1").ChartObjects.count).Chart
Fname = ThisWorkbook.Path & "\temp.gif"
CurrentChart.Export Filename:=Fname, Filtername:="GIF"
Image1.Picture = LoadPicture(Fname)

 

[Jon Peltier]

Depending on what's selected when you run the code, you may have any number of series in the chart as soon as it's added. The best thing to do is remove all series and start from scratch.

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
Do Until ActiveChart.SeriesCollection.Count = 0
  ActiveChart.SeriesCollection(1).Delete
Loop

' Series 1
With ActiveChart.SeriesCollection.NewSeries
  .Name = "Line 1"
  .XValues = line1Xarray
  .Values = line1Yarray
End With

' Series 2
With ActiveChart.SeriesCollection.NewSeries
  .Name = "Line 2"
  .XValues = line2Xarray
  .Values = line2Yarray
End With

A couple other points.

1. You might find it better to add the chart object directly to the worksheet, rather than adding a chart sheet, then moving it to the worksheet. This is described in my Quick Chart VBA page:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

2. IMO you should use xlXYScatterLines as your chart type, not xlXYScatterSmooth. Your series have two points so it doesn't matter, but if a series has multiple points, moving up and down, the smoothed line can overshoot the data points, and make it appear that there are minima or maxima where there is not even any data. The only data you have is at the points, so they should be connected by straight lines. Smooth lines might "look better", and so do false 3D effects in charts, but both of these effects make it more difficult to read the data off the charts.

 

[mortgageman]

Thanks to the kind help of everyone here, I have a pretty nice working UF application that solves 2 variable and 3 variable equations (ax+by=c and ax+by+cz=d). The two variable case shows a graph with the solution point. It is a nice hw helper for 9th and 10th graders. If there is a way to upload excel files - I will be happy to do so.

Gene, "The Mortgage Man", Klein

 

[sweater_vests_rock]

try this out, gene.

cheers. ben.

 

[mortgageman]

try this out, gene.

cheers. ben.

I read it. It says that the file must be zipped. Back before windows I knew how to zip a file using a utility called pkzip. Since windows came out, I have had no need to zip anything (although I have had to unzip files, which windows makes easy). All this rambling is meant to cover up the fact that I nolonger know how to zip a file

Gene, "The Mortgage Man", Klein