Ensure Data Integrity and Privacy with VBA

Published on
4,397 Points
2 Endorsements
Last Modified:
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. 
Sub Copy_WB

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

End Sub

Open in new window


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.
Author:Josh Snow

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Wrapper-1-Query. Use an Excel function to calculate a column for an Access query. Part 1. Shows a query in Access that has a calculated column with the results of an Excel worksheet function. See how to call a wrapper function from a query, and …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month