如何避免选择的一些示例

使用Dim‘d 变量

Dim rng as Range

Set变量到所需的范围。有很多方法可以引用单个单元格范围：

Set rng = Range("A1")
Set rng = Cells(1, 1)
Set rng = Range("NamedRange")

或多单元格范围：

Set rng = Range("A1:B10")
Set rng = Range("A1", "B10")
Set rng = Range(Cells(1, 1), Cells(10, 2))
Set rng = Range("AnotherNamedRange")
Set rng = Range("A1").Resize(10, 2)

您 可以 使用该方法的快捷Evaluate方式，但效率较低，通常应在生产代码中避免使用。

Set rng = [A1]
Set rng = [A1:B10]

以上所有示例均指 活动工作表 上的单元格。除非您特别想只使用活动工作表，否则最好也将Worksheet变量调暗：

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1, 1)
With ws
    Set rng = .Range(.Cells(1, 1), .Cells(2, 10))
End With

如果您 确实 想使用ActiveSheet，为了清楚起见，最好是明确的。但要小心，因为某些Worksheet方法会更改活动工作表。

Set rng = ActiveSheet.Range("A1")

同样，这指的是 活动工作簿 。除非您特别想只使用ActiveWorkbookor
ThisWorkbook，否则最好也将Workbook变量调暗。

Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")

如果您 确实 想使用ActiveWorkbook，为了清楚起见，最好是明确的。但要小心，因为许多WorkBook方法会更改活动书。

Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

您还可以使用该ThisWorkbook对象来引用包含运行代码的书。

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")

一个常见的（坏的）代码是打开一本书，获取一些数据然后再次关闭

这是不好的：

Sub foo()
    Dim v as Variant
    Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear
    Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = ActiveWorkbook.Sheets(1).Range("A1").Value
    Workbooks("SomeAlreadyOpenBook.xlsx").Activate
    ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v
    Workbooks(2).Activate
    ActiveWorkbook.Close()
End Sub

它会更好：

Sub foo()
    Dim v as Variant
    Dim wb1 as Workbook
    Dim  wb2 as Workbook
    Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx")
    Set wb2 = Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = wb2.Sheets("SomeSheet").Range("A1").Value
    wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
    wb2.Close()
End Sub

将范围传递给您Sub的 s 和Functions 作为 Range 变量：

Sub ClearRange(r as Range)
    r.ClearContents
    '....
End Sub

Sub MyMacro()
    Dim rng as Range
    Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")
    ClearRange rng
End Sub

您还应该将方法（例如Find和Copy）应用于变量：

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10")
Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10")
rng1.Copy rng2

如果您要遍历一系列单元格，通常最好（更快）先将范围值复制到变量数组并循环遍历：

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000")
dat = rng.Value  ' dat is now array (1 to 10000, 1 to 1)
for i = LBound(dat, 1) to UBound(dat, 1)
    dat(i,1) = dat(i, 1) * 10 ' Or whatever operation you need to perform
next
rng.Value = dat ' put new values back on sheet

这是可能的小尝试。