Bằng cách sử dụng VBA để tạo một bảng tính Worksheet - những con vú

Bạn đã bao giờ cố gắng để làm cho tinh thần của một bảng tính Excel không quen thuộc (và phức tạp)? Sẽ rất hữu ích để xem bản đồ cung cấp tổng quan về các ô nào chứa các hằng số và ô nào chứa các giá trị.

Bạn có thể tạo một tiện ích VBA tạo bản đồ của bảng tính đang hoạt động. Bản đồ được tạo ra trên một bảng tính mới và nó bao gồm các ô được mã hoá màu cho phép bạn nhanh chóng xác định các giá trị, văn bản và công thức.

Dưới đây, bạn sẽ thấy một ví dụ về một bản đồ như vậy. Các ô có chứa văn bản có màu xanh lá cây, những giá trị đó chứa một giá trị số có màu vàng, và các ô chứa các công thức có màu đỏ. Bản đồ như vậy có thể giúp bạn phát hiện 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 của QuickMap

Quy trình VBA tạo bản đồ trang tính được liệt kê dưới đây. 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ột 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.

Sub QuickMap () Dim FormulaCells As Variant Dim TextCells Như biến thể Dim NumberCells Như Variant Dim Area Như Range Nếu TypeName (ActiveSheet) "Worksheet" Sau đó Thoát Sub 'Tạo các biến đối tượng cho các tập con của ô trên Lỗi Tiếp tục Đặt Tiếp theo FormulaCells = "A1"). SpecialCells _ (xlFormulas, xlNumbers + xlTextValues ​​+ xlLogical) Đặt TextCells = Phạm vi ("A1"). SpecialCells (xlConstants, xlTextValues) Set NumberCells = Phạm vi ("A1"). SpecialCells (xlConstants, xlNumbers) Trên Error GoTo 0 'Thêm một trang tính mới và định dạng Sheets. Thêm với Cells. ColumnWidth = 2. Phông chữ. Kích thước = 8. HorizontalAlignment = xlCenter Kết thúc với ứng dụng. ScreenUpdating = False 'Làm các tế bào công thức nếu không IsEmpty (FormulaCells) Sau đó cho mỗi Khu vực Trong FormulaCells. Khu vực Với ActiveSheet. Phạm vi (Diện tích. Địa chỉ). Giá trị = "F". Nội địa. ColorIndex = 3 End With Next Area End Nếu 'Do các tế bào văn bản nếu không IsEmpty (TextCells) Sau đó cho mỗi Khu vực Trong TextCells. Khu vực Với ActiveSheet. Phạm vi (Diện tích. Địa chỉ). Giá trị = "T". Nội địa. ColorIndex = 4 End With Next Area End Nếu 'Làm các ô số nếu không IsEmpty (NumberCells) Sau đó Đối với Mỗi Khu vực Trong NumberCells. Khu vực Với ActiveSheet. Phạm vi (Diện tích. Địa chỉ). Giá trị = "N". Nội địa. ColorIndex = 6 Kết thúc với khu vực tiếp theo Nếu End Sub

Cách hoạt động

Thủ tục đầu tiên kiểm tra để chắc chắn rằng tờ hoạt động là một bảng tính. Nếu không, có một lối ra nhanh chóng mà không cần phải hành động thêm nữa. Khi bảng hoạt động là một bảng tính, thủ tục tạo ra ba biến đối tượng bằng cách sử dụng phương pháp SpecialCells để xác định các loại tế bào khác nhau.Phương pháp SpecialCells rất hữu ích. Nếu bạn không làm quen với nó, kiểm tra nó trong tập tin trợ giúp trực tuyến của Excel. Chú ý sử dụng On Error Resume Next. Đây là để tránh những lỗi xảy ra nếu không có tế bào đủ điều kiện - ví dụ, nếu bảng tính không có công thức.

Tiếp theo, thủ tục thêm một bảng tính mới, làm giảm chiều rộng ô, và thiết lập sự sắp xếp theo chiều ngang đến trung tâm. Bước này là mỹ phẩm. Các phụ sau đó tắt màn hình cập nhật để tăng tốc độ lên một chút.

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

Kiểm tra tiện ích Power Utility Pak cho một phiên bản phức tạp hơn của tiện ích này.