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.