时间戳

首页 > 解决方案 > VBA：如何从股票 OHLC 图表的 Y 轴上的值中删除小数点

excel - VBA：如何从股票 OHLC 图表的 Y 轴上的值中删除小数点

问题描述

VBA 专家。我已经为现有的 OHLC 股票图表编写了 VBA 代码。一切都很好; 除了带十进制的 Y 轴值（如 14083.23 到 14683.23），而我想要整数（14083 到 14683）。为此，我使用了 RoundUp 和 RoundDown 函数，但小数点仍然没有消失。你能否请建议我在哪里犯了错误。谢谢你。代码如下。

Sub Min75Candlestick()

Dim ws1 As Worksheet

    Set ws1 = ThisWorkbook.Worksheets("75Min")

Dim ws2 As Worksheet

    Set ws2 = ThisWorkbook.Worksheets("Exhibit")

Dim LastRow As Long

    LastRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row

Dim RngSt As Long

    RngSt = LastRow - 59

Dim RngEnd As Long

    RngEnd = LastRow + 15

Dim MyRng As Range

    Set MyRng = ws1.Range(ws1.Cells(RngSt, 1), ws1.Cells(RngEnd, 5))

Dim OHLCRng

    Set OHLCRng = ws1.Range(ws1.Cells(RngSt, 2), ws1.Cells(RngEnd, 5))

Dim OHLCMaxRng As Long

    OHLCMaxRng = Application.WorksheetFunction.Max(OHLCRng)

Dim RoundMax As Long

    RoundMax = Application.WorksheetFunction.RoundUp(OHLCMaxRng, 0)

Dim OHLCMinRng As Long

    OHLCMinRng = Application.WorksheetFunction.Min(OHLCRng)

Dim RoundMin As Long

    RoundMin = Application.WorksheetFunction.RoundDown(OHLCMinRng, 0)

Dim Padding As Double

    Padding = 0.005

Dim OHLCChart As ChartObject

    Set OHLCChart = ws2.ChartObjects(1)

        With OHLCChart.Chart

            .SetSourceData MyRng

            .Axes (xlValue)

                With .Axes(xlValue)

                    .MaximumScale = RoundMax * (1 + Padding) '+ (RoundMax * 1 / 100)

                    .MinimumScale = RoundMin * (1 - Padding) '- (RoundMin * 1 / 100)

                End With

            .ChartTitle.Text = "75Min Candlestick chart"

            .Axes(xlValue, xlPrimary).HasTitle = False

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

            .Parent.Name = "OHLC Chart"

        End With

结束子

标签： excelvbayaxis

解决方案

添加.TickLabels.NumberFormat = "#,##0"

Sub Min75Candlestick()

    Const padding As Double = 0.005

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim MyRng As Range, OHLCRng As Range
    Dim LastRow As Long, RngSt As Long, RngEnd As Long
    Dim RoundMax As Long, RoundMin As Long

    Set ws1 = ThisWorkbook.Worksheets("75Min")
    LastRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    RngSt = LastRow - 59
    RngEnd = LastRow + 15

    Set MyRng = ws1.Range(ws1.Cells(RngSt, 1), ws1.Cells(RngEnd, 5))
    Set OHLCRng = ws1.Range(ws1.Cells(RngSt, 2), ws1.Cells(RngEnd, 5))

    With Application.WorksheetFunction
        RoundMax = .RoundUp(.Max(OHLCRng), 0)
        RoundMin = .RoundDown(.Min(OHLCRng), 0)
    End With
    
    Dim OHLCChart As ChartObject
    Set ws2 = ThisWorkbook.Worksheets("Exhibit")
    Set OHLCChart = ws2.ChartObjects(1)
    With OHLCChart.Chart
        .Parent.Name = "OHLC Chart"
        .ChartTitle.Text = "75Min Candlestick chart"
        .SetSourceData MyRng
        .Axes xlValue
        .Axes(xlValue, xlPrimary).HasTitle = False
        With .Axes(xlValue)
             .MaximumScale = RoundMax * (1 + padding) '+ (RoundMax * 1 / 100)
             .MinimumScale = RoundMin * (1 - padding) '- (RoundMin * 1 / 100)
             .TickLabels.NumberFormat = "#,##0"
        End With
        .PlotArea.Format.Fill.ForeColor.RGB = RGB(242, 242, 242)
    End With
End Sub

推荐阅读