vba 数据有效性_使用VBA确保数据完整性和隐私性

vba 数据有效性

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;

通过Excel运行脚本时没有后退按钮，没有CTRL + Z。 一旦运行了宏，就可以了。 如果未备份所有原始数据，则可能会丢失所有原始数据。 我们都知道数据完整性的重要性。

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. 

话虽如此，有时备份数据在自动化中可能会遇到困难。 我遇到的一个例子是，当我使用SecureCRT和VBScript自动将远程登录到数百台交换机和路由器中时。 代码自动提取数据，进行相应的解析，然后将原始数据转储到我创建的Excel模板中。 完成此操作后，将触发VBA脚本，生成报告，并通过电子邮件发送给适当的人员。

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

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.

就是这样，您现在可以通过自动将原始数据复制到新工作簿中来安全地确保原始数据的完整性。 通过以仅VBA可以的方式隐藏数据来保护您的数据，就像保护您的房屋一样 。 用户无法轻松查看非常隐藏的工作表，而隐藏的工作表仅通过选择取消隐藏即可查看。 使用上面的代码将有助于确保您的数据安全，并防止更改指定的工作表。

翻译自: https://www.experts-exchange.com/articles/29102/Ensure-Data-Integrity-and-Privacy-with-VBA.html

vba 数据有效性