vba 将范围复制到虚拟范围

Copy a range to a virtual range

提问by CodeCamper

Is it possible to copy a range to a virtual range or does it require me to sloppily paste it in another range in the workbook?

是否可以将范围复制到虚拟范围，或者是否需要我草率地将其粘贴到工作簿的另一个范围中？

dim x as range
x = copy of Range("A1:A4")

obviously I usually use the following code

显然我通常使用以下代码

dim x as range
set x = Range("A1:A4")

but in the above example it only makes x a "shortcut" to that range rather than a copy of the range object itself. Which is usually what I want but lately I have been finding it would be quite useful to totally save a range and all it's properties in memory rather than in the workbook somewhere.

但在上面的例子中，它只使 xa 成为该范围的“快捷方式”，而不是范围对象本身的副本。这通常是我想要的，但最近我发现将范围及其所有属性完全保存在内存中而不是工作簿中的某个地方会非常有用。

回答by Jean-Fran?ois Corbett

I think this is what you are trying to do:

我认为这就是你想要做的：

'Set reference to range
Dim r As Range
Set r = Range("A1:A4")

'Load range contents to an array (in memory)
Dim v As Variant
v = r.Value

'Do stuff with the data just loaded, e.g.
'Add 123 to value of cell in 1st row, 3rd column of range 
v(1,3) = v(1,3) + 123

'Write modified data back to some other range
Range("B1:B4").Value = v

回答by dee

Is it possible to copy a range to a virtual range?

是否可以将范围复制到虚拟范围？

No it is not possible. Range allways represents some existing instance(s) of cells on a worksheet in a workbook.

不，这是不可能的。范围始终代表工作簿中工作表上的某些现有单元格实例。

Does it require me to sloppily paste it in another range in the workbook?

它是否需要我草率地将其粘贴到工作簿的另一个范围内？

It depends on what you want to do. You can paste everithing from one range to another, you can paste only something like e.g. formulas to another range.

这取决于你想做什么。您可以将所有内容从一个范围粘贴到另一个范围，也可以仅将诸如公式之类的内容粘贴到另一个范围。

dim x as range
set x = Range("A1:A4")

But in the above example it only makes x a "shortcut" to that range rather than a copy of the range object itself.

但是在上面的示例中，它只使 xa 成为该范围的“快捷方式”，而不是范围对象本身的副本。

Variable xholds a reference to that specific range. It is not possible to made any standalone copy of a range. It is possible to create references to a range and to copy everithing / something from one range to another range.

变量x包含对该特定范围的引用。不可能制作范围的任何独立副本。可以创建对范围的引用并将所有内容/内容从一个范围复制到另一个范围。

Lately I have been finding it would be quite useful to totally save a range and all it's properties in memory rather than in the workbook somewhere.

最近我发现将范围及其所有属性完全保存在内存中而不是工作簿中的某个地方会非常有用。

Again, it is not possible to save all range properties to some virtual, standalone copy of specific Range because Range allways represents an existing, concrete set of cells. What you could do is to create your own class with some properties of a Range or even all properties ... but it will be some extra work to do.

同样，不可能将所有范围属性保存到特定范围的某个虚拟独立副本，因为范围始终代表现有的具体单元格集。您可以做的是使用 Range 的某些属性甚至所有属性创建自己的类……但这将是一些额外的工作要做。

Here some examples how to use range as parameter and copy it to another range. HTH.

这里有一些示例如何使用范围作为参数并将其复制到另一个范围。哈。

Option Explicit

Sub Main()
    Dim primaryRange As Range
    Set primaryRange = Worksheets(1).Range("A1:D3")

    CopyRangeAll someRange:=primaryRange
    CopyRangeFormat someRange:=primaryRange

    ' Value property of a range represents and 2D array of values
    ' So it is usefull if only values are important and all the other properties do not matter.
    Dim primaryRangeValues As Variant
    primaryRangeValues = primaryRange.value
    Debug.Print "primaryRangeValues (" & _
        LBound(primaryRangeValues, 1) & " To " & UBound(primaryRangeValues, 1) & ", " & _
        LBound(primaryRangeValues, 2) & " To " & UBound(primaryRangeValues, 2) & ")"
    ' Prints primaryRangeValues (1 To 3, 1 To 4)

    Dim value As Variant
    For Each value In primaryRangeValues
        ' This loop throught values is much quicker then to iterate through primaryRange.Cells itself.
        ' Use it to iterate through range when other properties except value does not matter.
        Debug.Print value
    Next value
End Sub

Private Sub CopyRangeAll(ByVal someRange As Range)
    ' Here all properties of someRange which can be copied are copied to another range.
    ' So the function gets a reference to specific range and uses all its properties for another range.
    Dim secondaryRange As Range
    Set secondaryRange = Worksheets(2).Range("D4:G6")
    someRange.Copy secondaryRange
End Sub

Private Sub CopyRangeFormat(ByVal someRange As Range)
    ' Here only formats are copied.
    ' Function receives reference to specific range but uses only one special property of it in that another range.
    Dim secondaryRange As Range
    Set secondaryRange = Worksheets(3).Range("G7:J9")
    someRange.Copy
    secondaryRange.PasteSpecial xlPasteFormats ' and many more e.g. xlPasteFormulas, xlPasteValues etc.
End Sub