Oh My! Searching and Fixing Hard Coded Numbers with a Macro
The last post discussed the high level mechanics of setting up formulas to check for hard coded numbers. I ended with a comment about doing this check for 64 files and mentioned that a macro would needed to be done to do this kind of check.
Today, this post will show the macro. The last post mentioned four spreadsheets: A, B, C, and D. I'm keeping that scenario but altering it slightly to 3 files, where D is embedded into B. B would be represented by tab "formula check template" and D would become "form check". The "formula check template" will have already been created with CELLFORMULA (similar to the Spreadsheet B) but with the formulas converted to values. So the tab is just a template against which the "corrupted" file is checked.
The macro requires the corrupted file Spreadsheet C to be open and be the active Window before starting the macro. Upon starting the macro, the first thing it will do is clear out the prior data sitting in Spreadsheet D, which will henceforth be called "form check" tab. Then in cell G1, the macro will start to create a new formula that will compare the corrupted Spreadsheet C against the "formula check template" (otherwise known as Spreadsheet B in the last post). Once the initial formula is created, it will then be copied out to the rest of the cells in the "form check" tab.
When the macro is done copying, it will tell you that the program is done. At this point you can see in the "form check" tab where the cells have been compromised by the hard coded entries - they will be marked with an "x".
The part I had trouble with in creating this macro was to actually make the macro create the formula doing the comparison between the corrupted file C and the "formula check template". The single quote was giving me a problem. In that instance, I finally had to wrap the single quote in its own double quotes "". But in a later instance, I didn't need to. So you will have to watch the quotes. Normally, when I have something written in double quotes - "is greater than", I will wrap it again in double quotes when I have to embed it in a formula created by a macro: Range("A1").Formula = "=A5 & ""is greater than"" & B5".
And now, the macro is shown below.
The Macro
Sub CheckForHardNumbers()
'You need to start from the suspected file (Spreadsheet C) and you need to have this Spreadsheet B file with the macro open. Spreadsheet D is a tab within Spreadsheet B and will be called “form check”.
‘Spreadsheet A is the original template with the correct formulas.
‘Spreadsheet B references A, will contain the macro and shows where the formulas are with the ‘CELLFORMULA function. The tab with the CELLFORMULA will be called “formula check template”. Once the formuas are set up, convert them to values.
‘Spreadsheet C is the corrupted file.
‘Spreadsheet D will actually sit within file Spreadsheet B as a tab and will show where the cells are hardcoded when they should contain formulas. This tab is called “form check”.
Dim wbSource As Workbook 'This will be the Spreadsheet C file
Dim wbDest As Workbook 'This will be the Spreadsheet B file
Dim rangetocopy As Range
Dim FileName As String
Set wbDest = Workbooks("Spreadsheet B")
Set wbSource = ActiveWorkbook
'Now check for hard code formulas
FileName = ActiveWorkbook.Name
wbDest.Sheets("form check").Activate
Range("A1.AA100").ClearContents 'Clear prior contents
Range("G1").Select ‘The cell will depend on where you begin to check for formulas
***************************************************************************************************************
'The Main Formula to check for hard coded numbers.
ActiveCell.Formula = "=if(and(left(cellformula(" & "'" & "[" & FileName & "](sheet name in Spreadsheet C)" & "'" & "!G1),1)<>""=""," & "'" & "formula check template" & "'" & "!G1=""f""),""x"","""")"
***************************************************************************************************************
ActiveCell.Select
Selection.Copy
Range("A1:AA100").Select
ActiveSheet.Paste
MsgBox ("Program is done"
End Sub
You must be logged in to post a comment.