問題

我聽說過很多關於在Excel VBA中使用.Select可以理解的厭惡,但我不確定如何避免使用它.我發現如果我能夠使用變數而不是Select函式,我的程式碼將更可用.但是,如果不使用Select,我不確定如何引用東西(如ActiveCell等).

我找到了 this article 這個關於不使用select 的好處的示例,但找不到任何關於如何?

  最佳答案

如何避免選擇的一些例子

使用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]
 

所有上述示例都指活動表上的單元格.除非您特別想要使用活動表,否則Dim a 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")
 

再次,這是指活動工作簿.除非您特別想要使用ActiveWorkbookThisWorkbook,否則更好的是Dim a 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和Function s作為範圍變數

 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
 

您還應該將方法(例如 FindCopy)應用於變數

 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
 

這是一個可能的小牛仔。

  相同標籤的其他問題

excelvba