VBA Dim and Set Worksheet for all modules

GeorgesT

New Member
Joined
Mar 20, 2013
Messages
7
Hello there,

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

I have made 6 modules, all using the
Code:
Dim PROJECT As Workbook
Dim REGISTER As Worksheet

Secondly they all use
Code:
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
Code:
Call DimAndRange

Referring to this
Code:
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?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,268
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Mar 20, 2013
Messages
7
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

Code:
 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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,683
Messages
5,838,792
Members
430,569
Latest member
sbardelli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top