ExcelBanter - Making charts

I usually just record a macro and create variable for the things that need to
be changed. Strings can be used to create the Xvalues and ranges based on
your program flow.

recorded macro for Line chart and XY scatter:

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("FIRST TABLE").Range("A3:B22"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "='FIRST TABLE'!R3C1:R22C1"
ActiveChart.SeriesCollection(2).XValues = "='FIRST TABLE'!R3C1:R22C1"
ActiveChart.Location Whe=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "test"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "stuff"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "other stuff"
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.HasDataTable = False
Sheets("FIRST TABLE").Select

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("FIRST TABLE").Range("A3:B22"), _
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "title"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "stuff"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "otherstuff"
End With

This is an example of what I had done for a macro to create a large amount
of charts. The data charted was determined by another sub which found the
top and bottom cells of of data. I build the string using variables for
column number ,row number and chart name. This script actually modified a
generic chart that had already been created. I found it easier to do it that
way, I could use a previous macro to create 4 identical charts neatly spaced
on a worksheet and then call the subroutine to modify the charts to suit the
data required for that page.

Dim name1

activescreenupdating = False
Dim v11
Dim v12
Dim v31
Dim v32
Dim v33
Dim v21
Dim v22
Dim v23
Dim v41
Dim v42
Dim v43
Dim v44
Dim v45
Dim count
Range("z2").Select
Selection.Formula = "=Combined!D1"
count = Selection.Value
Dim bottom
Dim combined
combined = "=combined!r"
Dim mid
mid = ":r"
Dim cend
cend = "c"
v11 = 11
v12 = 15
v31 = 9
v32 = 10
v21 = 22
v22 = 23
v23 = 24
v41 = 17
v42 = 18
v43 = 19
v44 = 20
v45 = 21
name1 = 3

bottom = 67 + (count - 2) * 46
Top = 101 + (count - 2) * 46

'assemble target cell ranges using variables, reusing variable names to make
programming easier
v11 = combined & bottom & cend & v11 & mid & Top & cend & v11
v12 = combined & bottom & cend & v12 & mid & Top & cend & v12
v31 = combined & bottom & cend & v31 & mid & Top & cend & v31
v32 = combined & bottom & cend & v32 & mid & Top & cend & v32
v21 = combined & bottom & cend & v21 & mid & Top & cend & v21
v22 = combined & bottom & cend & v22 & mid & Top & cend & v22
v23 = combined & bottom & cend & v23 & mid & Top & cend & v23
v41 = combined & bottom & cend & v41 & mid & Top & cend & v41
v42 = combined & bottom & cend & v42 & mid & Top & cend & v42
v43 = combined & bottom & cend & v43 & mid & Top & cend & v43
v44 = combined & bottom & cend & v44 & mid & Top & cend & v44
v45 = combined & bottom & cend & v45 & mid & Top & cend & v45

name1 = combined & bottom & cend & name1 & mid & Top & cend & name1

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Values = [v11]
ActiveChart.SeriesCollection(2).Values = [v12]
ActiveChart.SeriesCollection(1).XValues = [name1]
ActiveChart.SeriesCollection(2).XValues = [name1]
ActiveSheet.Select

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).Values = [v21]
ActiveChart.SeriesCollection(2).Values = [v22]
ActiveChart.SeriesCollection(3).Values = [v23]
ActiveChart.SeriesCollection(1).XValues = [name1]
ActiveChart.SeriesCollection(2).XValues = [name1]
ActiveChart.SeriesCollection(3).XValues = [name1]
ActiveSheet.Select

ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Values = [v31]
ActiveChart.SeriesCollection(2).Values = [v32]
ActiveChart.SeriesCollection(1).XValues = [name1]
ActiveChart.SeriesCollection(2).XValues = [name1]
ActiveSheet.Select

ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Values = [v41]
ActiveChart.SeriesCollection(2).Values = [v42]
ActiveChart.SeriesCollection(3).Values = [v43]
ActiveChart.SeriesCollection(4).Values = [v43]
ActiveChart.SeriesCollection(1).XValues = [name1]
ActiveChart.SeriesCollection(2).XValues = [name1]
ActiveChart.SeriesCollection(3).XValues = [name1]
ActiveChart.SeriesCollection(4).XValues = [name1]
ActiveChart.SeriesCollection(5).Values = [v45]
ActiveChart.SeriesCollection(5).XValues = [name1]
ActiveSheet.Select

good luck.

"Fabrizio" wrote:

I am writing a macro that generates several chart from info that is placed in
a worksheet. The code I am writing has to be very dynamci so I cannot record
a macro etc. I want to create several chart types first e.g. one type of
chart that has some settings and borders etc. I then want to make e.g. three
charts (with different input) of one type of chart and four of another type.
Does anyone hve any idea how to make this? I know it is a big question but
please please help me! I am not very good a visual basic so please explain if
necessary...Thank you!