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

    plot range and worksheet conflict? (vb 6.3 excel 2003)

    For an active chart cht and worksheet wsh, I'm having trouble when I use a range like "sheet1!a1:a722,sheet1!b1:b722" . I get a range failure, and I think it's because the range points to a different sheet than the active sheet. I must be able to point to a range without the active work sheet coming into play, since the work sheet is specified directly in the range. How can I set up a line to add a series to plot without calling an active sheet? When I record a macro and type in "sheet1!a1:a722,sheet1!b1:b722", the syntax comes back with ActiveChart.SeriesCollection.Add Source:=Sheets("sheet2").Range( "A1:A722,B1:B722").

    Said another way,I want to be able to specify my ranges as "sheet1!a1:a722,sheet1!b1:b722" ahead of time, and then not have to deal with an active sheet in the syntax below. Is that possible? I.e., if the sheet name can be directly included in the range, why do I need bother specifying the active sheet? See code below, one that works, one that doesn't.

    ' This doesn't work:


    For seriesnum = 2 To numsheets Step 1

    .SeriesCollection.Add Source:=wsh.Range(xyRange(seriesnum)), _
    Rowcol:=xlColumns, SeriesLabels:=True, CategoryLabels:=True, Replace:=False

    ' Create reference to the new series:
    Set ser = .SeriesCollection(.SeriesCollection.Count)
    'Point to the primary axis:
    ser.AxisGroup = xlPrimary
    'Other series plot attributes:
    ser.MarkerStyle = xlNone
    ser.Smooth = True
    ser.Shadow = False
    ser.Border.ColorIndex = colorarray(seriesnum)
    ser.Border.Weight = xlThick
    ser.Border.LineStyle = xlContinuous
    Next





    'This does work:

    For seriesnum = 2 To numsheets Step 1

    thissheet = "sheet" & Format$(seriesnum)

    .SeriesCollection.Add Source:=Sheets(thissheet).Range(xyRange(seriesnum) ), _
    Rowcol:=xlColumns, SeriesLabels:=True, CategoryLabels:=True, Replace:=False


    ' Create reference to the new series:
    Set ser = .SeriesCollection(.SeriesCollection.Count)
    'Point to the primary axis:
    ser.AxisGroup = xlPrimary
    'Other series plot attributes:
    ser.MarkerStyle = xlNone
    ser.Smooth = True
    ser.Shadow = False
    ser.Border.ColorIndex = colorarray(seriesnum)
    ser.Border.Weight = xlThick
    ser.Border.LineStyle = xlContinuous
    Next

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

    Re: plot range and worksheet conflict? (vb 6.3 excel 2003)

    Notation such as Sheet1!A1:A722 is the way you refer to ranges in a formula. It is *not* valid in VBA; you need to use Sheets("Sheet1").Range("A1:A722") there.

    Your first code sample doesn't include the definition of wsh, so I have no way of telling whether it's correct or not.

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

    Re: plot range and worksheet conflict? (vb 6.3 excel 2003)

    Thanks once again Hans.

    Note that wsh is "ActiveSheet". Also, the formula range syntax works, as long as the active sheet agrees with the sheet name in the range syntax. That's partly what prompted me to ask about this. Also, in excel, when you add data to a chart, and you pick a new sheet in the process, the range field automatically populates with the sheetname! before the new range. That's why I figured must be you can include sheet names right in the range variable. But then the conflict with the active sheet occurred, and hence my question.

    So, if I understand correctly, I need to handle sheet names and ranges separately, and I'll rework my code.

    Kent

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

    Re: plot range and worksheet conflict? (vb 6.3 excel 2003)

    You can't use something like Worksheets("Sheet1").Range("Sheet2!A1:A10"), if makes no sense if you look at it carefully.

    You can, however, use Application.Range("Sheet2!A1:A10") and even Application.Range("[MyWorkbook.xls]Sheet2!A1:A10"). I wouldn't recommend it, though. I prefer using Worksheets("Sheet2").Range("A1:A10") and Workbooks("MyWorkbook.xls").Worksheets("Sheet2").R ange("A1:A10"). Of course, you can use object variables for each of the parts:

    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim rng As Range
    Set wbk = Workbooks("MyWorkbook.xls")
    Set wsh = wbk.Worksheets("Sheet2")
    Set rng = wsh.Range("A1:A10")

Posting Permissions

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