Declare global variable for sheet password

Question

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"

Answer 1

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.