将范围复制到虚拟范围-Copyarangetoavirtualrange

2  

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.

不,这是不可能的。 Range allways表示工作簿中工作表上的一些现有单元实例。

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.

但在上面的例子中,它只使x成为该范围的“捷径”,而不是范围对象本身的副本。

Variable x holds 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包含对该特定范围的引用。无法制作任何范围的独立副本。可以创建对范围的引用,并将everithing / something从一个范围复制到另一个范围。

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 allways表示现有的具体单元集。你可以做的是用Range的一些属性甚至所有属性来创建你自己的类......但是这将是一些额外的工作要做。

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

这里有一些例子如何使用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

6  

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