View Full Version : charting with vba

05-30-2008, 08:21 AM

I am trying to plot a few series on a chart.
Column A = Dates
Column B = Weight
Column C = Body Fat
Column D = Muscle Mass

I am a bit clueless on :
- how to handle the series through arrays
- plot the arrays in the chart

can someone point me in the right direction please?


Bob Phillips
05-30-2008, 09:01 AM
I have made quite a few assumptions, namely:

- you wanted a line chart
-weight would be in a weight unit such as kilos, but body feight and muscle mass would be a percentage, so I created a seconbdary axis for these,
you would want the fat and muscle mass to not overlap the weight, so as to read the chart better, so I set the max on the secondary scale to 1, even though the body fat and muscle mass should never go anywhere near these numbers

Option Explicit

Sub CreateChart()
Dim LastRow As Long
Dim sh As Worksheet

Set sh = ActiveSheet
LastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
With ActiveChart

.ChartType = xlLineMarkers

.SetSourceData Source:=sh.Range("A1:D" & LastRow)
.Location Where:=xlLocationAsObject, Name:=sh.Name
End With

With ActiveChart

.SeriesCollection(3).AxisGroup = 2
.SeriesCollection(2).AxisGroup = 2
With .Axes(xlValue, xlSecondary)

.TickLabels.NumberFormat = "0.00%"
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnit = 0.2
End With
.Axes(xlValue).MajorGridlines.Border.ColorIndex = 15
.Legend.Position = xlBottom

With .SeriesCollection(1)

With .Border
.ColorIndex = 57
.Weight = xlThick
.LineStyle = xlContinuous
End With

.MarkerBackgroundColorIndex = xlAutomatic
.MarkerSize = 9
End With

With .SeriesCollection(2)

With .Border
.ColorIndex = 3
.Weight = xlMedium
.LineStyle = xlContinuous
End With

.MarkerBackgroundColorIndex = 3
.Smooth = False
.MarkerSize = 7
End With

With .SeriesCollection(3)

With .Border
.ColorIndex = 4
.Weight = xlMedium
.LineStyle = xlContinuous
End With

.MarkerBackgroundColorIndex = 4
.MarkerSize = 7
End With
End With
End Sub

06-02-2008, 06:59 PM
sorry for the late reply.

thanks for the code!
trying it today! :)

Bob Phillips
06-03-2008, 01:47 AM
I thought you had forgotten this one, and after I went trhe extra for you :-)

Let us know how it goes.