[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Ensure Data Integrity and Privacy with VBA

Published on
4,566 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

Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Join & Write a Comment

Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month