VBA:如何从股票OHLC图表的Y轴上删除小数点

Question

VBA专家。我已经为现有的OHLC股票图表编写了VBA代码。一切都很好；除了带小数的Y轴值(如14083.23到14683.23(，而我想要整数(14083到14683(。为此，我使用了RoundUp和RoundDown函数，但小数点仍未消失。你能告诉我哪里出了错吗。非常感谢。代码如下。

Sub-Min75蜡烛棒((

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

结束子

Answer 1

谢谢大家。最终代码如下。。。Sub-Min75蜡烛棒((

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) 
.MinimumScale = RoundMin * (1 - padding) 
.TickLabels.NumberFormat = "#,##0"
End With
.PlotArea.Format.Fill.ForeColor.RGB = RGB(242, 242, 242)
End With

结束子

Answer 2

添加.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