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ướng dẫn nhanh về worksheet VBA

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.

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:

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.

Ẩn worksheet

Các ví dụ sau đây cho thấy cách ẩn và cách hiện một 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ã.

Bảo vệ Worksheet

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

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”) .

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:

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).

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.

Đ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ụ:

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.

2. Code name vẫn hoạt động nếu tên worksheet thay đổi. 
3. Code name chỉ được sử dụng cho các worksheet trong cùng một workbook với mã.
4. 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.
5. Có thể sử dụng chức năng 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. 

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.

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

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

2. 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:

sh.Delete

' Gán biến sh tới "sheet3"
Set sh = Worksheets("sheet3")

' Dòng này sẽ không có lỗi
Debug.Assert sh.Name

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:

Public Sub LoopForEach()

    ' Writes "Hello World" into cell A1 for each worksheet
    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Worksheets
         sht.Range("A1") = "Hello World"
    Next sht 

End Sub

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

Public Sub LoopFor()
    
    ' Writes "Hello World" into cell A1 for each worksheet
    Dim i As Long
    For i = 1 To ThisWorkbook.Worksheets.Count
         ThisWorkbook.Worksheets(i).Range("A1") = "Hello World"
    Next sht

End Sub

Đâ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.

Public Sub AllSheetNames()

    ' Prints the workbook and sheet names for
    ' all sheets in open workbooks
    Dim wrk As Workbook
    Dim sht As Worksheet
    For Each wrk In Workbooks
        For Each sht In wrk.Worksheets
            Debug.Print wrk.Name + ":" + sht.Name
        Next sht
    Next wrk

End Sub

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.

2. Nhấp chuột phải vào biểu đồ và Move.
3. 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 đồ.
• 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 :

Public Sub AllSheetNames()

    ' Prints the workbook and sheet names for
    ' all sheets in open workbooks
    Dim wrk As Workbook
    Dim sht As Worksheet
    For Each wrk In Workbooks
        For Each sht In wrk.Worksheets
            Debug.Print wrk.Name + ":" + sht.Name
        Next sht
    Next wrk

End Sub

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.