1 Attachment(s)
Creating consistent charts and layouts
Hello all,
I am trying to populate a chart from arrays for each sample read into the workbook. Below is the code I have thus far, which works GREAT.
Code:
Dim Cht As Chart
Dim seri As Series
Dim name1 As String
name1 = "Stress (MPa) vs Position (%)"
Set Cht = Charts.Add
With Cht
.HasTitle = True
.ChartTitle.Text = name1
.HasLegend = False
.Name = fileNameWBNChart
.ChartType = xlXYScatterSmoothNoMarkers
'''' Chart Generation Based on Sample Number
If Counter = 0 Then
.SetSourceData Source:=WS2.Range("A1:A" & UBound(loadArr)), _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=Sheet3!$B$1:$B$" & UBound(loadArr)
ElseIf Counter = 1 Then
.SetSourceData Source:=WS2.Range("C1:C" & UBound(loadArr)), _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=Sheet3!$D$1:$D$" & UBound(loadArr)
ElseIf Counter = 2 Then
.SetSourceData Source:=WS2.Range("E1:E" & UBound(loadArr)), _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=Sheet3!$F$1:$F$" & UBound(loadArr)
ElseIf Counter = 3 Then
.SetSourceData Source:=WS2.Range("G1:G" & UBound(loadArr)), _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=Sheet3!$H$1:$H$" & UBound(loadArr)
ElseIf Counter = 4 Then
.SetSourceData Source:=WS2.Range("I1:I" & UBound(loadArr)), _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=Sheet3!$J$1:$J$" & UBound(loadArr)
ElseIf Counter = 5 Then
.SetSourceData Source:=WS2.Range("K1:K" & UBound(loadArr)), _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=Sheet3!$L$1:$L$" & UBound(loadArr)
ElseIf Counter = 6 Then
.SetSourceData Source:=WS2.Range("M1:M" & UBound(loadArr)), _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=Sheet3!$N$1:$N$" & UBound(loadArr)
ElseIf Counter = 7 Then
.SetSourceData Source:=WS2.Range("O1:O" & UBound(loadArr)), _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=Sheet3!$P$1:$P$" & UBound(loadArr)
ElseIf Counter = 8 Then
.SetSourceData Source:=WS2.Range("Q1:Q" & UBound(loadArr)), _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=Sheet3!$R$1:$R$" & UBound(loadArr)
ElseIf Counter = 9 Then
.SetSourceData Source:=WS2.Range("S1:S" & UBound(loadArr)), _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=Sheet3!$T$1:$T$" & UBound(loadArr)
End If
''''
.SeriesCollection(1).Trendlines.Add
With .SeriesCollection(1).Trendlines(1)
.DisplayRSquared = True
.DisplayEquation = True
End With
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "0"
.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
.Axes(xlCategory, xlPrimary).TickLabelSpacing = 500
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Position (%)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Stress (MPa)"
End With
Attached is a picture of the chart that populates (of an example sample).
Attachment 22376
The thing is, I would like it to have a fixed minimum (start at the origin), increase the axis label font size (say at least 15 point), and be able to control the position of the equation of the trend-line and R^2 equation.
Also, as this macro runs with each sample, a new tab is opened per chart (to my understanding this is what: '.Charts.Add' does by nature).
If at all possible, I would prefer each chart to populate as a chart in a normal sheet with cells NOT its own tab (or both) (say Sheet1 - keeping the same exactness/customness I.E. labels, font size, positioning etc. [to my understanding this is a .ChartObject??])
I did some noodling around and it seems '.ChartObjects' dont have as much customization; is this so?
Thank you in advance, this forum is the best!
P.S. I am a noob, so if there is a way to better code this (the if statements with the counter variable) I would gladly take advice as well!
- Matt
