<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Ensure Data Integrity and Privacy with VBA

Published on
3,917 Points
717 Views
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.
2
Comment
Author:Josh Snow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Join & Write a Comment

This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month