如何创建一个可以绘制变化范围的 VBA 宏？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3111470/
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
How do I create a VBA macro that will graph a changing range?
提问by Nicole
Currently, I have a table that will automatically update when new data is added to a spreadsheet. I am able to easily create a macro that will graph a set range, but how do I get it to automatically update the range so that it graphs all the right data? My goal is to be able to create a button that I can press at any time that will run a macro on this table and graph the results.
目前，我有一个表格，当新数据添加到电子表格时会自动更新。我能够轻松创建一个宏来绘制设定范围的图表，但如何让它自动更新范围，以便绘制所有正确的数据？我的目标是能够创建一个我可以随时按下的按钮，该按钮将在此表上运行宏并绘制结果图表。
Right now, my code is:
现在，我的代码是：
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/24/2010 by Nicole
'
''
Range("R1:S12").Select
Range("S12").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Intakes").Range("R1:S12"),PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Intakes"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "# Cases that day"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub
Thanks,
谢谢，
Nicole
妮可
回答by Lance Roberts
All you need to do differently (though it slightly depends on how the original cell gets its value updated) is to put your Macro in a Worksheet Change Event, then compare the Target to the Range of Interest:
所有你需要做的不同（虽然它稍微取决于原始单元格如何更新其值）是将你的宏放在工作表更改事件中，然后将目标与感兴趣的范围进行比较：
Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Me.Range("R1:S12")) Is Nothing Then
Range("R1:S12").Select
Range("S12").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Intakes").Range("R1:S12"),PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Intakes"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "# Cases that day"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End If
End Sub
回答by Mark T.
FYI, found another solution for this...
仅供参考，为此找到了另一个解决方案......
I was struggling with the same problem:
我正在努力解决同样的问题：
I had already created a dynamic name that selects my desired graph range [the whole range], but after I inserted this name into the graph range, it immediately converted back to a static range [cell reference] and no longer graphed my dynamic range name.
我已经创建了一个动态名称来选择我想要的图形范围 [整个范围]，但是在我将此名称插入图形范围后，它立即转换回静态范围 [单元格引用] 并且不再绘制我的动态范围名称.
I created a macro: select graph, change data selection, overwrite static cell range with my created dynamic name, enter.
我创建了一个宏：选择图形，更改数据选择，用我创建的动态名称覆盖静态单元格范围，输入。
I assigned this macro to a "Update Graph" button, but noticed the macro had taken the name's range as static, not dynamic.
我将此宏分配给“更新图表”按钮，但注意到该宏已将名称范围设为静态，而不是动态。
SO HERE'S THE SOLUTION:open macro menu, modify created macro... in the macro, look for the fixed cell range, overwrite it with 'sheetname'!dynamicname
所以这里的解决方案：打开宏菜单，修改创建的宏...在宏中，查找固定的单元格范围，用'sheetname'覆盖它！dynamicname
done. button should now update graph with dynamic range data.
完毕。按钮现在应该使用动态范围数据更新图表。
hope this helps someone out there. cheers.
希望这可以帮助那里的人。干杯。
回答by Mathias
A reasonably simple solution is to make that range a named range, using the input box that is located to the right of the formula input section. That way, instead of referring to the range as "R1:S12", you can access it by name, Range("MyOwnRange"). If you insert rows or columns inside the named range, it will resize automatically to include the new rows.
一个相当简单的解决方案是使用位于公式输入部分右侧的输入框将该范围设为命名范围。这样，您可以通过名称 Range("MyOwnRange") 访问它，而不是将范围称为“R1:S12”。如果在命名范围内插入行或列，它将自动调整大小以包含新行。
