PDA

View Full Version : vba chart series formula property



voyagerphoen
01-14-2019, 08:12 AM
Sub Ohh(chtname As String, chtsheet As String, chttitle As String, chtrangex As Range, chtrangey As Range, chtrangey1 As Range, chtrangey2 As Range, chtxaxistitle As String, chtyaxistitle As String)
Dim OlaCab1 As Series
Dim olacab As SeriesCollection
On Error GoTo ErrorHandling


With Charts(chtname)

.ChartType = xlLine
.HasTitle = True

.ChartTitle.Text = chttitle
Set OlaCab1 = olacab.NewSeries
ActiveChart.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = chtrangex
Activechart.seriescollection.formula= "=series(""jan"",chtrangex,chtrangey,1)"


'ActiveChart.SeriesCollection.NewSeries
'.SeriesCollection(3).XValues = chtrangex

'ActiveChart.SeriesCollection.NewSeries
'.SeriesCollection(3).XValues = chtrangex


Set olacab = ActiveChart.SeriesCollection
' For Each OlaCab1 In olacab
' OlaCab1.Name = Sheets("Sheet1").Cells(1, i + 2)
' i = i + 1
' Next OlaCab1

.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = chtxaxistitle
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = chtyaxistitle
End With
Exit Sub


ErrorHandling:
MsgBox "No Chart found create one?"
Charts.Add
ActiveChart.Name = chtname
Resume
End Sub


Sub CallOhh()
Dim chtrangex As Range, chtrangey As Range, chtrangey1 As Range, chtrangey2 As Range
Set chtrangex = Sheets("Sheet1").Range(Sheets("Sheet1").Range("A2"), Sheets("Sheet1").Range("A2").End(xlDown))
Set chtrangey = Sheets("Sheet1").Range(Sheets("Sheet1").Range("B2"), Sheets("Sheet1").Range("B2").End(xlDown))
Set chtrangey1 = Sheets("Sheet1").Range(Sheets("Sheet1").Range("C2"), Sheets("Sheet1").Range("C2").End(xlDown))
Set chtrangey2 = Sheets("Sheet1").Range(Sheets("Sheet1").Range("D2"), Sheets("Sheet1").Range("D2").End(xlDown))
Call Ohh("Chart1", "Sheet1", "Fruits sales", chtrangex, chtrangey, chtrangey1, chtrangey2, "Fruits", "Sales")
End Sub







Here in this code i have declared set set ranges chtrangex and chtrangey for 1st serie(i have shown only ist serie here).and then i used activechart.seriescollection(1).formula = ........so many ways but the declared ranges seems to not working in the code above. It is even not working in the line seriescollection(1).xvalues = chtrangex. please help. But i know how to successfully create chart like using seriescollection.add property.My data is in sheet1.




jan
feb
mar


apple
208
215
239


bannana
272
156
102


grapes
124
82
112


orange
214
69
139


cherry
278
91
285


strawberry
235
291
257


jackfruit
221
287
84


peers
292
175
90

voyagerphoen
01-16-2019, 06:16 AM
I found a solution for this code i.e use address function with range variables i.e.
chtrangex.address(,,,true)......in the formula string.Any way if others have more thoughts please reply.

mana
01-16-2019, 07:28 AM
SetSourceData method


Option Explicit


Sub test()
Dim cht As Chart
Const chtname = "Chart1"
Const chtsheet = "Sheet1"
Const chttitle = "Fruits sales"
Const chtxaxistitle = "Fruits"
Const chtyaxistitle = "Sales"
Dim chtrange As Range

Set chtrange = Sheets(chtsheet).Cells(1).CurrentRegion

On Error Resume Next
Set cht = Charts(chtname)
On Error GoTo 0
If cht Is Nothing Then
Set cht = Charts.Add
cht.Name = chtname
End If

With cht
.ChartType = xlLine
.HasTitle = True
.ChartTitle.Text = chttitle

.SetSourceData chtrange

.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = chtxaxistitle
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = chtyaxistitle
End With

End Sub