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?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,255
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

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