如何避免使用Excel中的选择VBA [英] How to avoid using Select in Excel VBA

问题描述

我听说过在Excel VBA中使用 .Select 可以理解的憎恶，但不确定如何避免使用它。我发现如果我能够使用变量而不是选择函数，我的代码将会更加可重用。但是，如果不使用选择，我不知道如何引用这些东西（如 ActiveCell 等）。

我发现这篇关于范围的文章和这个例子说明了不使用select 的好处，但是在如何之间找不到任何东西？

解决方案

如何避免选择的一些示例

使用 Dim 'd variables

  Dim rng as Range 
  

将将变量设置为所需范围。有多种方式可以参考单个单元格范围

 设置rng =范围（A1）
设置rng = Cells（1,1）
设置rng = [A1] 
设置rng =范围（NamedRange）
  

或多单元格范围

 设置rng =范围（A1：B10 ）
设置rng =范围（单元格（1,1），单元格（2,10））
设置rng = [A1：B10] 
设置rng =范围（AnotherNamedRange）
  

所有上述示例均指向活动工作表上的单元格。除非你特别想要使用活动工作表，否则更好的是将工作表变量

<$ p $设置ws =工作表（Sheet1）
设置rng = ws.Cells（1,1）

再次，这是指活动工作簿，所以你可能想在这里也是明确的。

  Dim wb As Workbook 
设置wb = Application.Workbooks（Book1）
设置rng = wb.Worksheets（范围（A1）
  

通过范围到您的子和函数作为范围变量

  Sub ClearRange（r as Range）
 r.ClearContents 
'.... 
 End Sub 
 
 Sub MyMacro（）
 Dim rng作为范围
设置rng = [A1：B10] 
 ClearRange rng 
 End Sub 
  

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

设置rng1 = [A1：A10] 
设置rng2 = [B1：B10] 
 rng1.Copy rng2 
  

如果您循环使用一系列单元格，复制范围值通常会更好（更快）到一个变量数组，然后循环

  Dim dat As Variant 
 Dim rng As Range 
 Dim i As Long 
 
设置rng = [A1：A10000] 
 dat = rng.Value'dat现在是数组（1到10000，1到1）
 for i = LBound （dat，1）到UBound（dat，1）
 dat（i，1）= dat（i，1）* 10'或者你需要执行的任何操作
 next 
 rng。 Value = dat'将新值重新放在页面
  

这是一个很小的可爱者， p>

I've heard much about the understandable abhorrence of using .Select in Excel VBA, but 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.

I have found this article on ranges and this example on the benefits of not using select but can't find anything on how?

解决方案

Some examples of how to avoid select

Use Dim'd variables

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 = [A1]
Set rng = Range("NamedRange")

or a multi-cell range

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

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)

Again, this refers to the active workbook, so you may want to be explicit here too.

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

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 = [A1:B10]
    ClearRange rng
End Sub

You should also apply Methods (such as Find and Copy) to variables

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = [A1:A10]
Set rng2 = [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 = [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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！