Cách tránh sử dụng Select trong Excel VBA

552
BiGXERO 2012-05-23 19:57.

Tôi đã nghe nhiều về sự ghê tởm có thể hiểu được khi sử dụng .Selecttrong Excel VBA, nhưng tôi không chắc về cách tránh sử dụng nó. Tôi nhận thấy rằng mã của tôi sẽ có thể tái sử dụng nhiều hơn nếu tôi có thể sử dụng các biến thay vì các Selecthàm. Tuy nhiên, tôi không chắc chắn làm thế nào để tham chiếu đến những thứ (như ActiveCell, v.v.) nếu không sử dụng Select.

Tôi đã tìm thấy bài viết này về phạm viví dụ này về lợi ích của việc không sử dụng select , nhưng tôi không thể tìm thấy bất kỳ điều gì về cách thực hiện .

15 answers

578
chris neilsen 2012-05-24 00:23.

Một số ví dụ về cách tránh chọn

Sử dụng Dimbiến 'd

Dim rng as Range

Setbiến trong phạm vi yêu cầu. Có nhiều cách để tham chiếu đến một phạm vi ô đơn:

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

Hoặc một phạm vi nhiều ô:

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)

Bạn có thể sử dụng phím tắt cho Evaluatephương pháp này, nhưng cách này kém hiệu quả hơn và thường nên tránh trong mã sản xuất.

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

Tất cả các ví dụ trên đề cập đến các ô trên trang tính hiện hoạt . Trừ khi bạn đặc biệt muốn chỉ làm việc với trang tính đang hoạt động, tốt hơn là bạn nên làm mờ một Worksheetbiến:

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

Nếu bạn làm muốn làm việc với ActiveSheet, cho rõ ràng nó là tốt nhất để được rõ ràng. Nhưng hãy cẩn thận, vì một số Worksheetphương pháp thay đổi trang tính hiện hoạt.

Set rng = ActiveSheet.Range("A1")

Một lần nữa, điều này đề cập đến sổ làm việc đang hoạt động . Trừ khi bạn đặc biệt muốn chỉ làm việc với ActiveWorkbookhoặc ThisWorkbook, tốt hơn là bạn nên Dim một Workbookbiến.

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

Nếu bạn làm muốn làm việc với ActiveWorkbook, cho rõ ràng nó là tốt nhất để được rõ ràng. Nhưng hãy cẩn thận, vì nhiều WorkBookphương pháp thay đổi sổ đang hoạt động.

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

Bạn cũng có thể sử dụng ThisWorkbookđối tượng để tham chiếu đến sách có chứa mã đang chạy.

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

Một đoạn mã phổ biến (không tốt) là mở sách, lấy một số dữ liệu rồi đóng lại

Thật tệ:

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

Và nó sẽ tốt hơn như:

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

Chuyển phạm vi cho các Subs và Functions của bạn dưới dạng các biến Phạm vi:

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

Bạn cũng nên áp dụng các Phương thức (chẳng hạn như FindCopy) cho các biến:

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

Nếu bạn đang lặp qua một dải ô thì tốt hơn (nhanh hơn) trước tiên là sao chép các giá trị của dải ô vào một mảng biến thể và lặp qua đó:

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

Đây là một thử nghiệm nhỏ cho những gì có thể.

217
Siddharth Rout 2012-05-24 00:33.

Có hai lý do chính tại sao .Select, .Activate, Selection, Activecell, Activesheet, Activeworkbook,, vv nên tránh

  1. Nó làm chậm mã của bạn.
  2. Nó thường là nguyên nhân chính gây ra lỗi thời gian chạy.

Làm thế nào để chúng ta tránh nó?

1) Trực tiếp làm việc với các đối tượng liên quan

Hãy xem xét mã này

Sheets("Sheet1").Activate
Range("A1").Select
Selection.Value = "Blah"
Selection.NumberFormat = "@"

Mã này cũng có thể được viết là

With Sheets("Sheet1").Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

2) Nếu được yêu cầu, hãy khai báo các biến của bạn. Đoạn mã tương tự ở trên có thể được viết dưới dạng

Dim ws as worksheet

Set ws = Sheets("Sheet1")

With ws.Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With
89
Rick supports Monica 2014-05-29 04:04.

Một điểm nhấn nhỏ mà tôi sẽ thêm vào tất cả các câu trả lời xuất sắc được đưa ra trước đây:

Có lẽ điều lớn nhất bạn có thể làm để tránh sử dụng Chọn là sử dụng phạm vi được đặt tên (kết hợp với tên biến có ý nghĩa) trong mã VBA của bạn càng nhiều càng tốt . Điểm này đã được đề cập ở trên, nhưng nó đã được làm bóng hơn một chút; tuy nhiên, nó đáng được quan tâm đặc biệt.

Dưới đây là một số lý do bổ sung để sử dụng tự do các phạm vi đã đặt tên, mặc dù tôi chắc chắn rằng tôi có thể nghĩ ra nhiều hơn.

Các phạm vi được đặt tên giúp mã của bạn dễ đọc và dễ hiểu hơn.

Thí dụ:

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")
' E.g, "Months" might be a named range referring to A1:A12

Set MonthlySales = Range("MonthlySales")
' E.g, "Monthly Sales" might be a named range referring to B1:B12

Dim Month As Range
For Each Month in Months
    Debug.Print MonthlySales(Month.Row)
Next Month

Rõ ràng là các phạm vi được đặt tên MonthsMonthlySaleschứa những gì cũng như thủ tục đang thực hiện những gì.

Tại sao nó lại quan trọng? Một phần vì người khác dễ hiểu hơn, nhưng ngay cả khi bạn là người duy nhất nhìn thấy hoặc sử dụng mã của bạn, bạn vẫn nên sử dụng các dải ô đã đặt tên và tên biến tốt vì bạn sẽ quên mất ý định của bạn với nó. một năm sau, và bạn sẽ lãng phí 30 phút chỉ để tìm hiểu mã của bạn đang làm gì.

Các phạm vi được đặt tên đảm bảo rằng các macro của bạn không bị hỏng khi (không phải nếu!) Cấu hình của bảng tính thay đổi.

Hãy xem xét, nếu ví dụ trên được viết như thế này:

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range("A1:A12")
Set rng2 = Range("B1:B12")

Dim rng3 As Range
For Each rng3 in rng1
    Debug.Print rng2(rng3.Row)
Next rng3

Mã này lúc đầu sẽ hoạt động tốt - đó là cho đến khi bạn hoặc người dùng trong tương lai quyết định "gee wiz, tôi nghĩ rằng tôi sẽ thêm một cột mới với năm vào Column A!", Hoặc đặt một cột chi phí giữa các tháng và cột bán hàng hoặc thêm tiêu đề vào mỗi cột. Bây giờ, mã của bạn bị hỏng. Và bởi vì bạn đã sử dụng những tên biến khủng khiếp, bạn sẽ mất nhiều thời gian hơn để tìm ra cách sửa nó.

Nếu bạn đã sử dụng các dải ô đã đặt tên để bắt đầu, các cột MonthsSalescó thể được di chuyển xung quanh tất cả những gì bạn muốn và mã của bạn sẽ tiếp tục hoạt động tốt.

48
MattB 2014-02-28 11:09.

Tôi sẽ đưa ra câu trả lời ngắn vì những người khác đã đưa ra câu trả lời dài.

Bạn sẽ nhận được .select và .activate bất cứ khi nào bạn ghi macro và sử dụng lại chúng. Khi bạn chọn một ô hoặc trang tính, nó chỉ làm cho nó hoạt động. Từ thời điểm đó trở đi bất cứ khi nào bạn sử dụng các tham chiếu không đủ tiêu chuẩn như Range.Valuehọ chỉ sử dụng ô và trang tính đang hoạt động. Điều này cũng có thể có vấn đề nếu bạn không xem mã của mình được đặt ở đâu hoặc người dùng nhấp vào sổ làm việc.

Vì vậy, bạn có thể loại bỏ những vấn đề này bằng cách tham chiếu trực tiếp các ô của mình. Chọn nơi nào để đến:

'create and set a range
Dim Rng As Excel.Range
Set Rng = Workbooks("Book1").Worksheets("Sheet1").Range("A1")
'OR
Set Rng = Workbooks(1).Worksheets(1).Cells(1, 1)

Hoặc bạn có thể

'Just deal with the cell directly rather than creating a range
'I want to put the string "Hello" in Range A1 of sheet 1
Workbooks("Book1").Worksheets("Sheet1").Range("A1").value = "Hello"
'OR
Workbooks(1).Worksheets(1).Cells(1, 1).value = "Hello"

Có nhiều cách kết hợp các phương pháp này, nhưng đó sẽ là ý tưởng chung được thể hiện càng sớm càng tốt cho những người thiếu kiên nhẫn như tôi.

34
Noname 2015-02-25 05:41.

"... và tôi nhận thấy rằng mã của tôi sẽ có thể tái sử dụng nhiều hơn nếu tôi có thể sử dụng các biến thay vì Chọn các hàm."

Mặc dù tôi không thể nghĩ ra bất kỳ tình huống nào khác ngoài một số ít các tình huống .Selectsẽ là lựa chọn tốt hơn so với việc tham khảo trực tiếp tế bào, tôi sẽ bảo vệ Selectionvà chỉ ra rằng không nên loại bỏ nó vì những lý do tương tự mà .Selectnên tránh.

Đôi khi, việc gán các quy trình phụ macro ngắn, tiết kiệm thời gian cho các tổ hợp phím nóng có sẵn bằng cách nhấn một vài phím giúp tiết kiệm rất nhiều thời gian. Có thể chọn một nhóm ô để ban hành mã hoạt động trên các công trình kỳ diệu khi xử lý dữ liệu bỏ túi không phù hợp với định dạng dữ liệu trên toàn trang tính. Cũng giống như cách bạn có thể chọn một nhóm ô và áp dụng thay đổi định dạng, chọn một nhóm ô để chạy mã macro đặc biệt có thể là một cách tiết kiệm thời gian chính.

Ví dụ về khung phụ dựa trên lựa chọn:

Public Sub Run_on_Selected()
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Selected_Visible()
    'this is better for selected ranges on filtered data or containing hidden rows/columns
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL.SpecialCells(xlCellTypeVisible)
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Discontiguous_Area()
    'this is better for selected ranges of discontiguous areas
    Dim ara As Range, rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each ara In rSEL.Areas
        Debug.Print ara.Address(0, 0)
        'cell group operational code here
        For Each rng In ara.Areas
            Debug.Print rng.Address(0, 0)
            'cell-by-cell operational code here
        Next rng
    Next ara
    Set rSEL = Nothing
End Sub

Mã thực sự cần xử lý có thể là bất kỳ thứ gì từ một dòng đến nhiều mô-đun. Tôi đã sử dụng phương pháp này để bắt đầu các quy trình chạy lâu dài trên một vùng chọn ô có chứa tên tệp của sổ làm việc bên ngoài.

Tóm lại, đừng loại bỏ Selectiondo sự liên kết chặt chẽ của nó với .SelectActiveCell. Là một thuộc tính trang tính, nó có nhiều mục đích khác.

(Có, tôi biết câu hỏi này là về vấn đề này .Select, Selectionnhưng tôi muốn loại bỏ bất kỳ quan niệm sai lầm nào mà các lập trình viên VBA mới làm quen có thể suy ra.)

30
Vityata 2016-03-09 00:04.

Tránh SelectActivatelà động thái giúp bạn phát triển VBA tốt hơn một chút. Nói chung, SelectActivateđược sử dụng khi ghi macro, do đó trang Parenttính hoặc phạm vi luôn được coi là trang hoạt động.

Đây là cách bạn có thể tránh SelectActivatetrong các trường hợp sau:


Thêm một Trang tính mới và sao chép một ô trên đó:

Từ (mã được tạo bằng trình ghi macro):

Sub Makro2()
    Range("B2").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Tabelle1").Select
    Sheets("Tabelle1").Name = "NewName"
    ActiveCell.FormulaR1C1 = "12"
    Range("B2").Select
    Selection.Copy
    Range("B3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Đến:

Sub TestMe()
    Dim ws As Worksheet
    Set ws = Worksheets.Add
    With ws
        .Name = "NewName"
        .Range("B2") = 12
        .Range("B2").Copy Destination:=.Range("B3")
    End With
End Sub

Khi bạn muốn sao chép phạm vi giữa các trang tính:

Từ:

Sheets("Source").Select
Columns("A:D").Select
Selection.Copy
Sheets("Target").Select
Columns("A:D").Select
ActiveSheet.Paste

Đến:

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").Range("a1")

Sử dụng phạm vi được đặt tên ưa thích

Bạn có thể truy cập chúng bằng [], thực sự đẹp, so với cách khác. Kiểm tra bản thân:

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")    
Set MonthlySales = Range("MonthlySales")

Set Months =[Months]
Set MonthlySales = [MonthlySales]

Ví dụ từ trên sẽ trông như thế này:

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").[A1]

Không phải sao chép giá trị mà lấy chúng

Thông thường, nếu bạn muốn select, rất có thể bạn đang sao chép một cái gì đó. Nếu bạn chỉ quan tâm đến các giá trị, đây là một lựa chọn tốt để tránh chọn:

Range("B1:B6").Value = Range("A1:A6").Value


Cố gắng luôn luôn tham khảo Bảng tính

Đây có lẽ là lỗi phổ biến nhất trong vba . Bất cứ khi nào bạn sao chép phạm vi, đôi khi trang tính không được tham chiếu và do đó VBA coi trang tính đó là ActiveWorksheet.

'This will work only if the 2. Worksheet is selected!
Public Sub TestMe()
    Dim rng As Range
    Set rng = Worksheets(2).Range(Cells(1, 1), Cells(2, 2)).Copy
End Sub

'This works always!
Public Sub TestMe2()
    Dim rng As Range
    With Worksheets(2)
        .Range(.Cells(1, 1), .Cells(2, 2)).Copy
    End With
End Sub

Tôi có thể thực sự không bao giờ sử dụng .Selecthoặc .Activatecho bất cứ điều gì?

  • Một ví dụ điển hình về thời điểm bạn có thể hợp lý trong việc sử dụng .Activate.Selectlà khi bạn muốn đảm bảo rằng một Trang tính cụ thể được chọn vì lý do trực quan. Ví dụ: Excel của bạn sẽ luôn mở với trang tính bìa được chọn trước tiên, không quan tâm đến đó là Trang tính Hoạt động khi tệp được đóng.

Vì vậy, một cái gì đó giống như đoạn mã dưới đây là hoàn toàn OK:

Private Sub Workbook_Open()
    Worksheets("Cover").Activate
End Sub
30
Francesco Baruchelli 2012-05-23 20:11.

Xin lưu ý rằng trong phần sau, tôi đang so sánh cách tiếp cận Chọn (cách mà OP muốn tránh) với cách tiếp cận Phạm vi (và đây là câu trả lời cho câu hỏi). Vì vậy, đừng ngừng đọc khi bạn nhìn thấy Lựa chọn đầu tiên.

Nó thực sự phụ thuộc vào những gì bạn đang cố gắng làm. Dù sao, một ví dụ đơn giản có thể hữu ích. Giả sử bạn muốn đặt giá trị của ô hiện hoạt thành "foo". Sử dụng ActiveCell, bạn sẽ viết một cái gì đó như sau:

Sub Macro1()
    ActiveCell.Value = "foo"
End Sub

Nếu bạn muốn sử dụng nó cho một ô không phải là ô đang hoạt động, chẳng hạn như cho "B2", trước tiên bạn nên chọn nó, như sau:

Sub Macro2()
    Range("B2").Select
    Macro1
End Sub

Sử dụng Dãy, bạn có thể viết một macro chung chung hơn có thể được sử dụng để đặt giá trị của bất kỳ ô nào bạn muốn thành bất cứ điều gì bạn muốn:

Sub SetValue(cellAddress As String, aVal As Variant)
    Range(cellAddress).Value = aVal
End Sub

Sau đó, bạn có thể viết lại Macro2 thành:

Sub Macro2()
    SetCellValue "B2", "foo"
End Sub

Và Macro1 như:

Sub Macro1()
    SetValue ActiveCell.Address, "foo"
End Sub
18
user1644564 2014-08-25 23:54.

Luôn nêu rõ sổ làm việc, trang tính và ô / phạm vi.

Ví dụ:

Thisworkbook.Worksheets("fred").cells(1,1)
Workbooks("bob").Worksheets("fred").cells(1,1)

Bởi vì người dùng cuối sẽ luôn chỉ nhấp vào các nút và ngay sau khi tiêu điểm di chuyển khỏi sổ làm việc, mã muốn hoạt động thì mọi thứ hoàn toàn sai.

Và không bao giờ sử dụng chỉ mục của sổ làm việc.

Workbooks(1).Worksheets("fred").cells(1,1)

Bạn không biết những sổ làm việc khác sẽ mở khi người dùng chạy mã của bạn.

11
LFB 2018-06-17 04:18.

Những phương pháp này khá kỳ thị, vì vậy việc Cách tránh sử dụng Select trong Excel VBA và Jeeped dẫn đầu vì mục đích vẽ một đường trên cát:

Gọi .Activate, .Select, Selection, ActiveSomethingphương pháp / tài sản

Về cơ bản vì chúng được gọi chủ yếu để xử lý đầu vào của người dùng thông qua giao diện người dùng ứng dụng. Vì chúng là các phương thức được gọi khi người dùng xử lý các đối tượng thông qua giao diện người dùng, chúng là những phương thức được ghi lại bởi trình ghi macro và đó là lý do tại sao việc gọi chúng là dễ hỏng hoặc thừa đối với hầu hết các tình huống: bạn không cần phải chọn để thực hiện một hành động với Selectionngay sau đó.

Tuy nhiên, định nghĩa này giải quyết các tình huống mà chúng được gọi là:

Khi nào thì gọi .Activate, .Select, .Selection, .ActiveSomethingphương pháp / tài sản

Về cơ bản khi bạn mong đợi người dùng cuối cùng đóng một vai trò trong việc thực thi.

Nếu bạn đang phát triển và mong đợi người dùng chọn các phiên bản đối tượng để mã của bạn xử lý, thì .Selectionhoặc .ActiveObjectlà phù hợp.

Mặt khác, .Select.Activateđược sử dụng khi bạn có thể suy ra hành động tiếp theo của người dùng và bạn muốn mã của mình hướng dẫn người dùng, có thể tiết kiệm cho họ một khoảng thời gian và những cú nhấp chuột. Ví dụ: nếu mã của bạn vừa tạo một phiên bản hoàn toàn mới của biểu đồ hoặc cập nhật một phiên bản, người dùng có thể muốn kiểm tra nó và bạn có thể gọi .Activatenó hoặc trang tính của nó để tiết kiệm thời gian tìm kiếm nó; hoặc nếu bạn biết người dùng sẽ cần cập nhật một số giá trị phạm vi, bạn có thể chọn phạm vi đó theo chương trình.

6
Eleshar 2016-11-21 05:02.

Việc sử dụng IMHO .selectđến từ những người, những người như tôi bắt đầu học VBA thông qua việc ghi lại các macro và sau đó sửa đổi mã mà không nhận ra rằng .selectvà sau đó selectionchỉ là một người trung gian không cần thiết.

.select Có thể tránh, như nhiều bài đã đăng, bằng cách làm việc trực tiếp với các đối tượng đã có sẵn, cho phép tham chiếu gián tiếp khác nhau như tính toán i và j theo cách phức tạp và sau đó chỉnh sửa ô (i, j), v.v.

Nếu không, không có gì hoàn toàn sai với .selectbản thân và bạn có thể dễ dàng tìm thấy cách sử dụng cho việc này, ví dụ: tôi có một bảng tính mà tôi điền ngày tháng, kích hoạt macro thực hiện một số phép thuật với nó và xuất nó ở định dạng có thể chấp nhận được trên một trang tính riêng biệt, tuy nhiên, yêu cầu một số đầu vào thủ công cuối cùng (không thể đoán trước) vào một ô liền kề. Vì vậy, đây là thời điểm để .selecttiết kiệm cho tôi di chuyển chuột và nhấp chuột bổ sung.

4
FinPro.Online 2016-09-08 20:36.

Để tránh sử dụng .Selectphương thức này, bạn có thể đặt một biến bằng thuộc tính mà bạn muốn.

► Ví dụ, nếu bạn muốn giá trị trong ô đó, Cell A1bạn có thể đặt một biến bằng thuộc tính giá trị của ô đó.

  • Thí dụ valOne = Range("A1").Value

► Ví dụ, nếu bạn muốn tên mã của thuộc tính 'Sheet3 you could set a variable equal to theCodename` của trang tính đó.

  • Thí dụ valTwo = Sheets("Sheet3").Codename
3
PGSystemTester 2018-08-17 10:07.

Tôi nhận thấy rằng không có câu trả lời nào trong số này đề cập đến Thuộc tính .Offset . Điều này cũng có thể được sử dụng để tránh sử dụng Selecthành động khi thao tác các ô nhất định, đặc biệt là tham chiếu đến một ô đã chọn (như OP đề cập với ActiveCell).

Dưới đây là một vài ví dụ.

Tôi cũng sẽ giả sử "ActiveCell" là J4 .

ActiveCell.Offset(2, 0).Value = 12

  • Điều này sẽ thay đổi ô J6thành giá trị 12
  • Số trừ -2 sẽ tham chiếu đến J2

ActiveCell.Offset(0,1).Copy ActiveCell.Offset(,2)

  • Điều này sẽ sao chép các tế bào trong k4để L4.
  • Lưu ý rằng "0" là không cần thiết trong tham số offset nếu không cần thiết (, 2)
  • Tương tự như ví dụ trước, trừ 1 sẽ là i4

ActiveCell.Offset(, -1).EntireColumn.ClearContents

  • Thao tác này sẽ xóa các giá trị trong tất cả các ô trong cột k.

Những điều này không có nghĩa là chúng "tốt hơn" so với các tùy chọn trên, mà chỉ liệt kê các lựa chọn thay thế.

1
Dominique 2020-10-31 05:55.

Làm thế nào để tránh sao chép-dán?

Hãy đối mặt với nó: cái này xuất hiện rất nhiều khi ghi macro:

Range("X1").Select
Selection.Copy
Range("Y9).Select
Selection.Paste

Trong khi điều duy nhất người đó muốn là:

Range("Y9").Value = Range("X1").Value

Do đó, thay vì sử dụng copy-paste trong macro VBA, tôi khuyên bạn nên làm theo cách đơn giản sau:

Destination_Range.Value = Source_Range.Value
0
barneyos 2019-10-30 23:45.

Làm việc với tính năng .Parent , ví dụ này cho thấy cách chỉ đặt một tham chiếu myRng cho phép truy cập động vào toàn bộ môi trường mà không cần bất kỳ .Select, .Activate, .Activecell, .ActiveWorkbook, .ActiveSheet, v.v. (Không có bất kỳ tính năng .Child chung chung nào .)

Sub ShowParents()
    Dim myRng As Range
    Set myRng = ActiveCell
    Debug.Print myRng.Address                    ' An address of the selected cell
    Debug.Print myRng.Parent.name                ' The name of sheet, where MyRng is in
    Debug.Print myRng.Parent.Parent.name         ' The name of workbook, where MyRng is in
    Debug.Print myRng.Parent.Parent.Parent.name  ' The name of application, where MyRng is in

    ' You may use this feature to set reference to these objects
    Dim mySh  As Worksheet
    Dim myWbk As Workbook
    Dim myApp As Application

    Set mySh = myRng.Parent
    Set myWbk = myRng.Parent.Parent
    Set myApp = myRng.Parent.Parent.Parent
    Debug.Print mySh.name, mySh.Cells(10, 1).Value
    Debug.Print myWbk.name, myWbk.Sheets.Count
    Debug.Print myApp.name, myApp.Workbooks.Count

    ' You may use dynamically addressing
    With myRng
        .Copy

        ' Pastes in D1 on sheet 2 in the same workbook, where the copied cell is
        .Parent.Parent.Sheets(2).Range("D1").PasteSpecial xlValues

        ' Or myWbk.Sheets(2).Range("D1").PasteSpecial xlValues

        ' We may dynamically call active application too
        .Parent.Parent.Parent.CutCopyMode = False

        ' Or myApp.CutCopyMode = False
    End With
End Sub
0
Geoff Griswald 2020-03-03 03:04.

Lý do chính không bao giờ sử dụng Select hoặc Activesheet là vì hầu hết mọi người sẽ có ít nhất một vài sổ làm việc khác đang mở (đôi khi hàng chục) khi họ chạy macro của bạn và nếu họ nhấp ra khỏi trang tính của bạn trong khi macro của bạn đang chạy và nhấp vào một số sổ khác cuốn sách mà họ đã mở, sau đó "Activesheet" thay đổi và sổ làm việc đích cho lệnh "Chọn" không đủ tiêu chuẩn cũng thay đổi.

Tốt nhất, macro của bạn sẽ bị lỗi, tệ nhất là bạn có thể kết thúc việc ghi giá trị hoặc thay đổi các ô trong sổ làm việc sai mà không có cách nào để "Hoàn tác" chúng.

Tôi có một quy tắc vàng đơn giản mà tôi tuân theo: Thêm các biến có tên "wb" và "ws" cho đối tượng Workbook và đối tượng Worksheet và luôn sử dụng các biến đó để tham chiếu đến sổ macro của tôi. Nếu tôi cần tham khảo nhiều cuốn sách hoặc nhiều trang, tôi sẽ thêm nhiều biến hơn.

Ví dụ,

Dim wb as Workbook
Dim ws as Worksheet
Set wb = ThisWorkBook
Set ws = wb.sheets("Output")

Lệnh "Set wb = ThisWorkbook" là hoàn toàn chính. "ThisWorkbook" là một giá trị đặc biệt trong Excel và nó có nghĩa là sổ làm việc mà mã VBA của bạn hiện đang chạy . Một phím tắt rất hữu ích để đặt biến Workbook của bạn với.

Sau khi bạn đã hoàn thành việc đó ở đầu Sub của mình, việc sử dụng chúng không thể đơn giản hơn, chỉ cần sử dụng chúng ở bất cứ đâu bạn muốn sử dụng "Lựa chọn":

Vì vậy, để thay đổi giá trị của ô "A1" trong "Đầu ra" thành "Xin chào", thay vì:

Sheets("Output").Activate
ActiveSheet.Range("A1").Select
Selection.Value = "Hello"

Bây giờ chúng tôi có thể làm điều này:

ws.Range("A1").Value = "Hello"

Điều này không chỉ đáng tin cậy hơn nhiều và ít có khả năng bị lỗi hơn nếu người dùng đang làm việc với nhiều bảng tính; nó cũng ngắn hơn, nhanh hơn và dễ viết hơn.

Như một phần thưởng bổ sung, nếu bạn luôn đặt tên cho các biến của mình là "wb" và "ws", bạn có thể sao chép và dán mã từ cuốn sách này sang cuốn sách khác và nó thường sẽ hoạt động với những thay đổi tối thiểu cần thiết, nếu có.

Related questions

MORE COOL STUFF

Cate Blanchett chia tay chồng sau 3 ngày bên nhau và vẫn kết hôn với anh ấy 25 năm sau

Cate Blanchett chia tay chồng sau 3 ngày bên nhau và vẫn kết hôn với anh ấy 25 năm sau

Cate Blanchett đã bất chấp những lời khuyên hẹn hò điển hình khi cô gặp chồng mình.

Tại sao Michael Sheen là một diễn viên phi lợi nhuận

Tại sao Michael Sheen là một diễn viên phi lợi nhuận

Michael Sheen là một diễn viên phi lợi nhuận nhưng chính xác thì điều đó có nghĩa là gì?

Hallmark Star Colin Egglesfield Các món ăn gây xúc động mạnh đối với người hâm mộ tại RomaDrama Live! [Loại trừ]

Hallmark Star Colin Egglesfield Các món ăn gây xúc động mạnh đối với người hâm mộ tại RomaDrama Live! [Loại trừ]

Ngôi sao của Hallmark Colin Egglesfield chia sẻ về những cuộc gặp gỡ với người hâm mộ ly kỳ tại RomaDrama Live! cộng với chương trình INSPIRE của anh ấy tại đại hội.

Tại sao bạn không thể phát trực tuyến 'chương trình truyền hình phía Bắc'

Tại sao bạn không thể phát trực tuyến 'chương trình truyền hình phía Bắc'

Bạn sẽ phải phủi sạch đầu đĩa Blu-ray hoặc DVD để xem tại sao Northern Exposure trở thành một trong những chương trình nổi tiếng nhất của thập niên 90.

Where in the World Are You? Take our GeoGuesser Quiz

Where in the World Are You? Take our GeoGuesser Quiz

The world is a huge place, yet some GeoGuessr players know locations in mere seconds. Are you one of GeoGuessr's gifted elite? Take our quiz to find out!

8 công dụng tuyệt vời của Baking Soda và Giấm

8 công dụng tuyệt vời của Baking Soda và Giấm

Bạn biết đấy, hai sản phẩm này là nguồn điện để làm sạch, riêng chúng. Nhưng cùng với nhau, chúng có một loạt công dụng hoàn toàn khác.

Hạn hán, biến đổi khí hậu đe dọa tương lai của thủy điện Hoa Kỳ

Hạn hán, biến đổi khí hậu đe dọa tương lai của thủy điện Hoa Kỳ

Thủy điện rất cần thiết cho lưới điện của Hoa Kỳ, nhưng nó chỉ tạo ra năng lượng khi có nước di chuyển. Bao nhiêu nhà máy thủy điện có thể gặp nguy hiểm khi các hồ và sông cạn kiệt?

Quyên góp tóc của bạn để giúp giữ nước sạch của chúng tôi

Quyên góp tóc của bạn để giúp giữ nước sạch của chúng tôi

Tóc tỉa từ các tiệm và các khoản quyên góp cá nhân có thể được tái sử dụng như những tấm thảm thấm dầu và giúp bảo vệ môi trường.

Một thương hiệu kế thừa khác có được cuộc sống mới khi những người chủ mới của Black Opal có được Hội chợ thời trang

Một thương hiệu kế thừa khác có được cuộc sống mới khi những người chủ mới của Black Opal có được Hội chợ thời trang

Desiree Rogers, trái, và Cheryl Mayberry McKissack Chuyển sang, Fenty Beauty và Pat McGrath, có một đế chế làm đẹp mới do phụ nữ làm chủ đang trỗi dậy. Sau thương vụ mua lại mỹ phẩm Black Opal vào tháng 9, đội ngũ quyền lực của Giám đốc điều hành công ty Desiree Rogers và Chủ tịch Cheryl Mayberry McKissack đã thông báo mua lại thương hiệu làm đẹp tiên phong Fashion Fair từ người sáng lập Johnson Publishing Company (JPC).

Ngoài việc trở nên vô nghĩa, các bên bộc lộ giới tính giờ đây đã trở nên chết người

Ngoài việc trở nên vô nghĩa, các bên bộc lộ giới tính giờ đây đã trở nên chết người

Đối với những người có quá nhiều thời gian, tiệc tiết lộ giới tính là một cách thú vị để không cần áp đặt những định kiến ​​hạn chế lên thai nhi trước một đối tượng bạn bè giả vờ quan tâm một cách lịch sự. Tuy nhiên, việc chỉ dựa vào những chiếc bánh nướng có màu nhân tạo để biểu thị một tiếng hoo-ha hoặc một đứa trẻ đi tè đã trở nên xa xỉ trong các bậc cha mẹ trên Instagram.

Kirstjen Nielsen đang cố gắng đổi mới bản thân với tư cách là một người phụ nữ 'nói ra sự thật cho sức mạnh'

Kirstjen Nielsen đang cố gắng đổi mới bản thân với tư cách là một người phụ nữ 'nói ra sự thật cho sức mạnh'

Hôm thứ Tư, cựu Bộ trưởng An ninh Nội địa Kirstjen Nielsen vì một lý do nào đó đã được đưa ra một diễn đàn để phát biểu tại Hội nghị thượng đỉnh Những người phụ nữ quyền lực nhất của Fortune. Và có thể đoán trước được, người phụ nữ giám sát các nỗ lực (đang diễn ra) của chính quyền Trump nhằm chia cắt các gia đình ở biên giới đã sử dụng thời gian của mình như một cơ hội để tự bảo vệ mình và tự nhận mình là người — và hãy hít thở sâu ở đây — “đã nói sự thật với quyền lực.

Toyota Racing Development sẽ cho ra mắt Supra 3000GT Concept, Massive Wing vào tháng tới

Toyota Racing Development sẽ cho ra mắt Supra 3000GT Concept, Massive Wing vào tháng tới

Vì thế hệ mới nhất của chiếc xe điều chỉnh được yêu thích trên thế giới hiện đã có mặt tại đây, nên chỉ có điều kiện là Toyota Racing Development sẽ đưa phiên bản Supra thế hệ thứ năm sửa đổi của riêng mình đến SEMA vào tháng tới. Toyota đã cho chúng tôi một gợi ý về những gì sẽ xảy ra trong tuần này: một chiếc Concept GR Supra 3000GT hiện đại.

Nicky Hilton Forced to Borrow Paris' 'I Love Paris' Sweatshirt After 'Airline Loses All [My] Luggage'

Nicky Hilton Forced to Borrow Paris' 'I Love Paris' Sweatshirt After 'Airline Loses All [My] Luggage'

Nicky Hilton Rothschild's luggage got lost, but luckily she has an incredible closet to shop: Sister Paris Hilton's!

Kate Middleton dành một ngày bên bờ nước ở London, cùng với Jennifer Lopez, Julianne Hough và hơn thế nữa

Kate Middleton dành một ngày bên bờ nước ở London, cùng với Jennifer Lopez, Julianne Hough và hơn thế nữa

Kate Middleton dành một ngày bên bờ nước ở London, cùng với Jennifer Lopez, Julianne Hough và hơn thế nữa. Từ Hollywood đến New York và mọi nơi ở giữa, hãy xem các ngôi sao yêu thích của bạn đang làm gì!

17 tuổi bị đâm chết trong khi 4 người khác bị thương trong một cuộc tấn công bằng dao trên sông Wisconsin

17 tuổi bị đâm chết trong khi 4 người khác bị thương trong một cuộc tấn công bằng dao trên sông Wisconsin

Các nhà điều tra đang xem xét liệu nhóm và nghi phạm có biết nhau trước vụ tấn công hay không

Thanh thiếu niên, Gia đình Florida Hội đồng quản trị trường học về Luật 'Không nói đồng tính': 'Buộc chúng tôi tự kiểm duyệt'

Thanh thiếu niên, Gia đình Florida Hội đồng quản trị trường học về Luật 'Không nói đồng tính': 'Buộc chúng tôi tự kiểm duyệt'

Vụ kiện, nêu tên một số học khu, lập luận rằng dự luật "Không nói đồng tính" được ban hành gần đây của Florida "có hiệu quả im lặng và xóa bỏ học sinh và gia đình LGBTQ +"

Đường băng hạ cánh

Đường băng hạ cánh

Cuối hè đầu thu là mùa hoài niệm. Những chiếc đèn đường chiếu ánh sáng của chúng qua những con đường đẫm mưa, và những chiếc lá dưới chân - màu đỏ cam tắt trong bóng chạng vạng - là lời nhắc nhở về những ngày đã qua.

Hãy tưởng tượng tạo ra một chiến lược nội dung thực sự CHUYỂN ĐỔI. Nó có thể.

Hãy tưởng tượng tạo ra một chiến lược nội dung thực sự CHUYỂN ĐỔI. Nó có thể.

Vào năm 2021, tôi khuyến khích bạn suy nghĩ lại mọi thứ bạn biết về khách hàng mà bạn phục vụ và những câu chuyện bạn kể cho họ. Lùi lại.

Sự mất mát của voi ma mút đã mở ra trái tim tôi để yêu

Sự mất mát của voi ma mút đã mở ra trái tim tôi để yêu

Vào ngày sinh nhật thứ 9 của Felix The Cat, tôi nhớ về một trong những mất mát lớn nhất trong cuộc đời trưởng thành của tôi - Sophie của tôi vào năm 2013. Tôi đã viết bài luận này và chia sẻ nó trên nền tảng này một thời gian ngắn vào năm 2013.

Khi bạn không thể trở thành người mà Internet muốn bạn trở thành

Khi bạn không thể trở thành người mà Internet muốn bạn trở thành

Tôi ghét từ "tàu đắm". Mọi người cảm thấy thoải mái trong la bàn đạo đức của riêng mình, và khi làm như vậy, họ thấy mình vượt qua sự phán xét.

Language