Page 3 of 3 FirstFirst 123
Results 21 to 28 of 28

Custom date format on chart using VBA

This is a discussion on Custom date format on chart using VBA within the Excel Questions forums, part of the Question Forums category; You're welcome. When I said we were on two different pages, I was referring to the understanding of the scaling ...

  1. #21
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    10,951

    Default Re: Custom date format on chart using VBA

    You're welcome.

    When I said we were on two different pages, I was referring to the understanding of the scaling problem and not the length of the post.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Surround your pasted VBA code with code tags e.g.;
    [CODE]your VBA code here[/CODE]
    The pound # icon in the Advanced forum editor will apply the code tags around the highlighted text.

  2. #22
    Board Regular
    Join Date
    Aug 2002
    Posts
    530

    Default Re: Custom date format on chart using VBA

    Hi Alpha Frog,

    I hate to drag you back in, however, there was one more thing that I mentioned that would be nice if it were easy. What happens, when the title is added, is that the chart area shrinks. I want to maximize this, again for readability.

    I tried to modify the code in the arrange_charts sub to be 85% of the total chart height. I got this number from recording a macro. 85% is pretty much the maximum.
    Code:
            For iChart = 1 To .ChartObjects.Count
                With .ChartObjects(iChart)
                    .Height = dHeight
                    .Width = dWidth
                    .Top = dTop + Int((iChart - 1) / nColumns) * dHeight
                    .Left = dLeft + ((iChart - 1) Mod nColumns) * dWidth
                    .PlotArea.Height = 0.85 * .Height
                End With
    Not surprisingly, I got an error 438 that this method of selection wasn't supported.

    Do you have a suggestion for optimizing the chart area based on the chart height specified in cell T2 of the Charts worksheet.

    Thanks,

    Art

  3. #23
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    10,951

    Default Re: Custom date format on chart using VBA

    Quote Originally Posted by artz View Post
    Do you have a suggestion for optimizing the chart area based on the chart height specified in cell T2 of the Charts worksheet.
    Do you mean you want to maximize the Plot area to be a large as possible within the Chart area? The Plot area is just the gray plotted data area excluding the title and axis labels.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Surround your pasted VBA code with code tags e.g.;
    [CODE]your VBA code here[/CODE]
    The pound # icon in the Advanced forum editor will apply the code tags around the highlighted text.

  4. #24
    Board Regular
    Join Date
    Aug 2002
    Posts
    530

    Default Re: Custom date format on chart using VBA

    Hi Alpha Frog,

    Yes, that sounds right. For a given chart height, I would like the plotted area to be as large as possible limited on top by the title and limited on the bottom by the date on the x axis. Are we saying the same thing? I think so.

    Hopefully we are not on different pages again.

    Thanks,

    Art

  5. #25
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    10,951

    Default Re: Custom date format on chart using VBA

    Several tweaks throughout.

    Code:
    Sub Create_Charts()
    
        'previously named Sub X4()
        
        Dim lTop As Long, lCol As Long, lColumns As Long
        Dim rngX As Range, rngY As Range
        Dim sName As String
        Const ChtHeight As Long = 150 'Default Chart Height
        
        Application.ScreenUpdating = False
         
        Call delete_charts 'Delete all charts on worksheet "Charts"
        
        With Sheets("Data")
            lColumns = .Range("A4").CurrentRegion.Columns.Count
            If lColumns < 2 Then Exit Sub
            Set rngX = .Range("A5", .Range("A" & .Rows.Count).End(xlUp))
        End With
        
        'Add Chart for each data column
        For lCol = 2 To lColumns
        
            sName = Sheets("Data").Cells(4, lCol).Value
            Set rngY = rngX.Offset(, lCol - 1)
            
            'Add Chart
            With Sheets("Charts").ChartObjects.Add(1, lTop, 200, ChtHeight)
                .Name = sName
                With .Chart
                    'Data
                    With .SeriesCollection.NewSeries
                        .XValues = rngX
                        .Values = rngY
                    End With
                    .ChartType = xlLine
                    
                    ' Chart Formatting
                    .HasLegend = False
                    ' X Axis
                    With .Axes(xlCategory).TickLabels
                        .AutoScaleFont = False
                        .NumberFormat = "m/d/yy"  '"m/yy"
                        .Orientation = 45
                        With .Font
                            .Name = "Arial"
                            .FontStyle = "Regular"
                            .Size = 8
                        End With
                    End With
                    'Y Axis
                    .Axes(xlValue).MajorGridlines.Border.ColorIndex = 15    '   lt.grey plot area grid lines
                    Optimize_ScaleY .Axes(xlValue), rngY 'Optimize scaling
                    With .Axes(xlValue).TickLabels
                        .AutoScaleFont = False
                        With .Font
                            .Name = "Arial"
                            .FontStyle = "Regular"
                            .Size = 8
                        End With
                    End With
                    ' ChartTitle
                    .HasTitle = True
                    With .ChartTitle
                        .AutoScaleFont = False
                        With .Font
                            .Name = "Arial"
                            .FontStyle = "Bold"
                            .Size = 8
                            '.ColorIndex = 15
                        End With
                        .Top = 0
                        '.Left = 0
                        .Text = sName
                    End With
                    With .PlotArea
                        .Top = 5
                        .Height = 999999
                        .Left = 0
                        .Width = 999999
                        .Interior.ColorIndex = 19   'light yellow plot area
                    End With
                End With
            End With
            
            lTop = lTop + ChtHeight
        Next lCol
       
        Call mov_avg
        Call ArrangeMyCharts 'Position charts
        
        Application.ScreenUpdating = True
        
    End Sub
    
    Private Sub delete_charts()
        'Delete all charts on worksheet "Charts"
        Dim chtObj As ChartObject
        
        For Each chtObj In Sheets("Charts").ChartObjects
            chtObj.Delete
        Next chtObj
    
    End Sub
    
    Private Sub ArrangeMyCharts()
    
        Dim iChart As Long
        Dim dTop As Double
        Dim dLeft As Double
        Dim dHeight As Double
        Dim dWidth As Double
        Dim nColumns As Long
        
        With Sheets("Charts")
    
            dTop = .Range("R2")         ' top of first row of charts
            dLeft = .Range("S2")        ' left of first column of charts
            dHeight = .Range("T2")      ' height of all charts
            dWidth = .Range("U2")       ' width of all charts
            nColumns = .Range("V2")     ' number of columns of charts
        
            For iChart = 1 To .ChartObjects.Count
                With .ChartObjects(iChart)
                    .Height = dHeight
                    .Width = dWidth
                    .Top = dTop + Int((iChart - 1) / nColumns) * dHeight
                    .Left = dLeft + ((iChart - 1) Mod nColumns) * dWidth
                End With
            Next
        
        End With
        
    End Sub
    
    Sub range_update()
    
        Dim lChartCount As Long, lCol As Long
        Dim rngX As Range, rngY As Range
        
        lChartCount = Sheets("Charts").ChartObjects.Count
        If lChartCount = 0 Then Exit Sub
        
        With Sheets("Data")
            Set rngX = .Range("A5", .Range("A" & .Rows.Count).End(xlUp))
        End With
        
        For lCol = 2 To lChartCount
            Set rngY = rngX.Offset(, lCol - 1)
            With Sheets("Charts").ChartObjects(lCol - 1).Chart.SeriesCollection(1)
                .XValues = rngX
                .Values = rngY
                Optimize_ScaleY .Parent.Parent.Axes(xlValue), rngY 'Optimize scaling
            End With
            
        Next lCol
        
    End Sub
    
    
    Sub mov_avg()
        ' Procedure to show moving avg for user specified period
        ' Macro recorded 12/11/2005 by koday
        ' Cycles through all charts, applies same criteria to each chart
        
        Dim chtObj As ChartObject
        Dim TLine  As Trendline
        Dim per    As Integer
        
        ' User choices:
        '   1. Show data series?
        '   2. Moving Avg Period? If <2, no moving avg
        per = CInt(Sheets("Charts").Range("P2").Value)
        
        For Each chtObj In Sheets("Charts").ChartObjects
                
            With chtObj.Chart.SeriesCollection(1)
            
                ' Remove previous trendlines
                For Each TLine In .Trendlines
                        TLine.Delete
                Next TLine
                
                ' Check to see if period > 1; if yes, add trend line
                If per > 1 Then
                    '**** Set moving Averages
                    With .Trendlines.Add(Type:=xlMovingAvg, _
                                         Period:=per, _
                                         Forward:=0, Backward:=0, _
                                         DisplayEquation:=False, _
                                         DisplayRSquared:=False).Border
                            .ColorIndex = 3
                            '.Weight = xlMedium
                            .Weight = xlThin
                            '.LineStyle = xlHairline
                    End With
                End If
            
                ' Check to see if data series to be plotted
                With .Border
                    If Sheets("Charts").Range("Q2") = "Off" Then
                        '.Weight = xlHairline
                        .LineStyle = xlNone
                    Else
                        '.Weight = xlThin
                        .LineStyle = xlAutomatic
                    End If
                End With
    
            End With
        Next chtObj
            
    End Sub
    
    Private Sub Optimize_ScaleY(ByRef axY As Axis, ByVal rngY As Range)
        
        Dim MinScale As Double, MaxScale As Double, DataHeight As Double
    
        With Application.WorksheetFunction
            MinScale = .min(rngY)
            MaxScale = .max(rngY)
            DataHeight = MaxScale - MinScale
        End With
        With axY 'Chart Y-axis
            .MinimumScaleIsAuto = False
            .MaximumScaleIsAuto = False
            .MinimumScale = MinScale - DataHeight * 0.05
            .MaximumScale = MaxScale + DataHeight * 0.05
            With .TickLabels
                Select Case DataHeight
                    Case Is < 0.04: .NumberFormat = "0.000"
                    Case Is < 0.4: .NumberFormat = "0.00"
                    Case Is < 4: .NumberFormat = "0.0"
                    Case Else: .NumberFormat = "0"
                End Select
            End With
        End With
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Surround your pasted VBA code with code tags e.g.;
    [CODE]your VBA code here[/CODE]
    The pound # icon in the Advanced forum editor will apply the code tags around the highlighted text.

  6. #26
    Board Regular
    Join Date
    Aug 2002
    Posts
    530

    Default Re: Custom date format on chart using VBA

    Alpha Frog,

    Thanks, again. This awesome. The sizing is great and the color scheme makes the charts easy to read.

    Is the code dynamic in the sense that if I change the chart size for each chart in the panel, that the plot area will adapt correspondingly?

    Thanks,

    Art

  7. #27
    Board Regular
    Join Date
    Aug 2002
    Posts
    530

    Default Re: Custom date format on chart using VBA

    Alpha Frog,

    Could I enlist your help for one more chart macro? On another worksheet, I want to compare the percent growth of various stocks. Starting in in column B extending to column Y.

    Dates are in column A. Again, like the panel of charts, the data ranges and number of used columns can vary. The percent calculation is based on the values on the Data worksheet which I posted earlier.

    Below is an example of the new worsheet named Percent:

    Percent

    *ABCDEFGHIJK
    4DateCOHFASTTPXUAASNAATHNGCOLULUULTAVAL
    512/14/2011-0.9%-0.2%-1.8%-0.7%-0.6%-3.1%-1.2%-2.7%-4.8%-1.1%
    612/15/2011-2.1%1.0%-1.2%-0.5%-0.1%-18.1%-0.4%0.7%-5.5%-0.2%
    712/16/2011-4.1%2.6%2.6%-0.5%3.1%-17.6%0.2%1.0%-5.1%1.0%
    812/19/2011-5.2%2.8%-2.1%-3.3%4.1%-19.8%0.3%0.7%-8.1%0.9%
    912/20/2011-2.4%7.1%0.6%-2.9%9.0%-17.3%5.2%4.4%-5.2%3.2%
    1012/21/2011-0.3%6.8%0.7%-4.5%8.4%-17.7%7.6%2.4%-6.7%3.6%
    1112/22/2011-0.5%6.2%5.8%-5.2%6.9%-18.0%6.9%4.4%-9.1%5.7%
    1212/23/20111.9%8.2%4.9%-1.7%7.3%-17.6%7.2%6.9%-6.2%6.7%
    1312/27/20112.8%8.7%3.2%-1.0%8.7%-17.8%8.9%8.5%-7.2%5.9%
    1412/28/20111.3%8.4%0.8%-2.6%6.6%-17.5%6.9%5.1%-9.2%6.2%
    1512/29/20112.5%9.8%4.8%-3.5%8.7%-16.3%8.1%5.0%-8.4%7.7%
    1612/30/20111.6%8.0%2.3%-3.6%7.5%-18.0%6.9%4.1%-8.1%7.8%
    171/3/20120.0%8.4%7.5%-3.1%8.5%-16.4%1.5%5.0%-9.2%8.5%
    181/4/20122.5%9.9%9.1%-2.4%11.6%-15.7%5.0%14.1%-6.0%10.0%
    191/5/20123.0%11.8%13.4%-1.7%17.9%-11.9%9.3%16.3%-1.5%10.4%
    201/6/20124.3%12.0%14.0%-0.4%17.1%-11.2%7.3%19.8%-1.6%11.9%
    211/9/20124.7%11.9%13.1%-0.1%20.8%-9.6%7.3%19.3%-1.0%10.7%
    221/10/20123.8%11.5%13.0%0.9%21.7%-9.7%6.5%33.6%-0.2%13.1%
    231/11/20121.6%12.0%16.0%1.7%21.6%-8.0%6.4%35.2%1.6%13.6%
    241/12/20122.1%13.6%16.4%4.3%22.7%-6.6%6.2%36.7%1.9%14.9%
    251/13/20123.3%15.4%14.8%3.6%23.4%-9.6%2.5%38.3%3.6%15.8%
    261/17/20124.3%15.9%14.1%2.4%25.0%-9.6%3.5%35.4%5.0%15.8%
    271/18/20127.5%12.8%18.0%-1.7%27.6%-3.6%3.4%35.0%7.3%17.0%
    281/19/20128.2%13.3%22.3%-2.1%27.9%-3.6%3.5%34.2%9.6%17.9%
    291/20/20128.0%13.6%19.2%-1.2%28.2%-4.8%3.3%34.2%8.0%16.5%
    301/23/20127.0%14.1%17.8%1.7%27.8%-6.4%2.7%39.3%8.9%15.9%


    Excel tables to the web >> Excel Jeanie HTML 4

    I need just a simple linechart with all the series that are available in columns B through AY. No markers, thin lines, a legend, and the Y axis is on the right at the maximum date.

    Seems that the code that you posted earlier could be easily adapted to do this. Any help is appreciated.

    Thanks,

    Art

  8. #28
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    10,951

    Default Re: Custom date format on chart using VBA

    A new question should be in a new thread, and that would let the whole forum have the opportunity to respond.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Surround your pasted VBA code with code tags e.g.;
    [CODE]your VBA code here[/CODE]
    The pound # icon in the Advanced forum editor will apply the code tags around the highlighted text.

Page 3 of 3 FirstFirst 123

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com