时间戳

首页 > 解决方案 > VBA - Modification of SourceData (Dynamic Range) of existing STock OHLC Chart

excel - VBA - Modification of SourceData (Dynamic Range) of existing STock OHLC Chart

问题描述

I am working on Stock OHLC Chart present in Sheets("Exhibit") and selecting Data from Sheets("75Min"). However i am succeeding to choose appropriate data range but can not add at Line Nu 15 .SetSourcedata, could you please help me to get out from this problem Code is as follows.

Sub Edit75MinChartToOHLCCandlestickChart()

Dim OHLCChart As ChartObject

Dim LastRow As Integer

Dim RngSt As Integer

    Sheets("75Min").Select

    Range("A1").Select

    Range("A1").End(xlDown).Select

    LastRow = ActiveCell.Row

    RngSt = LastRow - 59

    RngEnd = LastRow + 15

    Set OHLCChart = ThisWorkbook.Worksheets("Exhibit").ChartObjects(1)

        With OHLCChart.Chart 'Worksheets("Exhibit").ChartObjects("Chart 2").Chart

            .SetSourceData ThisWorkbook.Worksheets("75Min").Range(RngSt, RngEnd)

            .ChartType = xlStockOHLC

            .HasTitle = True

            .ChartTitle.Text = "75Min Candlestick chart"

            .Axes(xlValue, xlPrimary).HasTitle = False

            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Price"

            .PlotArea.Format.Fill.ForeColor.RGB = RGB(242, 242, 242)

            .ChartArea.Format.Line.Visible = msoFalse

            .Parent.Name = "OHLC Chart"

        End With

    End Sub

Thank You

标签： excelvbachartsstockohlc

解决方案

您可以包括这些行来调整 Y 轴刻度：

Dim Ymin As Double, Ymax As Double
Ymin = WorksheetFunction.Min(MyRng)
Ymax = WorksheetFunction.Max(MyRng)

With OHLCChart.Chart.Axes(xlValue)
  .MinimumScale = Ymin
  .MaximumScale = Ymax
End With

但这些可能是丑陋的轴限制。我在 Excel VBA 中编写了一个名为“Calculate Nice Axis Scales”的教程，其中包含一个算法，可以稍微向外调整最小值和最大值，以便刻度更易于阅读。

推荐阅读