CODEWITHSUNDEEP
arraysvbaexcelcharts
Mauro Augusto
Mauro Augusto

Reputation: 143

Line Chart using Arrays instead of Range

So I have a code that generates 3 One-Dimensional Arrays (30 elements each). First array has dates (x values) and other 2 arrays have numbers (1 is sales from company K 2 is sales from company M on the day). Now I want to use VBA to use these arrays as the series. I've come across a simple code and tried to use it for my purposes... but this is my first time using charts in vba and i never was great with making charts to begin with. Please help!

You can assume I have Date, Sales_K and Sales_M as arrays with indexes going from 1 to 30 (30 entries) 

Dim myChart As Chart

Dim mySeries As Series

Set myChart = Charts.Add

Set myChart = myChart.Location(Where:=xlLocationAsObject, Name:="Sheet1")

'Kinda lost here I dont know how to activate the chart or if theres an important difference between defining a "chart" and a "chartObject"

Set myChart = ActiveChart  

Set mySeries = myChart.SeriesCollection(1)


series.xvalues = Date

series.values = Sales_K 'How do I add Sales_M y value series??

Please keep it as simple as possible. I tried watching some youtube videos on charts but they start going into chartObjects using the "Parent" property of charts... all I want is to create a line chart with 2 lines and date as the x values by using arrays.

Upvotes: 0

Views: 153

Answers (1)

MacroMarc
MacroMarc

Reputation: 3324

You can add another series via the newSeries method as shown below. You can also re-use the same XValues if you wish.

A ChartObject is the placeholder object, and the Chart contains all the detail(simple way of putting it).

Public Sub makeChart()

  Dim ch As Chart
  Dim ws As Worksheet
  Dim arr1 As Variant, arr2 As Variant, arr3 As Variant
  Dim ser As Series, ser2 As Series

  Set ws = Sheet5
  Set ch = ws.chartObjects.Add(Left:=100, Top:=100, Width:=500, Height:=400).Chart
  ch.ChartType = xlLine

  arr1 = Array(10, 15, 12, 25, 35, 20)
  arr2 = Array(17, 33, 20, 45, 11, 40)
  'arr3 = Array("01/05/2016", "02/05/2016", "03/05/2016", "04/05/2016", "05/05/2016", "06/05/2016")
  arr3 = Array(DateValue("01/05/2016"), DateValue("02/05/2016"), DateValue("03/05/2016"), _
              DateValue("04/05/2016"), DateValue("05/05/2016"), DateValue("06/05/2016"))

  Set ser = ch.SeriesCollection.NewSeries
  ser.XValues = arr3
  ser.Values = arr1
  Set ser2 = ch.SeriesCollection.NewSeries
  ser2.Values = arr2

End Sub

Upvotes: 0

Related Questions