PDA

View Full Version : Charting an array as a series



smr122
07-15-2008, 12:32 PM
I have come across a problem in my code that I am having trouble identifying. I have created 4 arrays based on data in a spreadsheet that was given to me, and I am trying to chart them; one as the x-axis and the others as y-axis values. I have provided my code for the array creation loop as well as the chart with-blocks. I don't get any run-time errors, but when the data is graphed, my three y-axis arrays show up as {0,0,...,0}. The x values are fine, its just the y values that are wrong. Also, when stepping through the program and printing values from the arrays at various steps, the data is correct. I am very new to VBA (two days in) and don't understand what I am doing wrong.

ReDim ClkDate(rowCount)
ReDim LSeries(rowCount)
ReDim MSeries(rowCount)
ReDim NSeries(rowCount)
For i = 1 To rowCount - 1
ClkDate(i) = Cells(counter, "H")
LSeries(i) = Cells(counter, "L")
MSeries(i) = Cells(counter, "M")
NSeries(i) = Cells(counter, "N")
counter = counter + 1
Next i

Sheet1.Activate
With Charts(tabTitle).SeriesCollection.NewSeries
.Name = "AttainedStd"
.Values = LSeries
.XValues = ClkDate
End With
With Charts(tabTitle).SeriesCollection.NewSeries
.Name = "Std Variance"
.Values = MSeries
.XValues = ClkDate
End With
With Charts(tabTitle).SeriesCollection.NewSeries
.Name = "%Efficiency"
.Values = NSeries
.XValues = ClkDate
End With

Any advice you guys can provide would be greatly appreaciated.

Edit: I found my solution. It was simply that I defined the arrays as strings, but to use them as a series they had to be integer values. It was a pretty stupid mistake and a simple fix, I just couldn't see it for the longest time.

xld
07-15-2008, 02:02 PM
Can you post the complete workbook, it is a bit lacking in context.

smr122
07-15-2008, 02:11 PM
Here is the spreadsheet. I don't have the code with me because I just got home from work. The basic idea that my boss wants is to be able to highlight a set of machines from column A and hit the macro so that it will graph the data from columns L, M, and N. Each of those will be separate series with the dates from column H as the x values. I'll post the full module first thing tomorrow morning.

Thanks for the reply!

smr122
07-16-2008, 04:24 AM
Here is my full code:

Sub GraphCreation()

' Keyboard Shortcut: Ctrl+q

Dim chtTitle As String
Dim tabTitle As String
Dim ClkDate() As String
Dim LSeries() As String
Dim MSeries() As String
Dim NSeries() As String
Dim counter As Integer
Dim rowCount As Integer
Dim i As Integer

rowCount = Selection.Rows.Count
chtTitle = Cells(ActiveCell.row, "B")
tabTitle = Cells(ActiveCell.row, "A")
counter = ActiveCell.row

Charts.Add
With ActiveChart
.ChartType = xlXYScatterLines
.HasTitle = True
.ChartTitle.Text = chtTitle
End With
ActiveSheet.Name = tabTitle
Charts(tabTitle).SeriesCollection(1).Delete

Sheet1.Activate
ReDim ClkDate(rowCount)
ReDim LSeries(rowCount)
ReDim MSeries(rowCount)
ReDim NSeries(rowCount)
For i = 1 To rowCount - 1
ClkDate(i) = Cells(counter, "H")
LSeries(i) = Cells(counter, "L")
MSeries(i) = Cells(counter, "M")
NSeries(i) = Cells(counter, "N")
counter = counter + 1
Next i

Sheet1.Activate
With Charts(tabTitle).SeriesCollection.NewSeries
.Name = "AttainedStd"
.Values = LSeries
.XValues = ClkDate
End With
With Charts(tabTitle).SeriesCollection.NewSeries
.Name = "Std Variance"
.Values = MSeries
.XValues = ClkDate
End With
With Charts(tabTitle).SeriesCollection.NewSeries
.Name = "%Efficiency"
.Values = NSeries
.XValues = ClkDate
End With

End Sub