VBA and Charts

dcanham

Active Member
Joined
Jun 7, 2006
Messages
306
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")
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

dcanham

Active Member
Joined
Jun 7, 2006
Messages
306
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

You can do this in fewer steps:

Code:
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

Active Member
Joined
Jun 7, 2006
Messages
306
ok neat...I didn't see that one on your site!! Thanks for the info and advice!
 

dcanham

Active Member
Joined
Jun 7, 2006
Messages
306
followup question, what is that chart type for 2D called? I don't see in the list that pops up??
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Forum statistics

Threads
1,141,761
Messages
5,708,367
Members
421,566
Latest member
7Nabisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top