Nếu bạn sử dụng nhiều ô và phạm vi được đặt tên trong sổ làm việc của mình, bạn có thể ngạc nhiên khi phát hiện ra rằng Excel không cung cấp cách nào để liệt kê chi tiết cho mỗi tên. Thông tin hữu ích xuất hiện trong hộp thoại Trình quản lý tên, nhưng không có cách nào để hiển thị thông tin này theo cách có thể được in.
Mã VBA được cung cấp ở đây tạo ra một báo cáo hữu ích mô tả các tên được xác định trong bất kỳ sổ làm việc nào.
Báo cáo tên ví dụ
Ở đây, bạn thấy một ví dụ về Báo cáo tên.
Báo cáo, được tạo trên một bảng tính mới, bao gồm các thông tin sau cho mỗi tên:
- Tên: Tên.
- RefersTo: Định nghĩa cho tên. Thông thường, đây sẽ là một ô hoặc phạm vi, nhưng một tên cũng có thể định nghĩa công thức.
- Các ô: Số lượng ô chứa trong phạm vi được đặt tên. Đối với các công thức được đặt tên, trường này hiển thị # N / A.
- Phạm vi: Phạm vi của tên – Sổ làm việc hoặc tên của bảng tính cụ thể mà tên đó hợp lệ.
- Ẩn: Đúng nếu tên bị ẩn. Tên ẩn được tạo bởi một số bổ trợ (như Bộ giải) và không xuất hiện trong hộp thoại Trình quản lý tên.
- Lỗi: Đúng nếu tên chứa tham chiếu sai.
- Liên kết: Một siêu liên kết mà khi nhấp vào sẽ kích hoạt phạm vi được đặt tên. Chỉ tên tham chiếu đến các ô hoặc phạm vi bao gồm một liên kết.
- Nhận xét: Nhận xét cho tên, nếu có.
Mã VBA
Để sử dụng mã này, nhấn Alt + F11 để kích hoạt Visual Basic Editor. Sau đó chọn Chèn → Mô-đun để chèn mô-đun VBA mới. Sao chép mã và dán nó vào mô-đun mới.
Sub GenerateNameReport ()
'Tạo một báo cáo cho tất cả các tên trong sổ làm việc
'(Không bao gồm tên bảng)
Tên n
Hàng dài như
Dim CellCount As Biến thể
'Thoát nếu không có tên
Nếu ActiveWorkbook.Names.Count = 0 thì
MsgBox vào Sổ làm việc đang hoạt động không có tên được xác định.
Thoát Sub
Kết thúc nếu
'Thoát nếu sổ làm việc được bảo vệ
Nếu ActiveWorkbook.ProtectStr struct thì
MsgBox Cảnh Một trang tính mới không thể được thêm vào vì sổ làm việc được bảo vệ.
Thoát Sub
Kết thúc nếu
'Chèn một tờ mới cho báo cáo
ActiveWorkbook.Worksheet.Add
ActiveSheet.Move After: = Sheets (ActiveWorkbook.Sheets.Count)
ActiveWindow.DisplayGridlines = Sai
'Thêm dòng tiêu đề đầu tiên
Phạm vi (Số A1: H1 đỉnh). Hợp nhất
Với Phạm vi (Truyền A1 A1)
.Value = Báo cáo tên cho: Tải & ActiveWorkbook.Name
.Không kích thước = 14
.Tont.Bold = Đúng
.HTHERAlocate = xlCenter
Kết thúc với
'Thêm dòng tiêu đề thứ hai
Phạm vi (Số A2: H2 hung). Hợp nhất
Với Phạm vi (Cúp A2)
.Value = Tiền được tạo ra
.HTHERAlocate = xlCenter
Kết thúc với
'Thêm tiêu đề
Phạm vi (Dòng A4: H4 Cảnh) = Mảng (Tên Tên,
Phạm vi phạm vi,
'Lặp qua tên
Hàng = 4
Khi xảy ra lỗi Tiếp tục
Đối với mỗi n trong ActiveWorkbook.Names
Hàng = Hàng + 1
'Cột A: Tên
Nếu n.Name Giống như Viking *! * Sau đó
Các ô (Hàng, 1) = Tách (n.Name, ăn! Nghi) (1) 'Xóa tên trang tính
Khác
Các ô (Hàng, 1) = n.
Kết thúc nếu
'Cột B: Tham chiếu
Các ô (Hàng, 2) = Hy '' & n.RefersTo
'Cột C: Số lượng ô
CellCount = CVErr (xlErrNA) 'Trả về giá trị cho công thức được đặt tên
CellCount = n.RefersToRange.CountLarge
Các ô (Hàng, 3) = CellCount
'Cột D: Phạm vi
Nếu n.Name Giống như Viking *! * Sau đó
Các ô (Hàng, 4) = Tách (n.Name, Mạnh! Lần) (0) 'tên bảng trích xuất
Các ô (Hàng, 4) = Thay thế (Các ô (Hàng, 4), lối vào '', tính năng) 'xóa dấu nháy đơn
Khác
Các ô (Hàng, 4) = Sổ làm việc
Kết thúc nếu
'Cột E: Trạng thái ẩn
Các ô (Hàng, 5) = Không n.
'Cột F: Tên lỗi
Các ô (Hàng, 6) = n.RefersTo Like * * # # REF! *
'Cột G: Siêu liên kết
Nếu không phải ứng dụng.IsNA (Ô (Hàng, 3)) thì
ActiveSheet.Hyperlinks. Thêm _
Neo: = Các ô (Hàng, 7), _
Địa chỉ: = Triệu, _
Địa chỉ phụ: = n.Name, _
TextToDisplay: = n.Name
Kết thúc nếu
'Cột H: Nhận xét
Các ô (Hàng, 8) = n.Comment
Tiếp theo
'Chuyển đổi nó thành một bảng
ActiveSheet.ListObjects. Thêm _
Loại nguồn: = xlSrcRange, _
Nguồn: = Phạm vi (phiên bản A4 A4). CurrentRegion
'Điều chỉnh độ rộng cột
Cột (Số A: Hước). EntireColumn.AutoFit
Kết thúc phụ
Tạo một báo cáo
Thực hiện thủ tục GenerateNameReport và báo cáo được tạo trên một bảng tính mới trong sổ làm việc đang hoạt động. Mã không phải có trong sổ làm việc có chứa tên cho báo cáo.
Xem thêm: Vòng lặp VBA Excel
