how to create chart by VBA coding?

Hi all,

I recorded a macro to add a chart, and altered the code shown as follows:

Sub newChart()
charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="User Preferences"
With ActiveChart
.SetSourceData Source:=Sheets("User Preferences").Range("A12").CurrentRegion, PlotBy:=xlColumns
.HasTitle = True
.ChartType = xlColumnClustered
.ChartTitle.Characters.Text = "Interactive Chart Analysis"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Countries"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Rating"
End With
End Sub

however, while 'Call newChart', error message shows method 'Add' is not found.

i've checked books and online information, but still couldn't solve it. can anyone help please?

Thanks
Fendic

Fendic it works for me, can you zip your excel file and attach to this post

i am afraid, the project is related to a huge database, and I don't think i am allowed to attach it together. + the code i wrote is exact the same as I posted. and I simply call the methed as:

Call newChart

Does it need to include some library or anything? i really got confused. Can anyone help pleeeeeeeeeeeeease?

You can test your code with new workbook, open new excel workbook, paste your macro code in vba editor, make sure you have text in cell A12

Test it and let me know

Sub newChart()
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.SetSourceData Source:=Sheets("Sheet1").Range("A12").CurrentRegion, PlotBy:=xlColumns
.HasTitle = True
.ChartType = xlColumnClustered
.ChartTitle.Characters.Text = "Interactive Chart Analysis"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Countries"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Rating"
End With
End Sub

Hi Anilsolipuram,

I tried in a new worksheet and it works. It seems the problem is the code generated by Macro in my project cannot find the source data. I tried activate the object worksheet in front of the Charts.Add as:

ActiveWorkbook.Sheets("User Preferences").Activate

and still doesn't work. i must be sth wrong with identifying the source data. Just don't know how....

oh btw, each time you reply my question as soon as i post it. I really really appreciate your help. Thanks

Hi Anilsolipuram,

i tried everything the same in a blank workbook, and it works brilliant. how come it's not working in my proj? i can't think of anything, can you?

Thanks
Fendic

Hi Anilsolipuram and everyone,

it's just sorted in the last 2 mins. i couldn't believe it's just because of losing a single word. the code should be as follows:

ThisWorkbook.charts.Add

or

ActiveWorkbook.charts.Add

maybe cos i just started to learn vba last month and only look for sth while needed, so that i would got stuck for such a small fault.

anyway, thank you Anilsolipuram and thank you everyone for you notice.

All the best
Fendic