使用VBA选择单元格的动态范围并创建图表 [英] Using VBA to select a dynamic range of cells and create a chart

问题描述

我正在尝试使用VBA使用动态范围创建图表。具体来说，我有一个 Excel表如下

I am attempting to use VBA to create a chart using dynamic ranges. Specifically, I have an Excel table as follows

根据这些数据，我想创建一个图表，日期范围按照需求。例如，在某种情况下，我将被要求在7月1日至7月6日之间，7月10日至7月14日之间生成一张图表。

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.

以下是我尝试生成这样的图表，但我觉得除了我以外，会有更好的方法。因此，我的问题是，还有其他更好的方式吗？

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-我首先在'帮助单元格'中输入日期值这是一个图表。在这种情况下，单元格M24的值为7月10日，而单元格M26的值为7月14日。

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.

2-然后，我使用 （）函数来查找我的表的日期列中的位置。该功能是 = MATCH（M24，Table1 [Dates]，0）和 = MATCH（M26，Table1 [Dates]，0） code>。

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).

3-假设我有日期的相对位置，然后使用以下VBA代码生成图表：

3- Given that I have the relative positions for the dates, I then use the following VBA code to generate the chart:

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

非常感谢提前！

推荐答案

正如Dave所说，这是非常稳固的。但是你可以尝试一下：

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

>
如果找不到值，它将返回一个消息框。

只要找到日期，它将创建图，而不管用户放置在哪里。

此外，我做了两种访问表的方法，1是使用范围，另一种是使用 ListObjects 。

这是有意的你得到两者的悬念。有时一个比另一个更好。

此外，我明确地使用 Me （这与包含您的CB的工作表有关）。

我也认为你的图应该有正确的图例而不是Series（x）名称，所以我添加了头到源。 HTH。

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 Range and 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.

这篇关于使用VBA选择单元格的动态范围并创建图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！