0

I have been working on creating a module that has multiple subs and functions that all are applied to the same sheet. In my efforts and research to clean up my code I found that instead of declaring the "Dim" for each sub, I can declare it at the very top of the module by using either "Dim" or "Private". 

Sub Sample()   
Dim DataSheet As Range
'Only declared for this sub, doesn't apply to other subs

'on the other hand,

Private DataSheet As Range
Sub Sample()
'declares it for each sub in this module.

What I can't figure out is, is there a way to set the value or in this case the exact range that I want to assign to "DataSheet" that will apply to the entire module? Currently each of my subs contains,

Set DataSheet = ThisWorkbook.Sheets(1).Range("A3:FU5002")

which, since this range is constant and never changes, seems a little redundant.

proxy156
  • 91
  • 1
  • 8

2 Answers2

1

Create a special sub to perform the initialization and run it first:

Dim DataSheet As Range

Sub RunMeFirst()
    Set DataSheet = ThisWorkbook.Sheets(1).Range("A3:FU5002")
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Interesting idea, am i correct in thinking that by using a sub like this, i can have all me set values in it, then I'd have one line in each sub there after that tells it to run this first sub? If that is the case, how would I declare the "RunMeFirst()" sub in each of the following subs? I haven't had a sub refer to another before. – proxy156 Jun 15 '15 at 16:54
  • NVM, found out how to call another sub in a sub here: [Excel VBA calling sub from another sub with multiple inputs, outputs of different sizes](http://stackoverflow.com/questions/16763112/excel-vba-calling-sub-from-another-sub-with-multiple-inputs-outputs-of-differen) – proxy156 Jun 15 '15 at 17:00
  • @proxy156 Good job with the research! – Gary's Student Jun 15 '15 at 17:01
0

add in a global variable in the ThisWorkbook module and use the workbook open event to set the value.

Public DataSheet As Range
Private Sub Workbook_Open()
    Set DataSheet = ThisWorkbook.Sheets(1).Range("A3:FU5002")
End Sub
Sorceri
  • 7,870
  • 1
  • 29
  • 38
  • When I added the above and ran one of my subs, it errors at `DataSheet.Interior.ColorIndex = 0` stating, that variable or with block not set. Any suggestions? Also, the module I am editing is a stand alone module, not the "ThisWorkbook module, is that going to be an issue when using this Private Sub or will it still work the same? – proxy156 Jun 15 '15 at 16:48