基于动态用户范围绘制图表的步骤

Question

目的是根据column A (X轴)绘制这些值。用户在单元格G4中选择一个列名，G4的值可以是B、C、C、D。根据列名，必须绘制图表。

​

我能够编写以下代码来选择数据，但是我不能动态地选择数据。

Sub Chart()

Dim Lastrow As Long
Dim TimeAxis As Range
Dim Values As Range
Dim cht As Object

Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
TimeAxis = Range("A1:A" & Lastrow).Select '<----- X Axis (Static) 
Values = Range("B1:B" & Lastrow).Select  '<----- User selected as per cell G4 (Dynamic)     

'Plot TimeAxis Vs Values

Set cht = ActiveSheet.Shapes.AddChart2
cht.Chart.SetSourceData Source:=rng    '<------ User Range (Dynamic)
cht.Chart.ChartType = xlXYScatterLines

End Sub

Answer 1

请注意，行计数变量必须是Long类型，因为Integer的行数超过了Dim Lastrow As Long的处理能力。我推荐在VBA中使用always to use Long instead of Integer，因为在Integer中根本没有任何好处。

您必须使用Set TimeAxis (因为范围是对象)并删除.Select。另请参阅How to avoid using Select in Excel VBA。

最后，Set你的rng，否则它是空的，你不能在Source:=rng中使用它。

所以你最终会得到类似这样的结果：

Option Explicit

Sub GenerateChart() 'dont use "chart" as procedure name it is a type in VBA and therefore a reserved word
    Dim LastRow As Long
    LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    Dim TimeAxis As Range
    Set TimeAxis = Range("A2:A" & LastRow) 'X Axis values start at A2!!!

    Dim SelCol As String
    SelCol = ActiveSheet.Range("G4") 'read column name from G4

    Dim Values As Range
    Set Values = Range(SelCol & "1:" & SelCol & LastRow)  '<----- User selected as per cell G4 (Dynamic)

    'Plot TimeAxis Vs Values

    Dim cht As Object
    Set cht = ActiveSheet.Shapes.AddChart2
    cht.Chart.SetSourceData Source:=Values    '<------ User Range (Dynamic)
    cht.Chart.FullSeriesCollection(1).XValues = TimeAxis 'x axis values
    cht.Chart.ChartType = xlXYScatterLines
End Sub