<

Go Premium for a chance to win a PS4. Enter to Win

x

Ensure Data Integrity and Privacy with VBA

Published on
4,044 Points
844 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
0 Comments

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Join & Write a Comment

This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month