how to create chart by VBA coding?

[Fendic]

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

 

[anilsolipuram]

Fendic it works for me, can you zip your excel file and attach to thi
pos

 

[Fendic]

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?

 

[anilsolipuram]

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

 

[Fendic]

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

 

[Fendic]

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

 

[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