Excel VBA：将范围名称作为函数参数传递
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10729424/
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
Excel VBA: Passing a range name as a function argument
提问by AndroidDev
I'm writing a macro to do a complex copy/paste exercise. It's pretty straightforward conceptually, but I'm stuck in one spot. All of the various blocks of data are identified with various named ranges. I need iterate through this list of names, passing each name as an argument to a function (actually a subroutine, but same idea). The source of the data is in one workbook while the destination is in another workbook.
我正在编写一个宏来进行复杂的复制/粘贴练习。这在概念上非常简单，但我被困在一个地方。所有不同的数据块都用不同的命名范围来标识。我需要遍历这个名称列表，将每个名称作为参数传递给函数（实际上是一个子例程，但想法相同）。数据源在一个工作簿中，而目标在另一个工作簿中。
Here is what I have (for just one block of data):
这是我所拥有的（仅用于一个数据块）：
Private Sub copyABU()
copyPaste(ThisWorkbook.Names("myRange1").RefersToRange)
copyPaste(ThisWorkbook.Names("myRange2").RefersToRange)
copyPaste(ThisWorkbook.Names("myRange3").RefersToRange)
//etc
End Sub
Private Sub copyPaste(thisRange As Range)
Windows(someworkbook).Range(thisRange).Copy
Range(thisRange).PasteSpecial Paste:=xlPasteValues
End Sub
Unfortunately, I get a run-time error on this. I think that there is a type mismatch, but I'm not sure about this and can't figure out what I am missing. Can anyone see why this fails? (I'm using Excel 2010).
不幸的是，我在这方面遇到了运行时错误。我认为存在类型不匹配，但我不确定这一点，也无法弄清楚我缺少什么。谁能看出为什么会失败？（我使用的是 Excel 2010）。
Thanks!
谢谢！
回答by systrue
here my answer:
这是我的答案：
Sub init()
Windows("Book1.xlsx").Activate
Call copyPaste(2, Range(Cells(1, 1), Cells(10, 10)), "copy")
Windows("Book2.xlsx").Activate
Call copyPaste(1, Range(Cells(1, 1), Cells(10, 10)), "paste")
End Sub
Function copyPaste(wksInt As Integer, thisRange As Range, copyPasteStr As String)
Dim workSheetRange As Range
With Worksheets(wksInt)
Set workSheetRange = thisRange
End With
workSheetRange.Select
If copyPasteStr = "copy" Then
Selection.Copy
Else
Worksheets(wksInt).Paste
End If
End Function
This works as long as the Ranges for copy and paste are the same dimension. If the dimension varies, you need to change workSheetRange.Select into the condition and only select the first cell of the range for pasting. PAX
只要复制和粘贴的范围是相同的维度，这就会起作用。如果尺寸变化，则需要将 workSheetRange.Select 更改为条件，仅选择范围的第一个单元格进行粘贴。帕克斯
回答by Jon Crowell
Your code will work with a couple small tweaks.
您的代码将通过一些小的调整工作。
First, you need to prefix your call to copyPaste with the word Call. (See note below if you don't want to.)
首先，您需要在对 copyPaste 的调用前加上单词Call。（如果您不想，请参阅下面的注释。）
Private Sub copyABU()
Call copyPaste(ThisWorkbook.Names("myRange1").RefersToRange)
Call copyPaste(ThisWorkbook.Names("myRange2").RefersToRange)
Call copyPaste(ThisWorkbook.Names("myRange3").RefersToRange)
' //etc
End Sub
Second, add a .Addressafter thisRange.
其次，添加一个.Addressafter thisRange。
Private Sub copyPaste(thisRange As Range)
Range(thisRange.Address).Copy
thisRange.PasteSpecial Paste:=xlPasteValues
End Sub
I didn't want to bother with creating a someworkbookvariable, so I just deleted that part.
我不想费心创建一个someworkbook变量，所以我只是删除了那部分。
Note:You have to use the Callkeyword if you are calling a procedure with an argument list enclosed in parentheses. https://stackoverflow.com/a/7715070/138938
注意：Call如果您使用括号括起来的参数列表调用过程，则必须使用关键字。https://stackoverflow.com/a/7715070/138938
If you don't want to use the Callkeyword, omit the parens:
如果您不想使用Call关键字，请省略括号：
copyPaste ThisWorkbook.Names("myRange1").RefersToRange
