VBA - 使用变量更改 XValues

问题描述

我是编程领域的新手，正在开始学习 VBA 以在 Excel 中建模金融场景.现在我有一个关于更改图表 XValues 以更改给定数据集显示给我的日期的问题.

I am completely new in the realm of programming and am starting to learn VBA for Modelling financial scenarios in Excel.Now I have a question regarding the change of the XValues of Chart in order to change the dates presented to me of a given data Set.

我使用变量来存储系列的第一行和最后一行编号，我想使用这些变量为 VBA 提供不同的范围，以根据我定义变量的方式从中选择 XValue.变量定义为整数.

I use Variables that store the first and last row number of the series which I want to use to give VBA a varying range to choose the XValues from based on how I define the variables.The variables are defined as integers.

我的代码:

Sheets("Result").ChartObjects("Chart 2").Activate
ActiveChart.FullSeriesCollection(1).XValues = ActiveWorkbook.Sheets("Data_Portfolio").Range(Cells(RangeStart, 1), Cells(RangeStop, 1))

如果您能向我解释第二行有什么问题以及我如何解决应用程序定义或对象定义的错误"，我会很高兴.

It would be very kind for you to explain to me what is wrong with the second row and how I can solve the "application-defined or object-defined error".

我先谢谢大家，

尼克

推荐答案

1) 用工作表名称完全限定 Cells

1) Fully qualify Cells with the worksheet name

 ActiveChart.FullSeriesCollection(1).XValues = ActiveWorkbook.Sheets("Data_Portfolio").Range(ActiveWorkbook.Sheets("Data_Portfolio").Cells(RangeStart, 1), ActiveWorkbook.Sheets("Data_Portfolio").Cells(RangeStop, 1))

2) 还应该确保有一个系列 (1) 要添加，对于某些版本，最好使用 SeriesCollection 而不是 FullSeriesCollection.

2) Should also be sure there is a series(1) to add to and for some versions it is better to use SeriesCollection rather than FullSeriesCollection.

3) 使用 Long 而不是 Integer 以避免潜在的溢出.

3) Use Long rather than Integer to avoid potential overflow.

示例如下:

Option Explicit

Public Sub test()

    Dim RangeStart As Long
    Dim RangeStop As Long

    RangeStart = 1
    RangeStop = 5
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets("Data_Portfolio")

    Worksheets("Result").ChartObjects("Chart 2").Activate '<== Is this ActiveWorkbook or ThisWorkbook?
    ActiveChart.SeriesCollection.NewSeries '<=  added for demo only to ensure present
    ActiveChart.SeriesCollection(1).XValues = ws.Range(ws.Cells(RangeStart, 1), ws.Cells(RangeStop, 1)) '< x
    ActiveChart.SeriesCollection(1).Values = ws.Range(ws.Cells(RangeStart, 1), ws.Cells(RangeStop, 1)) ' < y copied for test purposes only
End Sub

这篇关于VBA - 使用变量更改 XValues的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！