Global variables and constants

[snoh8r]

Dim Rng As Range
Dim rngCell As Range
Dim LastRow As Long
Dim LastColumn As Long
Dim thisMonth As Long
Dim thisYear As Long
Dim wBk1 As Workbook
Dim wBk2 As Workbook
Dim xRg As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5 As Worksheet


thisMonth = Month(Date)
thisYear = Year(Date)


Set wBk1 = Workbooks("Macros.xlsm")
Set wBk2 = Workbooks("Client " & MonthName(thisMonth) & " " & thisYear & ".xlsx")


Set ws1 = wBk2.Sheets(1)
Set ws2 = wBk1.Sheets(2)
Set ws3 = wBk1.Sheets(3)
Set ws4 = wBk2.Sheets(2)
Set ws5 = wBk2.Sheets(3)


Dim rngAddress1 As Range, rngAddress2 As Range, rngAddress3 As Range, rngAddress4 As Range, rngAddress5 As Range
Dim rngAddress6 As Range, rngAddress7 As Range, rngAddress8 As Range, rngAddress9 As Range, rngAddress10 As Range
Dim rngAddress11 As Range, rngAddress12 As Range, rngAddress13 As Range, rngAddress14 As Range, rngAddress15 As Range
Dim rngAddress16 As Range, rngAddress17 As Range, rngAddress18 As Range, rngAddress19 As Range, rngAddress20 As Range

I have the above written to several modules. I'd like to just simply create a global setting for all these so I don't have to keep writing them over and over (Ok, copy/paste). I keep a notepad sheet open to track them at all times as well.

Problem is, when I tried creating them as global and global const under an Option Explicit, it kept giving me issues with the Set ... 's.

Plus, if I run any macro and put something like set ThisMonth .... it fails and says it requires an object. This tells me that the global variables are not passing.

Any help would be appreciated.

 

[snoh8r]

Seriously? 80 views and not one shred of advice? I've moved on from this project but it would have been nice to have something to learn from on this. Everything I tried looking through the forums hasn't worked. Maybe I'm doing something wrong, like asking for help apparently.

 

[mikerickson]

If you are trying to create global object constants, as in ws1 is a constant for wbk2.Sheets(1), that can't be done. The expression wBk2.Sheets(1) is not a constant. (The 1 is an argument).

if you want ws2 to always refer to a particular sheet, its easiest to cast is as a function, rather than a global constant.

Public Function wBk1() as Workbook
    Set wBk1 = Workbooks("Macros.xlsm")
End Function

Public Function ws2() as Worksheet
    Set ws2 = wBk1.Sheets(2)
End Function

 

[snoh8r]

Thank you. I will do that. Much appreciated!

 

[RoryA]

I'd also suggest that, unless you have a really good reason for using separate variables for all those ranges, an array would be simpler - i.e. use:

Dim rngAddress(1 to 20) As Range

instead of:

Dim rngAddress1 As Range, rngAddress2 As Range, rngAddress3 As Range, rngAddress4 As Range, rngAddress5 As Range
Dim rngAddress6 As Range, rngAddress7 As Range, rngAddress8 As Range, rngAddress9 As Range, rngAddress10 As Range
Dim rngAddress11 As Range, rngAddress12 As Range, rngAddress13 As Range, rngAddress14 As Range, rngAddress15 As Range
Dim rngAddress16 As Range, rngAddress17 As Range, rngAddress18 As Range, rngAddress19 As Range, rngAddress20 As Range

 

[snoh8r]

I did that, and I did the same to my worksheets. Thanks. I'm still learning and these things help. I'm taking a C++ course and thought about using an array here like I learned to do for C++ but wasn't sure if it would work or how to implement it. This will save me a lot of time going forward.

 

[AppleSource]

Dim Rng As Range
Dim rngCell As Range
Dim LastRow As Long
Dim LastColumn As Long
Dim thisMonth As Long
Dim thisYear As Long
Dim wBk1 As Workbook
Dim wBk2 As Workbook
Dim xRg As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5 As Worksheet


thisMonth = Month(Date)
thisYear = Year(Date)


Set wBk1 = Workbooks("Macros.xlsm")
Set wBk2 = Workbooks("Client " & MonthName(thisMonth) & " " & thisYear & ".xlsx")


Set ws1 = wBk2.Sheets(1)
Set ws2 = wBk1.Sheets(2)
Set ws3 = wBk1.Sheets(3)
Set ws4 = wBk2.Sheets(2)
Set ws5 = wBk2.Sheets(3)


Dim rngAddress1 As Range, rngAddress2 As Range, rngAddress3 As Range, rngAddress4 As Range, rngAddress5 As Range
Dim rngAddress6 As Range, rngAddress7 As Range, rngAddress8 As Range, rngAddress9 As Range, rngAddress10 As Range
Dim rngAddress11 As Range, rngAddress12 As Range, rngAddress13 As Range, rngAddress14 As Range, rngAddress15 As Range
Dim rngAddress16 As Range, rngAddress17 As Range, rngAddress18 As Range, rngAddress19 As Range, rngAddress20 As Range

I have the above written to several modules. I'd like to just simply create a global setting for all these so I don't have to keep writing them over and over (Ok, copy/paste). I keep a notepad sheet open to track them at all times as well.

Problem is, when I tried creating them as global and global const under an Option Explicit, it kept giving me issues with the Set ... 's.

Plus, if I run any macro and put something like set ThisMonth .... it fails and says it requires an object. This tells me that the global variables are not passing.

Any help would be appreciated.

I just came across this problem, heres one option

You can Declare your Globals at the top, then set all of the globals using a function(which you just call the function at the start of each sub, so you dont have to copy the whole lots over and over)

Option Explicit

Global wBk1 As Workbook
Global wBk2 As Workbook
Global ws1 As Worksheet
Global ws2 As Worksheet
Global ws3 As Worksheet
Global ws4 As Worksheet
Global ws5 As Worksheet


Public Function GlobeSetting()
Set wBk1 = Workbooks("Macros.xlsm")
Set wBk2 = Workbooks("Client " & MonthName(thisMonth) & " " & thisYear & ".xlsx")
Set ws1 = wBk2.Sheets(1)
Set ws2 = wBk1.Sheets(2)
Set ws3 = wBk1.Sheets(3)
Set ws4 = wBk2.Sheets(2)
Set ws5 = wBk2.Sheets(3)
End Function

Sub example1()
Call GlobeSetting()

' now all your globals have been set

End Sub

Sub Example2()
Call GlobeSetting()

End Sub