Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2008
    Location
    Jamestown, Worcestershire, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    excel plot series attributes don't stay put (vb 6.3 excel 2003)

    The macro below plots a series of data. I set the line color index and the line weight to what I want, and it shows up. But when the macro finishes, these both go back to some default. Any idea why?

    Also, it seems that for every formatting command, excel completely refreshes the plot, which takes forever. Can this be turned off, so that the plot graphics are not updated until the whole thing is done?



    Sub Single_series()
    '
    '
    Dim wsh As Worksheet
    Dim ser As Series
    Dim sheetname As String
    Dim cht As Chart

    Dim chart_name As String
    Dim chart_title As String

    Dim xaxis_name As String
    Dim y1axis_name As String
    Dim y2axis_name As String

    Dim xscalemin As Long
    Dim xscalemax As Long

    Dim y1scalemin As Long
    Dim y1scalemax As Long
    Dim y2scalemin As Long
    Dim y2scalemax As Long

    Dim xyRange(10) As String
    Dim seriesnum As Integer

    '
    '
    ' Keyboard Shortcut: Ctrl+w
    '
    ' Define the data range on each sheet that you want plotted by editing the
    ' following plotrange definitions. Then, below, you'll edit the plotting
    ' controls that determine which axis each of these ranges gets plotted on
    ' primary, or secondary.

    xyRange(1) = "a1:b30001"
    xyRange(2) = "c1:c30001"
    xyRange(3) = "d1:d30001"
    xyRange(4) = "e1:e30001"
    xyRange(5) = "f1:f30001"
    xyRange(6) = "g1:g30001"
    xyRange(7) = "h1:h30001"
    xyRange(8) = "i1:i30001"
    xyRange(9) = "j1:j30001"


    ' Set up the chart names - title, x axis, y axis by editing the following
    ' definitions:

    chart_name = "new chart sheet"
    chart_title = "Whole test events" & Chr(10) & "Pin angle and temperature" & _
    Chr(10) & "vs. time"
    xaxis_name = "Time (Sec)"
    y1axis_name = "Pin angle (deg)/Engine speed (RPM)"
    y2axis_name = "Pin temperatures "


    ' Set up the axes ranges by editing the following:

    xscalemin = 0
    xscalemax = 1100
    y1scalemin = -7000
    y1scalemax = 7000
    y2scalemin = 30
    y2scalemax = 160

    ' Prepare the data ranges to add to a chart:
    Set wsh = ActiveSheet
    Set cht = ActiveWorkbook.Charts.Add

    With cht '************************************************* *********

    'Set the chart type for the primary axis:
    .ChartType = xlXYScatter
    'Note that without this, you get the row count on the x axis ,instead
    'of the column 1 values.

    'Establish the first range, so that a secondary axis can be added later.
    .SetSourceData Source:=wsh.Range(xyRange(1)), PlotBy:=xlColumns

    Set ser = .SeriesCollection(.SeriesCollection.Count)
    ser.MarkerStyle = xlNone
    ser.Smooth = True
    ser.Shadow = False
    ser.Border.colorindex = 5
    ser.Border.Weight = xlThick
    ser.Border.LineStyle = xlAutomatic


    ' Place chart on a separate sheet.
    .Location Where:=xlLocationAsNewSheet, Name:=chart_name

    End With '************************************************* *********

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: excel plot series attributes don't stay put (vb 6.3 excel 2003)

    The line

    ser.Border.LineStyle = xlAutomatic

    resets the line to Excel's standard settings. You can omit this line, or set the linestyle to a specific option, e.g.

    ser.Border.LineStyle = xlDot

  3. #3
    New Lounger
    Join Date
    May 2008
    Location
    Jamestown, Worcestershire, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    chart redraw is painfully slow

    Hans,

    Now, it seems I'm down to one remaining irritation - the chart redraws after each line of code, making it painfully slow with 30000 rows by 10 columns. Any idea how to make it complete the entire chart before drawing it?

    I'd be happy to share my plotting macro once I've got it polished.

    About the sereis formatting, I now have series format working, but I am frustrated with the logic. Since Line weight, color, and style are different entities, I would not have expected style to reset color and weight. Style is just dashed, solid, etc., so why would an automatic style wipe out the previously defined color and weight? Anyway, it works, so I can move on.



    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: chart redraw is painfully slow

    xlAutomatic is not a specific style like xlContinuous or xlDash, it means, "let Excel make the decisions for you, overriding all customized settings".

    You could insert a line

    Application.ScreenUpdating = False

    at the beginning of the macro, and

    Application.ScreenUpdating = True

    at the end. This should speed the code up since it doesn't have to redraw the screen after each line.

  5. #5
    New Lounger
    Join Date
    May 2008
    Location
    Jamestown, Worcestershire, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: chart redraw is painfully slow

    Hans,

    Once again, you've completely eliminated my troubles, and I can move ahead. I've attached my macro for reference, since I think it has things in it many folks could use. Appended to the bottom of the macro is a formatting reference table for colors etc.

    Many thanks again,
    Kent
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: chart redraw is painfully slow

    Thanks for posting the code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •