目的是针对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
请注意,行计数变量必须为类型,Long
因为Excel的行数超出了Integer
处理能力:Dim Lastrow As Long
。我建议始终在VBA中使用Long而不是Integer,因为根本没有任何好处Integer
。
您必须使用Set TimeAxis
(因为范围是对象)并删除.Select
。另请参阅如何避免在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
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句