Globally Setting Worksheet Variables

[scottcolbury]

Hi all,

I have a large project with multiple worksheet whose names may change in the future. I'm trying to create worksheet variables in VBA but can't seem to get the dimensioning correct. I'm using 'Option Explicit'.

My code goes something like this...

Some Sub()
    Call SetSheets(ws1, ws2, ws3)
        With ws1
            Logic code here... 
        end with
End Sub

And then in another module...

Public ws1 As Worksheet  
Public ws2 As Worksheet  
Public ws3 As Worksheet  

Function SetSheets(ByRef ws1, ByRef ws2, ByRef ws3) 
   Set ws1 = Sheet5
   Set ws2 = Sheet6
   Set ws3 = Sheet13
End Function

However I keep getting a 'duplicate declaration in current scope' error.
Any ideas?

Also, right now I have to call SetSheets() every time I want to use a ws1, ws2, etc variable in a separate procedure. Is there any way to set the ws1, ws2, etc variables at the beginning of my code so that all subsequent procedures will know what worksheets they refer to?

Much appreciated!
s_c

 

[TFCJamieFay]

Hi,

You could try refering to each sheet by it's number. e.g.

Sheets(3).Select

...would refer to the third sheet in your workbook. This way it doesn't matter what your sheet is called either. However if it is moved from third place to say forth, it would change.

HTH,

Jay