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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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
5,258
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
5,258
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,176,127
Messages
5,901,542
Members
434,899
Latest member
powerappsjoker99

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