Hướng dẫn active sheet vba - trang hoạt động vba

Khi lập trình trong VBA liên quan tới nhiều Worksheet trong 1 Workbook (1 file Excel), có thể bạn sẽ thấy có 2 phương thức khá giống nhau là Select và Activate. Nhưng sự giống nhau này đôi khi gây nhầm lẫn cho chúng ta. Trong bài viết này, Học Excel Online sẽ giúp các bạn phân biệt phương thức Select với Activate của đối tượng Worksheet trong VBA.

• Cú pháp gọi phương thức của đối tượng Worksheet
• Phân biệt phương thức Select với Activate

Cú pháp gọi phương thức của đối tượng Worksheet

Phân biệt phương thức Select với Activate

Trong bài viết Hướng dẫn cách viết code với đối tượng Worksheet trong VBA Excel chúng ta đã tìm hiểu về cú pháp gọi phương thức của đối tượng Worksheet như sau:.Select

Worksheets(“ABC”).Select.Activate

Worksheets(“ABC”).Activate

• Trong đó:
• Worksheets(“ABC”) là Sheet có tên là ABC
• .Select là để gọi ra phương thức Select của Sheet ABC (có dấu chấm ngăn cách cách giữa đối tượng và phương thức của đối tượng đó)

Phân biệt phương thức Select với Activate

Trong bài viết Hướng dẫn cách viết code với đối tượng Worksheet trong VBA Excel chúng ta đã tìm hiểu về cú pháp gọi phương thức của đối tượng Worksheet như sau:

Worksheets(“ABC”).Select

Worksheets(“ABC”).Activate

Trong đó:

• Worksheets(“ABC”) là Sheet có tên là ABC
• .Select là để gọi ra phương thức Select của Sheet ABC (có dấu chấm ngăn cách cách giữa đối tượng và phương thức của đối tượng đó)
• .Activate là để gọi ra phương thức Acivate của Sheet ABC (có dấu chấm ngăn cách cách giữa đối tượng và phương thức của đối tượng đó)

Select là việc bạn chọn tới 1 Sheet.

Activate là việc bạn kích hoạt tới 1 sheet.

Nếu chỉ làm việc với duy nhất 1 Sheet thì Sheet được chọn chính là Sheet được kích hoạt.

Sự khác nhau cơ bản là:

Bạn có thể chọn cùng 1 lúc nhiều Sheet, nhưng trong các Sheet đó chỉ có 1 Sheet được kích hoạt.

Sheet được chọn đầu tiên trong thao tác chọn nhiều Sheet chính là Sheet được kích hoạt

Khi đó đối tượng ActiveSheet sẽ khác với nhóm đối tượng Sheet được Select.

Ví dụ:

Trong 1 Workbook có 3 Sheet gồm: Sheet1, Sheet2, Sheet3

Chọn cùng 1 lúc Sheet2 và Sheet3, trong đó Sheet3 được viết trược, ta sử dụng cú pháp sau:

• Sheets(Array(“Sheet3”, “Sheet2”)).Select
• Gán giá trị abc vào ô A1 trong Sheet đang được kích hoạt, ta sử dụng cú pháp sau:
• ActiveSheet.Range(“A1”).Value = “abc”

Khi viết trong cùng 1 Sub ta có:

Khi chạy Sub Test_Select_MultiSheet, kết quả thu được là chỉ có duy nhất giá trị abc được điền vào ô A1 trong Sheet3, còn Sheet2 thì không có nội dung gì.

File ví dụ các bạn có thể tải về tại đây: http://bit.ly/2TgwCt8

• Như vậy qua ví dụ trên, chúng ta có thể thấy:: là trạng từ, chỉ 1 đối tượng nào đó đang được kích hoạt. Do đó từ khóa Active không đứng 1 mình mà đứng kèm với đối tượng: ActiveWorkbook, ActiveSheet…
• Chọn cùng lúc nhiều Sheet thông qua từ khóa Array ở bên trong đối tượng Sheets.: là động từ, chỉ hành động kích hoạt 1 đối tượng. Do đó từ khóa Activate luôn đứng ở vị trí sau dấu chấm của đối tượng mà nó kích hoạt: Workbook…Activate, Worksheet…Activate

Sheet3 và Sheet2 cùng được chọn, nhưng Sheet3 viết trước nên Sheet3 là Sheet được Activate

Khi đó ActiveSheet chính là Sheet3. Giá trị abc được gán vào ô A1 trong Sheet3

Do đó khi làm việc với nhiều Worksheet trong VBA, chúng ta hết sức lưu ý phân biệt giữa 2 phương thức này để đảm bảo kết quả lập trình đúng theo ý muốn.

* Chú ý:

Tác giả: duongquan211287

· · · · ·

Bài viết dưới đây, Học Excel Online hướng dẫn đầy đủ cách sử dụng Excel worksheet VBA. Nếu muốn biết cách thực hiện nhanh chóng thì có thể xem bảng hướng dẫn tóm gọn dưới đây. Học Excel Online hướng dẫn đầy đủ cách sử dụng Excel worksheet VBA. Nếu muốn biết cách thực hiện nhanh chóng thì có thể xem bảng hướng dẫn tóm gọn dưới đây.

• Hướng dẫn nhanh về worksheet VBA
• Giới thiệu
• Truy cập worksheet
• Ẩn worksheet
• Bảo vệ Worksheet
• Subscript nằm ngoài phạm vi
• Sử dụng Index (chỉ mục) để truy cập worksheet
• Sử dụng code name của worksheet
• Code name trong các workbook khác
• Sử dụng Activesheet
• Khai báo đối tượng worksheet
• Truy cập worksheet trong Nutshell
• Thêm worksheet
• Xóa worksheet
• Lặp qua các worksheet
• Sử dụng bộ sưu tập Sheets

Hướng dẫn nhanh về worksheet VBA

Giới thiệu

Truy cập worksheet	Ẩn worksheet
Bảo vệ Worksheet	Subscript nằm ngoài phạm vi
Sử dụng Index (chỉ mục) để truy cập worksheet	Worksheets(2)Worksheets(4) Worksheets(4)
Sử dụng code name của worksheetsheet đầu tiên, tính từ bên trái qua ở trên sheet bar	Worksheets(1)
Code name trong các workbook khácsheet đầu tiên, tính từ bên phải qua	Worksheets(Worksheets.Count)
Sử dụng Activesheet	Khai báo đối tượng worksheet
Truy cập worksheet trong Nutshell	Khai báo đối tượng worksheet
Truy cập worksheet trong Nutshell	Thêm worksheet
Xóa worksheet	Dim sh As Worksheet sh As Worksheet
Lưu ý: Các worksheet trong bảng dưới đây không chỉ định workbook, có nghĩa là Worksheet chứ không phải ThisWorkbook.Worksheets, wk.Worksheets… Mục đích để làm các ví dụ dễ đọc hơn. Trên thực tế khi sử dụng worksheet thì phải chỉ định workbook nếu không workbook sẽ được sử dụng theo mặc định.	Tác vụsh = Worksheets(“Sheet1”)
Thêm worksheet	Worksheets.Add
Xóa worksheet	Lưu ý: Các worksheet trong bảng dưới đây không chỉ định workbook, có nghĩa là Worksheet chứ không phải ThisWorkbook.Worksheets, wk.Worksheets… Mục đích để làm các ví dụ dễ đọc hơn. Trên thực tế khi sử dụng worksheet thì phải chỉ định workbook nếu không workbook sẽ được sử dụng theo mặc định. sh = Worksheets.Add
Tác vụ	Cách thực hiện
Truy cập worksheet theo tên	Worksheets(“Sheet1”)
Truy cập worksheet theo vị trí từ bên trái ở trên sheet bar	Truy cập worksheet sheet đầu tiên, tính từ bên trái qua ở trên sheet bar
Truy cập worksheet sheet đầu tiên, tính từ bên phải qua	Truy cập bằng cách sử dụng tên mã worksheet (chỉ workbook hiện tại)
xem phần code name (tên mã) bên dưới	sh.Copy
Truy cập bằng bằng tên mã worksheet (workbook khác)	Truy cập worksheet đang hoạt động
Activesheet	Khai báo biến worksheet
Xóa worksheet	sh.Delete
Lưu ý: Các worksheet trong bảng dưới đây không chỉ định workbook, có nghĩa là Worksheet chứ không phải ThisWorkbook.Worksheets, wk.Worksheets… Mục đích để làm các ví dụ dễ đọc hơn. Trên thực tế khi sử dụng worksheet thì phải chỉ định workbook nếu không workbook sẽ được sử dụng theo mặc định.	Tác vụFalse sh.Delete Application.DisplayAlerts = True
Cách thực hiện	Truy cập worksheet theo tên
Worksheets(“Sheet1”)	Truy cập worksheet theo vị trí từ bên trái ở trên sheet bar sh.Visible = xlSheetVisible
Truy cập worksheet sheet đầu tiên, tính từ bên trái qua ở trên sheet bar	Truy cập worksheet sheet đầu tiên, tính từ bên phải qua i As Long For i = 1 To Worksheets.Count     Debug.Print Worksheets (i) .Name Next i
Truy cập bằng cách sử dụng tên mã worksheet (chỉ workbook hiện tại)	Dim sh As WorksheetFor Each sh In Worksheets    Debug.Print sh.NameNextWorksheet For Each sh In Worksheets     Debug.Print sh.Name Next

Giới thiệu

Ba yếu tố quan trọng nhất của VBA là workbook, worksheet và cells. Trong tất cả các mã chúng ta viết, 90% sẽ liên quan đến 1 hoặc cả 3 yếu tố trên.

Sử dụng worksheet để truy cập vào các ô của nó rất phổ biến trong VBA. Chúng ta có thể sử dụng để ẩn, thêm, di chuyển hoặc sao chép worksheet khác. Tuy nhiên chúng ta sẽ sử dụng chủ yếu để thực hiện các hành động trên một hoặc nhiều ô.

Sử dụng worksheet đơn giản hơn workbook. Với workbook bạn phải mở chúng sau đó tìm thư mục nằm trong đó và kiểm tra xem có đang được sử dụng hay không. Với worksheet, nó có hiển thị trong workbook hay không.

Truy cập worksheet

Trong VBA, mỗi workbook sẽ có tập hợp nhiều worksheet. Mỗi worksheet trong workbook sẽ có mục để nhập vào một bộ sưu tập, bộ sưu tập được gọi là worksheets và hiểu đơn giản là nó chứa các worksheet trong workbook. Bạn phải đặt tên thì mới có thể truy cập vào worksheet.

Đoạn mã dưới đây viết “Hello World” trong Ô A1 của Sheet1, Sheet2 và Sheet3 của workbook hiện tại:

1-Truy cập worksheet

Bộ sưu tập worksheets luôn luôn nằm trong workbook. Nếu chúng ta không chỉ định workbook thì workbook được sử dụng theo mặc định.

2-Truy cập worksheet

Ẩn worksheet

Các ví dụ sau đây cho thấy cách ẩn và cách hiện một worksheet:

3-Ẩn worksheet

Nếu bạn không muốn người dùng truy cập vào worksheet thì có thể đặt ở chế độ “hide” (ẩn). Có nghĩa nó chỉ có thể được hiển thị bằng mã.

4-Ẩn worksheet

Bảo vệ Worksheet

Một ví dụ khác nếu bạn muốn bảo vệ Worksheet:

5-bảo mật wooksheet

Subscript nằm ngoài phạm vi

Khi bạn sử dụng worksheet có thể gặp lỗi.

Điều này có nghĩa bạn đang cố gắng truy cập một worksheet không tồn tại. Lý do có thể:

1. Worksheet được cung cấp cho bộ sưu tập worksheets viết sai chính tả.
2. Tên worksheet đã thay đổi.
3. Worksheet đã bị xóa.
4. Chỉ số này quá lớn, ví dụ : bạn sử dụng worksheets(5) nhưng chỉ có 4 worksheets.
5. Workbook đang sử dụng sai, ví dụ Workbooks ( “book1.xlsx” ) .Worksheets (“Sheet1”) thay vì Workbooks ( “book3.xlsx” ) .Worksheets (“Sheet1”) .Workbooks ( “book1.xlsx” ) .Worksheets (“Sheet1”) thay vì Workbooks ( “book3.xlsx” ) .Worksheets (“Sheet1”) .

Nếu gặp sự cố này hãy sử dụng  Loop Through the worksheets để hiển thị tên của tất cả các worksheet trong bộ sưu tập.

Sử dụng Index (chỉ mục) để truy cập worksheet

Ngoài cách sử dụng tên để truy cập worksheet thì có thể sử dụng Index. Index đề cập đến vị trí tab worksheet trong workbook. Ví dụ đoạn mã sau đây:

6- sử dụng index để cấp quyền wooksheet

Trong ví dụ trên sử dụng Debug.Print để xuất ra Immediate window. Để xem cửa sổ này chọn View→ Immediate window (hoặc ctrl + G).

Sử dụng code name của worksheet

Phương pháp tốt nhất để truy cập worksheet là sử dụng code name (tên mã). 

Thay đổi tên worksheet không làm thay đổi code name  có nghĩa là vẫn có thể tham chiếu worksheet theo tên mã. 

Trong hình ảnh, bạn có thể thấy rằng code name là tên ngoài ngoặc và tên worksheet nằm trong ngoặc.

Bạn có thể thay đổi cả tên worksheet và code name trong cửa sổ thuộc tính của worksheet (xem hình ảnh bên dưới).

8-code-name-properties1

Nếu mã của bạn tham chiếu đến code name thì người dùng có thể thay đổi tên của worksheet và nó sẽ không ảnh hưởng đến mã của bạn. Trong ví dụ dưới đây, chúng tôi tham chiếu trực tiếp worksheet bằng cách sử dụng code name.

9-code-name

Điều này làm cho mã dễ đọc và an toan toàn ngay cả thay đổi tên workbook.

Code name trong các workbook khác

Có một nhược điểm khi sử dụng code name là nó chỉ tham chiếu đến các worksheet trong workbook mà chứa mã như Thisworkbook.

Tuy nhiên chúng ta có thể sử dụng một hàm đơn giản để tìm code name của một worksheet trong workbook khác.

Ví dụ  trên có nghĩa khi người dùng thay đổi code name của worksheet thì mã cũng không bị ảnh hưởng.

Ngoài ra chúng ta có thể sử dụng VBAProject của workbook để làm điều tương tự. Ví dụ:

11-code-name

Tóm tắt code name

1.Code name của worksheet có thể được sử dụng trực tiếp trong mã, ví dụ: Sheet1.Range.Sheet1.Range.

1. Code name vẫn hoạt động nếu tên worksheet thay đổi. 
2. Code name chỉ được sử dụng cho các worksheet trong cùng một workbook với mã.
3. Bất cứ khi nào hiển thị ThisWorkbook.Worksheets (“sheetname”),thì chúng ta có thể thay nó bằng code name của worksheet.ThisWorkbook.Worksheets (“sheetname”),thì chúng ta có thể thay nó bằng code name của worksheet.
4. Có thể sử dụng chức năng SheetFromCodeName để lấy codename của worksheet từ một workbook khác.SheetFromCodeName để lấy codename của worksheet từ một workbook khác.

Sử dụng Activesheet

Đối tượng Activesheet ám chỉ worksheet đang hoạt động. Sử dụng Activesheet nếu xác định chắc chắn bạn muốn sử dụng worksheet đang hoạt động hiện tại.

 Nếu không phải thì xác định worksheet mà bạn muốn sử dụng. 

12-active

Nếu muốn sử dụng phương pháp Range mà không đề cập đến worksheet thì activesheet sẽ mặc định.

Khai báo đối tượng worksheet

Khai báo đối tượng worksheet rất hữu ích giúp mã của bạn gọn gàng và dễ đọc hơn.

Ví dụ dưới đây sẽ hiển thị mã cập nhật các vùng dữ liệu. Sub đầu tiên không khai báo đối tượng worksheet, các sub còn lại khai báo đối tượng worksheet nên mã nhìn rõ ràng hơn.

14-object

Chúng ta cũng có thể sử dụng lệnh With với đối tượng worksheet.

15-object

Truy cập worksheet trong Nutshell

Để không gặp rắc rối trong việc truy cập worksheet, phần này sẽ tóm gọn một cách dễ hiểu.

1.Nếu muốn sử dụng bất kỳ worksheet nào hiện đang hoạt động thì sử dụng Activesheet.

ActiveSheet.Range("A1") = 55

1. Nếu muốn worksheet nằm trong cùng một workbook làm việc với mã thì sử dụng code name.

Sheet1.Range("A1") = 55

3.Nếu worksheet nằm trong một workbook làm việc khác thì đầu tiên mở workbook và sau đó truy cập worksheet.

' Get workbook
Dim wk As Workbook
Set wk = Workbooks.Open("C:\Docs\Accounts.xlsx", ReadOnly:=True)

' Then get worksheet
Dim sh As Worksheet
Set sh = wk.Worksheets("Sheet1")

Nếu muốn tránh người dùng thay đổi code name của worksheet thì sử dụng chức năng SheetFromCodename từ code name.

' Get workbook
Dim wk As Workbook
Set wk = Workbooks.Open("C:\Docs\Accounts.xlsx", ReadOnly:=True)

' Then get worksheet
Dim sh As Worksheet
Set sh = SheetFromCodeName("sheetcodename",wk)

Thêm worksheet

Các ví dụ dưới đây sẽ hướng dẫn cách thêm một worksheet mới vào workbook . Nếu không cung cấp bất kỳ đối số nào cho hàm Add thì worksheet mới sẽ được đặt trước activesheet.

Khi thêm một worksheet nó sẽ được tạo với tên mặc định như “ Worksheet4”. Nếu bạn muốn thay đổi tên thì thực hiện bằng cách sử dụng thuộc tính Name.

Ví dụ sau thêm một worksheet mới và đổi tên thành “Accounts”. Nếu một worksheet có tên “Accounts” đã tồn tại thì sẽ xuất hiện lỗi.

Public Sub AddSheet()

    Dim sht As Worksheet

    ' Adds new sheet before active sheet
    Set sht = ThisWorkbook.Worksheets.Add
    ' Set the name of sheet
    sht.Name = "Accounts"

    ' Adds 3 new sheets before active sheet
    ThisWorkbook.Worksheets.Add Count:=3

End Sub

Trong ví dụ trước, chúng ta thêm worksheet liên quan đến worksheet đang hoạt động (activesheet) . Chúng ta cũng có thể chỉ định vị trí đặt worksheet mới . Để làm điều này bạn chỉ định worksheet nào chèn trước, worksheet nào chèn sau, ví dụ:

Public Sub AddSheetFirstLast()

    Dim shtNew As Worksheet
    Dim shtFirst As Worksheet, shtLast As Worksheet

    With ThisWorkbook

        Set shtFirst = .Worksheets(1)
        Set shtLast = .Worksheets(.Worksheets.Count)

        ' Adds new sheet to first position in the workbook
        Set shtNew = Worksheets.Add(Before:=shtFirst)
        shtNew.Name = "FirstSheet"

        ' Adds new sheet to last position in the workbook
        Set shtNew = Worksheets.Add(After:=shtLast)
        shtNew.Name = "LastSheet"

    End With

Xóa worksheet

Để xóa một worksheet chỉ cần sử dụng lệnh gọi Delete

Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Sheet12")
sh.Delete

Excel sẽ hiển thị thông báo cảnh báo khi bạn xóa worksheet. Nếu muốn ẩn thông báo sử dụng mã bên dưới:

Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True

Có hai vấn đề chú ý khi xóa worksheet. Nếu bạn cố gắng truy cập worksheet sau khi xóa sẽ xuất hiện lỗi “Subscript out of Range” mà chúng ta đã thấy trong phần trên.

Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Sheet2")
sh.Delete

' Dòng này sẽ phát sinh lỗi "Subscript out of Range" vì "Sheet2" không tồn tại
Set sh = ThisWorkbook.Worksheets("Sheet2")

Vấn đề thứ 2 là khi bán một biến cho đối tượng worksheet, nếu cố gắng sử dụng biến này sau khi worksheet bị xóa thì sẽ gặp lỗi Automation.

Run-Time error -21147221080 (800401a8′) Automation Error

Nếu sử dụng code name thay cho tên của worksheet thì điều này sẽ khiến excel gặp sự cố chứ không phải lỗi Automation. Ví dụ:

sh.Delete

' Dòng này sẽ ra lỗi Automation error
Debug.Assert sh.Name

Nếu bạn gán biến cho worksheet thì sẽ hoạt động ổn hơn:

Sheet1.Range("A1") = 55

3.Nếu worksheet nằm trong một workbook làm việc khác thì đầu tiên mở workbook và sau đó truy cập worksheet.

0

Lặp qua các worksheet

Workbook bao gồm các worksheet, chúng ta có thể xem từng worksheet trong bộ sưu tập worksheets bằng cách sử dụng For each Loop hoặc For Loop.

Ví dụ sau đây sử dụng For Loop:

Sheet1.Range("A1") = 55

3.Nếu worksheet nằm trong một workbook làm việc khác thì đầu tiên mở workbook và sau đó truy cập worksheet.

1

Ví dụ tiếp theo sử dụng For Loop:

Sheet1.Range("A1") = 55

3.Nếu worksheet nằm trong một workbook làm việc khác thì đầu tiên mở workbook và sau đó truy cập worksheet.

2

Đây là cách truy cập tất cả các workbook đang mở và truy cập tất cả các worksheet trong Thisworkbook. Tiếp theo truy cập tất cả các worksheet trong tất cả các workbook đang mở.

Lưu ý: nếu sử dụng mã này để ghi vào worksheet thì lưu mọi thứ trước vì có thể dữ liệu không chính xác có thể ghi vào worksheet.

Sheet1.Range("A1") = 55

3.Nếu worksheet nằm trong một workbook làm việc khác thì đầu tiên mở workbook và sau đó truy cập worksheet.

3

Sử dụng bộ sưu tập Sheets

Workbook có một bộ sưu tập khác tương tự như worksheets gọi là sheets. Điều này đôi khi gây ra sự nhầm lẫn cho người dùng. Để phân biệt trước tiên cần biết về một sheet biểu đồ như sau:

1.Tạo biểu đồ trên bất kỳ sheet nào.

1. Nhấp chuột phải vào biểu đồ và Move.
2. Chọn tùy chọn đầu tiên là “ New sheet ” và click ok.

• Bộ sưu tập worksheets đề cập đến tất cả các worksheet trong một sổ làm việc. Nó không bao gồm các biểu đồ.worksheets đề cập đến tất cả các worksheet trong một sổ làm việc. Nó không bao gồm các biểu đồ.
• Bộ sưu tập Sheets đề cập đến tất cả các worksheet thuộc workbook nhưng bao gồm các sheets của biểu đồ.

Có hai ví dụ mã bên dưới. Đầu tiên đi qua tất cả các sheets trong workbook, xuất tên và loại của sheet. Ví dụ thứ hai làm tương tự với bộ sưu tập Worksheets. 

Để thử ví dụ cần thêm sheet biểu đồ vào workbook để thấy sự khác biệt :

Sheet1.Range("A1") = 55

3.Nếu worksheet nằm trong một workbook làm việc khác thì đầu tiên mở workbook và sau đó truy cập worksheet.

3

Nếu không có sheet biểu đồ thì việc sử dụng bộ sưu tập sheets cũng giống như sử dụng bộ sưu tập worksheets.worksheets.