Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    los angeles, California, USA
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    excel vba stock chart

    I am trying to produce stock charts in excel vba - these charts use three series of data often labelled high, low, and close with a hi-lo line for each day. I use the following code:

    sub tsta()

    'make new chart

    charts.add

    'identify chart type

    activechart.charttype = xlChartHLC

    end sub

    When I run this code, the first line works - the second line produces a run time error 1004 although it compiles correctly. I tried to change xlstockHLC to its constant equivalent (88) but this made no difference.

    I am using Excel97 with w2000.

    Does anyone have an idea how to get this charttype to work or a workaround to generate such a chart in some other way.
    It is interesting that when one records the macro which Excel uses to make this chart the same code is generated. Even when Excel generates the code, the procedure will not work when it is run in vb and generates the same run time error.

    Phil Adamson

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: excel vba stock chart

    Phil, congratulations, you found a bug that not documented by Microsoft. Of course they'll fix it in XP! ;o)
    Fortunately there is an easy work around, just select the source data first. Pretty hokey! Check out the code below, the Works macro works; the Bombs macro bombs ( in XL79 & XL2K). I'll let you report it. --Sam

    Sam Barrett, MTL Systems, Inc., mailto:sam@mtl.com
    Brainbench MVP, <A target="_blank" HREF=http://www.brainbench.com>http://www.brainbench.com</A>

    And the things that you have heard...commit these to faithful
    men who will be able to teach others also. 2 Timothy 2:2
    <pre>Sub Works()
    Range("A14").Select
    Charts.Add
    ActiveChart.ChartType = xlStockHLC
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A14")
    End Sub

    Sub Bombs()
    Charts.Add
    ActiveChart.ChartType = xlStockHLC
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A14")
    End Sub
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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