I have written below code to generate a Graph based on a data in a sheet. But getting below errors.
1. The specified dimension is not valid for the current chart type.
2. Method 'MarkerStyle' of object 'Series' Failed
3. Method 'Delete' of object 'Series' Failed
4. Application Defined or object Defined Error
Below code creates total 36 Graphs first few graphs creats successfully but after that it fails and throws above mentioned error.
Any help appreciated.
Thanks in Advance.
1. The specified dimension is not valid for the current chart type.
2. Method 'MarkerStyle' of object 'Series' Failed
3. Method 'Delete' of object 'Series' Failed
4. Application Defined or object Defined Error
Below code creates total 36 Graphs first few graphs creats successfully but after that it fails and throws above mentioned error.
Code:
Sub CreateWeeklyGraph(ByVal rptName As String, ByVal numDays As Integer)
On Error GoTo Handler
Dim a, b, c, d, e, f, comma As Variant
Dim i, j, k, rCount, chartCount As Integer
Dim cto As ChartObject
Dim cht, chtChart As Chart
Dim CurSheet As Worksheet
Dim sheetName As String
i = 2
k = 2
'Application.ScreenUpdating = False
frmEEMReport.ProgressBar3.Visible = True
frmEEMReport.lblStep2Status.Visible = True
frmEEMReport.ProgressBar3.Min = 63
frmEEMReport.lblStep2Status.Caption = ""
'Workbooks.Open rptName
While i <= numDays
'Initialise progress bar
frmEEMReport.ProgressBar3.Max = 100
frmEEMReport.ProgressBar3.Value = i + 63
frmEEMReport.ProgressBar3.Refresh
frmEEMReport.lblStep2Status.Caption = "Graph Creation " & frmEEMReport.ProgressBar3.Value & " % Completed"
DoEvents
Application.Workbooks("EEM V1.0").Activate
sheetName = Range("A" & i).Value
Application.Workbooks(Dir(rptName)).Activate
Sheets(sheetName).Activate
rCount = Range("E1", Range("E1").End(xlDown)).Rows.Count
'HERE CALCULATE AVERAGE VALUE BASED ON TIME
rCount = rCount + 1
Range("E" & rCount).Select
'ActiveCell.FormulaR1C1 = "=AVERAGE(R[-3]C:R[-1]C)/1000"
ActiveCell.Formula = "=AVERAGE(E2:E" & rCount - 1 & ")/ 1000" ' R[-2]C:R[-1]C)/1000"
Selection.AutoFill Destination:=Range("E" & rCount & ":AZ" & rCount), Type:=xlFillDefault
'Range("E" & rCount & ":AZ" & rCount).Select
Range("D2").Select
ActiveCell.Formula = "=MAX(C2:C" & rCount - 1 & ")"
Range("YZ2").Select
ActiveCell.Formula = "=D2"
Selection.NumberFormat = "0;[Red]0"
Range("A1").Activate
'Application.Workbooks(Dir(rptName)).Activate
d = "='" & sheetName & "'" & "!$E$1:$AZ$1"
e = "='" & sheetName & "'" & "!$C$"
a = "'" & sheetName & "'" & "!$A$2"
b = "'" & sheetName & "'" & "!$E$1"
c = "'" & sheetName & "'" & "!$E$" & rCount & ":" & "$AZ$" & rCount
Range("A" & k & ",E1,E" & rCount & ":AZ" & rCount).Select
DoEvents
Sleep (500)
' Set CurSheet = Worksheets(sheetName)
' Set chtChart = CurSheet.ChartObjects.Add(Left:=100, Width:=300, Top:=100, Height:=300).Chart
ActiveSheet.Shapes.AddChart
DoEvents
'Get a reference to the chartobject on the sheet
Set cto = Sheets(sheetName).ChartObjects(1)
'Get a reference to the chart
Set cht = cto.Chart
cto.Select
DoEvents
ActiveChart.ChartType = xlLineStacked
'ActiveChart.SetSourceData Source:=Range("'02 July'!$A$2,'02 July'!$C$1,'02 July'!$C$2:$AX$2")
ActiveChart.SetSourceData Source:=ActiveSheet.Range("" & a & "," & b & "," & c & "")
ActiveChart.ApplyLayout (1)
ActiveChart.PlotArea.Select
DoEvents
'Sleep (1)
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "" & d & ""
'ActiveChart.SeriesCollection(1).XValues = "='02 July'!$C$1:$AX$1"
'ActiveSheet.ChartObjects("Chart " & chartCount).Activate
ActiveSheet.ChartObjects(1).Activate
DoEvents
ActiveChart.ChartTitle.Text = sheetName
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Response Time (in sec)"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Response Time (in sec)"
With Selection.Format.TextFrame2.TextRange.Characters(1, 13).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 13).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.Legend.Select
'ActiveChart.SeriesCollection(1).Name = "='02 July'!$A$2"
ActiveChart.SeriesCollection(1).Name = sheetName
'ActiveChart.PlotArea.Select
ActiveChart.ChartType = xlLineMarkers
ActiveChart.ChartArea.Select
DoEvents
ActiveSheet.Shapes("Chart " & cto.ZOrder).ScaleWidth 1.5961765092, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart " & cto.ZOrder).ScaleHeight 1.4803109507, msoFalse, msoScaleFromTopLeft
DoEvents
Sleep (400)
'Add Threshold Line
ActiveChart.SeriesCollection.NewSeries
DoEvents
Sleep (500)
ActiveChart.SeriesCollection(2).Name = "=""Threshold Value"""
comma = ","
f = "='" & sheetName & "'" & "!$YZ$2"
e = comma & "'" & sheetName & "'" & "!$YZ$2"
ActiveChart.SeriesCollection(2).Values = "" & f & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & e & ""
'--ActiveSheet.ChartObjects(1).Activate
ActiveSheet.ChartObjects(1).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.Axes(xlCategory).Select
DoEvents
Selection.MajorTickMark = xlOutside
ActiveChart.Axes(xlCategory).TickLabels.Orientation = 90
ActiveChart.Axes(xlCategory).AxisBetweenCategories = False
With ActiveSheet.Shapes("Chart " & cto.ZOrder).Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
.Weight = 1.75
End With
' Add Text Box to graph to put the comment
' ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 40, 340, 580, 50).ShapeStyle = msoLineStylePreset1
ActiveChart.SeriesCollection(1).Select
Sleep (400)
DoEvents
With Selection
.MarkerSize = 1.5
.MarkerStyle = -4142
End With
ActiveChart.SeriesCollection(2).Select
Sleep (400)
DoEvents
'Sleep (1)
With Selection
.MarkerStyle = -4142
.MarkerSize = 1.5
.Border.Color = RGB(120, 0, 0)
End With
Set cto = Nothing
Set cht = Nothing
Set chtChart = Nothing
i = i + 1
Wend
Application.Workbooks(Dir(rptName)).Activate
'Application.Workbooks(Dir(rptName)).Save
Exit Sub
Handler:
MsgBox Err.Description
Exit Sub
frmEEMReport.btnExcelReport.Enabled = True
End Sub
Any help appreciated.
Thanks in Advance.