vba 如何将宏引用到当前工作表中的单元格范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8833586/
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 to refer macro to cell range in current sheet?
提问by Fabrizio Bianchi
I recorded a macro to create graphs on a worksheet. The data are organized in the same way in all the sheets of the workbook, therefore I would like to generalize the macro so that it can be used on every sheet (or if it is possible to batch through the worksheets).
我录制了一个宏来在工作表上创建图表。数据在工作簿的所有工作表中以相同的方式组织，因此我想概括宏，以便它可以在每张工作表上使用（或者如果可以批量处理工作表）。
The code looks like this:
代码如下所示：
ActiveWindow.SmallScroll Down:=-57
Range("C5:C65").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Range("fr_1!$C:$C")
ActiveChart.Axes(xlCategory).Select
ActiveChart.SeriesCollection(1).XValues = "=fr_1!$A:$A"
Having recorded the macro on fr_1 I now have that reference in the 5th and last line, while I would like to have a general reference to the active sheet.
在 fr_1 上记录了宏后，我现在在第 5 行和最后一行中有该引用，而我想对活动工作表有一个一般性引用。
How do I do this?
我该怎么做呢？
回答by Alex K.
You can:
你可以：
Dim aSheet As Worksheet
For Each aSheet In ActiveWorkbook.Worksheets
With aSheet.Shapes.AddChart.Chart
.ChartType = xlLine
.SetSourceData Source:=aSheet.Range(aSheet.Name & "!$C:$C")
.SeriesCollection(1).XValues = "=" & aSheet.Name & "!$A:$A"
End With
Next
If you want to iterate the manually selected sheets change to for each asheet in activewindow.selectedsheets
如果要迭代手动选择的工作表更改为 for each asheet in activewindow.selectedsheets
To manually filter by name;
按名称手动过滤；
Dim aSheet As Worksheet
For Each aSheet In ActiveWorkbook.Worksheets
select case aSheet.name
case "sheet1", "sheet50", "sheet999"
With aSheet.Shapes.AddChart.Chart
.ChartType = xlLine
.SetSourceData Source:=aSheet.Range(aSheet.Name & "!$C:$C")
.SeriesCollection(1).XValues = "=" & aSheet.Name & "!$A:$A"
End With
end select
Next
回答by brettdj
You can
你可以
- add the desired sheets to an Array and just access the sheets you want to place charts on. This code only runs on the three sheet names supplied by
Arrshts = Array("Sheet1", "Sheet3", "MySheet With Space") - skip the potential naming issue by referring just to local ranges
- 将所需的工作表添加到数组中，然后访问要放置图表的工作表。此代码仅在由提供的三个工作表名称上运行
Arrshts = Array("Sheet1", "Sheet3", "MySheet With Space") - 通过仅引用本地范围来跳过潜在的命名问题
[Updated- added error handling for potential invalid sheet names]
[更新- 添加了对潜在无效工作表名称的错误处理]
Sub Sample()
Dim ws As Worksheet
Dim Arrshts()
Dim ArrSht
Dim strOut As String
Arrshts = Array("Sheet1", "Sheet3", "MySheet With Space")
For Each ArrSht In Arrshts
On Error Resume Next
Set ws = Nothing
Set ws = Sheets(ArrSht)
On Error GoTo 0
If Not ws Is Nothing Then
With Sheets(ArrSht).Shapes.AddChart.Chart
.ChartType = xlLine
.SetSourceData Range("$C:$C")
.SeriesCollection(1).XValues = Range("$A:$A")
End With
Else
strOut = strOut & (vbNewLine & ArrSht)
End If
Next
If Len(strOut) > 0 Then MsgBox strOut, , "These array names are incorrect and need adjusting"
End Sub
