将范围复制到虚拟范围 [英] Copy a range to a virtual range

问题描述

是否可以将范围复制到虚拟范围，还是需要我将其随意地粘贴到工作簿的另一个范围中?

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")

，但是在上面的示例中，它仅使x成为该范围的快捷方式"，而不是范围对象本身的副本.通常这是我想要的，但是最近我发现将一个区域及其所有属性完全保存在内存中而不是在工作簿中的某个地方非常有用.

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.

推荐答案

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

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")

但是在上面的示例中，它仅使x成为该范围的捷径"而不是范围对象本身的副本.

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.

最近我发现完全保存一个范围及其所有属性在内存中而不是在工作簿中某个地方.

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.

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

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.

这里有一些示例，说明如何使用范围作为参数并将其复制到另一个范围.HTH.

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

这篇关于将范围复制到虚拟范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！