PDA

View Full Version : Creating charts using VBA: problem with intervals



quintus333
02-07-2011, 06:24 AM
Good day,

I have written code in VBA to create charts. Let me quickly explain the just of the data:

I have data of a machine that sends amp readings every ten minutes, therefore on any given day I will have 144 readings. First reading is at 00:00, 2nd reading is at 00:10 ... last reading is at 23:50. So my x-interval values will start at 00:00 and end at 23:50.

My problems are as follows:

1) I want the intervals to be as follows: 00:00, 01:00 ... 24:00. But for some reason the interval starts at 00:00, but then jumps to 02:24, and then 04:48 and so on. I would like to solve this issue.

2) I specifically specify that the x-intervals start at 00:00 and end at 24:00, but for some reason it carries on up to 02:24. So between 24:00 and 02:24 there's just an open space, as I have no readings for it. I want the graph to end at 24:00, but somehow VBA automatically adds a few more intervals after 24:00.

Notes:

1) My amp readings are in column A1:A144
2) My interval readings are in column B1:B144 - it is in time format "HH:MM"

Any help would be greatly appreciated.

Thank you in advance,

Quintus

IBihy
02-08-2011, 06:50 AM
Hello Quintus,
I think it would be easier to help you if you'd provide your workbook (data sheet plus the code that is to produce the graph) as attachment.

Greetings,
Isabella

quintus333
02-09-2011, 04:01 AM
Hallo Isabella,

Thank you for your response, here is my code:

With Sheet2.Shapes.AddChart _
(Left:=8, Width:=675, Top:=58, Height:=318)
.Chart.SetSourceData Source:=Sheet1.Range("A1:A144")
.Chart.ChartType = xlXYScatterLinesNoMarkers
.Chart.HasTitle = True
.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Days"
'.Chart.SeriesCollection(1).XValues = Sheet1.Range("B1:B144")
.Chart.SeriesCollection(1).Border.Weight = xlMedium
.Chart.Axes(xlValue, xlPrimary).HasTitle = True
.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Amps"
.Chart.PlotArea.Format.Fill.ForeColor.RGB = RGB(217, 217, 217)
End With

As you can see the XValues range from B1:B144, but the chart will have extra XValues after the last XValue of B144.

Vielen dank,

Quintus