VBA and Charts

[dcanham]

Hi People,
Two things. First are there any tutorials out there on creating charts using VB that anyone has a link to? I'm starting to delve into this and its making my head hurt!!

Second, the code below creates a chart successfully (woohoo), however, how do I determine the chart size. It seems to be setting to a default on the target sheet? Also is there a list of all the 'ActiveChart' methods somewhere? The answer to this may answer the first question Thanks for any help.


' initializing parameters object
Set WS = Worksheets("Parameters")

' adding the chart
Charts.Add
ActiveChart.ChartType = xl3DBarClustered
ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"

' adding series
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "Fred1"
ActiveChart.SeriesCollection(1).Values = WS.Range(WS.Cells(3, 1), WS.Cells(12, 1))
ActiveChart.SeriesCollection(1).XValues = Array("Factor1", "Factor2", "Factor3", "Factor4", "Factor5", "Factor6", "Factor7", "Factor8", "Factor9", "Factor10")

 

[venkat1926]

QUESTION NO. 1

see this ulr

http://peltiertech.com/Excel/ChartsHowTo/index.html

 

[Jon Peltier]

Here are some specific pages that address the topic:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html
http://peltiertech.com/Excel/Charts/chartvba.html

 

[dcanham]

If anyone is interested the proper code for placing and sizing the chart ended up being.....

' adding the chart
Charts.Add
ActiveChart.ChartType = xl3DColumn
ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"

' setting chart size
With ActiveChart.Parent
.Left = 0
.Width = 600
.Top = 0
.Height = 350
End With

Thank you for the excellent links, they are helping

 

[Jon Peltier]

You can do this in fewer steps:

with sheets("Chart").chartobjects.add(0, 0, 600, 300).chart
    .charttype = xl3dcolumn
end with

I would also suggest using a better chart type, such as a 2D column. 3D effects might make a chart "look nice" to some people, but the 3D effects do not improve comprehension of the chart, and in fact generally make it harder to read actual values from the axes.

 

[dcanham]

ok neat...I didn't see that one on your site!! Thanks for the info and advice!

 

[dcanham]

followup question, what is that chart type for 2D called? I don't see in the list that pops up??

 

[Jon Peltier]

Use the tools in the VB Editor. View menu > Object Browser. Find Chart in the left hand list, click on ChartType in the right. At the bottom is says

Property ChartType As XlChartType

XlChartTypeis highlighted, click it to bring up the list of chart types, which include:

xlColumnClustered
xlColumnStacked