Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 21st, 2006, 02:13 AM
Registered User
 
Join Date: Sep 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Generating Charts with VBA

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 With Quote
  #2 (permalink)  
Old September 21st, 2006, 07:07 AM
Authorized User
 
Join Date: Sep 2006
Location: , , .
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 With Quote
  #3 (permalink)  
Old September 21st, 2006, 07:25 AM
Authorized User
 
Join Date: Mar 2006
Location: London, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

kotaiah - do not hijack other peoples posts. If you have a question, post it in a new post

Reply With Quote
  #4 (permalink)  
Old September 21st, 2006, 07:29 AM
Authorized User
 
Join Date: Sep 2006
Location: , , .
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

ok. Pls give solution for my request.

Reply With Quote
  #5 (permalink)  
Old September 21st, 2006, 07:50 AM
Authorized User
 
Join Date: Mar 2006
Location: London, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default



you could try
Code:
activeChart.seriescollection(1).values = "='All Data'!R2C7:R" & lastr & "C7"
what is an example of xaxis -

Reply With Quote
  #6 (permalink)  
Old September 21st, 2006, 08:04 AM
Registered User
 
Join Date: Sep 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Web Query Generating Error 1004 bob135 Excel VBA 1 May 28th, 2009 07:31 PM
Problems generating Charts with iReport2.0.0 Jenal J2EE 0 August 13th, 2007 06:20 AM
Generating report via VBA in Access gurkie Access 2 July 16th, 2006 02:16 PM
Charts by VBA jaymur BOOK: Expert One-on-One Access Application Development 7 June 8th, 2006 12:59 AM
Generating Squentila numbers in VBA Shabir Hussain Excel VBA 1 June 27th, 2005 06:20 AM



All times are GMT -4. The time now is 02:57 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.