Public variable value not being shared by other modules

[ecchahine]

Hi guys. I'm fairly new to VBA and I'm having a small problem here. On a certain project I'm working on I have declared a couple of public Boolean variables. The value of this variable is consistent inside the same module, but if for example I change the value of Public x as Boolean to True in module1 macro1 and try to print its value in module2 macro2 the message will be empty. Any ideas?

Here's a small example:

Public macro As Boolean
__________________________

Private Sub Workbook_Open()

macro = True
.
.
End Sub

' In a different Module

Sub Test()
MsgBox macro
End Sub

The msgbox will be empty. (I used the msgbox just to illustrate the fact that the value isn't being passed on)

 

[StephenCrump]

Welcome to the Forum!

This should work as intended.

Does this happen when you run the code from Excel, or when you are playing around in the VB editor, perhaps stopping and starting the code?

Have you declared macro anywhere else in your code?

 

[ecchahine]

Welcome to the Forum!

This should work as intended.

Does this happen when you run the code from Excel, or when you are playing around in the VB editor, perhaps stopping and starting the code?

Have you declared macro anywhere else in your code?

I tried stopping and restarting the code, also quitting excel, restarting the OS, nothing. And yes I only declared it once. It's so weird.
What I'm trying to do is creating a userform login page that requires a password. The user cannot press the (X) button to exit the userform, they can only exit the whole program. But then I realized that they can easily break the macro by pressing Alt+F4 or Ctrl+Break. So my Workbook_Open will know if the password was inputted correctly or not by changing the Boolean's value to true. But I can seem to exchange values between modules.
When I discovered that, I checked my other two public variables that I use throughout the workbook (another Boolean and a string) and I found out that I also can't return their value anywhere outside their original module (module, not macro)

 

[RoryA]

It looks like you declared the variable in the ThisWorkbook module. Since that's a class, the variable becomes a public member of that class and has to be accessed as such using ThisWorkbook.macro. Alternatively, move the variable declaration to a normal module.

 

[ecchahine]

It looks like you declared the variable in the ThisWorkbook module. Since that's a class, the variable becomes a public member of that class and has to be accessed as such using ThisWorkbook.macro. Alternatively, move the variable declaration to a normal module.

You just saved me from breaking my laptop. THANK YOU SIR!

 

[RoryA]

Glad we could help (and spare your laptop!)

 

[excelogeeko]

I have a similar problem but with a 3D array...I'll appreciate the help. Here is the situation:

===Module 1======
Option Explicit
Public temp_array_lbr() As Variant
____________________________
Sub CurrentProdAlloc()
<code in here> 'All runs well and the arrays are initialized.
End Sub

===Module 2=======
Sub test()
Dim temp2_array_lbr() As Variant
ReDim temp2_array_lbr(14, 12, 5) 'I am not sure and added this in my tries. Is it necessary to Redim a variant array to ensure the incoming public array fits?
temp2_array_lbr() = temp_array_lbr() 'All I want to do for now is dump into a new array in this module. In reality, I'd love to save code and start looping the public array right away.
End Sub

Both modules are in then same project. What I am doing wrong? I am about to pull my hair (what is left). Seems that what I am doing is "intuitive"...but I'm old enough to know that intuitiveness is subjective!

 

[StephenCrump]

Welcome to the Forum!

No it's not necessary to make the second array the same size. This will suffice:

'Module 2
Sub Test()
    
    Dim temp2_array_lbr As Variant

    temp2_array_lbr = temp_array_lbr

End Sub

But as you point out, you should be able to access the public array temp_array_lbr in Module 2.

So what's happening when you try to loop through the public array? How are you populating temp_array_lbr ? Have you perhaps inadvertently also declared temp_array_lbr in Sub CurrentProdAlloc? Can you post your code for this Sub?

 

[excelogeeko]

Thanks Stephen. I found out what the problem was. It was execution and Public variable behavior—I had a hunch.

Once I run Module 1, the array will be in memory—until consumed once. I immediately must run Module 2 to pass into temp2_array_lbr. In other words, I am finding out the data is wiped out from temp_array_lbr even though it is Public once it is used. I was under the impression Public variables data remained in memory regardless — UNTIL I ran Module 1 again, where the array resides. I was hoping that would allow me to run Module 2 or any other module using the array however many times. Objective: I'd like this Module 1 3D array to be 'master data' for subsequent calculations and scenarios in other modules (I have not coded this far yet until I get this figured out).

This experience brings two questions. I wonder — Can I Redim Preserve temp_array_lbr after I am done in Module 1 so I can have a "static" array? Or I have no choice but to pass the data as shown, that is: temp2_array_lbr = temp_array_lbr? Clearly, this last option will require I code and sequence execution more tightly to leverage the data right there and then.

 

[StephenCrump]

Once I run Module 1, the array will be in memory—until consumed once. I immediately must run Module 2 to pass into temp2_array_lbr. In other words, I am finding out the data is wiped out from temp_array_lbr even though it is Public once it is used. I was under the impression Public variables data remained in memory regardless — UNTIL I ran Module 1 again, where the array resides. I was hoping that would allow me to run Module 2 or any other module using the array however many times.

If your code is running OK, the values in temp_array_lbr will persist once it is populated.

But if the code halts due to an unhandled error, the values will not persist. So if you are still developing your code and making errors, you'll lose what's in temp_array_lbr when the code breaks.

Strip it back to basics - populate temp_array_lbr in Module 1 and test in Module 2. Something like this should work as expected?

Public temp_array_lbr() As Variant
Sub Test1()

    Dim i As Long, j As Long, k As Long
    ReDim temp_array_lbr(1 To 10, 1 To 15, 1 To 20)
   
    For i = LBound(temp_array_lbr, 1) To UBound(temp_array_lbr, 1)
        For j = LBound(temp_array_lbr, 2) To UBound(temp_array_lbr, 2)
            For k = LBound(temp_array_lbr, 3) To UBound(temp_array_lbr, 3)
                temp_array_lbr(i, j, k) = i + 2 * j + 3 * k
            Next k
        Next j
    Next i

End Sub
'Module 2
Sub Test2()

    MsgBox temp_array_lbr(1, 1, 1)  'answer 6
    MsgBox temp_array_lbr(1, 10, 5)  'answer 36
   
End Sub