khóa học vba cơ bản 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

Hệ thống các đối tượng trong Excel VBA

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

Trước khi đi học lập trình VBA hay bất cứ ngôn ngữ nào, một điều rất quan trọng chúng ta nên chú ý đến là hệ thống các đối tượng ở trong ngôn ngữ lập trình đó. Khi hiểu được hệ thống các đối tượng trong VBA, thì bạn sẽ có được hình dung rõ ràng hơn về cấu trúc của code, sẽ làm việc hiệu quả hơn với từng loại đối tượng.

Các thành phần đối tượng trong Excel VBA

Đối tượng Application

Các đối tượng trong Excel VBA có mối quan hệ cha-con. Ở bậc cao nhất, chúng ta có đối tượng Application, trong trường hợp bạn sử dụng VBA để tương tác với Excel, thì ngầm định đối tượng Application này là đối tượng Excel, và sẽ có những thành phần và phương thức tương ứng. Nếu các bạn sử dụng VBA bên trong môi trường phần mềm Microsoft Access, thì Application này sẽ là đối tượng Access. Trong bài viết này, chúng ta nói về các thành phần đối tượng và phương thức, thuộc tính trong Excel VBA.

Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").value = 234

Ví dụ để có thể tham chiếu tới ô A1, sau đó ghi dữ liệu là số 234 vào ô A1 thì chúng ta có thể viết code như dòng trên, tuy nhiên, khi bạn hiểu được cách phân cấp các đối tượng trong Excel VBA, thì bạn có thể rút ngắn đoạn code VBA trên khá nhiều.

Đối tượng Workbooks

Đối tượng Workbooks là một tập hợp các Workbooks Excel đang được mở, là một thành phần con của đối tượng Application. Khi muốn tham chiếu tới Workbooks, thì bạn sử dụng cú pháp

Application.Workbooks("_tên_workbook_")

Thường thì chúng ta có thể bỏ Application, và chỉ tham chiếu tới Workbooks như sau là đủ:

Workbooks("_tên_workbook_")

Lý do cho việc này là, hiếm khi chúng ta sẽ sử dụng cùng nhiều hơn 1 Instances của Excel, và trong 1 Instance của Excel, thì chúng ta không được phép mở Workbook trùng tên.

Đối tượng Worksheets

Mỗi Workbook, lại có thể chứa nhiều đối tượng Worksheets, và bạn có thể tham chiếu tới một Worksheet có tên là “Sheet1” trong File Excel có tên là “Book1.xlsx” như sau:

Application.Workbooks("Book1.xlsx").Worksheets("Sheet1")

Tuy nhiên, như lý do đã giải thích ở phần trên, chúng ta có thể yên tâm bỏ Application trong dòng code trên đi. Nếu trong trường hợp, code VBA của bạn chỉ làm việc trong File Excel “Book1.xlsx”, thì chúng ta có thể yên tâm bỏ luôn phần Workbooks(“Book1.xlsx”) đi, bởi vì lúc này, trong cùng một Workbook, thì VBA sẽ biết phải tham chiếu tới Sheet nào:

Worksheets("Sheet1")

Nhưng nếu code VBA của bạn làm việc với nhiều Workbooks, có thể trong những Workbooks này, cũng có Sheet tên là “Sheet1”, thì lúc này, bạn cần tham chiếu tới Sheet1 có kèm theo tham chiếu tới Workbook để code VBA không bị lẫn lộn:

Workbooks("Book1.xlsx").Worksheets("Sheet1")

Đối tượng Range

Sau đối tượng Worksheets, chúng ta có thể tham chiếu tới đối tượng Range, cũng tương tự như kiến thức ở phần trước, nếu code VBA chỉ làm việc với nội dung trên 1 bảng tính duy nhất, thì bạn có thể tham chiếu tới Range rất ngắn gọn:

Range("A1")

Nhưng nếu code VBA của bạn sẽ làm việc với nhiều Sheets khác nhau trong cùng một Workbook, thì chắc chắn tham chiếu của chúng ta tới ô A1, cần phải cụ thể hơn:

Worksheets("Sheet1").Range("A1")

Tương tự như vậy khi bạn làm việc với nhiều Workbooks khác nhau, thì chúng ta cần viết đầy đủ “đường dẫn” tới đối tượng Range của chúng ta:

Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1")

Cách tra cứu các thành phần và phương thức với đối tượng trong Excel VBA

Các thành phần và phương thức, cũng như thuộc tính, hay hằng số của mỗi đối tượng rất nhiều, nhưng thật may mắn, ngay phía bên trong trình soạn thảo code VBA của chúng ta đã có chức năng để chúng ta có thể tra cứu được, để mở các cửa sổ này, khi đang ở bên trong trình soạn thảo code VBA, bạn hãy bấm nút F2 trên bàn phím (Nếu không được, hãy thử bấm FN + F2 )

Trong cửa sổ này, bạn có thể tìm kiếm các đối tượng trong Excel VBA và tra cứu các thông tin liên quan đến các thuộc tính, phương thức và những hằng số như hình trên.

Các đối tượng và thuộc tính của VBA trong Excel rất nhiều, vậy làm thế nào để bạn có thể tiết kiệm thời gian tra cứu và lập trình tự động hóa được ngay những công việc bạn vẫn mong muốn? Hãy đồng hành cùng Thanh trong các khóa học VBA nâng cao năng suất công việc: