Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Macro Works 3 Times Then Stalls

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Glasgow, Scotland
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I am new to Macro's & VBA so I could use any help or advice. When I run the macro it works 3 times then fails what am I doing wrong?

    Sub ShowChart()
    Calculate
    ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(1).Formula = _
    ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(1).Formula
    ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(2).Formula = _
    ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(2).Formula
    ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(3).Formula = _
    ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(3).Formula
    ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(4).Formula = _
    ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(4).Formula

    DialogSheets("Chart").Show

    End Sub



  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Alan,

    What do you mean when you say it "fails"? Do you get a run-time error? If so, is there an error message? Can you go into the debugger and find which line of code it is failing on? The answers to these questions would be helpful in diagnosing the problem.

    Also, in looking at your code, I'm wondering why you are setting each series' formula to itself. This should not actually accomplish anything.

    In addition, the use of Dialog sheets (as opposed to userforms) is no longer recommended in versions of Excel from 97 on up.

    It might be helpful if you would describe what you are actually trying to accomplish. There may be a better way.

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    NY
    Posts
    1,056
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Might I also add that you can make your code a LOT easier to read & debug by changing it to:
    Sub ShowChart()
    Calculate
    For i=1 to 4
    With ActiveSheet.ChartObjects("Chart 5").Chart
    .SeriesCollection(i).Formula = _
    .SeriesCollection(i).Formula
    End With
    Next
    DialogSheets("Chart").Show
    End Sub



  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Glasgow, Scotland
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the replys, like I said vb is all Russian to me. The chart is based on a large table of costs spread over a year there are 4 dataseries associated with each project and lots of projects. The chart uses relative range names for the title,x values & 4 data series as follows;
    =SERIES(IIS_S.xls!name3,IIS_S.xls!XLabels,IIS_S.xls!Dataseries3,3) etc.
    Now comes the interesting bit....when you create a new chart and put in the ranges as above it works 3 times then it stops with "Runtime error 1004" "Unable to set the formula property of the series class" (the code stops at the Series(4) lines of code). If I delete the chart and start again it works for 3 more times.
    Bob, I tried your code and it works but stops at same place as before. Help!

    Regards

    [ This Message was edited by: almar_dc on 2002-03-28 12:08 ]

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    Glasgow, Scotland
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anybody Help? This one is driving me nuts!

    Thanks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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