Thread: Charting from internal data in a procedure

This array can be assigned directly to a chart series.

e.g.

Sub AssignArray()
Dim Points, Forces
  Points = Array(5, 15, 8)
  Forces = Array(1, 3, 9)
  
  ActiveChart.SeriesCollection(1).XValues = Points
  ActiveChart.SeriesCollection(1).Values = Forces
End Sub

Originally Posted by rwgrietveld

This array can be assigned directly to a chart series.

e.g.

Sub AssignArray()
Dim Points, Forces
  Points = Array(5, 15, 8)
  Forces = Array(1, 3, 9)
  
  ActiveChart.SeriesCollection(1).XValues = Points
  ActiveChart.SeriesCollection(1).Values = Forces
End Sub

Thx a lot for the quick response.


EDIT: problem not solved yet...see next post

I'm not quite there yet unfortunately:

I've made an array containing the numbers of all the datapoints (in this case 1-100) which i want plottet on the x-axis and i want to plot some corresponding values from my beamcalculation on the y-axis.

Here is some of the code i've written:

Dim xArray(ANTALDATAPUNKTER) As Byte (the number of datapoints)
Dim ufinTotalArray(ANTALDATAPUNKTER) As Single (an array containing the corresponding values to each x)
.
.
. (a lot of calculations filling the ufinTotalarray with data and the xArray with the numbers 1-100 in this case)
.
.
.
And now i want to make the graf using the data from the arrays:

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SeriesCollection(1).XValues = xArray
ActiveChart.SeriesCollection(1).Values = ufinTotalArray
ActiveChart.SeriesCollection(1).Name = "Deformation"


But this doesn't work.

Can you tell what's wrong??

And while i'm at it, i've noticed that when charting using Excel (and not vba) each graf is given a shapename (like "shape xx") and when i copy the sheet this shapenumber changes. How can name the graph/shape manually?

Almost there. need to add NewSeries ! and I belive you need a different chart type

Sub datap()

Const ANTALDATAPUNKTER = 100

Dim xArray(1 To ANTALDATAPUNKTER) As Single 'Byte 'the number of datapoints
Dim ufinTotalArray(1 To ANTALDATAPUNKTER) As Single 'an array containing the corresponding values to each x
Dim x As Long

For x = 1 To 100
  xArray(x) = x
  ufinTotalArray(x) = (x ^ 2) / 8 + 2 * x - 5
Next x

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = xArray
ActiveChart.SeriesCollection(1).Values = ufinTotalArray
ActiveChart.SeriesCollection(1).Name = "Deformation"

End Sub

Hi Kim

please put your code between code tags like this: [code] your code [/code]. That will make the code easier to read and copy. HTML otherwise strips out indentation and consecutive spaces.

Each series formula has a maximum length of 1024 characters.

So for a series with labels of a,b,c,d and data of 1,2,3,4 the series formula would look like this

=SERIES(,{"a","b","c","d"},{1,2,3,4},1)

If you divide the values by 3 then the formula balloons.
=SERIES(,{"a","b","c","d"},{0.333333333333333,0.666666666666667,1,1.33333333333333},1)

So if you have +100 decimal data values you have problably exceeded the limit.

Jon Peliter has some code for delinking charts from ranges to array values.
http://peltiertech.com/Excel/ChartsH...ChartData.html

In the code is examples of dealing with this issue.

Good addition Andy,

Didn't know about the limit.

Kim You could try to limit the number of points OR
limit the accuracy. In a graph, this is most probably not significant.

Originally Posted by rwgrietveld

Good addition Andy,

Didn't know about the limit.

Kim You could try to limit the number of points OR
limit the accuracy. In a graph, this is most probably not significant.

Thank you for the reply both of you (rwgrietveld and Andy Pope).

rwgrietveld: you're right.

Each corresponding calculation doesn't need to be that accurate since it is for chart-use only so i think i'll declare x as byte (since i never need more than 256 datapoints) and the result as integer (thus avoiding decimals).

Frustrating is it. You have reached a limit in what the chart will allow with code and arrays.

If you had a chart with a series that reference the values 1 to 81 in the range A1:A81 you could use this code to convert range to array.

activechart.SeriesCollection(1).values=activechart.SeriesCollection(1).values

Increase that to row 82 and the above will fail.

Why do you not want to use a worksheet to hold the values?

Why do you not want to use a worksheet to hold the values?

Hello Andy.

The reason i don't want the worksheet to hold the values is this:

I'm making a worksheet that calculates different forces and deflections at x number of places in a beam (i "slice" the beam into x slices).

Each sheet holds the information for one beam and i want each of these sheets to be cleansed of any unnecessary information. I only want the graphs depicting these forces and deflections and then some textoutput with maximum values.

And then i want to be able to make multiple copies of the sheet so i can have multiple different beams (different after i change the input to the procedure that is) in one workbook.

If i have one sheet for the beam and one sheet to hold the data for the beam there is to big a risc of confusion at some point

EDIT: i forgot to mention, that i want to be able to manually delete some rows in the beam-sheet. If i put the data in that sheet and hides them by setting the colomnwith to 0, these data will be deleted

If the chart is on a chart sheet then I can how a chart sheet AND data sheet may make things difficult to keep together.

But you code moves the chart to a worksheet, which would suggest the chart and data could reside on the same worksheet.

I'm still not getting why you can not use a hidden range to hold the chart information.