PDA

View Full Version : Creating Charts using VBA



iWiishh
11-23-2011, 05:25 AM
Hi! I need a little help with something. Using VBA to macro some excel data.
I want to create a chart using arrays created in Visual Basics, instead of from the cell range i excel.

What i'm refering to is the part in bold and dark red. It's actually a range of cell values in excel i assume, but the thing is that I've got different sets of data, where this specific data type is not always in the same position in the excel sheet. So I cant specify a set of range. So what i did was to convert that specific data type to an array, but I have no idea if I can create a chart, using that set of array.

Btw, i'm pretty new to programming, so my terms may not really be right :X

Example:
Sub AddChartSheet()
Dim chtChart As Chart
'Create a new chart.
Set chtChart = Charts.Add
With chtChart
.Name = "Tool Sales2"
.ChartType = xlColumnClustered
'Link to the source data range.
.SetSourceData Source:=Sheets("Sheet1").Range("A1:H5"), _
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "=Sheet1!R1C2"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
End With
End Sub


Ok i change the range from D5 to H5, coz with the colon it gives me =.=

Thanks alot!!

Aflatoon
11-23-2011, 05:46 AM
You can use arrays with charts, though you are limited in the amount of data you can use, since it gets hardcoded into the SERIES formula. You also can't use the SetSourceData method with arrays. See Andy's sample code here (http://www.ozgrid.com/forum/showthread.php?t=27955).

iWiishh
11-24-2011, 05:20 AM
oh! ok i managed to get it to work. Thanks alot!!