Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
There is no back button, no CTRL+Z when a script is run through Excel. Once you run a macro, that’s it, there is no going back. You pose the risk of losing all your raw data if it isn’t backed up. We all know how important data integrity is; backing up your data
will always be best practice.
That being said, there are times when backing up your data can prove difficult in automation. An example I faced was when I automated remote logins into hundreds of switches and routers using SecureCRT and VBScript. The code automatically pulled the data, parsed it accordingly, and dumped the raw data into an Excel template I created. Once this was completed, the VBA script was triggered, reports were generated, and emailed to the appropriate people.
The down side I faced was when things didn’t go according to plan, requiring the data pull again, I would lose hours of time, as the script had to remotely login and pull the data a second time. To remedy this, I wrote some code that calls the macro-enabled template, copies the data dump and creates a new workbook. This way, the raw data is saved and the script runs on a copy of the data, instead of the original data set.
There were also times that the reports I created included drop down lists. I wanted to ensure only the options listed could be chosen. The data that the drop down list used was located on a separate worksheet. A user could easily add or change the drop down list options by editing the list. To prevent this from happening, you can make the worksheet very hidden
, preventing any alterations in the data as shown below.
Dim wbReport As Workbook
'This copies all the worksheets from your template into report
‘Worksheets are put in an array, you can add as many as you need
Worksheets(Array("First WS", "Second WS", "Third WS", "Fourth WS")).Copy
'Saves a local copy of your WB.
‘This also renames the file with current date.
ActiveWorkbook.SaveAs Filename:="Your workbook name here " & Format(Date, "Long Date") & ".xlsx", FileFormat:=51
'we want to make sure that we are working with the right workbook
‘This alse saves the copy as a macro-enabled workbook
Set wbReport = Workbooks.Open("Your workbook name here " & Format(Date, "Long Date") & ".xlsx")
‘Define and set worksheets
Dim firstWS As Worksheet
Set firstWS = wbReport.Worksheets("First WS")
Dim secondWS As Worksheet
Set secondWS = wbReport.Worksheets("Second WS")
Dim thirdWS As Worksheet
Set thirdWS = wbReport.Worksheets("Third WS")
Dim fourthWS As Worksheet
Set fourthWS = wbReport.Worksheets("Fourth WS")
‘Let’s REALLY hide the data in the fourth worksheet using this code, ensuring users are unable
‘Unable to unhide it
wbReport.Sheets("Fourth WS").Visible = xlSheetVeryHidden
That's it, you can now safely ensure the integrity of your raw data by automatically copying it into a new workbook. Protect your data, just as you would safeguard your home
, by hiding data in a way that only VBA can. A user is unable to easily view a very hidden worksheet, whereas hidden worksheets are viewable by simply selecting unhide. Using the code above will help keep your data safe and prevent alterations of specified worksheets.