如何避免在VBA中出现默认属性陷阱？

Question

我只是偶尔使用VBA，每次回到它时，我都会遇到以下某种变化：

我有一个Range对象，名为currentCell，我用它来跟踪我在电子表格中正在处理的单元格。当我将其更新为指向不同的单元格时，我写入：

currentCell = currentCell.Offset(ColumnOffset:=1)

问题在于我忘了使用 "Set" 关键字，所以上述代码实际上使用的是 Range 对象的默认属性：

问题在于我忘了使用 "Set" 关键字，所以上述代码实际上使用的是 Range 对象的默认属性：

currentCell.Value = currentCell.Offset(ColumnOffset:=1).Value

所以当前单元格的内容被新单元格的内容覆盖了，我的currentCell变量没有更改为指向一个新单元格，当我意识到我已经犯了同样的错误第一百次时，我感到非常愤怒。

也许最好的答案就是在我的显示器上贴个便笺，上面写着“你今天记得使用Set了吗？”。但如果有人有任何建议可以帮助我，我会很感激听到他们的想法。特别是：

• 是否有任何方法在您隐式使用默认属性时打开警告？我从来没有故意这样使用过它们，如果我需要，我总是调用Range.Value。
• 有没有将变量标记为“只能用于从电子表格中读取数据”的良好实践？在我编写的大多数代码中，几乎所有变量都是用于收集数据的，如果某些东西开始无意中编辑单元格，那么获得警告会很方便。

- Neil Vass

---

做过了。很多次。 - Kent

又忘记使用 Set 了。 - Neil Vass

Answer 1

我花了一些时间才明白你遇到的问题是怎么发生的，因为我觉得使用范围对象多年来从未遇到过这个问题。经过思考，我意识到当处理单元格时我100% 的时间都会使用 offset 或 cells 函数 - 很少使用 Set 来重新定义当前变量。

如果我要迭代某个循环以浏览电子表格，我可能会这样做：

Dim startRng as Range
Set startRng = range("A1")

for i = 1 to 100
   startRng.offset(i,0).value = i
   startRng.offset(i,1).value = startRng.offset(i,0).value
next i

或者

for i = 1 to 100
    cells(i,0).value = i
    cells(i,1).value = cells(i,0).value
next i

任何一种符号表示法都意味着我几乎不需要使用 Set 命令与范围对象配合使用——几乎总是只需在迭代或引用区域的第一个单元格时使用一次（如果有必要的话）。

此外，offset 的含义非常明确——因为你可以指定一个 row/column，这使得代码中正在发生的情况变得非常简单，并且更容易跟踪，因为它只涉及到单个单元格。你不必回溯并跟踪最后三个更新 currentCell Range 对象的地方。

采用这些样式的编码风格应该可以消除你所犯的几乎所有错误。当我说在我多年编写 VBA 代码中从未犯过类似错误时，我是非常认真的——我在我的代码中连续使用 offset 和 cells 函数（在这些示例的循环中使用了这些方法，但对于代码中的所有其他示例，我也使用类似的方法），而不是将范围设置为新范围。副作用是，当你在代码中设置一个范围时，它几乎总是紧接着一个 Dim 语句，这使得代码更加清晰。

- enderland

Answer 2

无论你选择做什么，很抱歉，你都需要使用便利贴。毕竟，将范围对象的值设置为另一个单元格中的值是一种完全有效和常见的操作。代码无法知道你想要做什么，除非你明确告诉它。

你可以尝试在更新范围对象之前和之后检查其地址，以确保它不同，但如果你记得这样做，最好直接使用“set”关键字按照你的意愿更新对象。

现在，这个问题已经激怒了你，我想你再次访问VBA时永远不会忘记它。所以也许你根本不需要便利贴了。

- Jon Crowell

---

你说得对，问这个问题会让我记住下次写VBA的时候。 - Neil Vass

Answer 3

我想建议你在显示器上贴一张便利贴，上面写着“你今天使用了Set吗？”但如果有人有更好的建议，请告诉我。特别是以下几点：

我会稍微修改便利贴上的措辞。

“你确定你没有忘记使用Option Explicit和Error Handling吗？”

否则相信我，没有更好的方法！话虽如此，我想确认的是，“使用Set”不是你最担心的问题。你最关注的应该是“编写优秀的代码”，这不是一蹴而就的。需要通过实践来学习。

我的建议是，对于所有初学者，不要做任何假设！例如，.Value是范围的默认属性，因此

Range("A1") = "Blah" 

是正确的。但仍要避免使用它。

1. 始终完全限定您的变量
2. 始终使用Option Explicit
3. 始终使用错误处理

例如，这样做是有效的。

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range

    On Error GoTo Whoa

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("A1")
    rng.Value = "Blah"

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

现在让我们尝试不使用Set命令运行上面的代码。请尝试以下代码。会发生什么？

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range

    On Error GoTo Whoa

    ws = ThisWorkbook.Sheets("Sheet1")
    rng = ws.Range("A1")
    rng.Value = "Blah"

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

推荐阅读

主题：人非圣贤，孰能无过

链接：http://siddharthrout.wordpress.com/2011/08/01/to-err-is-human/

- Siddharth Rout

---

总的来说，这是非常好的建议，谢谢。但在VBA中，默认的错误处理会在这种情况下弹出相同的消息，不是吗？此外，我认为我的“陷阱”经常会在范围已经指向单元格并且我尝试更新它时弹出 - 在这种情况下不会有错误。 - Neil Vass

这不仅仅是错误处理。它是整个包。想象一下当你开始编写代码时，缩进它，注释它，完全限定变量，然后你会意识到使用“Set”自然而然地出现在你的脑海中。那就是你停止使用便笺的时刻 :) - Siddharth Rout

Answer 4

有没有办法在隐式使用默认属性时打开警告？

没有。

有没有什么好的做法来标记变量为“这只能用于从电子表格中读取”？

嗯，你可以制定自己的变量命名约定，就像Making Wrong Code Look Wrong，但你仍然需要通过视觉检查自己的代码，编译器无法帮助你完成。所以我不会过度依赖这个。

更好的选择是避免需要反复重新定义currentCell，而是完全使用.Offset绕过它的需要。

相反，将感兴趣的整个范围读入Variant数组，在该数组上执行操作，然后在修改完成后将其放回工作表。

Dim i As Long
Dim j As Long
Dim v As Variant
Dim r As Range

Set r = Range("A1:D5") 'or whatever

v = r.Value 'pull from sheet

For i = 1 To UBound(v, 1)
    For j = 1 To UBound(v, 2)
        'code to modify or utilise element v(i,j) goes here
    Next j
Next i

r.Value = v 'slap v back onto sheet (if you modified it)

就是这样，没有使用默认属性或任何可能被误认为是默认属性的东西。作为奖励，这将加快您的代码执行速度。

- Jean-François Corbett

---

我之前没有意识到将所有东西放入 Variant 并在其中操作是多么简单。如果你知道不想更改范围中的任何内容，这似乎是一个很好的习惯。为了更加简洁，我们可以使用 v = Range("A1:D5").Value 而不是使用单独的 Range 变量。 - Neil Vass

如果你不得不在代码结尾写上Range("A1:D5").Value = v，那么这并不能使它更加简洁...最好避免文本出现在代码中间。 - Jean-François Corbett

好的，我没有注意到最后一点。这是一个非常巧妙的方法将数据返回到工作表上。 - Neil Vass

Answer 5

我认为enderland已经强调了基本解决方案，即处理循环中的多个单元格。为了进一步完善，我建议使用For Next循环来循环遍历单元格。可能我写的最常见的代码之一是：

Dim cell as Excel.Range
Dim rngCellsToProcess as Excel.Range

Set rngCellsToProcess = 'whatever you set it to
For each cell in rngCellsToProcess 
   'do something
Next cell

这并不会消除对Set的需求，但可以帮助您记得使用它，并使正在进行的操作更清晰明了。

- Doug Glancy

---

谢谢Doug。你之前是否提到过使用Protect和Locked来防止意外更改内容的评论？这听起来是一个非常方便的方式，可以标记我不想用代码更改的区域。这可能值得添加一个回答。 - Neil Vass

Answer 6

也许编写自己的自定义函数并使用它会更好？

Sub offset_rng(ByRef my_rng As Range, _
    Optional row As Integer, Optional col As Integer)
    Set my_rng = my_rng.Offset(row, col)
End Sub

可以像这样使用：

Sub test()
    Dim rng As Range
    Set rng = Range("A1")
    offset_rng my_rng:=rng, col:=1
    rng.Value = "test1"
    offset_rng my_rng:=rng, col:=1
    rng.Value = "test2"
    offset_rng my_rng:=rng, col:=1
    rng.Value = "test3"
    offset_rng my_rng:=rng, col:=1
    rng.Value = "test4"
End Sub

- ApplePie

---

我注意到你的示例需要使用 Set :)。 - Doug Glancy

我猜想在某个特定的时候，你必须记得你正在使用的语言。 - ApplePie

@AlexandreP.Levasseur，这是真的，无法逃避！ - Doug Glancy

@AlexandreP.Levasseur：我承认真正的答案可能是“学会如何正确使用VBA”。但在我使用的其他语言中，x = something将使x引用something。在VBA中相同的语法可以编译通过，但实际上会执行完全不同的操作 - 这就是每次让我困惑的地方。 - Neil Vass