VBA Dim and Set Worksheet for all modules

[GeorgesT]

Hello there,

I am not new anymore to VBA, but I'm still strugglin:

I have made 6 modules, all using the

Dim PROJECT As Workbook
Dim REGISTER As Worksheet

Secondly they all use

Set PROJECT = Workbooks("Project Register TEST.xlsm")
Set REGISTER = PROJECT.Sheets("ProjectRegister")

Now because of the changing names for the file I would like to refer to a single Dim and Set statement in a 'overal' module, that will work for all 6 modules.
But I dont know what to do. I tried to refer to a sub

Call DimAndRange

Referring to this

Public Sub DimAndRange()
Dim PROJECT As Workbook
Dim REGISTER As Worksheet
Const MAX = 2000
Set PROJECT = Workbooks("Project Register FINAL VERSION.xlsm")
Set REGISTER = PROJECT.Sheets("ProjectRegister")

End Sub

But I wont work if I refer to REGISTER in my module.



Is there a way to fix this?

 

[NewOrderFac33]

Just declare your variables using PUBLIC instead of DIM but BEFORE your first SUB() procedure.
So:

Public MyVariable as range
Public MyNextVariable as String etc etc

Sub Procedure1()
End Sub

Sub Procedure2()
End Sub

and so on.

 

[GeorgesT]

mm,
The problem is, every sub procedure has it own module.
And with the public statement i cant set the dimension for the worksheet.

What I want is a code like this

 Public Sub DimAndRange()
Dim PROJECT As Workbook
Dim REGISTER As Worksheet
Const MAX = 2000
Set PROJECT = Workbooks("Project Register FINAL VERSION.xlsm")
Set REGISTER = PROJECT.Sheets("ProjectRegister")

End Sub

And with this code in another module and within that module a new sub:
Call DimAndRange
That would fix the PROJECT en REGISTER so then I only have to change 1 value if needed.