Excel VBA error: Method Add of ChartObjects failed

I'm using Excel 2007 trying to create a macro to draw a chart. Using Record Macro doesn't work. So have tried many attempts at writing one, now even the suggestion in Excel Help gives an error.
Method 'Add' of 'ChartObjects' failed.

Here's the code:

Dim ch As ChartObject
Set ch = Worksheets("Sheet1").ChartObjects.Add(100, 30, 400, 250)
ch.Chart.ChartWizard Source:=Worksheets("Sheet1").Range("c1:f20"), _
Gallery:=xlLine, Title:="New Chart"

From similar links I've made sure that Sheet protection is off and there is no printer enabled.
Hope someone can help solve this.

It appears to me as an office automation issue try to take help from the codes which are given below:

Code:

---

            object missing = Type.Missing;
            Excel.Application myApp= new Microsoft.Office.Interop.Excel.Application();
            myApp.Visible = true;
            myApp.Workbooks.Add(missing);
            Excel.Worksheet ws = (Excel.Worksheet)myApp.ActiveSheet;
            Excel.ChartObjects chartobjects = (Excel.ChartObjects)ws.ChartObjects(missing);
            Excel.ChartObject chartobject = (Excel.ChartObject)chartobjects.Add(10 /*Left*/, 100 /*Top*/, 600 /*Width*/, 400 /*Height*/);
            Excel._Chart chart = (Excel._Chart)chartobject.Chart;

---

Hi Kelfro,
The code I assume is external to the application, something that calls Excel. Not quite sure where to embed it. What I was looking for was some solution to the problem of creating a chart within a macro. A VBA solution. I can accept that the problem maybe a Microsoft Office problem associated with Excel, but not sure where to go from there.
I tried assembling the code into the macro, but as it isn't VBA code most of the lines errored and trying to fix those errors just made more errors and the code ended up not resembling what you had submitted. For instance
VBA doesn't understand Type missing --> it expects an expression etc ending with an End Type.
Any further thoughts will be gratefully received.
Thanks,
T_J.