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.

 

[Jon Peltier]

You should know that these three statements:

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

are not doing what you think. Only Short_Chart is a Chartobject, only MaxDate is a date, and only Series_Count is a long. The rest are all variants. You need to use "As Whatever" after each variable, like this:

Dim Long_Chart As ChartObject, Short_Chart As ChartObject

This may fix your problem, since

Set Long_Chart = ActiveSheet.ChartObjects.Add(Left:=500, Width:=800, Top:=50, Height:=600)

assumes that Long_Chart is declared as a chartobject, not a variant.