Declare the parameter As Worksheet. E.g,

Sub AAA()
BBB Sheet3
End Sub

Sub BBB(WS As Worksheet)
Debug.Print WS.Name, WS.Range("A1").Text
End Sub

As you'll see by the output generated by BBB, you are passing Sheet3
(code name) that doesn't necessarily have the same tab name.

If you attempt to use a sheet that doesn't exist (e.g., code name
Sheet5), you'll get a compiler error, not a runtime error.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Sun, 13 Dec 2009 13:16:30 -0700, "Robert Crandal"
wrote:

Suppose I define the following subroutine in one of my VBA modules:

Public Sub DoCalculations()
invoiceDate = Sheet1.Range("A1").Value
invoiceTotal = Sheet1.Range("A2).Value
Sheet1.Range("A30).Value = invoiceTotal + 20
End Sub

The above subroutine is limited to using Sheet1, so how
I can I modify this subroutine to accept any sheet codename
as a parameter??? Please keep in mind, I prefer to use
sheet codenames rather than the names on my sheet
tabs, since users can change the names on tabs at any time.

So, I want to modify the subroutine definition as follows:

Public Sub DoCalculations (ByVal SheetCodeName as String)
'
' Revised code here
'
End Sub

The revised routine above will accept a sheet codename in string
format, then I want the same code above to be executed on
an arbitrary/given sheet referenced by codename.

I hope that makes sense...

thank you