Declare global variable for sheet password

0
votes

I have a larger Excel file with multiple sheets and modules. In the code for each of these I need to protect or unprotect a password protected sheet temporarily in order to update certain protected parts.

So far I use one of the following lines which works but this means that the password appears multiple times throughout the code. Is there a way I can declare this password just once like a global variable and then just refer to this variable whenever needed so that it only has to be changed once if there is need for a change ? Also, would this reduce security on the file ?

Current code:
To protect a sheet:

ActiveSheet.Protect Password:="MyPassword", UserInterfaceOnly:=True

To unprotect a sheet:

ActiveSheet.Unprotect Password:="MyPassword"
2
vbaexcelvariablespassword-protection
If security is a real concern, note that excel is probably not the right choice. A password stored as a VBA string is easy to retrieve from someone who knows how to crack the password of a VBA project. – Ioannis

2 Answers

3
votes
  1. In your VB editor, right click on the project, and then Insert > Module
  2. Call it something useful like 'Constants'

  3. Insert the following statement:

    Public Const strPwd as String = "MyPassword"

It is optional to type the constant, so the 'as String' part is down to taste. You can use this constant in any place in your project where you would previously have used your literal password string.

Regarding security, the best thing to do would be to make sure you have protected the VB project itself with a strong password. You can explore the options here in VB IDE > Tools > VBAProject Properties > Protection tab.

1
votes

You can use this code as an example

Option Explicit

Public Const g_strPASSWARD As String = "MyPassword"

' To Protect
Sub ProtectSheet(ByRef shToProtect As Worksheet)

    shToProtect.Protect Password:=g_strPASSWARD, UserInterfaceonly:=True

End Sub


'To Protect
Sub UnprotectSheet(ByRef shToUnprotect As Worksheet)

    shToUnprotect.Unprotect Password:=g_strPASSWARD

End Sub


' To Use
Sub MyTest()

    ProtectSheet ActiveSheet

    UnprotectSheet ActiveSheet

End Sub

I hope this helps.