Consulting

Results 1 to 7 of 7

Thread: Creating consistent charts and layouts

  1. #1

    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.

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

    CompressionPlot.jpg

    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
    Last edited by mattreingold; 06-07-2018 at 08:28 AM. Reason: Clarity

  2. #2
    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!

  3. #3
    Bump

  4. #4
    Quote Originally Posted by mattreingold View Post
    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?

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Post a sample workbook, including a example chart
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    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.
    Be as you wish to seem

  7. #7
    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!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •