Excel 2003: VBA Syntax causes problems in Excel 2010 - Alternatives?

Question

I've got an old VBA-script (Office 2003) to fix which fails in Office 2010. The problem seems to be caused by the syntax used to reference sheets and cells.

ActiveChart.SeriesCollection(1).XValues = _
    "=EP!Z1S2:Z1S" & Mid(Str(Schritte + 2), 2)

    With ActiveChart.SeriesCollection(1)
       .Values = "=EP!Z3S2:Z3S" & Mid(Str(Schritte + 2), 2)
       .Name = "=EP!Z3S1"
    End With

Is there an alternative to using "=Sheet!CellRange"? Or can the problem be solved by changing some configuration in Office / Excel?

Yep. A VBA error message appears saying: "Runtime - Error 1004, Application- or object defined Error" ... ?:-| ... and the script stops. — cluster1
– cluster1, Commented Jul 15, 2015 at 8:29
Those cell references appear to be non-EN-US xlR1C1 style. What language pack is the 2010 under and what language pack was the 2003 under? — user4039065
– user4039065, Commented Jul 15, 2015 at 8:31
@Jeeped: It's an Integer asked via an input box. For example: 11 => "=EP!Z3S2:Z3S13". I think that's alright. The stopping of the script (in Excel 2010) seems caused by the used syntax. — cluster1
– cluster1, Commented Jul 15, 2015 at 8:42

user4039065 · Accepted Answer · 2015-07-15 10:54:36Z

1

You need to provide the cell ranges as coded Range objects and/or Range.Cells property, possibly with the Range.Resize property.

dim ws as worksheet
set ws = worksheets("EP")

ActiveChart.SeriesCollection(1).XValues = _
  ws.range("Z1S2:Z1S" & Mid(Str(Schritte + 2), 2))

With ActiveChart.SeriesCollection(1)
   .Values = ws.cells(3, 2).resize(1, int(Mid(Str(Schritte + 2), 2)))    '=EP!Z3S2:Z3S" & Mid(Str(Schritte + 2), 2)
   .Name = ws.cells(3, 1)    '=EP!Z3S1
End With

When I created a chart using strictly strings describing the ranges, I received Compile error: Type mismatch. As soon as I wrapped the strings in Range or referenced them more directly with .Cells the problem disappeared and the chart generated.

answered Jul 15, 2015 at 10:54

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

cluster1 Over a year ago

Thanks a lot for your effort and time. I really appreciate it.

Collectives™ on Stack Overflow

Excel 2003: VBA Syntax causes problems in Excel 2010 - Alternatives?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related