{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["hardcode check"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":2,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"hardcode check","index":0,"n_rows":18,"n_cols":6,"rows_kept":18,"cut":false,"hidden":false,"merged":[[3,5,9,5]]}
[null,"Check Hardcoded Formulas"]
[]
[null,"Cell with formula","Hardcoded?","Formula",null,"What is this?"]
[{"$f":"IF(C4, 1,\"\")","v":1},{"$f":"SUM(1,2,3)","v":6},{"$f":"hasConstants(B4)","v":true},{"$f":"getFormula(B4)","v":"=SUM(1,2,3)"},null,"Often we write formulas with hardcoded values in them, like =SUM(A1:A2, 30) These hardcoded formulas can create trouble when auditing spreadsheets. So I created an user defined function (UDF) to find out if a cell contains hardcoded formula arguments. The function hasConstants(cell) will return TRUE if cell has hardcoded formula parameters, FALSE otherwise."]
[{"$f":"IF(C5, 1,\"\")","v":1},{"$f":"SUM(A2,A3, 3)","v":3},{"$f":"hasConstants(B5)","v":true},{"$f":"getFormula(B5)","v":"=SUM(A2,A3, 3)"}]
[1,{"$f":"IF(x=y,SUM(1,2,3),COUNT(1,2,3))","v":6},true,"=IF(x=y,SUM(1,2,3),COUNT(1,2,3))"]
["",{"$f":"SUM(x,y)","v":0},false,"=SUM(x,y)"]
["",{"$f":"SUM(x,SUM(x,y,SUM(x,y,A4,'hardcode check'!C1)))","v":1},{"$f":"hasConstants(B8)","v":false},"=SUM(x,SUM(x,y,SUM(x,y,A4,'hardcode check'!C1)))"]
["",{"$f":"SUM(I3:I12)","v":0},false,"=SUM(I3:I12)"]
[1,{"$f":"AVERAGE(1,2,3,4,5,J6:J17)","v":3},true,"=AVERAGE(1,2,3,4,5,J6:J17)"]
[1,73,true,"73"]
[1,{"$f":"SUM(A1*3, A2)","v":0},true,"=SUM(A1*3, A2)"]
["",{"$f":"A1+A2","v":0},false,"=A1+A2",null,"How to see the code?"]
["",{"$f":"A1>=A2","v":true},{"$f":"hasConstants(B14)","v":false},"=A1>=A2",null,"Press Alt+F11, go to Module 1"]
[]
[null,null,null,null,null,"Learn VBA"]
[null,null,null,null,null,"Write your first VBA program"]
[null,null,null,null,null,"Online VBA Classes from Chandoo.org"]