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
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 Display Modes
  #1 (permalink)  
Old July 17th, 2007, 05:27 PM
Registered User
 
Join Date: Jul 2007
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 2 related questions on Graphs

I am trying to make automate the creation of a graph that I have to create quite often and on many pages. I have not used Visual basic much but have taken a class on programing in qbasic so I do understand most of the more basic concepts but don't understand the more advanced stuff.

The Columns I use are always the same.
The only two things that I will change are what spreadsheet it the data is on and the upper and lower limits. The problem is that I can not seem to put any kind of a variable into the appropriate fields.

Here is the relevant code
Charts.Add
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "=('30030'!R2C2:R10000C2,'30030'!R2C6:R10000C6 )"
    ActiveChart.SeriesCollection(1).Values = "=('30030'!R2C3:R10000C3,'30030'!R2C7:R10000C7 )"
    ActiveChart.SeriesCollection(1).Name = "=""Series"""
    ActiveChart.SeriesCollection(2).XValues = "={0,600}"
    ActiveChart.SeriesCollection(2).Values = "={2,2}"
    ActiveChart.SeriesCollection(2).Name = "=""Upper limit"""
    ActiveChart.SeriesCollection(3).XValues = "={0,600}"
    ActiveChart.SeriesCollection(3).Values = "={0,0}"
    ActiveChart.SeriesCollection(3).Name = "=""Lower limit"""
    ActiveChart.Location where:=xlLocationAsObject, Name:="30030"

The Name of the sheet is 30030

The other question is how do you move a graph to a location in a spreadsheet. I have seen a few bits of code that supposedly do this but none have worked.

I am using Microsoft Excel 2003 SP2 and Microsoft Visual Basic 6.3 if it makes any difference.

Thanks in advance for any help.

Reply With Quote
  #2 (permalink)  
Old July 22nd, 2007, 10:35 AM
Registered User
 
Join Date: Jul 2007
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try the following VBA Code in a procedure. It will position and size your chart, get the values for your chart, set the scale for the y variable, format the gridlines and plot area.


    Dim ws As Worksheet

    Set ws = Sheets("SHIPMENTS")
    ws.ChartObjects.Delete

    Dim co As ChartObject
    Set co = ws.ChartObjects.Add(1, 1, 1, 1)

    'Position and size the chart

    Dim RngToCover As Range
    Set RngToCover = ws.Range(ws.Cells(20, 1), ws.Cells(20 + 20, 9))
    co.Height = RngToCover.Height
    co.Width = RngToCover.Width
    co.Top = RngToCover.Top
    co.Left = RngToCover.Left

    'Chart type line
    co.Chart.ChartType = xlLine


    With co.Chart.SeriesCollection.NewSeries 'Series 1
        .Name = "Shipments"
        .Values = ws.Range("SHIPMENTS") ' Range name on worksheet with Y variable
        .XValues = ws.Range("DATE") ' Range name on worksheet with X variable
    End With

    'Set chart Title, and Y variable Scale


    With co.Chart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Shipments"
        .ChartTitle.Characters.Font.Size = 20
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Dollars"
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Font.Size = 12
        With .Axes(xlValue)
            .MinimumScale = 0
            .MaximumScale = 100000
            .MinorUnit = 1000
            .MajorUnit = 10000
            .MajorTickMark = xlCross
            .MinorTickMark = xlInside
            .TickLabelPosition = xlNextToAxis
        End With
    End With

    'Format gridline and plotArea

    co.Activate

    With co.Chart.Axes(xlValue).MajorGridlines.Border
        .ColorIndex = 57
        .Weight = xlHairline
        .LineStyle = xlDot
    End With

    co.Chart.PlotArea.Interior.ColorIndex = xlNone

If you chart data ranges change, consider making them dynamic ranges.




Reply With Quote
Reply


Thread Tools
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
book related questions here jminatel BOOK: Professional JavaScript for Web Developers ISBN: 978-0-7645-7908-0 1 January 30th, 2013 06:52 PM
Book related questions here jminatel BOOK: Professional Ajax ISBN: 978-0-471-77778-6 0 September 27th, 2006 11:02 AM
line graphs goldablasha C# 1 April 6th, 2005 02:36 AM
Graphs jieli1001 ASP.NET 1.0 and 1.1 Professional 4 August 1st, 2003 06:23 PM



All times are GMT -4. The time now is 09:17 AM.


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