Sử dụng VBA để tạo bản đồ trang tính

0
2

Bạn đã bao giờ thử tìm hiểu về một bảng tính Excel không quen thuộc (và phức tạp) chưa? Sẽ rất hữu ích khi xem bản đồ cung cấp tổng quan về các ô chứa hằng số và ô nào chứa giá trị.
Dưới đây, bạn thấy một ví dụ về bản đồ như vậy. Các ô chứa văn bản có màu xanh lá cây, các ô chứa giá trị số có màu vàng và các ô chứa công thức có màu đỏ. Một bản đồ như vậy có thể giúp bạn phát hiện ra các lỗi tiềm ẩn. Ví dụ: nếu một công thức trong một khối công thức đã bị ghi đè bởi một giá trị, ô đó sẽ nổi bật trong chế độ xem bản đồ (như trong ô Q11 trong ví dụ).

Mã VBA QuickMap

Quy trình VBA tạo bản đồ trang tính được liệt kê bên dưới. Nếu bạn muốn sử dụng tiện ích này, chỉ cần sao chép mã và dán nó vào mô-đun VBA. Sau đó, kích hoạt một bảng tính và thực hiện chương trình con QuickMap.

Bản đồ phụ ()
Dim FormulaCells As Biến thể
Dim TextCells As Biến thể
Dim NumberCells As Biến thể
Diện tích chiều như phạm vi
Nếu TypeName (ActiveSheet) <> Ban Worksheet 'Sau đó thoát Sub
'Tạo các biến đối tượng cho các tập hợp con
Khi xảy ra lỗi Tiếp tục
Đặt FormulaCells = Range (Đăng A1 A1). SpecialCells _
(xlForm Formula, xlNumbers + xlTextValues ​​+ xlLogical)
Đặt TextCells = Range (Đăng A1 A1). SpecialCells (xlConstants, xlTextValues)
Đặt NumberCells = Range (Đăng A1 A1). SpecialCells (xlConstants, xlNumbers)
Trên Lỗi GoTo 0
'Thêm một trang tính mới và định dạng nó
Tờ .Thêm
Với các tế bào
.ColumnWidth = 2
.Không kích thước = 8
.HTHERAlocate = xlCenter
Kết thúc với
Ứng dụng.ScreenUpdating = Sai
'Làm các ô công thức
Nếu không phải là IsEmpty (FormulaCells) thì
Đối với mỗi khu vực trong FormulaCells.Areas
Với ActiveSheet.Range (Area.Address)
.Value = đổi Fiên
.Interior.Color Index = 3
Kết thúc với
Khu vực tiếp theo
Kết thúc nếu
'Làm các ô văn bản
Nếu không phải là IsEmpty (TextCells) thì
Đối với mỗi khu vực trong TextCells.Areas
Với ActiveSheet.Range (Area.Address)
.Value = Đổi Tiên
.Interior.Color Index = 4
Kết thúc với
Khu vực tiếp theo
Kết thúc nếu
'Làm các ô số
Nếu không phải là IsEmpty (NumberCells) thì
Đối với mỗi khu vực trong NumberCells.
Với ActiveSheet.Range (Area.Address)
.Value =
.Interior.Color Index = 6
Kết thúc với
Khu vực tiếp theo
Kết thúc nếu
Kết thúc phụ

Làm thế nào nó hoạt động

Quy trình đầu tiên kiểm tra để đảm bảo bảng hoạt động là một bảng tính. Nếu không, sẽ có một lối thoát nhanh mà không cần thực hiện thêm. Khi trang tính hoạt động là một bảng tính, quy trình tạo ba biến đối tượng bằng cách sử dụng phương thức SpecialCells để xác định các loại ô khác nhau. Các SpecialCells phương pháp rất hữu ích. Nếu bạn không quen thuộc với nó, hãy kiểm tra nó trong tệp trợ giúp trực tuyến của Excel. Lưu ý việc sử dụng On Error Resume Next . Điều này là để tránh lỗi xảy ra nếu không có ô nào đủ điều kiện – ví dụ: nếu bảng tính không có công thức.

Tiếp theo, quy trình thêm một bảng tính mới, giảm chiều rộng ô và đặt căn chỉnh ngang thành chính giữa. Bước này là mỹ phẩm. Các phụ sau đó tắt cập nhật màn hình để tăng tốc mọi thứ lên một chút.

Ba khối mã tiếp theo xử lý các ô. Nếu không có ô nào đủ điều kiện, biến đối tượng là rỗng, vì vậy các kiểm tra phụ cho việc này. Sau đó, các vòng lặp thường quy thông qua từng Vùng trong đối tượng Phạm vi và định dạng ô. Bạn có thể dễ dàng tùy chỉnh phần này của chương trình con để áp dụng các định dạng khác nhau.

Xem thêm: Mẹo giảm lỗi cho VBA Excel 2016

Khóa học VBA Excel tại Hà Nội

LEAVE A REPLY

Please enter your comment!
Please enter your name here