VBA Chart Macro Error (Specific Dimensions not valid for current chart type)

[mrhammerstein]

I have created a macro to make some charts on the activesheet. I'm using 2007 SP2 excel on a Vista 64 bit computer. It works perfect for me. However, the person ultimately who's going to run it can't get the macro to run. That computer is XP with 2007 SP2 as well. This error is returned.

Run-Time error '-2147467259 (80004005)':
The specified dimension is not valid for the current chart type.

The line that hangs is the ChartObject.Add line below. It creates the outline of the chartobject, but fails before adding the chart.

Dim Long_Chart, Short_Chart As ChartObject
Dim X_Data As Range
Dim MyNewSrs As Series
Dim Long_StrtDate, MaxDate As Date
Dim MaxDepth, X_Unit, Y_MaxScale, Y_MinScale, Interval, Well_Series, Series_Count As Long

'----- CHART OPTIONS -----
[B] [SIZE=3]   Set Long_Chart = ActiveSheet.ChartObjects.Add(Left:=500, Width:=800, Top:=50, Height:=600)[/SIZE][/B]
    With Long_Chart.Chart
        .ChartArea.AutoScaleFont = False
        .ChartType = xlXYScatter                        'Chart Type
        .HasTitle = True                                'Sets Chart to have a Title
        .ChartTitle.Characters.Text = Cells(2, 2)       'Creates Chart Title
        .ChartTitle.Font.Name = "Calibri"               'Chart Title Font
        .ChartTitle.Font.Bold = True                    'Makes Chart Title Bold
        .ChartTitle.Font.Size = 18                      'Chart Title Font Size
    End With

I've checked multiple sites and the closest thing I've seen is a similar error you get from going from 2003 to 2007 versions, but everything I've done is in 2007. The chart type is also a built-in chart, not a user template. I've tried changing it to different types (xlXYScatterLines, xlLine), but that still gives an error.

The excel file does reside on a network drive, but both users have the same access rights.

 

[venkat1926]

from the cursory glance it looks source data is not available . Perhaps the source was highlighted in one case and not highlighted in another case. Please check this. add source data some thing like this

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B10"),

 

[mrhammerstein]

from the cursory glance it looks source data is not available . Perhaps the source was highlighted in one case and not highlighted in another case. Please check this. add source data some thing like this

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B10"),

Same error. This also adds the chart as a new sheet which isn't what my goal is (I have over 30 worksheets so I want to limit creating anymore). Setting the source data as above adds unwanted series which I can control through SeriesCollection, but I'd rather not.

It seems like I'm stuck.

 

[venkat1926]

I suggest you RECORD the macro taking the necessary steps and then edit the macro to suit you.

 

[mrhammerstein]

I started this way, but I went ahead and tried it again. I came up with

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'2924 34N'!$D$1:$H$148")
ActiveChart.ChartType = xlXYScatterLines

Which I can get to work on my computer, but again, cannot get it to work on the second computer. I still get the "specified dimension" error. So I went to the second computer and recorded a macro on that machine. It gave me the same code above. When I ran it (only what it recorded) it failed yet again on the same error. I'm beginning to believe it will fail on any attempt to add a chart in any manner.

I believe my next step is to uninstall/reinstall Office 2007 on the second computer and give it another shot.

The charts do look beautiful on my machine though. Now about getting a high-resolution pdf from those charts, that's my next challenge...

 

[venkat1926]

My macro works both in 2003 and 2007. I have another confusion in your source data.
xyscatter is only between two sets of data. buy you have five sets of data.(columns D,E,F,G,H. Of course this will not give error and give some chart but what this chart signify. If you are clear on this it is ok.

 

[mrhammerstein]

The source data (columns D,E,F,G,H) are just to get the chart started. I actually delete all the series right after making the graph and then add series one by one according to different criteria. I kept that setsourcedata line in there to try and satisfy the chart as how the macro was recorded. In my original code (which works great on one computer only), there is no setsourcedata line.

Below is my full macro. It won't do much without the data. But like I said. Works great on one computer, not at all on a second computer. I'm beginning to think the second computer is messed up somehow. I know there has to be some out of place code, but it is my first time trying to create charts by a macro.

But it always hangs on Set chrt_SHORT = ActiveSheet.ChartObjects.Add(250, 100, 800, 616). Always the "specified dimension" error.

Sub AddChartObject()

Dim chrt_LONG, chrt_SHORT, chrt As ChartObject
Dim ws As Worksheet
Dim rng_X, rng_DEPTH As Range
Dim srs_NEW As Series
Dim dt_XSTART, dt_MAX, dt_CURRENTMAX, dt_MJRSCALE, dt_MNRSCALE As Date
Dim str_GSELEVBOX, str_KCWABOX, str_PAGENOBOX, str_MAXSHEET, str_CURRENTSHEET As String
Dim num_MAXDEPTH, num_LASTROW, num_YMAX, num_YMIN, num_INTERVAL, num_WELLS, num_SERIESNO, num_SRSTOTAL As Double
Dim str_LABEL, num_CURRENT, num_MAX As Double
Dim int_WS, int_YEARMULT, int_MAJORUNIT As Integer


'----- DELETES ANY CHARTOBJECT -----
For Each chrt In ActiveSheet.ChartObjects
    chrt.Delete                         
Next                                      

'==========================================================================================================================
'================================ FINDS MAXIMUM DEPTH AND READING DATE OF ALL WORKSHEETS ==================================
'==========================================================================================================================

str_CURRENTSHEET = ActiveSheet.Name
Application.ScreenUpdating = False

num_MAX = 0
dt_MAX = DateSerial(2000, 1, 1)

For int_WS = 1 To Worksheets.Count                                      
    Worksheets(int_WS).Activate
    num_LASTROW = ActiveSheet.UsedRange.Rows.Count
    str_LABEL = 4
    Do While Len(Cells(6, str_LABEL)) > 0
        If Left(Cells(6, str_LABEL), 5) = "DEPTH" Then
            Set rng_DEPTH = Range(Cells(7, str_LABEL), Cells(num_LASTROW, str_LABEL))    
            num_CURRENT = WorksheetFunction.Max(rng_DEPTH)                             
            If num_CURRENT > num_MAX Then
                num_MAX = num_CURRENT
                str_MAXSHEET = ActiveSheet.Name
                str_WELL = Cells(1, str_LABEL - 1)
            End If
        ElseIf Right(Cells(6, str_LABEL), 4) = "DATE" Then
            Set rng_DEPTH = Range(Cells(7, str_LABEL), Cells(num_LASTROW, str_LABEL))      
            dt_CURRENTMAX = WorksheetFunction.Max(rng_DEPTH)                            
            If dt_CURRENTMAX > dt_MAX Then
                dt_MAX = dt_CURRENTMAX
                str_MAXSHEET = ActiveSheet.Name
            End If
        End If
        str_LABEL = str_LABEL + 1
    Loop
Next int_WS

num_MAX = WorksheetFunction.RoundUp(num_MAX, 0)
'MsgBox ("num_MAX = " & num_MAX & Chr(10) & "dt_MAX = " & dt_MAX)

Application.ScreenUpdating = True
Worksheets(str_CURRENTSHEET).Activate


'==========================================================================================================================
'=============================================== MAKES THE SHORT HYDROGRAPH ===============================================
'==========================================================================================================================

If Cells(12, 2) = "Yes" Then


'----- CHART OPTIONS -----
    Set chrt_SHORT = ActiveSheet.ChartObjects.Add(250, 100, 800, 616)           '(left, top, width, height)
    chrt_SHORT.Name = "Short"
    With chrt_SHORT.Chart
        .ChartArea.AutoScaleFont = False
        .ChartType = xlXYScatter                        'Chart Type
        .HasTitle = True                                'Sets Chart to have a Title
        .ChartTitle.Characters.Text = Cells(2, 2)       'Creates Chart Title
        .ChartTitle.Font.Name = "Calibri"               'Chart Title Font
        .ChartTitle.Font.Bold = True                    'Makes Chart Title Bold
        .ChartTitle.Font.Size = 18                      'Chart Title Font Size
        .ChartArea.Border.LineStyle = xlLineStyleNone

'----- X-AXIS DETAILS -----

        With .Axes(xlCategory, xlPrimary)
            .HasMajorGridlines = True
            .HasMinorGridlines = False                 

'            dt_MAX = InputBox("dt_MAX", , "6/15/2010") 
            int_YEARMULT = Year(dt_MAX) Mod 4
            Select Case int_YEARMULT
            Case 0
                Select Case (Month(dt_MAX) + 1)
                Case 3, 5
                    dt_MNRSCALE = DateSerial(Year(dt_MAX) - 2, Month(dt_MAX) + 1, 1)
                    dt_MJRSCALE = dt_MNRSCALE + 24 * 31
                    int_MAJORUNIT = 31
                Case Else
                    dt_MJRSCALE = DateSerial(Year(dt_MAX), Month(dt_MAX) + 1, 1)
                    dt_MNRSCALE = dt_MJRSCALE - 24 * 30
                    int_MAJORUNIT = 30
                End Select
            Case Else
                Select Case (Month(dt_MAX) + 1)
                Case 3, 4, 5
                    dt_MNRSCALE = DateSerial(Year(dt_MAX) - 2, Month(dt_MAX) + 1, 1)
                    dt_MJRSCALE = dt_MNRSCALE + 24 * 31
                    int_MAJORUNIT = 31
                Case Else
                    dt_MJRSCALE = DateSerial(Year(dt_MAX), Month(dt_MAX) + 1, 1)
                    dt_MNRSCALE = dt_MJRSCALE - 24 * 30
                    int_MAJORUNIT = 30
                End Select
            End Select

            .MajorUnit = int_MAJORUNIT
            .MinimumScale = dt_MNRSCALE
            .MaximumScale = dt_MJRSCALE

            .TickLabels.NumberFormat = "MMM-dd-YY"         'Sets X-Axis Format
            .TickLabels.Orientation = 45                'Rotates X-Axis Lables to set degrees
            .HasTitle = True                            'Sets X-Axis Title On/Off
            .AxisTitle.Characters.Text = "Date"         'Creates Chart Title
            .AxisTitle.Font.Name = "Calibri"            'Chart Title Font
            .AxisTitle.Font.Bold = True                 'Makes Chart Title Bold
            .AxisTitle.Font.Size = 14                   'Chart Title Font Size
            .Border.Color = vbBlack
            .Border.Weight = 1
            With .MajorGridlines
                .Border.LineStyle = xlContinuous
                .Border.Weight = xlHairline
                .Border.Color = vbBlack
            End With

        End With
'----- Y-AXIS PRIMARY DETAILS -----
        With .Axes(xlValue, xlPrimary)
            .HasTitle = True                                                   
            .AxisTitle.Characters.Text = "Water Level Elevation (ft. amsl)"    
            .AxisTitle.Font.Name = "Calibri"
            .AxisTitle.Font.Size = 14
            .AxisTitle.Font.Bold = True
            With .MajorGridlines
                .Border.LineStyle = xlContinuous
                .Border.Weight = xlHairline
                .Border.Color = vbBlack
            End With
        End With
    End With

'----- CREATES ELEVATION DATA SERIES FOR CHART -----
    num_WELLS = 5
    num_SERIESNO = 0
    num_LASTROW = ActiveSheet.UsedRange.Rows.Count                                                
    Do While Cells(6, num_WELLS) <> ""
        If Left(Cells(6, num_WELLS), 4) = "ELEV" Then
            Set srs_NEW = chrt_SHORT.Chart.SeriesCollection.NewSeries
            With srs_NEW                                                                       
                .ClearFormats
                .Name = Cells(3, num_WELLS)                                                        
                .Values = ActiveSheet.Range(Cells(7, num_WELLS), Cells(num_LASTROW, num_WELLS))    
                .XValues = Range(Cells(7, 4), Cells(num_LASTROW, 4))
                .AxisGroup = xlPrimary
            End With
            num_WELLS = num_WELLS + 2
            num_SERIESNO = num_SERIESNO + 1
        ElseIf Left(Cells(6, num_WELLS), 4) = "READ" Then
            Set srs_NEW = chrt_SHORT.Chart.SeriesCollection.NewSeries
            With srs_NEW                                                                       
                .ClearFormats
                .Name = Cells(3, num_WELLS)                                                             
                .Values = ActiveSheet.Range(Cells(7, num_WELLS + 1), Cells(num_LASTROW, num_WELLS + 1)) 
                .XValues = Range(Cells(7, num_WELLS), Cells(num_LASTROW, num_WELLS))
                .AxisGroup = xlPrimary
            End With
            num_WELLS = num_WELLS + 3
            num_SERIESNO = num_SERIESNO + 1
        Else
            num_WELLS = num_WELLS + 1
        End If
    Loop

'----- Y-AXIS PRIMARY DETAILS -----
    num_YMAX = Round(num_MAX, 0)                                        
    num_INTERVAL = WorksheetFunction.RoundUp(num_MAX / 20, 0) * 20      
    num_YMIN = num_YMAX - num_INTERVAL                                  
    With chrt_SHORT.Chart.Axes(xlValue)
        .MaximumScale = num_YMAX                                        
        .MinimumScale = num_YMIN                                       
        .CrossesAt = num_YMIN
        .MajorUnit = 20
        .TickLabels.NumberFormat = "0"
        .Border.Color = vbBlack
        .Border.Weight = 1
    End With

'----- CHANGES THE SERIES MARKER SIZE AND LINE WIDTH -----
    For num_SRSTOTAL = 1 To num_SERIESNO
        With chrt_SHORT.Chart
            .SeriesCollection(num_SRSTOTAL).Format.Line.Weight = Cells(27, 2)
            .SeriesCollection(num_SRSTOTAL).MarkerSize = Cells(26, 2)
            .SeriesCollection(num_SRSTOTAL).Shadow = False                      
        End With
    Next num_SRSTOTAL

'=============================================== ADDING SECONDARY AXIS ===============================================
'----- CREATES SECONDARY Y-AXIS DATA SERIES FOR CHART (DEPTH OF WATER DATA -----
    Set srs_NEW = chrt_SHORT.Chart.SeriesCollection.NewSeries
    With srs_NEW
        .Name = "srs_SECONDARY"                                            
        .XValues = Range(Cells(7, 4), Cells(num_LASTROW, 4))                
        .Values = ActiveSheet.Range(Cells(7, 6), Cells(num_LASTROW, 6))    
        .AxisGroup = xlSecondary                                            
        .MarkerStyle = None                                                 
        .Border.LineStyle = None                                            
    End With
'----- Secondary Y-Axis Scale Details -----
    With chrt_SHORT.Chart.Axes(xlValue, xlSecondary)
        .MaximumScale = num_INTERVAL                            
        .MinimumScale = 0                                      
        .ReversePlotOrder = True                            
        .MajorUnit = 20
        .TickLabels.NumberFormat = "0"
        .HasTitle = True                                
        .Border.Color = vbBlack
        .Border.Weight = 1
        With .AxisTitle                                      
            .Characters.Text = "Depth to Water Level (ft.)"   
            .Font.Name = "Calibri"
            .Font.Size = 14
            .Font.Bold = True
        End With
    End With

'=============================================== CHART OPTIONS ===============================================
'----- LEGEND BOX PROPERTIES -----
    With chrt_SHORT.Chart.Legend                    
        .LegendEntries(num_SERIESNO + 1).Delete    
        .IncludeInLayout = False
        .Position = xlLegendPositionTop            
        .Top = 555                                 
    End With
'----- CHART PLOT AREA PROPERTIES -----
    With chrt_SHORT.Chart
        .PlotArea.Interior.Pattern = xlNone
        .PlotArea.Height = 475
        .PlotArea.Top = 55
    End With

'=============================================== TEXTBOXES & IMAGES ===============================================
'----- ADD FOR GS ELEVATION TEXTBOX -----
    With chrt_SHORT.Chart.Shapes.AddTextbox(msoTextOrientationHorizontal, 60, 45, 125, 25)
        str_GSELEVBOX = "GS Elevation  " & Format(Cells(3, 2), "#0.0") & " ft."
        With .TextFrame.Characters
            .Text = str_GSELEVBOX
            .Font.Name = "Calibri"
            .Font.FontStyle = "Regular"
            .Font.Size = 10
            .Font.ColorIndex = xlAutomatic
        End With
    End With
'----- ADD PAGE NUMBER TEXTBOX -----
    With chrt_SHORT.Chart.Shapes.AddTextbox(msoTextOrientationHorizontal, 350, 850, 100, 15)
        str_PAGENOBOX = "Page  " & Cells(16, 2) & "  of  " & Cells(18, 2)
        With .TextFrame
            .Characters.Text = str_PAGENOBOX
            .Characters.Font.Name = "Calibri"
            .Characters.Font.FontStyle = "Regular"
            .Characters.Font.Size = 9
            .Characters.Font.Bold = False
            .Characters.Font.ColorIndex = xlAutomatic
            .VerticalAlignment = xlVAlignCenter
            .HorizontalAlignment = xlHAlignCenter
        End With
    End With
End If
End Sub

 

[venkat1926]

this is a guess work. remove the dimensions of the chart within the brackets and leave it as default size chart

 

[tusharm]

My guess is that the data are different in the 2 cases, one that works and the other that does not.

When you add a chart, Excel picks a default type and tries to match the data in the current region to the chart type. That leads to my guess. The chart type and the underlying current region don't match.

I have created a macro to make some charts on the activesheet. I'm using 2007 SP2 excel on a Vista 64 bit computer. It works perfect for me. However, the person ultimately who's going to run it can't get the macro to run. That computer is XP with 2007 SP2 as well. This error is returned.

The line that hangs is the ChartObject.Add line below. It creates the outline of the chartobject, but fails before adding the chart.

Dim Long_Chart, Short_Chart As ChartObject
Dim X_Data As Range
Dim MyNewSrs As Series
Dim Long_StrtDate, MaxDate As Date
Dim MaxDepth, X_Unit, Y_MaxScale, Y_MinScale, Interval, Well_Series, Series_Count As Long

'----- CHART OPTIONS -----
[B] [SIZE=3]   Set Long_Chart = ActiveSheet.ChartObjects.Add(Left:=500, Width:=800, Top:=50, Height:=600)[/SIZE][/B]
    With Long_Chart.Chart
        .ChartArea.AutoScaleFont = False
        .ChartType = xlXYScatter                        'Chart Type
        .HasTitle = True                                'Sets Chart to have a Title
        .ChartTitle.Characters.Text = Cells(2, 2)       'Creates Chart Title
        .ChartTitle.Font.Name = "Calibri"               'Chart Title Font
        .ChartTitle.Font.Bold = True                    'Makes Chart Title Bold
        .ChartTitle.Font.Size = 18                      'Chart Title Font Size
    End With

I've checked multiple sites and the closest thing I've seen is a similar error you get from going from 2003 to 2007 versions, but everything I've done is in 2007. The chart type is also a built-in chart, not a user template. I've tried changing it to different types (xlXYScatterLines, xlLine), but that still gives an error.

The excel file does reside on a network drive, but both users have the same access rights.

 

[Dag]

Excel 2010 bug seems to me...
"
The problem occurs whenever the default chart type is set to other chart types than the ones you are trying to create. For an example, if you try to create line chart and if the default chart in Excel is OHLC (Candlestick Stock Chart) then Excel will be fast to complain "The specified dimension is not valid for the current chart type". The same occurs even if you try to create charts from VB.NET. Hence, first of all change the default chart type to some basic chart types like Line charts. The problem will be solved.
"
http://www.excelbanter.com/showthread.php?t=204071