đối tượng worksheet Archives -

Đối tượng Worksheet trong Excel VBA

Written by dtnguyen on 27/08/2021. Posted in VBA. Leave a Comment

Đối tượng Worksheet trong Excel VBA là đối tượng đại diện cho một Worksheet trong lập trình VBA. Các thao tác với Worksheet khi các bạn làm việc với Excel (thủ công, bằng tay) như di chuyển sheet, copy sheet, đổi tên sheet, xóa sheet, … đều có thể thực hiện tự động bằng code VBA.

Đối tượng Worksheets trong Excel VBA là một tập hợp các Worksheet trong một Workbook

Các loại Sheet trong một tài liệu Excel

Worksheet chúng ta đề cập đến trong bài viết này, thực ra chỉ là một loại Sheets trong Workbook Excel. Đã bao giờ các bạn gặp các loại Sheets khác chưa?

Các bạn có thể nhìn thấy ở trong hình trên, trong cửa sổ Project – VBAProject của VBE, trong Book1.xlsm, chúng ta có thấy Sheet1 là một Worksheet, đối tượng mà chúng ta vẫn quen thuộc. Số lượng Worksheets trong một Workbook được thể hiện qua câu lệnh:

Debug.Print ThisWorkbook.Worksheets.Count

và kết quả là trong tài liệu Excel này, chúng ta có 1 Worksheet. Tuy nhiên, ở câu lệnh thứ 2:

Debug.Print ThisWorkbook.Sheets.Count

kết quả của câu lệnh này lại là 5. Tại sao lại như vậy? Ok, chúng ta còn có thể nhìn thấy một đối tượng nữa ở trong cửa sổ Project, đó là đối tượng Chart2 và nếu bạn nào đã từng bấm nhầm phím tắt ALT + F11 thì, bạn sẽ có thể tạo ra được 1 sheet mà chỉ có biểu đồ, không có gì khác ngoài biểu đồ, chúng ta có thể gọi là Chart Sheet, và Chart Sheet này cũng có mặt trong các thành viên của đối tượng Sheets ở trong Thisworkbook.Sheets. Tuy nhiên, như vậy mới có 2, vậy con số 5 ở đây là ở đâu ra?

Như các bạn đã thấy ở hình trên, một số sheets có tên Macro ở đầu, những sheets này đã được tạo ra như thế nào? Những sheets này có thể được tạo ra khi bạn bấm phím tắt CTRL + F11, và loại sheet này còn không được thể hiện trong VBA Project. Những Sheets này, được gọi là Macro Sheet, chỉ tồn tại với lý do tương thích ngược với những file Excel cũ, những file Excel vẫn sử dụng Excel Macro 4.0 (Ngôn ngữ VBA ra đời ở Excel phiên bản 5).

Như vậy, các bạn đã biết rằng, chúng ta có nhiều hơn một loại sheet trong một Workbook, tuy nhiên, trong bài này, chúng ta chỉ tập trung vào đối tượng Worksheet mà thôi.

Cách tham chiếu tới một Worksheet trong Excel VBA

Trước khi đi làm việc được với một Worksheet, thì chúng ta cần tìm hiểu cách tham chiếu tới Worksheet đó.

Như ở bài Hệ thống các đối tượng trong Excel VBA thì các bạn đã biết, để tham chiếu tới một Worksheet, chúng ta có nhiều cách, tham chiếu từ Application, hay từ Workbook thì còn tùy vào trường hợp của chúng ta sử dụng. Nếu các bạn làm việc với nhiều Workbooks cùng lúc, thì chúng ta cần tham chiếu tới Worksheet từ “tầng Workbook”. Ví dụ, chúng ta muốn tham chiếu tới worksheet có tên “Data” ở trong Workbook đang chạy code:

ThisWorkbook.Worksheets("Data")

Nếu chúng ta chỉ làm việc trong phạm vi 1 workbook, thì bạn có thể tham chiếu Worksheets “Data” như sau:

Worksheets("Data")

Tham chiếu tới Worksheet theo tên

Để tham chiếu tới một Worksheet theo tên trong Excel VBA, chúng ta có thể viết câu lệnh như sau:

Set dataSheet = Worksheets("Data")

vì Worksheet cũng là Sheet, nên chúng ta có thể tham chiếu tới Sheet Data như sau:

Set dataSheet = Sheets("Data")

Tham chiếu tới Worksheet theo thứ tự (index)

Để tham chiếu tới một Worksheet theo thứ tự trên Sheetbar trong Excel VBA, chúng ta có thể viết câu lệnh như sau:

Set dataSheet = Worksheets(1)

Lưu ý Trong hình sau, chúng ta muốn tham chiếu tới Worksheet có tên là Sheet1 theo thứ tự trên Sheetbar của Excel, thứ tự của đối tượng này là 1 chứ không phải là 5, bởi vì 4 đối tượng trước nó không thuộc vào loại Worksheet.

Nếu chúng ta tham chiếu bằng Sheets thì code sẽ là:

Set dataSheet = Sheets(5)

Tham chiếu tới Worksheet theo code name trong VBA

Đối với hai cách tham chiếu tới Worksheet ở hai phần trước, sẽ có hạn chế trong quá trình sử dụng đó là:

Người dùng có thể đổi tên Worksheet
Người dùng có thể đổi vị trí Worksheet trên Sheetbar

Điều này đưa chúng ta đến một cách thứ ba để có thể tham chiếu đến một Worksheet trong VBA: theo code name hay (name) trong cửa sổ Properties ở trong VBA Editor:

Trong hình trên: trên SheetBar, Worksheet của chúng ta tên là Data, chúng ta sẽ tham chiếu tới Worksheet này như thế nào:

Trong cửa sổ Project – VBAProject, chúng ta thấy đối tượng dataSheet(Data) – tên Sheet được để trong ngoặc đơn
Trong cửa sổ Properties – dataSheet: để đổi tên tham chiếu trong VBA, chúng ta có thể đổi trong cửa sổ này, ở mục (Name)

Một số phương thức của đối tượng Worksheet trong VBA Excel

Phương thức Activate

Phương thức Activate của đối tượng Worksheet trong VBA dùng để kích hoạt một Worksheet, phương thức này tương đương với hành động chúng ta dùng chuột click vào tên Sheet ở trên SheetBar.

Kích hoạt Worksheet có tên Data, cú pháp một trong những cách sau:

Worksheets("Data").Activate
Sheets("Data").Activate
Worksheets(1).Activate
Sheets(5).Activate
dataSheet.Activate

Tư vấn của Thanh: trừ khi bạn biết chính xác bạn đang làm gì, thì bạn mới nên sử dụng phương thức này của đối tượng Worksheet, bởi vì phương thức này có thể dẫn đến rất nhiều lỗi trong code VBA của bạn.

Phương thức Calculate

Phương thức Calculate của đối tượng Worksheet trong VBA khi được kích hoạt sẽ giúp tính toán lại các công thức trên Sheet này.

Worksheets("Data").Calculate

Phương thức Copy

Phương thức Copy của đối tượng Worksheet trong VBA giúp chúng ta sao chép một Worksheet. Việc sao chép này có thể được thực hiện trong một Workbook, hoặc sang một Workbook khác.

Worksheets("_worksheet_cần_copy").Copy Before:=_Worksheet_A_
Worksheets("_worksheet_cần_copy").Copy After:=_Worksheet_A_

Một số ví dụ:
Sao chép Sheet “Data” ra phía trước của Sheet có tên là “Analysis” trong cùng Workbook:

Worksheets("Data").Copy Before:=Worksheets("Analysis")
' hoặc
Sheets("Data").Copy Before:=Sheets("Analysis")

Sao chép Sheet “Data” trong Workbook “Thanh.xlsm” ra phía sau Sheet “Big” trong Workbook “Master.xlsm”:

Workbooks("Thanh.xlsm").Worksheets("Data").Copy After:=Workbooks("Master.xlsm").Worksheets("Big")

Phương thức Delete

Phương thức Delete của đối tượng Worksheet trong VBA được sử dụng để xóa một hay một mảng các Sheets.

Lưu ý: Thao tác xóa Sheets sử dụng code VBA sẽ không thể hoàn tác (Undo) được, nếu bạn thực hành trên file Excel của bạn, hãy đảm bảo sao lưu lại các file Excel trước khi thực hiện code.

Để xóa Sheets có tên “Big”, chúng ta có thể viết đoạn code như sau:

Sheets("Big").Delete

Sau khi thực hiện code, bạn sẽ nhận được thông báo của Excel, bấm Delete nếu bạn muốn tiếp tục xóa.

Để xóa đồng thời 2 sheets có tên là “Big” và “Analysis”, không hiện hộp thoại thông báo xóa Sheets, chúng ta có thể sử dụng đoạn code sau đây:

Application.DisplayAlerts = False
Sheets(Array("Big", "Analysis")).Delete
Application.DisplayAlerts = True

chi tiết về các sử dụng Application.DisplayAlerts hãy tham khảo bài viết Đối tượng Application trong Excel VBA

Phương thức Move

Phương thức Move của đối tượng Worksheet trong VBA giúp chúng ta di chuyển một Worksheet.

Để di chuyển Sheet “Big” ra phía trước Sheet “Data” và Sheet “Analysis” ra phía sau Sheet “Data”, ta có thể dùng code sau đây:

 Sheets("Big").Move Before:=Sheets("Data")
Sheets("Analysis").Move After:=Sheets("Data")

Để di chuyển cùng lúc đồng thời 2 sheets “Big”, “Data” ra phía trước Sheet “Analysis”, chúng ta có thể dùng code sau đây:

Sheets(Array("Big","Data")).Move Before:=Sheets("Analysis")

Thuộc tính Name

Thuộc tính Name của đối tượng Worksheet trong VBA giúp bạn có được tên trên SheetBar của một Worksheet.

Ví dụ, lấy tên của Worksheet ở vị trí thứ nhất trên SheetBar:

Debug.print Worksheets(1).Name

Ngoài ra, chúng ta có thể đổi tên Worksheet ở vị trí thứ nhất trên SheetBar như sau:

Worksheets(1).Name = "Very Big Data"

Thuộc tính Visible

Video liên quan tới phần này:

Thuộc tính Visible của đối tượng Worksheet trong VBA giúp bạn thiết lập sự ẩn / hiện của một Worksheet.

Để hiện Worksheet “Data”:

Worksheets("Data").Visible = xlSheetVisible

Để ẩn Worksheet “Data”, nhưng có thể cho hiện lại ở trên SheetBar:

Worksheets("Data").Visible = xlSheetHidden

Để ẩn Worksheet “Data”, nhưng không cho phép hiện lại bằng thao tác trên SheetBar:

Worksheets("Data").Visible = xlSheetVeryHidden

Như vậy, qua bài viết này, Thanh đã giới thiệu với các bạn một số phương thức và thuộc tính của đối tượng Worksheet trong Excel VBA. Đối tượng Worksheet trong Excel VBA còn rất nhiều phương thức, thuộc tính và thành phần nữa, để kết hợp nhuần nhuyễn và hiểu rõ cách kết hợp tốt nhất những thành phần này cho ứng dụng VBA của bạn, hãy tham khảo khóa học VBA của Thanh:

Khóa học VBA cơ bản:

Tự động hóa Excel với lập trình VBA cho người mới bắt đầu

Toàn bộ các khóa học lập trình VBA:

Tham khảo toàn bộ các khóa học lập trình VBA

Thẻ: đối tượng worksheet