Tổng hợp mã VBA Excel thao tác với trang tính Excel cực nhanh

Bạn có tin rằng với VBA Excel, các thao tác ẩn hoặc hiện trang tính Excel, thêm mới 1 trang tính Excel, hay khóa và mở khóa trang tính Excel đều có thể được hoàn thành chỉ với 1 nút Enter? Cùng Gitiho tìm hiểu các mã VBA Excel thần kỳ này trong bài viết ngày hôm nay nhé.

XEM NHANH BÀI VIẾT

1 Tìm hiểu về worksheet trong VBA Excel
2 Các mã VBA Excel thao tác với trang tính Excel
3 Tổng kết

Tìm hiểu về worksheet trong VBA Excel

Nếu bạn còn cảm thấy mơ hồ về đối tượng worksheet trong VBA Excel thì trước tiên, chúng ta hãy cùng giải đáp các thắc mắc thường gặp về đối tượng này nhé.

Worksheet là gì?

Worksheet Excel, hay còn được gọi với cái tên trang tính Excel, là giao diện làm việc chính của Excel, bao gồm các hàng và cột giao nhau tạo thành các ô tính. Mỗi ô tính trong trang tính Excel có thể hiển thị các giá trị văn bản, giá trị số, hoặc kết quả của các công thức hàm được nhập vào trong ô.

Trang tính Excel chính là những gì bạn nhìn thấy ngay sau khi mở phần mềm.

Phân biệt Workbook và Worksheet trong Excel

Sau khi nghe khái niệm worksheet, có thể bạn sẽ nghĩ đến một khái niệm trong Excel tương tự mang tên workbook. Liệu bạn có nghĩ rằng 2 khái niệm này đều được dùng để chỉ một đối tượng?

Trên thực tế, workbook và worksheet hoàn toàn khác nhau. Do đó, trong hầu hết các trường hợp, chúng ta không thể dùng chúng thay cho nhau.

Workbook là một file Excel, trong khi đó, worksheet chỉ là một trang tính Excel nằm trong file đó. Nói cách khác, một workbook có thể bao gồm nhiều worksheet. Bạn có thể hiểu workbook như một quyển sách mà các trang của nó được gọi là worksheet.

Các cách gọi sheet trong VBA Excel

Đặt trong một mã VBA Excel cụ thể, có 4 phương pháp bạn có thể sử dụng để gọi sheet. Cụ thể là các lệnh dưới đây:

ActiveSheet - Lệnh gọi trang tính Excel đang được kích hoạt.
Sheet Tab Name - Lệnh gọi trang tính Excel dựa theo tên.

Ví dụ: Sheets("Data") là trang tính Excel có tên Data.

Sheet Index Name - Lệnh gọi trang tính Excel dựa theo số thứ tự trong file Excel.

Ví dụ: Sheets(2) là trang tính thứ hai trên thanh sheet tab tính từ trái sang.

Sheet Code Name - Lệnh gọi trang tính Excel có dựa theo code name.

Ví dụ: Sheet3 là trang tính Excel có code name trong thẻ Properties VBA Excel là Sheet3.

Xem thêm: Hướng dẫn cách ẩn/hiện thanh cuộn trong Excel nhanh chóng

Phân biệt lệnh VBA Excel Sheet.Activate và Sheet.Select

Một lưu ý nữa khi sử dụng VBA Excel với đối tượng worksheet là sự khác nhau giữa 2 lệnh trong VBA Excel mang tên Sheet.Activate và Sheet.Select.

Sheet.Activate là lệnh kích hoạt một sheet. Điều này nghĩa là chỉ có duy nhất một trang tính Excel được kích hoạt tại một thời điểm. Trang tính được kích hoạt với VBA Excel sẽ trở thành ActiveSheet.

Sheet.Select là lệnh chọn một sheet. Tuy nhiên, lệnh VBA Excel này có thể chọn nhiều trang tính Excel cùng một lúc.

Nếu chỉ chọn một trang tính Excel duy nhất, trang tính này sẽ trở thành ActiveSheet.
Nếu chọn nhiều trang tính Excel cùng một lúc, chỉ có trang tính đầu tiên tính từ trái sang trong sheet tab được coi là ActiveSheet.

Để chọn nhiều sheet với VBA Excel, chúng ta sẽ sử dụng lệnh viết theo Sheet Tab Name như sau:

Worksheets(Array("Sheet2", "Sheet3")).Select

Xem thêm: Giới thiệu đặc điểm file VBA Excel và các tính năng VBA cơ bản

Các mã VBA Excel thao tác với trang tính Excel

Vậy là bạn đã sẵn sàng khám phá VBA Excel để thao tác với đối tượng trang tính Excel rồi. Chúng ta hãy cùng tìm hiểu ngay các mã VBA Excel thần kỳ có thể giúp bạn rút ngắn thời gian làm việc nhé!

Với từng lệnh VBA Excel, mình sẽ đưa ra mục đích thực hiện thao tác để dựa vào đó, chúng ta viết ra lệnh phù hợp.

Mã VBA Excel ẩn/hiện trang tính Excel

Mã VBA Excel ẩn/hiện toàn bộ các trang tính Excel

1. Mục đích: Ẩn hoặc hiện toàn bộ các trang tính Excel trong file hiện tại, không quan tâm đến tên hay số thứ tự của trang tính.

2. VBA Excel

Sub MoAn_ToanBo_Sheet()
'Khai báo các biến sử dụng
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

Trong đó:

ws.Visible = xlSheetVisible - Lệnh VBA Excel bỏ ẩn trang tính Excel.
ws.Visible = xlSheetHidden - Lệnh VBA Excel ẩn trang tính Excel cấp độ 1 (cấp thông thường), mở ẩn trong sheet tab.
ws.Visible - xlSheetVeryHidden - Lệnh VBA Excel ẩn trang tính Excel cấp độ 2, chỉ bỏ ẩn được trong VBA Excel.

Mã VBA Excel ẩn một trang tính Excel nhất định

1. Mục đích

Ẩn một trang tính Excel cụ thể dựa theo tên hoặc số thứ tự, code name của trang.
Kích hoạt trang tính MENU (trang tính mục lục có tính chất điều hướng đến các trang tính khác).

2. VBA Excel

Sub An_Mot_Sheet()
Sheets("MENU").Visible = xlSheetVisible
Sheets("Data").Visible = xlSheetHidden 'Ẩn Sheet có tên là Data
Sheets("MENU").Activate
End Sub

Lưu ý:

File Excel luôn phải có ít nhất một trang tính được hiển thị nên chúng ta không được ẩn tất cả các trang tính. Do đó, khi làm việc trên VBA Excel, code hiển thị Sheets("MENU") phải luôn viết trước code ẩn Sheets("Data").

Nếu bạn chưa biết cách sử dụng VBA Excel để tạo một trang tính Menu (mục lục) để liên kết đến tất cả các trang tính trong file Excel thì hãy tìm hiểu ngay cách làm trong bài viết dưới đây nhé.

Xem thêm: Hướng dẫn sử dụng VBA để tạo menu Excel liên kết nhiều chức năng

Mã VBA Excel hiện một trang tính Excel nhất định

1. Mục đích

Hiện một trang tính Excel cụ thể dựa theo tên hoặc số thứ tự, code name của trang.
Kích hoạt trang tính Excel vừa được mở ẩn.

2. VBA Excel

Sub An_Sheet()
With Sheet_01
.Visible = xlSheetVisible
.Activate
End With
End Sub

Nếu thao tác ẩn sheet trong Excel của bạn với VBA Excel yêu cầu cao hơn như ẩn một số sheet nhất định hay ẩn tất cả các sheet trừ sheet cuối cùng, hãy tham khảo các mã VBA Excel trong bài viết dưới đây.

Xem thêm: Hướng dẫn thao tác ẩn Sheet trong Excel bằng lệnh VBA

Mã VBA Excel thêm mới một trang tính

1. Mục tiêu: Tạo một trang tính Excel mới dựa vào các yếu tố dưới đây.

Tên trang tính mới không được trùng với tên các trang tính đã có sẵn trong file Excel.
Vị trí đặt trang tính trên thanh sheet tab được gán biến trong VBA Excel để tiện theo dõi và quản lý.

2. VBA Excel

Sub ThemMoi_Sheet()
Dim NewWS As Worksheet, ws As Worksheet
Set NewWS = ThisWorkbook.Worksheets.Add 'Thêm mới vào vị trí mặc định
Dim TenSheet As String
TenSheet = "Data" 'Tên Sheet muốn tạo
For Each ws In ThisWorkbook.Worksheets 'Vòng lặp xét tên các Sheet đã có
If ws.Name = TenSheet Then
TenSheet = TenSheet & "(1)" 'Nếu trùng với tên đã có thì thêm ký tự (1)
End If
Next ws
NewWS.Name = TenSheet 'Đặt tên cho Sheet mới tạo
End Sub

Tại dòng 3 SetNewWS:

Có thể viết ThisWorkbook.Worksheets gọn lại là Worksheets hoặc Sheets.
Với vị trí trang tính Excel được thêm mới, bạn có thể chọn một trong các lệnh dưới đây để điền vào VBA Excel:
- Sheets.Add(After:=Sheets("Sheet1")) - Lệnh VBA Excel thêm trang tính Excel mới vào sau Sheet1.
- Sheets.Add(Before:=Sheets("Sheet1")) - Lệnh VBA Excel thêm trang tính Excel mới vào trước Sheet1.
- Sheets.Add(After:=Sheets(Sheets.Count)) - Lệnh VBA Excel thêm trang tính Excel mới vào vị trí cuối cùng trên thanh sheet tab.

Mã VBA Excel khóa/mở khóa một trang tính

Mã VBA Excel khóa trang tính

1. Mục đích: Khóa trang tính Excel hiện tại.

2. VBA Excel

Dưới đây là đoạn mã bạn có thể đưa vào trong module VBA Excel của mình, kết hợp với các dòng lệnh khác để tạo nên một lệnh VBA Excel hoàn chỉnh.

Worksheets("Sheet1").Protect _
Password:="nội dung mã", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True, _
AllowFormattingCells:=False, _
AllowFormattingColumns:=False, _
AllowFormattingRows:=False, _
AllowInsertingColumns:=False, _
AllowInsertingRows:=False, _
AllowInsertingHyperlinks:=False, _
AllowDeletingColumns:=False, _
AllowDeletingRows:=False, _
AllowSorting:=False, _
AllowFiltering:=False, _
AllowUsingPivotTables:=False

Lưu ý rằng lệnh UserInterfaceOnly:=True:

Không có sẵn trong code Record Macro, do đó bạn phải viết thủ công đoạn mã VBA Excel này.
Không lưu trữ khi đóng file Excel, do đó bạn phải kích hoạt lại mỗi khi mở file.

Mã VBA Excel mở khóa trang tính

1. Mục đích: Mở khóa trang tính Excel dựa theo phương pháp khóa đã thiết lập cho trang tính.

2. VBA Excel

Dưới đây là lệnh mở khóa trang tính Excel bạn có thể đưa vào trong VBA Excel:

Worksheets("Sheet1").Unprotect

Lệnh VBA Excel này sẽ mở khóa trang tính Excel dựa vào đúng phương pháp bạn đã sử dụng để khóa trang tính.

Còn dưới đây là một lệnh hoàn chỉnh trong VBA Excel được dùng để mở khóa trang tính:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect UserInterfaceOnly:=True
Next ws
End Sub

Lưu ý: Lệnh VBA Excel này sử dụng cấu trúc vòng lặp để áp dụng lên toàn bộ các trang tính trong file Excel. Nếu bạn chỉ dùng lệnh cho một trang tính Excel thì không cần dùng cấu trúc vòng lặp này.

Tổng kết

Vậy là chúng ta đã tìm hiểu tất cả các lệnh VBA Excel thực hiện các thao tác cơ bản dành cho đối tượng worksheet, hay còn gọi là trang tính Excel. Với các lệnh VBA Excel trong bài, chắc chắn bạn sẽ có thể dễ dàng thực hiện các công việc ẩn/hiện trang tính Excel, thêm mới trang tính Excel và khóa/mở khóa trang tính Excel.

Nếu bạn cần sưu tầm thêm các lệnh khác hữu ích trong quá trình sử dụng VBA Excel, hãy tham khảo thêm các bài viết về chủ đề VBA Excel trên blog Gitiho. Còn nếu bạn muốn học cách tự viết các lệnh VBA Excel từ A-Z, các khóa học lập trình VBA trong Excel của Gitiho là lựa chọn phù hợp nhất dành cho bạn.