# Excel 2007 - The Specified Dimension is not Valid for the Chart Type



## ExcelentEnginer (Oct 2, 2008)

Hello

I've got quite a complex macro that was written in Excel 2003 and want to upgrade it to work in Excel 2007/Excel 2010. The routine produces a series of charts but it seems from what I've read that Excel 2007 handles charts quite differently from Excel 2003. My macro generates a chart sheet onto which I want to puttow charts side by side. The way i works it that it plots the two charts on separate sheets first then moves both ot them onto another sheet and deletes the two original chart sheets.

The problem occurs when I attempt to move the charts. I get an error message that says



> "Run-time error '1004'
> 
> The specified dimension is not valid for the current chart type


My code is shown below. 


```
'Add two new charts

    'Chart for normal Q-Q plot

        Charts.Add
        CurrentChart = CurrentOutputProperty & " (Q-Q)N"
        ActiveChart.Name = CurrentChart
        
    'Have to create the chart as a column chart then convert it to a
    'scatter plot otherwise Excel bitches about empty cells in the
    'column input range
    
        Charts(CurrentChart).ChartType = xlColumnClustered
        
    'Remove any data series added by default
        
        On Error Resume Next
    
        NumberOfDataSeries = Charts(CurrentChart).SeriesCollection.Count
        
        For SeriesCollectionCounter = 1 To NumberOfDataSeries
        
            SeriesToBeDeleted = "Series" & SeriesCollectionCounter
            
            ActiveChart.SeriesCollection(SeriesToBeDeleted).Delete
            
        Next SeriesCollectionCounter
        
        On Error GoTo 0
        
    'Add data series to plot the results
            
        ActiveChart.SeriesCollection. _
            NewSeries.Name = "Observed"
        Charts(CurrentChart).SeriesCollection. _
            NewSeries.Name = "Upper 95% CI"
        Charts(CurrentChart).SeriesCollection. _
            NewSeries.Name = "Lower 95% CI"
        Charts(CurrentChart).SeriesCollection. _
            NewSeries.Name = "Upper 95% PI"
        Charts(CurrentChart).SeriesCollection. _
            NewSeries.Name = "Lower 95% PI"
            
    'Plot the data
        
        Charts(CurrentChart).SeriesCollection("Observed"). _
            XValues = CurrentZiNorm
        Charts(CurrentChart).SeriesCollection("Observed"). _
            Values = CurrentYiNorm
        
        Charts(CurrentChart).SeriesCollection("Upper 95% CI"). _
            XValues = CurrentZiNorm
        Charts(CurrentChart).SeriesCollection("Upper 95% CI"). _
            Values = CurrentUpperCINorm
            
        Charts(CurrentChart).SeriesCollection("Lower 95% CI"). _
            XValues = CurrentZiNorm
        Charts(CurrentChart).SeriesCollection("Lower 95% CI"). _
            Values = CurrentLowerCINorm
            
        Charts(CurrentChart).SeriesCollection("Upper 95% PI"). _
            XValues = CurrentZiNorm
        Charts(CurrentChart).SeriesCollection("Upper 95% PI"). _
            Values = CurrentUpperPINorm
            
        Charts(CurrentChart).SeriesCollection("Lower 95% PI"). _
            XValues = CurrentZiNorm
        Charts(CurrentChart).SeriesCollection("Lower 95% PI"). _
            Values = CurrentLowerPINorm
    
    'Format the chart
            
        Call FormatNormQuantilePlot(CurrentOutputProperty, CurrentManufacturer, _
            CurrentNominalThickness, CurrentDirection, CurrentStressState)
        
    'Add the summary table to the chart
    
        On Error Resume Next
        
        CurrentAD_ValueAdjNorm = CurrentAD_ValueAdjNorm.Value
        CurrentP_ValueNorm = CurrentP_ValueNorm.Value
    
        CurrentSampleSize = WorksheetFunction.Count(CurrentYiNorm)
        CurrentAD_Value = CurrentAD_ValueAdjNorm
        CurrentP_Value = CurrentP_ValueNorm
        
        On Error GoTo 0
    
        Call AddSummaryTableToChart(CurrentChart, CurrentSampleSize, _
            CurrentAD_Value, CurrentP_Value)
            
    'Move chart to the correct location
            
        Application.Workbooks(CurrentChartOutputFilename).Charts(CurrentChart). _
            Location Where:=xlLocationAsObject, Name:=CurrentOutputProperty & " (Q-Q)"
            
        With Sheets(CurrentOutputProperty & " (Q-Q)").ChartObjects("Chart 1")
            
            .Left = 0
            .Top = 0
            .Width = 360
            .Height = 800
        
        End With
        
    'Correct the position of the legend
        
        With ActiveChart
        
            .Legend.Top = 40
            .Legend.Left = 30
            .Legend.Width = 310
            .Legend.Height = 15
            .ChartArea.Border.Weight = xlMedium
        
        End With
        
    'End of normal Q-Q plot
```
The error message occurs at the line


```
Application.Workbooks(CurrentChartOutputFilename).Charts(CurrentChart). _
            Location Where:=xlLocationAsObject, Name:=CurrentOutputProperty
```
I've tried all possible methods of referencing the chart I'm trying to move so I'm satisfied that the problem is not with the reference. I tried recording the macro to ensure the arguments are the same for Excel 007 as they are for Excel 2003 and they are. The odd thing about it is that te chart does actually move to the correct location.


----------



## RSpecianjr (Jan 20, 2010)

Hey ExcelentEnginer,

I am not that great with charts, but I have a question.

If the charts are being created and moved with the macro, then why not just create them where they will end up? Seems like it would save you some time and possibly some frustration.

Though, I have had to put some things into my macros that seemed unneccesary to others.

Regards,

Robert D. Specian Jr.


----------



## ExcelentEnginer (Oct 2, 2008)

RSpecianjr said:


> Hey ExcelentEnginer,
> 
> I am not that great with charts, but I have a question.
> 
> ...


Thanks for taking the time to respond to my question. When I wrote the macro originally, I did try to generate the chart in its final location but for some reason it just didn't want to do it. An error would occur when the chart was being generated. I trawled the forums but never actually found a solution so the easiest workaround was to let Excel generate the chart where it wanted to generate it then move it to its final location. I did try generating the chart in its finished location again before posting this to see if it was perhaps some issue with Excel 2003 but got the same error message as I used to get when I was writing the macro in Excel 2003.


----------



## RSpecianjr (Jan 20, 2010)

Hey ExcelentEnginer,

Hmm, okay, can you post the workbook? It helps us out a lot when we have an actual workbook to play around with.

Thanks,

Robert D. Specian Jr.


----------



## ExcelentEnginer (Oct 2, 2008)

RSpecianjr said:


> Hey ExcelentEnginer,
> 
> Hmm, okay, can you post the workbook? It helps us out a lot when we have an actual workbook to play around with.
> 
> ...


Thanks for your offer but I don't think my workbook will work on your computer. The routine makes reference to several different workbooks in several different folders on my computer so I'd have to send you the entire folder. 

Anyhow, I found a way around the problem. Part of my macro involved opening a series of workbooks to retrieve data. My initial difficulty was that in Excel 2003, my macro used to randomly halt after opening a workbook. No error message or any info about what caused the halt was given. There was also no consistency as when it halted other than it was always after a workbook had been opened. It could be attempting to open the first workbook or it could complete several hundred before halting. Initially, this was not a problem as the workbook took no longer than 10 minutes to calculate so I could easily restart the macro and it wasn't a big deal. This macro outputs results to a text file, which is then read into ANSYS as a input.

I have since moved on and am now building a macro to process output from ANSYS, which involves opening and retrieving data from 5000+ text files, which could take several hours to complete. I want to be able to start the macro and let it run overnight without worrying that potentiall, it could stop ten minutes after I leave. I decided that a permanent fix to the problem was badly needed. Otherwise, I could loose alot of time because of this problem. My ANSYS output processing macro is only in its early stages so it runs fine in Excel 2007. I noticed my macro for processing the ANSYS output didn't halt when it was run in Excel 2007 so I though the best approach was to upgrade everything to Excel 2007 rather than generating inputs for ANSYS in Excel 2003 and processing the output in Excel 2007. 

Yesterday, I was lucky enough to stumble upon a little known bug in the workbooks.open command in Excel 2003. Apparently, if you press the Shift button while Excel 2003 is executing the workbooks.open command, it halts the macro ecen if you have pressed the shift button while using another program. In other words, if I were on the Internet or typing an e-mail or anything that involves frequently pressing the shift button, invariable you'd press it during one of the executions of the workbooks.open command and Excel would halt. This is a problem that has obviously been rectified in Excel 2007. I found a simple Function procedure that when inserted before the workbooks.open command prevents the shift button interfering with the macro execution.

So, very long story short, all my macros now run with 100% reliability in Excel 2003 so I don't need to upgrade to Excel 2007. This means I can use the one version of Excel to generate inputs and process outputs from ANSYS. I'm trying to finish a PhD at the moment so I could really do without spending alot of time upgrading my macros to run in Excel 2007. 

Thanks for your help :smile:


----------

