vba 将范围保存到变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9951415/
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
Save range to variable
提问by Prashant Kumar
I wrote some functional VBA:
我写了一些函数式 VBA：
Sheets("Src").Range("A2:A9").Copy Destination:=Sheets("Dest").Range("A2")
I want to extract the source range into a variable for flexibility.
我想将源范围提取到一个变量中以提高灵活性。
SrcRange = Sheets("Src").Range("A2:A9")
SrcRange.Copy Destination:=Sheets("Dest").Range("A2")
However, this doesn't work.
What SrcRange should be Dimmed as? Is the first line even correct?
I tried Dimming SrcRange as Range and it gave meRuntime error 91: Object Variable or With block variable not set
但是，这不起作用。什么 SrcRange 应该变暗？第一行甚至正确吗？
我尝试将 SrcRange 调光为范围，它给了我Runtime error 91: Object Variable or With block variable not set
I'm not very familiar with the language and the documentation has left me wanting (I couldn't find the return type to the Sheets(index) invocation, thiswas the closest I found). When I hit Record Macro, perform some actions, and hit stop, the Macro body is still blank.
我对语言不是很熟悉，文档让我很想要（我找不到 Sheets(index) 调用的返回类型，这是我找到的最接近的）。当我点击录制宏，执行一些操作，然后点击停止时，宏主体仍然是空白的。
Could anyone shed some light on how to use SrcRange as a variable?
谁能解释一下如何使用 SrcRange 作为变量？
回答by Jean-Fran?ois Corbett
In your own answer, you effectively do this:
在你自己的回答中，你有效地做到了这一点：
Dim SrcRange As Range ' you should always declare things explicitly
Set SrcRange = Sheets("Src").Range("A2:A9")
SrcRange.Copy Destination:=Sheets("Dest").Range("A2")
You're not really "extracting" the range to a variable, you're setting a reference to the range.
您并不是真正将范围“提取”到变量，而是设置了对范围的引用。
In many situations, this can be more efficient as well as more flexible:
在许多情况下，这可以更有效也更灵活：
Dim Src As Variant
Src= Sheets("Src").Range("A2:A9").Value 'Read range to array
'Here you can add code to manipulate your Src array
'...
Sheets("Dest").Range("A2").Value = Src 'Write array back to another range
回答by K1W1
Just to clarify, there is a big difference between these two actions, as suggested by Jean-Fran?ois Corbett.
澄清一下，正如 Jean-Fran?ois Corbett 所建议的那样，这两种操作之间存在很大差异。
One action is to copy / load the actual data FROM the Range("A2:A9")INTO a Variant Array called vArray(Changed to avoid confusion between Variant Array and Sheet both called Src):
一种操作是从Range("A2:A9")INTO 中复制/加载实际数据，称为 Variant Array vArray（已更改以避免在 Variant Array 和 Sheet 两者之间混淆，都称为 Src）：
vArray = Sheets("Src").Range("A2:A9").Value
vArray = Sheets("Src").Range("A2:A9").Value
while the other simply sets up a Range variable (SrcRange) with the ADDRESS of the range Sheets("Src").Range("A2:A9"):
而另一个只是使用范围的地址设置一个范围变量（SrcRange）Sheets("Src").Range("A2:A9")：
Set SrcRange = Sheets("Src").Range("A2:A9")
Set SrcRange = Sheets("Src").Range("A2:A9")
In this case, the data is not copied, and remains where it is, but can now be accessed in much the same way as an Array. That is often perfectly adequate, but if you need to repeatedly access, test or calculate with that data, loading it into an Array first will be MUCH faster.
在这种情况下，数据不会被复制，而是保留在原处，但现在可以以与数组大致相同的方式进行访问。这通常是完全足够的，但如果您需要重复访问、测试或计算这些数据，首先将其加载到数组中会快得多。
For example, say you want to check a "database" (large sheet) against a list of known Suburbs and Postcodes. Both sets of data are in separate sheets, but if you want it to run fast, load the suburbs and postcodes into an Array (lives in memory), then run through each line of the main database, testing against the array data. This will be much faster than if you access both from their original sheets.
例如，假设您想根据已知郊区和邮政编码列表检查“数据库”（大表）。两组数据都在单独的工作表中，但如果您希望它快速运行，请将郊区和邮政编码加载到一个数组中（存在于内存中），然后遍历主数据库的每一行，针对数组数据进行测试。这将比从原始工作表访问两者要快得多。
回答by Prashant Kumar
... And the answer is:
......答案是：
Set SrcRange = Sheets("Src").Range("A2:A9")
SrcRange.Copy Destination:=Sheets("Dest").Range("A2")
The Setmakes all the difference. Then it works like a charm.
这Set让一切变得不同。然后它就像一个魅力。
回答by MatthewKen
To save a range and then call it later, you were just missing the "Set"
要保存范围然后稍后调用它，您只是缺少“设置”
Set Remember_Range = Selection or Range("A3")
Remember_Range.Activate
But for copying and pasting, this quicker. Cuts out the middle man and its one line
但是对于复制和粘贴，这更快。切断中间人和它的一条线
Sheets("Copy").Range("A3").Value = Sheets("Paste").Range("A3").Value
回答by citynorman
My use case was to save range to variable and then select it later on
我的用例是将范围保存到变量，然后稍后选择它
Dim targetRange As Range
Set targetRange = Sheets("Sheet").Range("Name")
Application.Goto targetRange
Set targetRangeQ = Nothing ' reset
