Insert Chart Help Needed

[Saladsamurai]

I have the following Code and I wouls like to plot on an XY Scatter Plot (smoothed lines and no data points) the elements of the array "Bins()" on the X-axis and elements of the array "Classes()" on the y-axis.

Sub InsertChartPlease()
Dim i As Long
Dim j As Long
Dim nRow As Long
Dim nCol As Long
Dim Sheet1 As Worksheet
Dim Classes(3) As Long 'Each Element of Classes is a class (bin)
Dim Bins(3) As Long


Bins = (10, 20, 30)
Classes = (0, 0, 0)

nRow = 300
nCol = 130
Set Sheet1 = Worksheets("Sheet1")


With Sheet1
For i = 1 To nRow
For j = 1 To nCol
If Cells(i, j) <> "" Then
' Frequency for each class
Select Case Cells(i, j)
Case Is <= 10
Classes(0) = Classes(0) + 1
Case Is <= 20
Classes(1) = Classes(1) + 1
Case Is <= 30
Classes(2) = Classes(2) + 1
End Select
End If

Next j
Next i
End With

End Sub


I would also like to be able to set the position and size of the plot. But what does Excel use to do that? Do we specify by pixels or inches or a % of the screen size...etc?

Any advice would be great!

Thanks

PS

Does this not work?
Bins = (10, 20, 30)
Classes = (0, 0, 0)


How can I initialize the whole array at once?

[Bob Phillips]

What is the code supposed to be doing?

[Saladsamurai]

What is the code supposed to be doing?

It is a histogram code. I know there is one out there, but I like mine.

It does the following

Loops through a Range

Counts all how many values are from 0 - 10 --> Classes(0)
Counts all how many values are from 10 - 20 --> Classes(1)
Counts all how many values are from 20 - 30 --> Classes(2)

Now each element of "Classes()" countains the frequency of each interval

Now I want to Plot my XSeries = All elements of Bins

against my YSeries = Elements of Classes

So far I have added this (but do not know how to handle Values and XValues)




Here's what I have so far (Sample Book attached)


Sub InsertChartPlease()
Dim i As Long
Dim j As Long
Dim nRow As Long
Dim nCol As Long
Dim nClasses As Integer
Dim Sheet1 As Worksheet
Dim Classes() As Long 'Each Element of Classes is a class (bin)
Dim Bins(3) As Long


Bins(0) = 10
Bins(1) = 20
Bins(2) = 30
ReDim Classes(3) ' Initializes Classes() to Zero
nRow = 300
nCol = 130
Set Sheet1 = Worksheets("Sheet1")

With Sheet1
For i = 1 To nRow
For j = 1 To nCol
If Cells(i, j) <> "" Then
' Frequency for each class
Select Case Cells(i, j)
Case Is <= 10
Classes(1) = Classes(1) + 1
Case Is <= 20
Classes(2) = Classes(2) + 1
Case Is <= 30
Classes(3) = Classes(3) + 1
End Select
End If

Next j
Next i
End With

Charts.Add

With ActiveChart
Do While .SeriesCollection.Count > 0
.SeriesCollection(.SeriesCollection.Count).Delete
Loop

.ChartType = xlXYScatterSmoothNoMarkers
.SeriesCollection.NewSeries

With .SeriesCollection(.SeriesCollection.Count)
.Values = Bins
.XValues = Classes
End With
End With

End Sub


It fails in many ways as you will see. The plot is all crazy for one. I presume as a result of .Values and .XValues

It ONLY WORKS if you ACTIVATE "Sheet1" (I though 'With Sheet1' would have remedied that)

I have not figured out how to set size/position either.

:)

[Bob Phillips]

Option Explicit

Sub InsertChartPlease()
Dim i As Long
Dim j As Long
Dim nRow As Long
Dim nCol As Long
Dim Sheet1 As Worksheet
Dim Classes() As Variant
Dim Bins() As Variant
Dim mChart As Chart

Bins = Array(10, 20, 30)
Classes = Array(0, 0, 0)

nRow = 300
nCol = 130
Set Sheet1 = Worksheets("Sheet1")

With Sheet1
For i = 1 To nRow
For j = 1 To nCol
If Cells(i, j) <> "" Then
' Frequency for each class
Select Case Cells(i, j)
Case Is <= Bins(0)
Classes(0) = Classes(0) + 1
Case Is <= Bins(1)
Classes(1) = Classes(1) + 1
Case Is <= Bins(2)
Classes(2) = Classes(2) + 1
End Select
End If

Next j
Next i
End With

Set mChart = Charts.Add

With mChart
Do While .SeriesCollection.Count > 0
.SeriesCollection(.SeriesCollection.Count).Delete
Loop

.ChartType = xlXYScatterSmoothNoMarkers
.SeriesCollection.NewSeries

With .SeriesCollection(.SeriesCollection.Count)
.Values = Join(Bins, ",")
.XValues = Join(Classes, ",")
End With
End With

End Sub

[Saladsamurai]

Oh Sweet man. I have more questions, but not yet. I will eventually like it so that I can set the Chart's 'destination Sheet'/size/position.

I think I figure everything else out by recording Macros.

Thanks agina xld!