Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100

    Unanswered: Formatting a chart in VB

    I'm not sure if this belongs in Excel or VB, but I think Excel people may be better equipped to answer it.

    I've got a sub in VB that exports data into an Excel spreadsheet then builds a chart based on that data. Excel is building the chart fine, but the chart is meant to look exactly like an older legacy report from Access so each data series has to be formatted.

    What I'm trying to do is change the series marker to xlX, which works in VBA...works in VB when I hard code it...but doesn't work when I pass it to this sub:

    Code:
    Public Sub FormatSHChart(xlSheetF As Excel.Worksheet, xlChart As Chart, chartNum As Integer, _
        seriesNum, colIndex As Integer, bgColor, fgColor As Integer, _
        Marker As String, markerSize As Integer)
    
        With xlSheetF.ChartObjects(chartNum).Chart
            With .SeriesCollection(seriesNum)
                With .Border
                    .ColorIndex = colIndex
                    .Weight = xlMedium
                    .LineStyle = xlContinuous
                End With
                .MarkerBackgroundColorIndex = bgColor
                .MarkerForegroundColorIndex = fgColor
                .MarkerStyle = Marker
                .markerSize = markerSize
            End With
        End With
        End Sub
    Here's how I call it:

    Code:
    Call FormatSHChart(xlSheet, xlChart(2), 2, 1, 39, 39, 1, xlX, 5)
    It works with line markers like xlTriangle and xlDiamond, but not xlX.

    Error message is: 'Run-time error '1004': Unable to set the MarkerStyle property of the Series class'

    I googled this but found nothing helpful. Help!

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi disrutivehair

    Having a rummage in Excel, I think it fails because there is no marker typ X and, therefore, no constant xlX.

    Also the xl... consant are integers not string ('though vb dose coerce data types if it can), ie xlTriange = 3 and xlSquare = 1 etc.

    HTH

    MTB

  3. #3
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by MikeTheBike
    Hi disrutivehair

    Having a rummage in Excel, I think it fails because there is no marker typ X and, therefore, no constant xlX.

    Also the xl... consant are integers not string ('though vb dose coerce data types if it can), ie xlTriange = 3 and xlSquare = 1 etc.

    HTH

    MTB
    The constant xlX works in VBA.

    I don't know where to find the integer values of these constants.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi agian

    After further rummaging, Yes xlX does exist as a member of the XlErrorBarDirection Class. Its value is -4168.

    There is also a constant xlMarkerStyleX which is a member of the XlMarkerStyle Class (suprise, suprise), which also has a value of -4168.


    It would seem the cause of your error (as suggested) is the variable type decraration, as I also get this error using a string varable declaration, but works OK with integer declared variable when either of the above constants are passed as an argument.

    To find the values of the consants either

    1) put msgbox xlX in code or

    2) use the object browser (type in the constant in the search box), this also list all other contant (or objects etc) in the Class.

    HTH

    MTB

Posting Permissions

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