使用 VBA 选择动态范围的单元格并创建图表
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/25204059/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using VBA to select a dynamic range of cells and create a chart
提问by info_seekeR
I am attempting to use VBA to create a chart using dynamic ranges. Specifically, I have an Excel table
as follows
我正在尝试使用 VBA 来创建使用动态范围的图表。具体来说,我有Excel table
如下
Based on this data, I would like to create a chart, with the date ranges changed as per requirement. For example, at one instance, I would be required to produce a chart for 1st July - 6th July, and at another, from 10th July - 14th July.
基于这些数据,我想创建一个图表,根据要求更改日期范围。例如,在某个情况下,我需要为 7 月 1 日至 7 月 6 日制作图表,而在另一种情况下,我需要制作 7 月 10 日至 7 月 14 日的图表。
The following is my attempt at generating such a chart, but I feel there would be much better ways other than mine. Hence, my question, is there any other, better way?
以下是我尝试生成这样的图表,但我觉得除了我的之外还有更好的方法。因此,我的问题是,还有其他更好的方法吗?
1- I first enter the date values in 'helper cells' for which a chart is sought. In this case, cell M24 has the value 10th July, while cell M26 has the value 14th July.
1- 我首先在寻找图表的“辅助单元格”中输入日期值。在这种情况下,单元格 M24 的值为 7 月 10 日,而单元格 M26 的值为 7 月 14 日。
2- Then, I use the match()
function to find the positions from the date column of my Table. The function is =MATCH(M24,Table1[Dates],0)
, and =MATCH(M26,Table1[Dates],0)
.
2- 然后,我使用该match()
函数从表的日期列中查找位置。函数是=MATCH(M24,Table1[Dates],0)
, 和=MATCH(M26,Table1[Dates],0)
。
3- Given that I have the relative positions for the dates, I then use the following VBA code to generate the chart:
3- 鉴于我有日期的相对位置,然后我使用以下 VBA 代码生成图表:
Private Sub CommandButton1_Click()
Dim mySheet As Worksheet
Dim myShape As Shape
Dim myChart As Chart
Dim myVal1 As String
Dim myVal2 As String
Set mySheet = ActiveWorkbook.Worksheets("dataSheet")
If myShape Is Nothing Then
Set myShape = mySheet.Shapes.AddChart(XlChartType:=xlColumnClustered, _
Left:=CommandButton1.Left + CommandButton1.Width + 2, _
Width:=370, Height:=200)
End If
'In the following, I am offsetting from the first cell
'of my Table, which contains the `value 1-Jul.
'My objective is to use the range 10-Jul to 14th Jul,
'so I also add a column offset
'Cells O24 and O26 contain the results of the match functions
myVal1 = Range("B4").Offset(Range("O24").Value, 0).Address
myVal2 = Range("B4").Offset(Range("O26").Value, 4).Address
Set myChart = myShape.Chart
myChart.ChartType = xlLine
myChart.SetSourceData Source:=Sheets("dataSheet") _
.Range(CStr(myVal1 & ":" & myVal2))
End Sub
So, now hoping that my question is clear, could somebody please educate me of a better method than this one? This seems to be more a hacking method than proper coding to me...
所以,现在希望我的问题很清楚,有人可以教我比这个更好的方法吗?对我来说,这似乎更像是一种黑客方法,而不是正确的编码......
Many thanks in advance!
提前谢谢了!
采纳答案by L42
As what Dave said, it is pretty solid. But you can try this one:
正如戴夫所说,它非常可靠。但是你可以试试这个:
Private Sub CommandButton1_Click()
Dim d1 As Range, d2 As Range
Dim ws As Worksheet: Set ws = Thisworkbook.Sheets("datasheet")
'~~> Look for the dates
With ws.Range("Table1[Dates]")
Set d1 = .Find(ws.Range("M24").Value, .Cells(.Cells.Count))
Set d2 = .Find(ws.Range("M26").Value, .Cells(.Cells.Count))
End With
'~~> Handle unavailable dates, interchanged inputs
Dim i As Long, j As Long
If d1 Is Nothing Or d2 Is Nothing Then MsgBox "Invalid coverage": Exit Sub
If d2.Value > d1.Value Then i = 0: j = 4 Else i = 4: j = 0
'~~> Set the chart source
Dim chsource As Range
Set chsource = ws.ListObjects("Table1").HeaderRowRange
Set chsource = Union(chsource, ws.Range(d1.Offset(0, i), d2.Offset(0, j)))
'~~> Clean up existing chart
Dim sh As Shape
For Each sh In Me.Shapes
If sh.Type = msoChart Then sh.Delete
Next
'~~> Create the chart
With Me.Shapes.AddChart(, Me.CommandButton1.Left + _
Me.CommandButton1.Width + 2, Me.CommandButton1.Top, _
370, 200).Chart
.ChartType = xlLine
.SetSourceData chsource
.SetElement msoElementChartTitleAboveChart
.ChartTitle.Text = "Trend Chart"
End With
End Sub
You still retrieve dates on M24 and M26 respectively, but no need to use additional ranges with the formulas.
If the values aren't found, it returns a message box.
As long as the dates are found it will create the graph regardless where the user put it.
Also I did 2 ways of accessing the Table, 1 is using Rangeand the other is using ListObjects.
That is intentional for you to get a hang of both. Sometimes one is better than the other.
Also I am explicit in using Me
(which pertains to the sheet that contain your CB).
I also think that your graph should have the correct legends instead of Series(x) names so I added the header to the source. HTH.
您仍然可以分别检索 M24 和 M26 上的日期,但无需在公式中使用其他范围。
如果未找到值,则返回一个消息框。
只要找到日期,无论用户把它放在哪里,它都会创建图表。
我还做了 2 种访问表的方法,一种是使用Range,另一种是使用ListObjects。
这是有意让您掌握两者的窍门。有时一个比另一个更好。
我也明确使用Me
(与包含您的 CB 的工作表有关)。
我还认为你的图表应该有正确的图例而不是 Series(x) 名称,所以我将标题添加到源中。哈。
回答by Jon Peltier
In my tutorial Chart Partial Range Between Variable Endpoints, I show a couple alternatives using defined Names, without VBA. One way simply gives the index of the first and last record to include in the chart, another uses match to find the range of records that begin and end at dates entered by the user.
在我的教程Chart Partial Range Within Variable Endpoints 中,我展示了几个使用定义名称的替代方案,没有 VBA。一种方法简单地给出要包含在图表中的第一条和最后一条记录的索引,另一种方法使用匹配来查找在用户输入的日期开始和结束的记录范围。