|
Subject:
|
Generating Charts with VBA
|
|
Posted By:
|
davidimurray
|
Post Date:
|
9/21/2006 2:13:43 AM
|
Hi
I'm currently writing some code in VBA to automatically create charts in excel but I'm running into some difficulties.
The script I'm creating is generic and is used to graph two columns. The number of rows can vary on each run but both columns will always have the same number. So far I have written the following :-
Public Sub makechart(datasheet As String, xaxis As String, yaxis As String, name As String)
Dim achart As Chart
Dim lastr As Integer
lastr = lastrow(datasheet)
Set achart = Charts.Add
With achart
.ChartType = xlXYScatter
'ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='All Data'!R2C7:R74C7"
ActiveChart.SeriesCollection(1).Values = "='All Data'!R2C8:R74C8"
End With
End Sub
The function lastrow simply returns the last row number. What I want to do is control the ranges for the x and Y values but I can't seem to get it to work. I'm looking for something like -
ActiveChart.SeriesCollection(1).XValues = "='datasheet'!xaxis &"2"::xaxis &lastr"
How do I actually script this?
Cheers
Dave
|
|
Reply By:
|
kotaiah
|
Reply Date:
|
9/21/2006 7:07:17 AM
|
Hey This Kotaiah , working L&T Infotech.
I did one application Leave Tracker. I want to make a chart that to dyanamically canges.
In sheet(2) i have data coloumns are
NAME DATEFROM DATETO REASON
how to write code for make a chart(any)
ASAP
Thanks&Regards
Kotaiah.K
|
|
Reply By:
|
jrogers
|
Reply Date:
|
9/21/2006 7:25:04 AM
|
kotaiah - do not hijack other peoples posts. If you have a question, post it in a new post
|
|
Reply By:
|
kotaiah
|
Reply Date:
|
9/21/2006 7:29:16 AM
|
ok. Pls give solution for my request.
|
|
Reply By:
|
jrogers
|
Reply Date:
|
9/21/2006 7:50:25 AM
|
you could try
activeChart.seriescollection(1).values = "='All Data'!R2C7:R" & lastr & "C7"
what is an example of xaxis -
|
|
Reply By:
|
davidimurray
|
Reply Date:
|
9/21/2006 8:04:08 AM
|
Hi
Got it sorted now after some help on another forum - completed code is :-
Public Sub makechart(datasheet As String, xaxis As String, yaxis As String, name As String)
Dim achart As Chart
Dim lastr As String
lastr = (lastrow(datasheet))
If Not IsNumeric(xaxis) Then xaxis = Range(xaxis & "1").Column
If Not IsNumeric(yaxis) Then yaxis = Range(yaxis & "1").Column
Charts.Add
ActiveChart.ChartType = xlXYScatter
'ActiveChart.SetSourceData Source:=Sheets("All Data").Range("A1:AV74"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='" & datasheet & "'!R2C" & xaxis & ":R" & lastr & "C" & xaxis
ActiveChart.SeriesCollection(1).Values = "='" & datasheet & "'!R2C" & yaxis & ":R" & lastr & "C" & yaxis
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "WR V WR top"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "WR"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Vr"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.PlotArea.Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Interior.ColorIndex = xlNone
End Sub
Cheers
Dave
|
