Đây là hướng dẫn cuối cùng để làm việc với Trang tính / Trang tính trong Excel.
Ở cuối hướng dẫn này, chúng tôi đã tạo một bảng lừa đảo gồm các lệnh phổ biến để làm việc với trang tính.
Trang tính Vs. Trang tính
Có hai cách để tham chiếu Trang tính bằng VBA. Đầu tiên là với đối tượng Trang tính:
| 1 | Trang tính ("Trang 1"). Kích hoạt |
Cái còn lại là với đối tượng Worksheets:
| 1 | Worksheets ("Sheet1"). Kích hoạt |
99% thời gian, hai đối tượng này giống hệt nhau. Trên thực tế, nếu bạn đã tìm kiếm trực tuyến các ví dụ về mã VBA, bạn có thể đã thấy cả hai đối tượng được sử dụng. Đây là sự khác biệt:
Bộ sưu tập Trang tính chứa Trang tính VÀ Trang tính Biểu đồ.
Vì vậy, hãy sử dụng Trang tính nếu bạn muốn bao gồm Trang tính thông thường VÀ Trang tính biểu đồ. Sử dụng Trang tính nếu bạn muốn loại trừ Trang tính biểu đồ. Đối với phần còn lại của hướng dẫn này, chúng tôi sẽ sử dụng Trang tính và Trang tính thay thế cho nhau.
Tham chiếu Trang tính
Có một số cách khác nhau để tham chiếu Trang tính:
- Bảng hoạt động
- Tên tab trang tính
- Số chỉ mục trang tính
- Tên mã trang tính
Bảng hoạt động
ActiveSheet là Sheet hiện đang hoạt động. Nói cách khác, nếu bạn tạm dừng mã của mình và nhìn vào Excel, thì đó là trang tính sẽ hiển thị. Ví dụ mã dưới đây sẽ hiển thị Hộp thư có tên ActiveSheet.
| 1 | MsgBox ActiveSheet.Name |
Tên trang tính
Bạn có lẽ quen thuộc nhất với việc tham chiếu Trang tính theo Tên tab của chúng:
| 1 | Trang tính ("TabName"). Kích hoạt |
Số chỉ mục trang tính
Số chỉ mục trang tính là vị trí trang tính trong sổ làm việc. 1 là trang tính đầu tiên. 2 là tờ thứ hai, v.v.:
| 1 | Trang tính (1). Kích hoạt |
Số chỉ mục trang tính - Trang tính cuối cùng trong sổ làm việc
Để tham chiếu Trang tính cuối cùng trong sổ làm việc, hãy sử dụng Sheets.Count để lấy Số chỉ mục cuối cùng:
| 1 | Trang tính (Sheets.Count). Kích hoạt |
Trang tính "Tên mã"
Tên mã trang tính là tên đối tượng trong VBA:
| 1 | CodeName.Activate |
Tham chiếu trang tính trong sổ làm việc khác
Cũng dễ dàng tham khảo Trang tính trong các Sổ làm việc khác. Để làm như vậy, bạn cần sử dụng Đối tượng Workbooks:
| 1 | Workbooks ("VBA_Examples.xlsm"). Worksheets ("Sheet1"). Kích hoạt |
Quan trọng: Sổ làm việc phải mở trước khi bạn có thể tham chiếu Trang tính đó.
Kích hoạt so với Chọn Trang tính
Trong một bài viết khác, chúng tôi thảo luận mọi thứ về kích hoạt và chọn trang tính. Phiên bản ngắn là sau:
Khi bạn kích hoạt một Trang tính, nó sẽ trở thành một Trang tính Hoạt động. Đây là trang tính bạn sẽ thấy nếu bạn xem chương trình Excel của mình. Mỗi lần chỉ có thể kích hoạt một trang tính.
Kích hoạt một Trang tính
| 1 | Trang tính ("Trang 1"). Kích hoạt |
Khi bạn chọn một Trang tính, nó cũng sẽ trở thành một Trang tính Hoạt động. Tuy nhiên, bạn có thể chọn nhiều trang tính cùng một lúc. Khi nhiều trang tính được chọn cùng một lúc, trang tính “trên cùng” là Trang tính Hoạt động. Tuy nhiên, bạn có thể chuyển đổi ActiveSheet trong các trang tính đã chọn.
Chọn một trang tính
| 1 | Trang tính ("Trang 1"). Chọn |
Chọn nhiều trang tính
Sử dụng một mảng để chọn nhiều trang tính cùng một lúc:
| 1 | Bảng tính (Mảng ("Sheet2", "Sheet3")). Chọn |
Biến bảng tính
Gán trang tính cho một biến cho phép bạn tham chiếu trang tính theo tên biến của nó. Điều này có thể tiết kiệm rất nhiều lần nhập và làm cho mã của bạn dễ đọc hơn. Ngoài ra còn có nhiều lý do khác mà bạn có thể muốn sử dụng các biến.
Để khai báo một biến trang tính:
| 1 | Làm mờ ws dưới dạng trang tính |
Gán trang tính cho một biến:
| 1 | Đặt ws = Sheets ("Sheet1") |
Bây giờ bạn có thể tham chiếu biến trang tính trong mã của mình:
| 1 | ws.Activate |
Vòng qua tất cả các trang tính trong Workbook
Biến trang tính rất cần thiết khi bạn muốn lặp lại tất cả các trang tính trong sổ làm việc. Cách dễ nhất để làm điều này là:
| 12345 | Làm mờ ws dưới dạng Trang tínhĐối với mỗi ws trong WorksheetsMsgBox ws.nameTuần tiếp theo |
Mã này sẽ lặp qua tất cả các trang tính trong sổ làm việc, hiển thị mỗi tên trang tính trong một hộp thông báo. Vòng lặp qua tất cả các trang tính trong sổ làm việc rất hữu ích khi khóa / mở khóa hoặc ẩn / hiện nhiều trang tính cùng một lúc.
Bảo vệ trang tính
Bảo vệ sổ làm việc
Bảo vệ sổ làm việc khóa sổ làm việc khỏi các thay đổi về cấu trúc như thêm, xóa, di chuyển hoặc ẩn trang tính.
Bạn có thể bật tính năng bảo vệ sổ làm việc bằng VBA:
| 1 | ActiveWorkbook.Protect Password: = "Mật khẩu" |
hoặc tắt tính năng bảo vệ sổ làm việc:
| 1 | ActiveWorkbook.UnProtect Password: = "Mật khẩu" |
Lưu ý: Bạn cũng có thể bảo vệ / bỏ bảo vệ mà không cần mật khẩu bằng cách bỏ qua đối số Mật khẩu:
| 1 | ActiveWorkbook.Protect |
Bảo vệ trang tính
Bảo vệ cấp trang tính ngăn các thay đổi đối với các trang tính riêng lẻ.
Bảo vệ trang tính
| 1 | Worksheets ("Sheet1"). Bảo vệ "Mật khẩu" |
Bỏ bảo vệ trang tính
| 1 | Worksheets ("Sheet1"). Bỏ bảo vệ "Mật khẩu" |
Có nhiều tùy chọn khi bảo vệ trang tính (cho phép thay đổi định dạng, cho phép người dùng chèn hàng, v.v.) Chúng tôi khuyên bạn nên sử dụng Trình ghi Macro để ghi lại các cài đặt mong muốn của mình.
Chúng tôi thảo luận về bảo vệ trang tính chi tiết hơn ở đây.
Thuộc tính hiển thị trang tính
Bạn có thể đã biết rằng các trang tính có thể bị ẩn:
Thực tế có ba cài đặt hiển thị trang tính: Hiển thị, Ẩn và VeryHidden.Bất kỳ người dùng Excel thông thường nào cũng có thể bỏ ẩn các trang tính - bằng cách nhấp chuột phải vào khu vực tab trang tính (được hiển thị ở trên). Trang tính VeryHidden chỉ có thể được hiển thị bằng mã VBA hoặc từ trong Trình chỉnh sửa VBA. Sử dụng các ví dụ mã sau để ẩn / hiện trang tính:
Bỏ ẩn trang tính
| 1 | Worksheets ("Sheet1"). Visible = xlSheetVible |
Ẩn trang tính
| 1 | Worksheets ("Sheet1"). Hiển thị = xlSheetHidden |
Rất ẩn trang tính
| 1 | Worksheets ("Sheet1"). Visible = xlSheetVeryHidden |
Sự kiện cấp trang tính
Sự kiện là trình kích hoạt có thể khiến "Thủ tục sự kiện" chạy. Ví dụ: bạn có thể làm cho mã chạy mỗi khi bất kỳ ô nào trên trang tính được thay đổi hoặc khi trang tính được kích hoạt.
Các thủ tục sự kiện trang tính phải được đặt trong một mô-đun trang tính:
Có rất nhiều sự kiện trang tính. Để xem danh sách đầy đủ, hãy chuyển đến mô-đun trang tính, chọn “Trang tính” từ menu thả xuống đầu tiên. Sau đó, chọn một thủ tục sự kiện từ menu thả xuống thứ hai để chèn nó vào mô-đun.
Sự kiện kích hoạt bảng tính
Các sự kiện kích hoạt trang tính chạy mỗi khi trang tính được mở.
| 123 | Private Sub Worksheet_Activate ()Phạm vi ("A1"). ChọnKết thúc Sub |
Mã này sẽ chọn ô A1 (đặt lại vùng xem ở trên cùng bên trái của trang tính) mỗi khi trang tính được mở.
Sự kiện thay đổi trang tính
Các sự kiện thay đổi trang tính chạy bất cứ khi nào giá trị ô được thay đổi trên trang tính. Đọc hướng dẫn của chúng tôi về Sự kiện Thay đổi Trang tính để biết thêm thông tin.
Bảng Cheat Bảng tính
Dưới đây, bạn sẽ tìm thấy một bảng lừa đảo chứa các ví dụ mã phổ biến để làm việc với các trang tính trong VBA
VBA Worksheets Cheatsheet
Bảng tính VBA Cheatsheet| Sự miêu tả | Ví dụ về mã |
|---|---|
| Tham chiếu và Kích hoạt Trang tính | |
| Tên tab | Trang tính ("Đầu vào"). Kích hoạt |
| Tên mã VBA | Sheet1.Activate |
| Vị trí chỉ mục | Trang tính (1). Kích hoạt |
| Chọn trang tính | |
| Chọn trang tính | Trang tính ("Đầu vào"). Chọn |
| Đặt thành Biến | Làm mờ ws dưới dạng Trang tính Đặt ws = ActiveSheet |
| Tên / Đổi tên | ActiveSheet.Name = "NewName" |
| Trang tiếp theo | ActiveSheet.Next.Activate |
| Vòng qua tất cả các trang tính | Làm mờ ws dưới dạng Trang tính Đối với mỗi w trong Worksheets Msgbox ws.name Tuần tiếp theo |
| Lặp qua các trang tính đã chọn | Dim ws As Worksheet Đối với mỗi ws Trong ActiveWindow.SelectedSheets MsgBox ws.Name Tuần tiếp theo |
| Nhận ActiveSheet | MsgBox ActiveSheet.Name |
| Thêm trang tính | Sheets.Add |
| Thêm trang tính và tên | Sheets.Add.Name = "NewSheet" |
| Thêm trang tính có tên từ ô | Sheets.Add.Name = range ("a3"). Giá trị |
| Thêm trang tính sau trang khác | Sheets.Add After: = Trang tính ("Đầu vào") |
| Thêm trang tính sau và tên | Sheets.Add (After: = Sheets ("Input")). Name = "NewSheet" |
| Thêm trang tính trước và tên | Sheets.Add (Trước: = Trang tính ("Đầu vào")). Name = "NewSheet" |
| Thêm trang tính vào cuối sổ làm việc | Sheets.Add After: = Trang tính (Sheets.Count) |
| Thêm trang tính vào đầu sổ làm việc | Sheets.Add (Trước: = Trang tính (1)). Name = "Trang tính đầu tiên" |
| Thêm trang tính vào biến | Dim ws As Worksheet Đặt ws = Sheets.Add |
| Sao chép trang tính | |
| Di chuyển trang tính đến cuối sổ làm việc | Trang tính ("Trang tính1"). Di chuyển sau: = Trang tính (Trang tính.Count) |
| Tới sổ làm việc mới | Trang tính ("Trang 1"). Sao chép |
| Các trang tính đã chọn thành sổ làm việc mới | ActiveWindow.SelectedSheets.Copy |
| Trước một trang tính khác | Trang tính ("Trang tính1"). Sao chép Trước: = Trang tính ("Trang tính2") |
| Trước trang đầu tiên | Trang tính ("Trang 1"). Sao chép Trước: = Trang tính (1) |
| Sau trang cuối cùng | Trang tính ("Trang tính1"). Sao chép Sau: = Trang tính (Trang tính.Count) |
| Sao chép và đặt tên | Trang tính ("Trang tính1"). Sao chép Sau: = Trang tính (Trang tính.Count) ActiveSheet.Name = "LastSheet" |
| Sao chép và đặt tên từ giá trị ô | Trang tính ("Trang tính1"). Sao chép Sau: = Trang tính (Trang tính.Count) ActiveSheet.Name = Range ("A1"). Giá trị |
| Đến một sổ làm việc khác | Trang tính ("Trang tính1"). Sao chép Trước: = Sách làm việc ("Ví dụ.xlsm"). Trang tính (1) |
| Ẩn / Hiện Trang tính | |
| Ẩn trang tính | Trang tính ("Trang 1"). Hiển thị = Sai hoặc Trang tính ("Trang 1"). Hiển thị = xlSheetHidden |
| Bỏ ẩn trang tính | Trang tính ("Trang 1"). Visible = True hoặc Trang tính ("Trang 1"). Visible = xlSheetVible |
| Rất ẩn trang tính | Trang tính (“Trang tính1”). Visible = xlSheetVeryHidden |
| Xóa hoặc Xóa Trang tính | |
| Xóa trang tính | Trang tính ("Trang 1"). Xóa |
| Xóa trang tính (Xử lý lỗi) | Khi có lỗi Tiếp tục tiếp theo Trang tính ("Trang 1"). Xóa Lỗi GoTo 0 |
| Xóa trang tính (Không có lời nhắc) | Application.DisplayAlerts = False Trang tính ("Trang 1"). Xóa Application.DisplayAlerts = True |
| Xóa trang tính | Trang tính ("Trang 1"). Cells.Clear |
| Chỉ xóa nội dung trang tính | Sheets ("Sheet1"). Cells.ClearContents |
| Xóa trang tính được sử dụng | Trang tính ("Trang 1"). UsedRange.Clear |
| Bảo vệ hoặc Bỏ bảo vệ Trang tính | |
| Bỏ bảo vệ (Không có mật khẩu) | Trang tính ("Trang 1"). Bỏ bảo vệ |
| Bỏ bảo vệ (Mật khẩu) | Trang tính ("Trang 1"). Bỏ bảo vệ "Mật khẩu" |
| Bảo vệ (Không có mật khẩu) | Trang tính ("Trang 1"). Bảo vệ |
| Bảo vệ (Mật khẩu) | Trang tính ("Trang 1"). Bảo vệ "Mật khẩu" |
| Bảo vệ nhưng cho phép truy cập VBA | Sheets ("Sheet1"). Bảo vệ UserInterfaceOnly: = True |
| Bỏ bảo vệ tất cả các trang tính | Dim ws As Worksheet Đối với mỗi ws trong Worksheets ws.Unprotect "mật khẩu" Tuần tiếp theo |
