Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How can I declare and set specific worksheets in one module, and then use those settings in another?

    Code:
    Public Sub test()
    
    	Dim wks As Worksheet
    
    If Worksheets("Input Sheet").Range("B1") > 0 Then
    	Set wks = Worksheets("10")
    	Call Process
    End If
    If Worksheets("Input Sheet").Range("B2") > 0 Then
    	Set wks = Worksheets("20")
    	Call Process
    End If
    If Worksheets("Input Sheet").Range("B3") > 0 Then
    	Set wks = Worksheets("25")
    	Call Process
    End If
    If Worksheets("Input Sheet").Range("B4") > 0 Then
    	Set wks = Worksheets("30")
    	Call Process
    End If
    If Worksheets("Input Sheet").Range("B5") > 0 Then
    	Set wks = Worksheets("40")
    	Call Process
    End If
    
    End Sub
    
    Public Sub Process()
    
    	MsgBox "wks = " & wks
    
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Remove the declaration

    Dim wks As Worksheet

    from the macro, and insert

    Public wks As Worksheet

    at the top of the module, before all Subs and Functions.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ah, I knew it was to do with something being public but was'nt sure. Do the sub's need to be public? This is unfarmiliar teritory, but a little more understanding would make a lot of my code much easier to maintain!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    A procedure (sub) or function in a standard module is public by default. So

    Sub Test()

    is equivalent to

    Public Sub Test()

    If you want the procedure or function to be known inside the module only, you have to declare it as private:

    Private Sub Test()

    Variables and constants, however, are private by default, so

    Dim wks As Worksheet

    is equivalent to

    Private wks As Worksheet

    If you want the variable or constant to be known outside its own module, you have to declare it as public:

    Public wks As Worksheet

    Why the opposite behavior? I don't have the slightest idea.

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='783147' date='05-Jul-2009 22:13']Why the opposite behavior? I don't have the slightest idea.[/quote]

    The mysterious world of MS Thanks for the lesson, this could make my life a little easier!

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can I just double check that I have this right.

    Code:
    Public wks As Worksheet
    Public wks1 As String
    
    Public Sub test()
    
    If Worksheets("Input Sheet").Range("B1") > 0 Then
    	Set wks = Worksheets("10")
    	wks1 = "10"
    	Call Process
    End If
    If Worksheets("Input Sheet").Range("B2") > 0 Then
    	Set wks = Worksheets("20")
    	wks1 = "20"
    	Call Process
    End If
    If Worksheets("Input Sheet").Range("B3") > 0 Then
    	Set wks = Worksheets("25")
    	wks1 = "25"
    	Call Process
    End If
    If Worksheets("Input Sheet").Range("B4") > 0 Then
    	Set wks = Worksheets("30")
    	wks1 = "30"
    	Call Process
    End If
    If Worksheets("Input Sheet").Range("B5") > 0 Then
    	Set wks = Worksheets("40")
    	wks1 = "40"
    	Call Process
    End If
    
    End Sub
    
    Public Sub Process()
    
    	MsgBox "wks = " & wks1
    
    End Sub
    For each If statement, I will be calling Process in a separate module. Process will be working with the sheet that is set in the If statement. Will this work?

  7. #7
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    How is Process defined in the other module?

    It would be easiest to declare it is a Public Sub (or Function) that takes parameters, as in

    Public Sub Process(wks as Worksheet, strwks as String)


    You could then call it as
    Call Process(Worksheets("10"),"10")

    or something similar.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    That should work, but you don't really need two public variables for that. Just the string variable wks1 would be sufficient. You can then define wks inside Process:

    Code:
    Sub Process()
      Dim wks As Worksheet
      Set wks = Worksheets(wks1)
      ...
    End Sub
    It is also possible to avoid the use of public variables entirely in this example, by passing the value to the Process procedure:

    Code:
    Sub Test()
      If Worksheets("Input Sheet").Range("B1") > 0 Then
    	Call Process("10")
      End If
      ...
    End Sub
    The Process procedure would take a string as argument:

    Code:
    Sub Process(wks1 As String)
      Dim wks As Worksheet
      Set wks = Worksheets(wks1)
      ...
    End Sub

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Gents, All of your suggestions make perfect sense!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •