如何优化Range Set和.Value

Is there a more efficient way of writing the following:

是否有更有效的方式编写以下内容:

Private Sub ConvertDatesToValue_Click()

Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range

Set Rng1 = Range("Q8:Q12")
Set Rng2 = Range("Q16:Q20")
Set Rng3 = Range("T8:T12")
Set Rng4 = Range("T16:T20")

Rng1.Value = Rng1.Value
Rng2.Value = Rng2.Value
Rng3.Value = Rng3.Value
Rng4.Value = Rng4.Value

End Sub

It looks and feels a bit janky.

它看起来和感觉有点janky。

3 个解决方案

#1

Private Sub ConvertDatesToValue_Click()

So we're looking at a control's Click handler, presumably an ActiveX button on a worksheet, in which case we're in that worksheet's code-behind module.

所以我们正在查看控件的Click处理程序,可能是工作表上的ActiveX按钮,在这种情况下,我们就在该工作表的代码隐藏模块中。

The button means to invoke a command that will convert the formulas with their value in a specific range of cells that contain dates.

该按钮表示调用一个命令,该命令将使用包含日期的特定单元格区域中的值转换公式。

I would start with a procedure that's responsible for assigning Range.Value onto itself, given a Range:

在给定Range的情况下,我将从一个负责将Range.Value赋值给自身的过程开始:

Public Sub FreezeFormulaResult(ByVal target As Range)
    target.Value = target.Value
End Sub

Next we need to identify the range we'll pass into that procedure.

接下来,我们需要确定我们将进入该过程的范围。

Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range

Set Rng1 = Range("Q8:Q12")
Set Rng2 = Range("Q16:Q20")
Set Rng3 = Range("T8:T12")
Set Rng4 = Range("T16:T20")

Kudos for avoiding the implicit Variant trap, and declaring an explicit type for every one of these variables.

感谢避免隐式Variant陷阱,并为每个变量声明一个显式类型。

it looks and feels a bit janky

它看起来和感觉有点janky

That's because the variables have that numeric suffix. Rng1...RngN is a code smell indeed: it's a dirty solution to the problem of needing a bunch of something.

那是因为变量具有数字后缀。 Rng1 ...... RngN确实是一种代码味道:对于需要一堆东西的问题来说,这是一个肮脏的解决方案。

Often, a more elegant solution would be to use an array:

通常,更优雅的解决方案是使用数组:

Dim ranges As Variant
ranges = Array(Range("Q8:Q12"), Range("Q16:Q20"), Range("T8:T12"), Range("T16:T20"))

There are different many ways to skin a cat, but a union'ed disjointed Range¹ will not produce the expected results. Because you need 4 distinct areas, you'll need 4 distinct operations.

皮肤猫有很多不同的方法,但工会脱节的Range1不会产生预期的结果。因为您需要4个不同的区域,所以您需要4个不同的操作。

How the click handler needs to fetch the ranges, depends on where that handler is.

单击处理程序如何获取范围取决于处理程序的位置。

If we're looking at an MSForms.CommandButton (ActiveX) button on a Worksheet, then the click handler is in the very same sheet we want to get the cells from.

如果我们在工作表上查看MSForms.CommandButton(ActiveX)按钮,那么单击处理程序就在我们想要获取单元格的同一个工作表中。

In that case we can work off the current object, Me - and in fact by not qualifying Range calls we're doing exactly that... implicitly.

在这种情况下,我们可以解决当前对象,我 - 事实上,通过不限定Range调用我们正在做那个......隐含地。

In other words this:

换句话说:

Set Rng1 = Range("Q8:Q12")

Means this:

Set Rng1 = Me.Range("Q8:Q12")

You can't have a button Click handler in a standard module, but if you were to write this in one:

您不能在标准模块中单击处理程序按钮,但如果您要将其写入一个:

Set Rng1 = Range("Q8:Q12")

Then that would be implicity this:

然后那将是隐含的:

Set Rng1 = ActiveSheet.Range("Q8:Q12")

Note the difference: that's why implicit code is evil, and why context is everything - by writing explicit code, you reduce the cognitive load by making the context local rather than ambient.

注意区别:这就是为什么隐式代码是邪恶的,为什么上下文就是一切 - 通过编写显式代码,你可以通过使上下文局部而不是环境来减少认知负荷。

We need something that gives us an array of Range objects to work with. Let's try abstraction - it could be a public property in the sheet's code-behind:

我们需要能够为我们提供一系列Range对象的东西。让我们尝试抽象 - 它可能是工作表代码隐藏中的公共属性:

Public Property Get ImportantDateRanges() As Variant
    ImportantDateRanges = Array( _
        Me.Range("Q8:Q12"), _
        Me.Range("Q16:Q20"), _
        Me.Range("T8:T12"), _
        Me.Range("T16:T20"))
End Property

And now the button's Click handler no longer needs to care what the cells are, and the abstraction level is just perfect:

而现在按钮的Click处理程序不再需要关心单元格是什么,抽象级别是完美的:

Private Sub ConvertDatesToValues_Click()
    FreezeDateFormulas
End sub

Private Sub FreezeDateFormulas()

    Dim dateRanges As Variant
    dateRanges = Me.ImportantDateRanges

    Dim i As Long
    For i = LBound(dateRanges) To UBound(dateRanges)
        FreezeFormulaResult dateRanges(i)
    Next

End Sub

If we're looking at an MSForms.CommandButton on a UserForm, it's the exact same ActiveX button, ...but it's an entirely different story, because while you don't own a Worksheet instance (Excel does), you do own a UserForm instance - and that comes with implications too numerous to explain here (that link goes to an article I wrote about how forms don't need to run the show).

如果我们在UserForm上查看MSForms.CommandButton,它就是完全相同的ActiveX按钮,...但这是一个完全不同的故事,因为虽然你没有一个Worksheet实例(Excel可以),但你拥有一个UserForm实例 - 这带来的影响太多,无法解释(链接转到我写的关于表单不需要运行show的文章)。

_{¹How can I optimise Range Set and .Value}

我可以优化Range Set和.Value

#2

Try a direct value reversion.

尝试直接值恢复。

Private Sub ConvertDatesToValue_Click()

    With Worksheets("sheet9")
        .Range("Q8:Q12") = .Range("Q8:Q12").Value
        .Range("Q16:Q20") = .Range("Q16:Q20").Value
        .Range("T8:T12") = .Range("T8:T12").Value
        .Range("T16:T20") = .Range("T16:T20").Value
    End With

End Sub

You really should be aware of what worksheet you're on.

你真的应该知道你在做什么工作表。

#3

You can loop through the 4 areas :

您可以遍历4个区域:

Dim r As Range
For Each r In Range("Q8:Q12,Q16:Q20,T8:T12,T16:T20").Areas
    r = r.Value
Next

#1

Private Sub ConvertDatesToValue_Click()