0

I am learning how to use public variables in my excel projects, at this moment I am running into the following problem: I am declaring my variable in a single module as follows:

Option Explicit
Public Pass_H As String
Public Pass_L As String

At the beginning of the project I declare as follows:

Private Sub Workbook_Open()
ThisWorkbook.Windows(1).Visible = False
Pass_H = Sheets("Pass").Range("C4").Value
Pass_L = Sheets("Pass").Range("F4").Value

And I use it in other projects of other sheets for example:

Private Sub CB_3_Click()
Dim Final_T As Range, Final_N As Range
With ActiveSheet
    Set Final_T = .Range("A1").End(xlToRight)
    Set Final_N = Final_T.End(xlDown)
    .Unprotect Pass_H
    .Range("A1", Final_N).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
    .Protect (Pass_H)
End With
End Sub

Or:

Private Sub Blo()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
        ws.Protect (Pass_H)
    Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

But even in these simple lines the value is lost at some point and it appears as nothing. I already went through the rest of the project and the only statement or reference is in the Workbook.Open() I don't understand what I could be doing wrong.

BigBen
  • 46,229
  • 7
  • 24
  • 40
Armas.Chuy
  • 17
  • 8
  • Public variables only work on the same execution, if you stop the execution (this means the original execution ended which will be the workbook open event) they just don't exist. – Sgdva Jan 07 '22 at 19:10
  • In fact, they continue to work in the aforementioned projects, but there comes a time when it loses its value, but after several uses. – Armas.Chuy Jan 07 '22 at 19:13
  • 2
    I'd advise against public variables whenever possible. – BigBen Jan 07 '22 at 19:15
  • I'm pretty sure the execution continues somehow, the public variables stop as soon as you end the routine (they don't continue to exist/hold values after the routine has been stopped). This means, a public variable won't exist indefinitely for the workbook just because it was computed once. – Sgdva Jan 07 '22 at 19:18
  • Would you rather call a Public Sub? `Public Sub Pass() Pass_H = Sheets("Pass").Range("C4").Value End Sub` And `Sheets("Pass").Pass` Still a Public Variable tho but i guess you refresh the value – Armas.Chuy Jan 07 '22 at 19:19
  • 2
    @Sgdva perhaps read [Understanding the lifetime of variables](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-the-lifetime-of-variables), and [Understanding Scope and Visibility](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-scope-and-visibility) – BigBen Jan 07 '22 at 19:19
  • I read the first one already, going to read the other one thnx. – Armas.Chuy Jan 07 '22 at 19:22
  • 1
    Why not just create a function that returns the value as needed, instead of storing it in a public variable. Such as, `Function GetPass() GetPass = Range("C4").Value End Function` To test it, you can just call: `Sub test() Debug.Print GetPass() End Sub` – basodre Jan 07 '22 at 19:24
  • 2
    Or create a property in that sheet. – BigBen Jan 07 '22 at 19:24
  • 3
    @Sgdva sorry, but you are wrong. Public variables do hold their values between executions. They are reset for example by unhandled errors, or executing an `End` – chris neilsen Jan 07 '22 at 19:24
  • 2
    Previously: https://stackoverflow.com/questions/7041138/what-is-the-lifetime-of-a-global-variable-in-excel-vba/7043901#7043901 – Tim Williams Jan 07 '22 at 19:28
  • @BigBen I am testing the function and it seems to be working quite well, if it is not annoying, do you have any article on why they do not recommend the use of public variables? I would appreciate it. – Armas.Chuy Jan 07 '22 at 19:45

0 Answers0