Hướng dẫn hard-coded values excel color - giá trị được mã hóa cứng màu excel

• Macro VBA

• Cập nhật lần cuối vào ngày 14 tháng 1 năm 2015

Chandoo

Đây là một vấn đề phổ biến. Hãy tưởng tượng bạn đang tìm kiếm một bảng tính phức tạp, có tiêu đề một cách khéo léo Chiến lược của công ty 2020.xlsx, đó là 17 tab, công thức thông tin và cấu trúc ô uế. Bất cứ ai đã thiết kế nó trong điên cuồng.

Như thể Homer Simpson & NBSP; đã tạo ra nó trong khi Peter Griffin giám sát dự án.

Vậy làm thế nào để bạn phát hiện tất cả các ô chứa các công thức có giá trị mã hóa cứng?

Than ôi, các phương pháp thông thường không thành công

Các phương pháp thông thường để các công thức kiểm toán không có ích ở đây. Hãy xem nào:

Hiển thị các công thức [Ctrl+`]: & nbsp; Vì chúng ta có quá nhiều công thức, nên cách tiếp cận này đòi hỏi rất nhiều nheo mắt và gallon cà phê.Since we have way too many formulas, this approach requires a lot of squinting and gallons of coffee.

Chuyển đến Đặc biệt> Hằng số: & NBSP; Điều này sẽ chỉ phát hiện các ô không đổi [tức là các ô đầu vào], nhưng không phải các ô chứa các công thức như = if [2 = 2, ngân sách2014! A2, ngân sách2015! A2]This will only detect constant cells [ie input cells], but not cells containing formulas like =IF[2=2, Budget2014!A2, Budget2015!A2]

Trace Precedents: & NBSP; Điều này chỉ có thể được sử dụng cho các công thức có chứa tất cả các giá trị được mã hóa cứng [ví dụ: Sum [1,2,3] sẽ không có mũi tên, trong đó Sum [A1, A2, 7] sẽ có một số mũi tênThis can be used only for formulas that contain all hard-coded values [ex: SUM[1,2,3] will have no arrows, where as SUM[A1,A2, 7] will have some arrows

FormulationXt []: & nbsp; Có một hàm mới gọi là formulationxt [] được giới thiệu trong excel 2013. Điều này có thể cho chúng ta biết công thức trong ô là gì. Nhưng chúng ta vẫn cần phát triển logic bổ sung để xem văn bản công thức có chứa bất kỳ hằng số nào không. There is a new function called as FORMULATEXT[] introduced in Excel 2013. This can tell us what is the formula in a cell. But we still need to develop additional logic to see if the formula text contains any constants.

Hãy để xây dựng Tính năng phát hiện các công thức mã hóa cứng cho Excel

Vẻ đẹp của Excel là, nếu có điều gì đó bạn có thể làm với các tính năng trên màn hình, bạn có thể xây dựng nó. Đây là nơi VBA có ích.

Vì vậy, chúng tôi có thể tạo một hàm do người dùng xác định người dùng sử dụng ô làm ô làm đầu vào và cho chúng tôi biết đúng hoặc sai. Đúng nếu ô có hằng số [hoặc giá trị mã hóa cứng] dưới dạng tham số công thức và sai.

Nhưng những gì nên là logic cho HasConstants []?

Quá trình phát hiện các giá trị được mã hóa cứng & nbsp; có thể được xác định như thế này:

1. Đọc công thức từ trái sang phải
2. Đối với mỗi đối số của công thức
   1. Xem nếu đối số là một tham chiếu hoặc tên hợp lệ
   2. Nếu không, hãy phá vỡ vòng lặp và trả về đúng
3. Trả lại sai

Làm thế nào để chúng tôi chỉ phát hiện các tham số?

Không có cách trực tiếp để chỉ trích xuất các tham số của một công thức. Vì vậy, những gì chúng tôi làm là chúng tôi chia công thức thành một mảng bằng dấu phẩy Delimiter.

Và chúng tôi kiểm tra từng mục của mảng này để xem nó có

• Một cuộc gọi chức năng [như tổng, đếm, vlookup]
• một tên hợp lệ hoặc tham chiếu

Điều gì về các chức năng lồng nhau?

Cách tiếp cận hoạt động theo cùng một cách.

Điều gì về các hoạt động số học, văn bản hoặc so sánh?

Ví dụ: một công thức như = A1+A2+17 sẽ ném đúng vì nó có giá trị mã hóa cứng.

Vì vậy, những gì chúng tôi làm là, chúng tôi thay thế tất cả các toán tử như vậy bằng & nbsp; delimiter [dấu phẩy] trước khi chia văn bản công thức.

Chúng ta có thể xem xét +-*/%&>>

Vậy mã trông như thế nào?

Đây là cách nó trông như thế nào:

Const COMMA = ","
Const OPERATORS = "+-*/%^&>