Can variable declared in the Private Sub Workbook_Open of the ThisWorkbook Excel Object be accessed by another method in another module? I want to declare and assign a variable at the start of my code that can be changed by any module using it. This change should be reflected in the variable when the next method calls it.
I have a sub in a module that assigns value to the public variable. I require this value set by module1 to be accessible to that of module2
A global variable needs to have Public accessibility, and be declared at module-scope in a standard module (.bas).
Option Explicit Public Foo As Long ' global variable The problem with global variables is that they can be read and written to by anything anywhere in the code: global state easily leads to unmaintainable spaghetti code and should be avoided whenever possible.
There are a number of alternatives, notably using parameters:
Option Explicit Public Sub SomeEntryPoint() Dim foo As Long ' local variable DoSomething foo MsgBox foo 'prints 42 End Sub 'this procedure could be in any module, public. Private Sub DoSomething(ByRef foo As Long) foo = 42 'byref assignment; caller will receive the updated value End Sub Another alternative, if the variable needs to be written by the module that declares it, but needs to be read from somewhere else, is to use properties:
Option Explicit Private foo As Long ' private field Public Sub DoSomething() 'do stuff... foo = 42 '... End Sub Public Property Get SomeFoo() As Long SomeFoo = foo End Property Now code in that module can write to foo as needed, and other modules can only read foo through the SomeFoo property - assuming the field and property are defined in Module1:
Debug.Print Module1.SomeFoo 'gets the value of the encapsulated private field Building on the answer of @David, this is how to use Dim and Public and their differences (in a Modul, named Modul1 write the following and run TestMe):
Dim a As String Public b As String Private c As String Global d As String Private Sub TestA() 'Whatever is not declared in TestMe, would take its value from here for the print. '^-If it is declared, the value would be attached to the public/private/dim/glb above. a = 11 b = 22 c = 33 d = 44 End Sub Private Sub TestMe() Dim a As String 'Dim b As String 'Dim c As String Dim d As String a = 1 b = 2 c = 3 d = 4 TestA Debug.Print a; vbTab; Modul1.a Debug.Print "----------------" Debug.Print b; vbTab; Modul1.b Debug.Print "----------------" Debug.Print c; vbTab; Modul1.c Debug.Print "----------------" Debug.Print d; vbTab; Modul1.d End Sub This is what you get:
1 11 ---------------- 22 22 ---------------- 33 33 ---------------- 4 44 No - the variable should be declared as Public in a normal module.
It can't be called out, as you are calling a Private Sub.
In order to have variables which can be changed Publically, try outside of any Sub:
Public wBk As Workbook Public var As String This would declare the variables. To modify the values, you need to set them in a public Subroutine:
Public Sub myPublicVar() Set wBk = Workbooks("Workbook1.xlsm") Set var = "Whatever you like" End Sub If then you want your module, or subroutine to include the variables, you would do the following
Sub myOtherSub() Call myPublicVar MsgBox var End Sub Hope this helps!
Public var1 as String - Makes var1 a public string Instead of Sub M1(), use Public Sub M1() As this will let you use the variable in another sub. In your third Sub you need to do the following: Sub M2(), call M1, MsgBox var1, End Sub' Public variable Public a as String ' Local variable Public sub hello() Dim a as String End sub The first a is public and you can use where you want and the second variable is local and the only site you can use is into the function hello(). So both variable are diferent.
Dim is never public. This answer is wrong and misleading. That "private variable" is really a local variable, and what you're calling a "public variable" is in fact a private field, a module-scope variable that can only be accessed within that module.