Setting ChartObjects Properties throws Run-time Error '1004'

Hot Topic

MS Office document and image writer print driver
Extracting an embedded document within a cell in a table in a Word document
Combo Box add items from excel column and add to excel column
PASSING MS OUTLOOK DATA TO EXCEL
How do I open CSV using Excel without deleting leading zeros?
RunTime Error '2001' - FIX!
OnChange ?
Setting Password reminder questions.
Open PDF file
Adding a command button to the toolbar or menu bar in Excel?

Microsoft ISV

asp .Net 2.0 --> The specified module could not be found. (Exception from HRESULT: 0x8007007E)
inserting row in excel
Can i embed MS Access database inside Visio and query it using VBA
Table or Array in the header of an excel sheet
saving data in a userform without saving the workbook
Page Setup in Excel 2003 using VBA
Ajax in CRM
VBA code for use in several worksheets in a workbook
Form Locking
Registration Limits and Useage Rules

Setting ChartObjects Properties throws Run-time Error '1004'

I have been trying to figure out what is wrong with this macro for a month now. Can someone please help me, I give up I have columns of data ranging from A to EK and 352 rows of data for each. Each column will be a new dataset and therefore a new chart. Each set of 5 columns is a group so after I go through 5, I offset the output column for the charts by 7 and start a new group. It throws really random Runtime Errors '1004 ' , like "Unable to set the Size property of the Font class" or "Method 'HasTitle' of object'_Chart' failed." One day it will be the Legend object, then the Axes. There doesn't seem to be any consistent error besides the fact that it progressively quits creating charts the more I run it while it switches to completely out of proportioned charts where you can't read the data. I have not been able to create any more than 16 charts before it gives up. What I don't understand is that if it creates charts to start, the syntax cannot be incorrect. I found a similar post on here that speaks about the same issue but it is not being used with ChartObjects and is very basic: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=391049&SiteID=1 

Please help.

 

Option Explicit

Sub ChartData()
    Dim myChtObj As ChartObject
    Dim rngChtData As Range
    Dim rngChtXVal As Range
    Dim rng As Range
    Dim cht As ChartObject
    Dim x As Integer
    Dim y As Integer
    Dim z As Integer
    Dim q As Integer
    Dim o As Integer

    x = 0
    y = 2
    z = 1
    q = 0

Do Until y = 141
        
    For o = 1 To 5
         ' define chart data
         Set rngChtData = ActiveSheet.Range("A2:A352").Offset(0, z)
          
        ' define chart's X values
         Set rngChtXVal = ActiveSheet.Range("A2:A352")
      
   
        ' add the chart
            
        ActiveSheet.ChartObjects.Add Left:=100, Width:=360, Top:=75, Height:=225
            
          Set myChtObj = ActiveSheet.ChartObjects(z)
         
          Set rng = ActiveSheet.Range("B356:G382").Offset(x, q)
                x = x + 28
              
               
                myChtObj.Left = rng.Left
                myChtObj.Width = rng.Width
                myChtObj.Top = rng.Top
                myChtObj.Height = rng.Height
               
       
        With myChtObj.Chart
       
            ' make a Line chart
           
            .ChartType = xlLine
            .HasLegend = True
            .HasTitle = True
           
            .Legend.Position = xlLegendPositionBottom
            .Legend.Border.LineStyle = xlNone
            .Legend.Fill.BackColor.SchemeColor = xlNone
            .Legend.Font.Size = 8
       
           ' remove extra series
            Do Until .SeriesCollection.Count = 0
                     .SeriesCollection(1).Delete
            Loop
   
            ' add series from selected range
                .SeriesCollection.NewSeries
                .SeriesCollection(1).Values = rngChtData
                .SeriesCollection(1).XValues = rngChtXVal
                .SeriesCollection(1).Name = ActiveSheet.Range("A1").Offset(0, z)
               
                .Axes(xlValue, xlPrimary).HasTitle = True
                .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "bps"
                .Axes(xlCategory, xlPrimary).HasTitle = True
                .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
                .Axes(xlValue).Border.ColorIndex = 15
                .Axes(xlValue).HasMajorGridlines = False
                .Axes(xlValue).TickLabels.Font.Size = 8
                .Axes(xlValue).TickLabels.Font.Bold = True
                .Axes(xlCategory).TickLabels.Orientation = xlUpward
               
                .SeriesCollection(1).Trendlines.Add.Type = xlMovingAvg
                .SeriesCollection(1).Trendlines(1).Period = 7
               
                .ChartTitle.Text = ActiveSheet.Range("A1").Offset(0, z)
                .ChartTitle.Font.Bold = True
               
                .ChartArea.Border.LineStyle = xlNone
                .ChartArea.Interior.ColorIndex = xlNone
               
                .PlotArea.Interior.ColorIndex = xlNone
               
               
        End With             
                
       z = z + 1
       y = y + 1
       
   Next o
    x = 0
    q = q + 7
   
Loop
End Sub

 


 

Answer this question

Setting ChartObjects Properties throws Run-time Error '1004'

  • Sweeps78

    Step through the code, and watch what is going on. At the point where your code tries adding a chart title (.HasTitle = True), the chart is but a blank rectangle. In the UI, Chart Options is disabled, so you cannot manually add a title, and neither can VBA.

    You should always add the data to a chart first, then deal with enhancements like chart and axis titles, legends, even chart type.

    I've punched up your code a little:

    Sub ChartData()
 Dim myChtObj As ChartObject
 Dim rngChtData As Range
 Dim rngChtXVal As Range
 Dim rng As Range
 Dim cht As ChartObject
 Dim x As Integer
 Dim y As Integer
 Dim z As Integer
 Dim q As Integer
 Dim o As Integer

 x = 0
 y = 2
 z = 1
 q = 0

 ' define chart's X values
 Set rngChtXVal = ActiveSheet.Range("A2:A352")

 Do

  For o = 1 To 5
   
   ' define chart data
   Set rngChtData = ActiveSheet.Range("A2:A352").Offset(0, z)

   ' add the chart

   Set rng = ActiveSheet.Range("B356:G382").Offset(x, q)
   Set myChtObj = ActiveSheet.ChartObjects.Add _
     (Left:=rng.Left, Width:=rng.Width, Top:=rng.Top, Height:=rng.Height)

   With myChtObj.Chart

    ' remove extra series
    Do Until .SeriesCollection.Count = 0
     .SeriesCollection(1).Delete
    Loop

    ' add series from selected range
    With .SeriesCollection.NewSeries
     .Values = rngChtData
     .XValues = rngChtXVal
     .Name = ActiveSheet.Range("A1").Offset(0, z)
    End With

    With .Axes(xlValue, xlPrimary)
     .HasTitle = True
     .AxisTitle.Characters.Text = "bps"
     .Border.ColorIndex = 15
     .HasMajorGridlines = False
     With .TickLabels.Font
      .Size = 8
      .Bold = True
     End With
    End With
    With .Axes(xlCategory, xlPrimary)
     .HasTitle = True
     .AxisTitle.Characters.Text = "Date"
     .TickLabels.Orientation = xlUpward
    End With

    .ChartType = xlLine

    With .SeriesCollection(1)
     .Trendlines.Add.Type = xlMovingAvg
     .Trendlines(1).Period = 7
    End With
    
    .HasLegend = True
    .HasTitle = True

    With .Legend
     .Position = xlLegendPositionBottom
     .Border.LineStyle = xlNone
     .Fill.BackColor.SchemeColor = xlNone
     .Font.Size = 8
    End With

    With .ChartTitle
     .Text = ActiveSheet.Range("A1").Offset(0, z)
     .Font.Bold = True
    End With

    With .ChartArea
     .Border.LineStyle = xlNone
     .Interior.ColorIndex = xlNone
    End With

    .PlotArea.Interior.ColorIndex = xlNone

   End With

   x = x + 28

   z = z + 1
   y = y + 1
   
   If y > 141 Then Exit Do

  Next o
  x = 0
  q = q + 7

 Loop
End Sub
    - Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


  • i7hira7

    Jon,

    Thank you very much. I was trying to figure out if I was committing the same error as the other post but I am not familiar with Charts in VBA at all, so this is my first learning experience. The code works perfectly and consistently, something I thought would never happen. Much appreciate your assitance. You wouldn't happen to know a method of identifying outliers on a chart would you Like circle the spikes in a Line chart Again, thank you very much.

  • Juliusz

    There is no built-in way to identify outliers, but you can add series to a chart to highlight them for you. You need to develop an algorithm that identifies the outlier, than you have to plot the points. I show how to identify high and low in a chart on this page:

    http://peltiertech.com/Excel/Charts/FormatMinMax.html

    Instead of this formula that gives a valid point

    =IF($A2=MAX($A$2:$A$22),$A2,NA())

    you could put your criterion into a cell, say, D1, and change the formula to

    =IF($A2>$D$1,$A2,NA())

    and plot column B as the high end outliers.

    - Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


  • Setting ChartObjects Properties throws Run-time Error '1004'
Answer this question