Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Series Collection variables (excel 2003)

    If I have the following code :
    ActiveChart.SeriesCollection(2).XValues = "=Risk!R2C6:R3C6"

    What is the proper way to pass in a chart name variable?

    For example:

    Sub addsecondseries(ByVal sheetName As Variant, chartName As String)
    ...
    ActiveChart.SeriesCollection(2).XValues = chartName & "R2C6: R3C6"
    ...

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

    Re: Series Collection variables (excel 2003)

    Do you mean 'sheet name' instead of 'chart name'? If so, try
    <code>
    Dim strSheetName As String
    strSheetName = ...
    ActiveChart.SeriesCollection(2).XValues = "='" & strSheetName & "'!R2C6:R3C6"
    </code>
    The single quotes around the sheet name are necessary if the sheet name contains one or more spaces; they won't hurt otherwise.

  3. #3
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Series Collection variables (excel 2003)

    Yes thanks!

    Can you tell me the significance of the "='" and "'! part of R2C6:R3C6? I can never seem to think through these syntaxes, even with defining Range Names.

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

    Re: Series Collection variables (excel 2003)

    Let's say that strSheetName = "My Data". The expression
    <code>
    "='" & strSheetName & "'!R2C6:R3C6"
    </code>
    evaluates to
    <code>
    ='My Data'!R2C6:R3C6
    </code>
    i.e. to a formula that refers to range R2C6:R3C6 on the sheet named My Data. The = starts the formula, and ! is used as a separator between the sheet name and the cell reference in Excel formulas. R2C6:R3C6 is the "R1C1"-style reference to the range that is usually referred to as F2:F3.

    Another way to obtain the same result would be
    <code>
    ActiveChart.SeriesCollection(2).XValues = Worksheets(strSheetName).Range("F2:F3")
    </code>
    Here, we don't use a formula but the Excel VBA way to refer to a cell range on a sheet.

Posting Permissions

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