[SOLVED] Setting and Declaring Global Constant

Setting and Declaring Global Constant

I have looked online for this answer and based on my results, I cannot tell what I am doing wrong. Upon opening an Excel workbook, I want to declare and set a global constant. I have the following codes to do this. However, when I manually try to unprotect the worksheets, they have no password. Even when I put a MsgBox in workbook open, it shows the right password.

Private Sub Workbook_Open()
    Application.Run "SetConstants"
End Sub

Sub SetConstants()
    Const Pass As String = "QEOps"
End Sub

Sub Protect()
    ThisWorkbook.Worksheets("Start").Protect Password:=Pass
    ThisWorkbook.Worksheets("Cognos Trans by Date w Lot").Protect Password:=Pass, AllowSorting:=True, 
End Sub

Sub Unprotect()
    ThisWorkbook.Worksheets("Start").Unprotect Password:=Pass
    ThisWorkbook.Worksheets("Cognos Trans by Date w Lot").Unprotect Password:=Pass
End Sub

When I manually try to unprotect the worksheets, they have no password even though they should.

Solution

You need to insert the new module into the project and declare this constant there:

Public Const Pass As String = "QEOps"

Use "Option Explicit" to avoid non-declared variables.

In your code Pass is the local constant or variable in every subroutine.

Consider other concerns to store the password by such way.