如何避免在 Excel VBA 中使用 Select [英] How to avoid using Select in Excel VBA
问题描述
我听说过很多关于在 Excel VBA 中使用 .Select
的可理解的憎恶,但我不确定如何避免使用它.我发现如果我能够使用变量而不是 Select
函数,我的代码将更具可重用性.但是,如果不使用 Select
,我不确定如何引用事物(如 ActiveCell
等).
I've heard much about the understandable abhorrence of using .Select
in Excel VBA, but I am unsure of how to avoid using it. I am finding that my code would be more re-usable if I were able to use variables instead of Select
functions. However, I am not sure how to refer to things (like the ActiveCell
, etc.) if not using Select
.
我找到了这篇关于范围的文章 和 这个例子关于不使用的好处选择,但我找不到任何关于how的信息.
I have found this article on ranges and this example on the benefits of not using select, but I can't find anything on how.
推荐答案
一些避免选择的例子
使用Dim
'd 变量
Dim rng as Range
设置
变量到需要的范围.有多种方法可以引用单个单元格范围:
Set
the variable to the required range. There are many ways to refer to a single-cell range:
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
方法的快捷方式,但这效率较低,通常应避免在生产代码中使用.
You can use the shortcut to the Evaluate
method, but this is less efficient and should generally be avoided in production code.
Set rng = [A1]
Set rng = [A1:B10]
以上所有示例均引用活动工作表上的单元格.除非您特别想只使用活动工作表,否则最好也将 Worksheet
变量调暗:
All the above examples refer to cells on the active sheet. Unless you specifically want to work only with the active sheet, it is better to Dim a Worksheet
variable too:
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
方法会更改活动工作表.
If you do want to work with the ActiveSheet
, for clarity it's best to be explicit. But take care, as some Worksheet
methods change the active sheet.
Set rng = ActiveSheet.Range("A1")
同样,这指的是活动工作簿.除非您特别想只使用 ActiveWorkbook
或 ThisWorkbook
,否则最好也将 Workbook
变量调暗.
Again, this refers to the active workbook. Unless you specifically want to work only with the ActiveWorkbook
or ThisWorkbook
, it is better to Dim a Workbook
variable too.
Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")
如果您确实想使用ActiveWorkbook
,为了清楚起见,最好是明确的.但要小心,因为许多 WorkBook
方法会更改活动书.
If you do want to work with the ActiveWorkbook
, for clarity it's best to be explicit. But take care, as many WorkBook
methods change the active book.
Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
您还可以使用 ThisWorkbook
对象来引用包含运行代码的书.
You can also use the ThisWorkbook
object to refer to the book containing the running code.
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")
一个常见的(坏的)代码是打开一本书,获取一些数据然后再次关闭
A common (bad) piece of code is to open a book, get some data then close again
这很糟糕:
Sub foo()
Dim v as Variant
Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear
Workbooks.Open("C:PathToSomeClosedBook.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:PathToSomeClosedBook.xlsx")
v = wb2.Sheets("SomeSheet").Range("A1").Value
wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
wb2.Close()
End Sub
将范围作为范围变量传递给您的 Sub
和 Function
:
Pass ranges to your Sub
s and Function
s as Range variables:
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
)应用于变量:
You should also apply Methods (such as Find
and Copy
) to variables:
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
如果您在一系列单元格上循环,通常最好(更快)先将范围值复制到变体数组并循环:
If you are looping over a range of cells it is often better (faster) to copy the range values to a variant array first and loop over that:
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
这是一个小尝尝可能的东西.
This is a small taster for what's possible.
这篇关于如何避免在 Excel VBA 中使用 Select的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!