PDA

View Full Version : [SOLVED] Creating consistent charts and layouts



mattreingold
06-07-2018, 08:27 AM
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.


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).

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

mattreingold
06-07-2018, 08:30 AM
Clarification: The source data comes from pasting the data arrays into Sheet 3 (Dummy data sheet) from which the charts then pull from.

(From my understanding, the only way to populate a chart - probably wrong again lol)

Thanks!

mattreingold
06-11-2018, 10:42 AM
Bump

mattreingold
06-11-2018, 10:44 AM
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 actually have a change of heart, keeping it as a seperate tab would be great, so essentially formatting professionally is what I am looking for.

Also, I would like multiple data sets to plot on this graph, is there a way I could do this?

Paul_Hossler
06-12-2018, 06:50 AM
Post a sample workbook, including a example chart

Aflatoon
06-12-2018, 07:24 AM
Unless you are using Excel 2007 (in which case, I feel for you), simply recording a macro while formatting the chart the way you want would give you most of the code.

As far as chartobjects go, they are just a container for the Chart object, so all the same formatting is possible.

mattreingold
06-12-2018, 08:55 AM
The record macro trick did it, I always forget about that, I'm so bound up in coding that I forget object manipulation can be recorded... Thank you so much!